need more efficient php

[evildj]

Here is some php code that gets 38 categories from a table, then 5 articles per category, and the authors name from yet another table. Its very inefficient, I think its making about 77 queries. Any ideas how to optimize this?
Thanks, DJ

$sql = "SELECT id, category FROM categories ORDER BY category";	
$result = mysql_query($sql, $db);
$x = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$sql = "SELECT id, member, title, datesubmitted FROM articles WHERE category = $line[id] " .
	"ORDER BY datesubmitted DESC, id DESC LIMIT 0, 5";	
	$inner_result = mysql_query($sql, $db);
	if ($x % 2 == 0) $links .= "<tr>";
	$links .= "<td valign=top><table><tr><td><b>$line[category]</b></td></tr>\n";
	while ($inner_line = mysql_fetch_array($inner_result, MYSQL_ASSOC)) {
		$sql = "SELECT id, firstname, surname FROM member WHERE id = $inner_line[member]";
		$member_result = mysql_query($sql, $db);
		$member_line = mysql_fetch_row($member_result);
		$name = "$member_line[1] $member_line[2]";
		$links .= "<tr><td><a href=\"article-details-testing.php?id=$inner_line[id]\">$inner_line[title]</a></td></tr>\n" .
		"<tr><td class=\"tiny\">" . makeDate("m/d/y", $inner_line[datesubmitted]) . "</td></tr>\n" .
		"<tr><td class=\"tiny\"><a href=\"member-profile-testing.php?id=$member_line[0]\">$name</a></td></tr>\n";
	}
	$links .= "<tr><td><br><a href=\"whats-new-details-testing.php?id=$line[id]\" style=\"color: maroon\">" .
	"MORE ARTICLES</a><br><br></td></tr></table></td>\n";
	if ($x % 2 > 0) $links .= "</tr>\n\n";
	$x++;
}

[eoghain]

Damn thats ugly. Let me see if I can decipher it.

Looks like you are building a list of links from the data that you are gathering. You should be able to do this all with 1 query and some join statements.

SELECT c.id AS cat_id, c.category, 
       a.id AS article_id, a.member, a.title, a.datesubmitted,
       m.id AS member_id, m.firstname, m.surname
  FROM categories c
 INNER JOIN articles a ON (c.id = a.category)
 INNER JOIN members m ON (a.member = m.id)
 ORDER BY c.id, a.datesubmitted DESC
 LIMIT 0,5

I don't want to try and figure out what your formatting is but the above query should get you all of the data that you want in a single query. You'll have to adjust all of your formatting code to deal with this, but run that query against you database and see if it gets what you are looking for.

[evildj]

Very cool!
Based on your suggestion, I did this...

$sql = "SELECT id, category FROM categories ORDER BY category";	
$result = mysql_query($sql, $db);
$x = 0;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$sql = "SELECT c.id AS cat_id, c.category, a.id AS article_id, a.member, a.title, a.datesubmitted, m.id AS member_id, m.firstname, m.surname " .
	"FROM categories c INNER JOIN articles a ON (c.id = a.category) INNER JOIN member m ON (a.member = m.id) " .
	"WHERE c.id = $line[id] " .
	"ORDER BY c.id, a.datesubmitted DESC, a.id DESC LIMIT 0,5";

$inner_result = mysql_query($sql, $db);
if ($x % 2 == 0) $links .= "<tr>";
$links .= "<td valign=top><table><tr><td><b>$line[category]</b></td></tr>\n";
while ($inner_line = mysql_fetch_array($inner_result, MYSQL_ASSOC)) {
	$links .= "<tr><td><a href=\"article-details-testing.php?id=$inner_line[article_id]\">$inner_line[title]</a></td></tr>\n" .
	"<tr><td class=\"tiny\">" . makeDate("m/d/y", $inner_line[datesubmitted]) . "</td></tr>\n" .
	"<tr><td class=\"tiny\"><a href=\"member-profile-testing.php?id=$inner_line[member]\">" .
	"$inner_line[firstname]  $inner_line[surname]</a><br><br></td></tr>\n";
}
$links .= "</table></td>\n";
if ($x % 2 > 0) $links .= "</tr>\n\n";
$x++;
}

Its definitely faster, as there are now half as many hits on the database, but I am still running that query 38 times.
Thanks, DJ

[eoghain]

That's because my 1 query was designed to replace all 3 of yours, not just the 2 inside your loop of Categories.

[evildj]

I knew that, but I can't figure out how to make the one query return just the most recent 5 articles in each of the 38 categories, from a table with 5000 articles.
Thanks, DJ

[eoghain]

Sorry I didn't realize that was what you were after. I guess I should have looked at your code a little closer.

Unfortuntatly I don't have any better solution for you than the one above. I think you are stuck with the 38 queries.

[evildj]

Thanks, you helped me cut the time in half.