How to execute two mysql queries as one in PHP MYSQL

Question

I have two queries  as following   SELECT SQL CALC FOUND ROWS Id  Name FROM my table WHERE Name LIKE   prashant   LIMIT 0  10  SELECT FOUND ROWS      I want to execute both these queries in a single attempt     result   mysql query  query     But then tell me how I will handle each tables set separately  Actually in ASP NET we uses dataset which handles two queries as  ds Tables 0   ds Tables 1      etc   How can I do the same using PHP MYSQL

User · Answer

Yes it is possible without using MySQLi extension    Simply use CLIENT MULTI STATEMENTS in mysql connect s 5th argument   Refer to the comments below Husni s post for more information

User · Answer

You ll have to use the MySQLi extension if you don t want to execute a query twice   if  mysqli multi query  link   query          result1   mysqli store result  link        result2   null       if  mysqli more results  link                 mysqli next result  link            result2   mysqli store result  link                 do something with both result sets       if   result1          mysqli free result  result1        if   result2          mysqli free result  result2

User · Answer

Using SQL CALC FOUND ROWS you can t      The row count available through FOUND ROWS   is transient and not intended to be available past the statement following the SELECT SQL CALC FOUND ROWS statement    As someone noted in your earlier question  using SQL CALC FOUND ROWS is frequently slower than just getting a count   Perhaps you d be best off doing this as as subquery   SELECT    select count    from my table WHERE Name LIKE   prashant     as total rows  Id  Name FROM my table WHERE Name LIKE   prashant   LIMIT 0  10

User · Answer

You have to use MySQLi  below code works well   lt  php  mysqli   new mysqli  localhost    my user    my password    world        check connection    if  mysqli connect errno          printf  Connect failed   s n   mysqli connect error         exit        query     SELECT CURRENT USER       query     SELECT Name FROM City ORDER BY ID LIMIT 20  5       execute multi query    if   mysqli- gt multi query  query         do              store first result set            if   result    mysqli- gt store result                  while   row    result- gt fetch row                      printf   s n    row 0                               result- gt free                         print divider            if   mysqli- gt more results                  printf  ----------------- n                    while   mysqli- gt next result           close connection     mysqli- gt close      gt

User · Answer

Like this    result1   mysql query  query1    result2   mysql query  query2       do something with the 2 result sets     if   result1      mysql free result  result1    if   result2      mysql free result  result2

User · Answer

Update  Apparently possible by passing a flag to mysql connect    See Executing multiple SQL queries in one statement with PHP Nevertheless  any current reader should avoid using the mysql -class of functions and prefer PDO   You can t do that using the regular mysql-api in PHP  Just execute two queries  The second one will be so fast that it won t matter  This is a typical example of micro optimization  Don t worry about it   For the record  it can be done using mysqli and the mysqli multi query-function

User · Answer

As others have answered  the mysqli API can execute multi-queries with the msyqli multi query   function   For what it s worth  PDO supports multi-query by default  and you can iterate over the multiple result sets of your multiple queries    stmt    dbh- gt prepare       select sql calc found rows   from foo limit 1        select found rows       stmt- gt execute    do     while   row    stmt- gt fetch          print r  row         while   stmt- gt nextRowset       However  multi-query is pretty widely considered a bad idea for security reasons   If you aren t careful about how you construct your query strings  you can actually get the exact type of SQL injection vulnerability shown in the classic  Little Bobby Tables  XKCD cartoon   When using an API that restrict you to single-query  that can t happen

User · Answer

It says on the PHP site that multiple queries are NOT permitted  EDIT  This is only true for the mysql extension  mysqli and PDO allow multiple queries     So you can t do it in PHP  BUT  why can t you just execute that query in another mysql query call   like Jon s example    It should still give you the correct result if you use the same connection   Also  mysql num rows may help also

[php] How to execute two mysql queries as one in PHP/MYSQL?

Examples related to php

Examples related to mysql

Examples related to loops