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
– mickmackusa ♦, Commented Dec 19, 2017 at 4:30
See what happens when you implement this kind of snippet: stackoverflow.com/a/22469722/2943403 — mickmackusa
– mickmackusa ♦, Commented Dec 19, 2017 at 4:38
Single query like this ? stackoverflow.com/questions/4407030/… — mickmackusa
– mickmackusa ♦, Commented Dec 19, 2017 at 5:42

mickmackusa · Accepted Answer · 2017-12-23 11:01:23Z

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, 2017 at 11:01

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mickmackusa Over a year ago

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 Over a year ago

@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?

NDSAC Over a year ago

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.

Ivan86 · Accepted Answer · 2018-01-16 13:53:13Z

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, 2018 at 13:53

answered Dec 19, 2017 at 3:52

Ivan86

5,7182 gold badges16 silver badges30 bronze badges

3 Comments

NDSAC Over a year ago

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 Over a year ago

sorry i missed your last message , i have just marked your answer

Ivan86 Over a year ago

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

Collectives™ on Stack Overflow

How to mysqli_fetch_row while using mysqli_multi_query

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related