Display data from db is slow

[hlstriker]

Hi, I am trying to pull data from my database from 2 different tables and it seems to slow the page down a lot the way I did it. I was hoping someone can show me a better way to do the same thing but with faster page generation time.

 

<?php
$sql = 'SELECT dl_id, dl_name FROM downloads';
$result = mysql_query($sql);

if(!$result)
{
	die('Could not get data ' . mysql_error());
}

echo '<table>';

while($row = mysql_fetch_assoc($result))
{
	// Seems to slow page down a LOT when running this query in the loop
	// Is there a better way?
	// Also, how do I display info from this query below since it's only one line?
	$sqlImg = 'SELECT pic_url, pic_thumburl FROM images WHERE dl_id='.$row['dl_id'].'';
	$resultImg = mysql_query($sqlImg);

	if(!$resultImg)
	{
		die('Could not get thumbnail ' . mysql_error());
	}

	echo '<tr>
			<td><a href="page.php?id='.$row['dl_id'].'">'.$row['dl_name'].'</a></td>
			<td>DISPLAY THUMBNAIL HERE</td>
		</tr>';
}
mysql_free_result($result);
mysql_free_result($resultImg);

echo '</table>';
?>

[btherl]

Do you have an index on dl_id on the images table?

 

Regardless of the answer to that, you can use this query to fetch all data in one go:

 

SELECT dl_id, dl_name, pic_url, pic_thumburl
FROM downloads
JOIN images ON (downloads.dl_id = images.dl_id)

[hlstriker]

Thanks a lot, I have another question though.

 

Some downloads have multiple images, and it's making that download show up multiple times. How do I check to see if the dl_id has already been gathered and stop it from getting anymore of the same dl_id?

[btherl]

If you're talking about the single query, you can do this:

 

1.  Order the query by dl_id

2.  In the loop that fetches the data, remember the last dl_id and check if the new one is equal.  If it's equal, then this is another image from the same dl_id.  If it's not equal, then it's a new dl_id.  Then take appropriate action.