Problems displaying data in nested loop

CHL3

Hi,

I'm trying to display data in an inner loop based on the current record being processed in the out loop. The goal is to display records from one table in one column and the corresponding records in the next column.

The outer loop works and displays tiles based on the title_type that is passed as a HTTP variable. But, when I introduced the inner loop (which is supposed to display records associated with the title, it shortcuits the outer loop.

Here's the code snippet:

$query = "SELECT title, title_id 
           FROM titles  

           WHERE title_type =  $type_id 
		  order by title 
";

$query2 = "SELECT I.issue_no , I.issue_id 
           FROM titles T, issues I  

           where T.title_id = I.title_id 
             AND T.title_id = '$title_id'  

           order by I.issue_no";		  

$result = mysql_query($query) or die('Could not find title'); 

// Display title and corresponding issues 
echo "<table width = 100% cellpadding=5 cellspacing=2>";

while($row = mysql_fetch_array($result, MYSQL_ASSOC))    {
    $title_id= $row['title_id'];
	$title= $row['title'];
	echo "<tr>\n";
	echo "<td width=10%><a href='index.php?content=series&id=$title_id'>$title</a></td>\n";

//display all issues for title
echo "$title_id\n";
echo "$query2\n";
$result2 = mysql_query($query2) or die('Could not find issue');
echo "<td width=90%>\n";
while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))    {
    $issue_id= $row2['issue_id'];
	$issue_no= $row2['issue_no'];
	echo "<a href='index.php?content=issue&id=$issue_id'>$issue_no</a>\n";
	echo " \n";
}  
echo "</td>";
echo "</tr>"; 
}
echo "</table>";

I've tested this by harding the title_id in query2 to a value. The outer loop retrieves data and the inner loop displays records. So, I thought the problem is that $title_id cannot be usd in the inner and outer loop... which seems strange to me from other languages (but I'm still learning PHP).

So, I even tried changing $title_id to $title2_id in $query2 and dumping $title_id into another variable $title2_id... but that did work. (this is not pictured in th snippet.)

Finally, I tried placing $title_id in single quotes. When I did this, the out loop works, but the inner loop failed. When I echoED the contents of $query2, $title_id is null. This odd since I didn't have to do this for $query.

I have a feeling that I'm so close... yet so far away. Does anyone know what I am over looking?

Thanks in advance.

-CHL3

bpat1434

This seems to be the perfect spot for you to learn about a great function: [man]sprintf/man.

Since you dynamically want to replace $title_id in the second query, you have to do it after $title_id has been set. What the sprintf function does is take "place holders" and does a simple replacement (with a little more functionality than just replacing) with what you send to it. So your $query2 item would be:

$query2 = "SELECT SELECT I.issue_no , I.issue_id
           FROM titles T, issues I  

           where T.title_id = I.title_id
             AND T.title_id = '%s'  

           order by I.issue_no";

Then, later during your loop, you'd just have to call [man]sprintf/man to read that string, and replace that "%s" with whatever $title_id currently is:

while($row = mysql_fetch_array($result, MYSQL_ASSOC))    {
    $title_id= $row['title_id'];
    $title= $row['title'];
    echo "<tr>\n";
    echo "<td width=10%><a href='index.php?content=series&id=$title_id'>$title</a></td>\n";

//display all issues for title
echo "$title_id\n";
echo "$query2\n";
$tmp_query = sprintf($query2, $title_id);
$result2 = mysql_query($tmp_query) or die('Could not find issue');

That should just about do it. If not, it should get you closer....

CHL3

Thanks bpat1434!

After a night's sleep, I went back to the page and realized that query2 values would be bound when the string is created it. So, I just moved it inside the inner while loop... and it worked.

However, I was unaware of the sprintf function. I'll definitely read up on it and try out your solution as well.

Thanks again!

-CHL3

bpat1434

Great!! Don't forget to mark this thread as resolved if it is.