CODEWITHSUNDEEP
phpmysql
user3056538
user3056538

Reputation: 73

How to Select from the result of another select mysql and php

I tried to solve this problem but its not working

I have two tables "lessons" and "lessons_pass"

I would like to check for each lesson from lessons if the current user passes it or not

so I need to select from the table of lessons for each result to check the from the "lessons_pass" the "status" for each result

CREATE TABLE `lessons` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `cat_id` int(10) default NULL,
  PRIMARY KEY  (`id`)
) 



CREATE TABLE `lessons_pass` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `lesson_id` int(11) default NULL,
  `status` int(11) NOT NULL default '2',
  PRIMARY KEY  (`id`)
)

my current code didn't work for me

<?
$user_id = 4;
$sql = "SELECT id, title, cat_id FROM lessons";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    $lesson_id  = $row["id"];
  
    // my try for the next table
  
    $sql_two = "SELECT * FROM lessons_pass where user_id='$user_id' and lesson_id='$lesson_id' ";
    $result_two = $conn->query($sql_two);
    if ($result_two->num_rows > 0) {
      while($row_two = $result_two->fetch_assoc()) {
        echo "id: " .$lesson_id . " - Status For user : " . $result_two["status"]. "<br>";
      }
    } else {
      echo "0 results";
    }
  }
} else {
  echo "0 results";
}


?>

Upvotes: 0

Views: 49

Answers (2)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

According to @GMB query your PHP code may be next:

<?php
$user_id = 4;
$conn = $mysqli;
$sql = "SELECT 
    `l`.`id` `lesson_id`,
    `l`.`title` `lesson_title`,
    `u`.`firstname`,
    `u`.`lastname`,
    IFNULL(`lp`.`status`, 'not pass') `status`
FROM `lessons` `l`
LEFT JOIN `lessons_pass` `lp` 
    ON `lp`.`lesson_id` = `l`.`id` AND `lp`.`user_id` = " . (int)$user_id 
." LEFT JOIN `users` `u` ON `lp`.`user_id` = `u`.`id`";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Lesson ID: " . $row["lesson_id"]
            . " Lesson title: {$row['lesson_title']}"
            . " Name: " . $row["firstname"]. " " . $row["lastname"]
            . " Status: {$row['status']}"
            . "\n";
    }
} else {
  echo "0 results";
}

You can test it on PHPize.online

Upvotes: 0

GMB
GMB

Reputation: 222462

You seem to be looking for a join:

select l.*, lp.status
from lessons l
left join lessons_pass lp 
    on  lp.lesson_id = ll.id
    and lp.user_id = ?

You did not tell which result you want for lessons that the given user did not attend: this returns a row with a null status. If you don't want that, then use inner join instead of left join.

Upvotes: 1

Related Questions