MySQL Multi Query store result second query

Question

I am querying from two different tables in the database here and I can store the values from the first query but how do I store the information from the second query?

$query  = "SELECT * ";
$query .= "FROM user_account ";
$query .= "WHERE user_id = $user_id ";
$query .= "SELECT * ";
$query .= "FROM user_profile ";
$query .= "WHERE user_id = $user_id ";


if (mysqli_multi_query($mysqli, $query)) {
    do {
        if ($result = mysqli_store_result($mysqli)) {
            while ($row = mysqli_fetch_row($result)) {
                $Firstname = $row['Firstname'];
                $Lastname = $row['Lastname'];
                $Email = $row['Email'];
                $Birthday = $row['Birthday'];
                $Address = $row['Address'];
                $Zip = $row['Zip'];
                $City = $row['City'];
                $State = $row['State'];
                $Country = $row['Country'];
                $Avatar = $row['Avatar']; Will be added later
                $Phone = $row['Phone'];
                $Website = $row['Website'];
                $Member_level = $row['Member_level'];
            }
            mysqli_free_result($result);
        }
        if (mysqli_more_results($mysqli)) {
        }
        while (mysqli_next_result($mysqli)) ;
    }
}

score 1 · Accepted Answer · answered Feb 01 '17 at 08:23

Just the usual way

$query = "SELECT * FROM user_account WHERE user_id = $user_id ";
$account = $mysqli->query($query)->fetch_assoc();

$query = "SELECT * FROM user_profile WHERE user_id = $user_id ";
$profile = $mysqli->query($query)->fetch_assoc();

as simple as that.

I am really wondering why PHP users are inclined to writing SO MUCH code and to using so much intricate ways for the most trifle operations

On a side note, in your particular case you can write a single JOIN query.

Yeah, I thought about doing this from the beginning but I thought there was a more effective way of doing it by using the "multi" query. — Freddos, Feb 01 '17 at 21:59

score 0 · Answer 2 · answered Feb 01 '17 at 08:19

In your code, why are you using mysqli_free_result function because it will frees the memory associated with a result object so when while loop run it will not show any result.

$query  = "SELECT * FROM user_account WHERE user_id = $user_id ";
$query .= "SELECT * FROM user_profile WHERE user_id = $user_id ";

if (mysqli_multi_query($mysqli, $query)) {
     do {
      if ($result = mysqli_store_result($mysqli)) {
           while ($row = mysqli_fetch_assoc($result)) {
               var_dump($row); //for checking result 
              // Now use array to show your result
          }

     } }while (mysqli_next_result($mysqli)) ;

You should always free your result with mysqli_free_result(), when your result object is not needed anymore.

MySQL Multi Query store result second query

2 Answers2