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
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'