mysqli_multi_query array issue

Question

There's something weird going on. I have I database which could look something like this:

table name: dm
|id|receiver|sender|msg          |
|1 |John    |Emma  |Hey John!    |
|2 |Emma    |John  |Hey!         |
|3 |John    |Emma  |Whats up     |
|4 |Emma    |John  |Not too much |
|5 |John    |Keira |Have you got...|

I have an html page with a on it and for now what I want is for the messages that either

1. Emma sent to John => receiver = "John", sender = "Emma" or
2. John sent to Emma => receiver = "Emma", sender = "John"

to be displayed in the console. I can do that with JQuery using the console.log() function.

My PHP looks like this:

$user1 = "John";
$user2 = "Emma";
$sql = "SELECT * FROM dm WHERE receiver = '$user1' AND sender = '$user2';SELECT * FROM dm WHERE receiver = '$user2' AND sender = '$user1'";

// Execute multi query
if (mysqli_multi_query($link,$sql))
{
  do
    {
      $i = 0;
      $msg = array();

    if ($result=mysqli_store_result($link)) {

      while ($row=mysqli_fetch_row($result))
        {
        printf("%s\n",$row[0]);
        $msg[$i] = $row[0];
          $i++;
        }
      mysqli_free_result($result);
      }
    }
  while (mysqli_next_result($link));
}

echo json_encode($msg);
exit();
mysqli_close($link);

You will notice, that I have "printf" in there, because I wanted to see what the databse would give back to me. It correctly gave back the id's: 1,3,2,4.

However, I normally would like to do the request using JQuery ajax. I know how to do that also. Because of that I want to have an array which holds all the id's of the posts like this:

["1","2","3","4"]

Unfortunately the way I do it as written in the code what it returns to me is this:

["2","4"]

So what's going on is that he only puts what was in the first query into the array.

So what exactly is my question?

I want to fix the array to hold all the id's of the respective messages which would be 1,2,3,4. Preferably in this order as well and not like this 1,3,2,4.

I hope I made the problem clear and thank you for any advice!

Answer 1

You reset $i and $msg inside the outer loop. That means you will only see results from the second query in your array. Move the $i = 0; and $msg = array(); lines outside that loop.

That said, there’s no reason to do 2 queries here. Just use an OR in your WHERE clause to get both results at once. This will be faster and easier.

Also, this is very important: you are wide open to SQL injection. You need to use prepared statements, rather than concatenating variables into your query. See How can I prevent SQL injection in PHP?.