phpmysqlmysqli-multi-query

How to mysqli_fetch_row while using mysqli_multi_query


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?


Solution

  • 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);