user1410050 user1410050 - 7 months ago 16
Javascript Question

Executing a Query within another Query

I am trying to execute an Sql query that relies on the output of another sql query for each loop.

I have 3 tables books, students and borrowed.

Books table has the full details of all books (b_id is primary key), student has full details of all students (s_id as primary key) and borrowed is a table which has the s_id and b_id as foreign keys and each entry is created when a student borrows a book.

The relationship is one to many i.e Student can borrow one or many books or none but a book cannot be with more than one student. As such my b_id is unique in the table index.

My plan is to create a Html table that lists all students that have borrowed books and on clicking a student in this table a drop down table shows all the books borrowed by that student.

I just can't seem to get the SQL query to run as I need the s_id from the first Sql query to get the next one to work. plus my drop down table isn’t functioning properly either.

Please help see code below



Click on a student to see books borrowed:
<table class="table2">
<thead>
<tr>
<th></th>
<th>Name</th>
<th>Mat number/th>
<th>Dept</th>
<th>level</th>
</tr>
</thead>
<tbody>
<?php
$sql_query = "SELECT student.* FROM student, borrowed where student.s_id = borrowed.s_id Group by student.s_id;";

$result = $db->query($sql_query);

if(mysqli_num_rows($result)>0){
$counter = 0;

while ($row = $result->fetch_array())
{
$counter++;
?>

<tr>
<td class="btn"><?php echo $counter;?></td>
<td><?php echo $row['name'];?></td>
<td><?php echo $row['matNo'];?></td>
<td><?php echo $row['Dept'];?></td>
<td><?php echo $row['level'];?></td>
</tr>
<tr>
<th class="expand1"></th>
<th class="expand1">Title</th>
<th class="expand1">Author</th>
<th class="expand1">Genre</th>
<th class="expand1">ISBN</th>
</tr>
<tr><?php
foreach($row as $studentid){

$sql = "SELECT books.*
FROM books
inner Join borrowed on books.b_id = borrowed.b_id
WHERE borrowed.s_id = $studentid";

$result2= $dbs->query($sql);
while( $row2 = $result2->fetch_array()){
?>
<td class="expand1"><?php echo $row2['title'];?></td>
<td class="expand1"><?php echo $row2['author'];?></td>
<td class="expand1"><?php echo $row2['genre'];?></td>
<td class="expand1"><?php echo $row2['isbn'];?></td>
</tr>
<?php
}
}
}
}
$result->close();
$dbs->close();
?>
</table>


This is my Script to create a toggle/collapsible table:

<script>
$(document).ready(function(){
$(".btn").click(function(){
$(".expand1").toggle();
});
$(".btn2").click(function(){
$(".expand2").toggle();
});
})

</script>

Answer

The second query does not need to be in a loop and you can use the first queries s_id in the where clause of the second query

           $sql = "SELECT books.* 
                   FROM books 
                      inner Join borrowed on books.b_id = borrowed.b_id  
                   WHERE borrowed.s_id = {$row['s_id']}";

               $result2= $dbs->query($sql);
               while( $row2 = $result2->fetch_array()){
    ?>

You will also have to remove one of the following closing }