#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    25
    Rep Power
    0

    Php return two table from mysql


    Hi
    This code is simple select with limit.
    I used SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() as row; to find row count without limit.
    This code return two table, so sql_execute_return_table not work here.
    How can i return two seprates table in one query?
    Thanks.
    PHP Code:
    $query="SELECT  SQL_CALC_FOUND_ROWS     ws.idWord,ws.word
     FROM webtranslator_searchedwords ws 
     limit 0,10 ; SELECT FOUND_ROWS() as row; "
    ;
    $dtResult mysql::sql_execute_return_table($query); 
    ------------------------
    PHP Code:
        public static function sql_execute_return_table($sql_query){
        
    $mysqli = new mysqli(Enums::HostNameEnums::UserName,Enums::PassEnums::DBNameMemorybox);
        
        
    $mysqli->query("SET NAMES 'utf8';");
        
    $ret_val = array();
        
    $result $mysqli->query($sql_query);
        
        if(
    $result){
             
    // Cycle through results
            
    while ($row $result->fetch_array(MYSQL_BOTH)){
                
    $ret_val[] = $row;
            }
            
    // Free result set
            
    $result->close();
            if(
    $mysqli->more_results()) $mysqli->next_result();
        }

        return (
    $ret_val);
        } 
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    You can't, PHP explicitly forbids this. You have to do two queries as two separate calls, not combine them like this.

    I could have sworn there was a built-in function for found_rows but I can't find it.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    25
    Rep Power
    0
    Originally Posted by ManiacDan
    You can't, PHP explicitly forbids this. You have to do two queries as two separate calls, not combine them like this.
    data of FOUND_ROWS() will clean with close mysql_conection, so i cant use in two seperate request.
    if there is no solution, so i should use stored procedure.
  6. #4
  7. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    For starters I think you're wanting to use "multi_query".

    It looks like you're going in the right direction using "next_result" to shift to the second query being run, but it doesn't look like you're doing anything with that next result.

    I'm curious, though: what's your use case for doing this? The only time I've needed the total number of rows in addition to the my limited list is for pagination controls... but then I've always needed the full count before my limit query in case I need to adjust the limit query to be within an acceptable start/limit range.
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    data of FOUND_ROWS() will clean with close mysql_conection, so i cant use in two seperate request.
    Then don't close the connection
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by dmittner
    For starters I think you're wanting to use "multi_query".
    Be very, very careful with multi-queries, though. Don't even think about using anything but constant queries. If there's any vulnerability, attackers can actually write their own queries and have your database execute them.

    Comments on this post

    • dmittner agrees : I can't imagine ever using them, myself.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    25
    Rep Power
    0
    Originally Posted by Jacques1
    Be very, very careful with multi-queries, though. Don't even think about using anything but constant queries. If there's any vulnerability, attackers can actually write their own queries and have your database execute them.
    that's good advice
    thank you

IMN logo majestic logo threadwatch logo seochat tools logo