nested database queries within a loop

Anon

Hey there,

I need to write up a database query wherein the data extracted from the table in one query is used to build, then execute another query. I made an attempt to nest the second query in a loop within the loop of the initial query, but it failed. Nesting loops probably also isn't the most efficient way to do it. Below is an example of the code I used so you can see what I mean.

$db_query_str = "SELECT sid, cid FROM $db_tbl_sced WHERE uid = $db_data_pnt_user[uid]";
$db_query_pnt_sced = db_query($db_query_str, $db_conn_pnt);
while ($db_data_pnt_sced = mysql_fetch_array($db_query_pnt_sced)) {

$db_query_str = "SELECT hid, title, body FROM $db_tbl_hmwrk WHERE sid = $db_data_pnt_sced[sid]";
$db_query_pnt_hmwrk = db_query($db_query_str, $db_conn_pnt);
while($db_data_pnt_hmwrk = mysql_fetch_array($db_query_pnt_hmwrk)) {
	echo $db_data_pnt_hmwrk[title];
}

}

Sort of get the idea? I searched the forum and found some relevant posts. Something about a "join" caught my eye, but I'm a bit confused. If someone could please explain to me how I might go about executing a query like the one above I'd greatly appreciate it.

Thanks in advance

--ben

maxpup979

yeah....a join will do it...although I dont use mySQL, so I dont know if it supports joins. Anyway...if I understand your queries, you are getting 2 id's from one table based on a 3rd id. So you get one record set, and loop across it to get a second record set...heres what ya do.

Select t1.sid,t1.pid,t2.hid,t2.title, t2.body from $db_tbl_sced t1,
$db_tbl_hmwrk t2 WHERE
t2.sid=t1.sid AND t1.uid=the variable you passed in.

I do not know if this will work in mySQL, but I know that it will in PostgreSQL

Hope this helps
Darren