How do I use the results of one query in antother query

[comboy]

Hi all,
I'm just starting to look and php & mysql so bear with me please.

I was wondering how I can use the results of one query to generate results for another query.

I have two tables trainer and schedule the trainer primary key (id) is a forigen key of schedule.trainer row.

What I'm trying to do is have a table in which one row is the trainer id from the trainer table(done this) and the rows below select information from the schedule table based on the trainer id in the row above. To do this I think I'll need another query in which the trainer id is taken from the first query to help build the second query but don't know how.

exampe

trainerid | trainerid|
Date | Date |
Comment | Comment |

Thanks in advance


Graham

 

[bezierstek]

Presumably you are retrieving the trainer information in an array, in which case it should be quite simple.

Basically display the info you need from the trainer table then use the trainer id to select from the schedule table, displaying the info that is held there.

So, roughly this:

$trainersql=mysql_query("select * from trainer");
while ($trainerresult=mysql_fetch_array($trainersql)){
  //code to display trainer info in a table, something like 
?>
<tr>
  <td><? echo $trainerresult['first_bit_of_data']</td>
  <td><? echo $trainerresult['second_bit_of_data']</td>
</tr>
<?
$schedulesql=mysql_query("select * from schedule where trainerid='$trainerresult['id']");
while ($scheduleresult=mysql_fetch_array($schedulesql){
?> <tr>
      <td> <? echo $schedulerresult['first_bit_of_data'] ?></td>
</tr>
}
}

That should sort of do it, but you may need to play around a bit.

 

[comboy]

Hi bezierstek ,

I'm trying things a bit differently as following the book and it uses OO methods.

Basicly I'm getting zero records returned when four records should be returned from the query where I try to use the results of the first query to build the second query.

Here's My code

<?php

@ $db = new mysqli('localhost', 'root', 'password', 'test');

/* check connection */
if (mysqli_connect_errno()) {
    
    echo 'Sorry cant connect to the database';
    exit();
}
else

 echo 'connected';
/* first query */
$query="SELECT id FROM trainer";

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


/* second query */
$query2="SELECT s.Sdate, s.Comments from Scheule s, t ($result) where s.trainer = '$result' ORDER BY Sdate";

$result2=$db->query($query2);

/*Show first results*/
echo '<p>Number of records:';
echo $result->num_rows;
echo '</p>';

/* Show second results */
echo '<p>Number of records:';
echo $result2->num_rows;
echo '</p>';

if ($result->num_rows)

 {

	echo '<table border="2">';
	echo '<tr>';
	while ($row = $result->fetch_assoc())
	{
	echo '<td>';
	echo $row['id'];
	echo '</td>';
	}
	echo '</tr>';

	/* waiting to figure out rest of call */

	echo '</table>';

}

$result->free();

$db->close();

?>

I'm sure its a typo but can't figure out how to do it.

Thanks


Graham

 

[jet042]

A good starting point for debugging in general is to echo out the results of any query before you attempt to use it, just to make sure you are actually getting what you expect. After you call the query method, output $result with either echo or the print_r() function.

I bet that the method is returning either an array or a resource. If it is the first, you just need to iterate through the array with a foreach block and submit a new query each time thorough. If it is the second, you will need to use other functions to get your data out of the resource and into an array before you can work with it (mysqli_fetch_arry() or myslqi_fetch_assoc() most likely).

 

[jpadie]

I agree with jet042.

after a very brief look, as a start, change

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


/* second query */
$query2="SELECT s.Sdate, s.Comments from Scheule s, t ($result) where s.trainer = '$result' ORDER BY Sdate";

to

$result=$db->query($query);
$_row = $result->fetch_assoc();
$s_trainer = mysql_real_escape_string($_row['id']);

$query2="SELECT s.Sdate, s.Comments from Schedule s, t where s.trainer = '$s_trainer' ORDER BY Sdate";

 

[comboy]

Hi all thanks for the replies.

jpadie I tried your suggestion and got the following error on echoing the number of rows

Number of records:
Notice: Trying to get property of non-object in C:\Program Files\Apache Group\Apache2\htdocs\testtrainer.php on line 28


Jet042 I'll have a look at the functions that you mentioned and see if I can get it to work.


Thanks again,


Graham

 

[jpadie]

why don't you retrieve the data in a single query?

	SELECT 
		t.id,
		s.Sdate, 
		s.Comments 
	FROM
		trainer as t
		JOIN
		schedule as s
		ON
		(t.id = s.trainer) 
	ORDER BY s.Sdate

 

[comboy]

Hi jpadie,

I was thinking of using just one query but I've got to emulate an excel workbook that displays the data in the following way

|Trainer|Trainer|
| Date | Date |
|comment|comment|

Hence why I was using one query to select the trainer id to build one row of the table and then wanted to use the other query to build the rows containing the related data under the rows containing the trainer id.

Thanks Graham