How to mysqli_fetch_row while using mysqli_multi_query

Question

I'm trying to get the latest info about some specific person, and I'm using a query like

SELECT * FROM Table WHERE Name LIKE 'Peter' ORDER BY ID DESC LIMIT 1

and

SELECT * FROM Table WHERE Name LIKE 'Mary' ORDER BY ID DESC LIMIT 1

because in the Table each day will insert new data for different person at the instant of updating it (for record reference), so I would like to print out a few persons latest info by "ORDER BY ID DESC LIMIT 1"

I have tried to print it out with "mysqli_multi_query" and "mysqli_fetch_row"

like

    $con=mysqli_connect($localhost,$username,$password,'Table');

    $sql = "SELECT * FROM Table WHERE Name LIKE 'Peter' ORDER BY ID 
    DESC LIMIT 1 ";
    $sql .= "SELECT * FROM Table WHERE Name LIKE 'Mary' ORDER BY ID 
    DESC LIMIT 1";

    // Execute multi query
if (mysqli_multi_query($con,$sql))
{
  do
    {
    // Store first result set
    if ($result=mysqli_store_result($con)) {
      // Fetch one and one row
      while ($row=mysqli_fetch_row($result))
        {
            echo '<tr>'; // printing table row
            echo '<td>'.$row[0].'</td>';
            echo '<td>'.$row[1].'</td>';
            echo '<td>'.$row[2].'</td>';
            echo '<td>'.$row[3].'</td>';
            echo '<td>'.$row[4].'</td>';
            echo '<td>'.$row[5].'</td>';
            echo '<td>'.$row[6].'</td>';
            echo '<td>'.$row[7].'</td>';
            echo '<td>'.$row[8].'</td>';
            echo '<td>'.$row[9].'</td>';
            echo '<td>'.$row[10].'</td>';
            echo '<td>'.$row[11].'</td>';
            echo '<td>'.$row[12].'</td>';
            echo '<td>'.$row[13].'</td>';
            echo '<td>'.$row[14].'</td>';
            echo'</tr>'; // closing table row
        }
      // Free result set
      mysqli_free_result($result);
      }
    }
  while (mysqli_next_result($con));
}

mysqli_close($con);

?>

In the result page , it doesn't show any error message , but no results are printed. The individual queries were tested.

Please advise, much thanks

Is there another way to keep the query simple, so there is no need to use mysqli_multi_query?

Typo? There is no semicolon between your queries. Search for any of my mysqli_multi_query answers -- I normally build in error checking with these posts. — mickmackusa, Dec 19 '17 at 04:30
See what happens when you implement this kind of snippet: https://stackoverflow.com/a/22469722/2943403 — mickmackusa, Dec 19 '17 at 04:38
Single query like this ? https://stackoverflow.com/questions/4407030/get-the-row-with-the-highest-value-in-mysql — mickmackusa, Dec 19 '17 at 05:42

score 1 · Answer 1 · answered Dec 23 '17 at 11:01

1

Best practice indicates that you should always endeavor to make the fewest number of calls to the database for any task.

For this reason, a JOIN query is appropriate.

SELECT A.* FROM test A INNER JOIN (SELECT name, MAX(id) AS id FROM test GROUP BY name) B ON A.name=B.name AND A.id=B.id WHERE A.name IN ('Peter','Mary')

This will return the desired rows in one query in a single resultset which can then be iterated and displayed.

Here is an sqlfiddle demo: http://sqlfiddle.com/#!9/2ff063/3

P.s. Don't use LIKE when you are searching for non-variable values. I mean, only use it when _ or % are logically required.

answered Dec 23 '17 at 11:01

mickmackusa

43,625
12
83
136

If you have questions or troubles implementing this please ask, but don't compromise on quality. I'd format my answer better, but I am posting from my phone. – mickmackusa Dec 23 '17 at 11:02
@NDSAC your question appears to be abandoned. Does my answer satisfy your question? Do you need any clarification from me? Do you know how to accept an answer? – mickmackusa Jan 01 '18 at 06:39
thanks mickmackusa , sorry i havent reply you since Ivan86 gave me a solution and it work , then i havent notice your answer. Sorry im new to coding and here , so i may not know the culture here. – NDSAC Jan 17 '18 at 01:14

Ivan86 · Accepted Answer · 2018-01-16T13:53:13.207

0

This should work for you:

$con = mysqli_connect($localhost,$username,$password,'Table');


// Make a simple function
function personInfo($con, $sql)
{
    $result = mysqli_query($con, $sql);
    if(mysqli_num_rows($result) > 0)
    {
        while($row = mysqli_fetch_array($result))
        {
            echo '<table>
                  <tr>';
            for($i=0; $i < count($row); $i++) echo '<td>'.$row[$i].'</td>';
            echo '</tr>
                  </table>';
        }
    }
}


$sql1 = "SELECT * FROM Table WHERE Name='Peter' ORDER BY ID DESC LIMIT 1 ";
$sql2 = "SELECT * FROM Table WHERE Name='Mary' ORDER BY ID DESC LIMIT 1";


// Simply call the function
personInfo($con, $sql1);
personInfo($con, $sql2);

edited Jan 16 '18 at 13:53

answered Dec 19 '17 at 03:52

Ivan86

5,695
2
14
30

Thanks Ivan , but i can only print one person data out , how can i to print another person or even few more persons info out ? – NDSAC Dec 19 '17 at 04:17
sorry i missed your last message , i have just marked your answer – NDSAC Jan 16 '18 at 13:04
Thanks, glad to have been able to help. I encourage you to take a look at @mickmackusa answer as he has a good point there on using `INNER JOIN`. Instead of the `$sql1` and `$sql2` from above you can just make one `$sql`. Everything from above will remain intact and will work, you would just replace two function calls with one. For this I will upvote his answer as it will give a performance upgrgade which could be felt once your tables get a little bigger. Peace – Ivan86 Jan 16 '18 at 15:05

How to mysqli_fetch_row while using mysqli_multi_query

2 Answers2