phpmysqlmysqlimysqli-multi-query

mysqli_multi_query() repeats the fetched with null


I am trying to fetch all the users who know "Photoshop" and all their details from multiple tables using mysqli_multi_query(). And I am getting the results also, But there are some uninvited guests too along with the correct results which are null. Why are they even here?

Code and Output are given below:

<?php
    $sql = "SELECT user_id FROM ts_users_skills WHERE skillsTeach='Photoshop'";
    if($res = mysqli_query($conn,$sql)) {
        while ($id=mysqli_fetch_array($res)) {
                $id=$id['user_id'];
                // echo($id);
                $get_teachers_details="SELECT * FROM ts_students_log WHERE user_id='$id';";
                $get_teachers_details.="SELECT * FROM ts_students_info WHERE user_id='$id';";
       if (mysqli_multi_query($conn,$get_teachers_details))
        {
            do
                {
    // Store first result set
                if ($mul_res=mysqli_store_result($conn)) {
                  // Fetch one and one row
                  while ($row=mysqli_fetch_assoc($mul_res))
                    {
                    // printf("%s\n",$row['mobile']);
                         // print_r($row);
                        echo "Id: " . $row['user_id']. " Mobile: " . $row['mobile']. "Email:-" . $row['email'] . "Name:-" .$row['name'] . "<br/>";      
                    }           
                  //            Free result set         
                  mysqli_free_result($mul_res);         
                  }
                }
    while (mysqli_next_result($conn));
}
}
}
?>

Output:

     Id: 3 Mobile: 7895447755Email:-aditya@chutiya.comName:-
     Id: 3 Mobile: Email:-Name:-Aditya Porwal
     Id: 4 Mobile: 9300010740Email:-rishabhgaandu@gmail.comName:-
     Id: 4 Mobile: Email:-Name:-Rishabh Agrawal

Solution

  • Combine your queries to obtain a single result set that you can sort anyway you like:

    SELECT 
        a.`user_id`,
        b.*,
        c.*
    FROM ts_users_skills a
    LEFT JOIN ts_students_log b
        ON a.`user_id` = b.`user_id`
    LEFT JOIN ts_students_info c
        ON a.`user_id` = c.`user_id`
    WHERE a.`skillsTeach` = 'Photoshop'
    ORDER BY a.`user_id`;
    

    I used LEFT JOINs, but if all users will have rows in both ts_students_info and ts_students_info, you can change them to just JOINs.

    EDIT

    To answer your question in the comments below, to select multiple skills, change the where clause to something like:

    WHERE  a.`skillsTeach` in ('Photoshop','Video Editing','Web Design')
    

    or

    WHERE  a.`skillsTeach` = 'Photoshop'
        OR a.`skillsTeach` = 'Video Editing'
        OR a.`skillsTeach` = 'Web Design'