Fantastic! I'm going to have to study the code more to really understand it. Thanks for the help. I had to change the code just a little for it to work:
$sql = "SELECT u.id, u.username, u.album_title, date_format(MAX(f.date), '%b %D, %Y') as date1, COUNT(*) AS cnt FROM Chart_Users AS u INNER JOIN Chart_Files AS f USING (id) GROUP BY f.id ORDER BY u.album_title ASC"; $result = @mysql_query($sql,$connection) or die(" Couldn't execute query."); while ($row = mysql_fetch_array($result)) { $id = $row['id']; $username = $row['username']; $title1 = $row['album_title']; $title = stripslashes($title1); $date1 = $row['date1']; $cnt = $row['cnt']; $display_block .= "<tr><td nowrap align=\"left\"> <b><a href=\"display_album.php?id=$id\">$title </a></b></td> <td align=\"left\">$cnt</td><td align=\"right\" nowrap>$date1</td></tr>"; } > Hi Jeff, > > the most important rule you should follow: don't query the database > in a loop to avoid a join! The following lines (maybe some changes > are necesary) will do the same job as your code, but considerable faster. > Especially if 'id' is an index in both tables. > > Lutz > > > $sql = 'SELECT u.id, u.username, u.album_title,' > .' date_format(MAX(f.date), '%b. %D, %Y') as date1,' > .' COUNT(*) AS cnt' > .' FROM Chart_Users AS u' > .' INNER JOIN Chart_Files AS f USING (id)' > .' GROUP BY f.id' > .' ORDER BY u.album_title ASC'; > > $result = @mysql_query($sql,$connection) or die(" Couldn't execute query."); > > while ($row = mysql_fetch_array($result)) { > $id = $row['id']; > $username = $row['username']; > $title1 = $row['album_title']; > $title = stripslashes($title1); > $date1 = $row['date1']; > > $display_block .= "<tr><td nowrap align=\"left\"><b><a > $href=\"display_album.php?id=$id\">$title </a></b></td><td > align=\"left\">$cnt</td><td align=\"right\" nowrap>$date1</td></tr>"; > } > > > [EMAIL PROTECTED] (Jeff Oien) writes: > > > Here is some code I have for an index page of people who post > > charts on the Web, kind of like Yahoo Photos or something. > > It displays the album title, number of images and date of last > > upload. The page takes about 5-6 seconds to load which is all > > in the queries I'm sure. Is there a way I can make this more efficient? > > http://www.webdesigns1.com/temp/code.txt > > Jeff > > > > -------------------------------------- > > > > $table_name = "Chart_Users"; > > $sql = "SELECT * FROM $table_name order by album_title"; > > $result = @mysql_query($sql,$connection) or die(" Couldn't execute query."); > > //if(! $result = mysql_query($sql,$connection)) { > > // print("ERROR ".mysql_errno().": > ".mysql_error()."<br>\n$sql<br>\n"); > > // } > > > > while ($row = mysql_fetch_array($result)) { > > $id = $row['id']; > > $username = $row['username']; > > $title1 = $row['album_title']; > > $title = stripslashes($title1); > > > > $sql1 = "SELECT COUNT(*) FROM Chart_Files where id = '$id'"; > > $result1 = @mysql_query($sql1,$connection) or die(" Couldn't > execute query."); > > //if(! $result = mysql_query($sql1,$connection)) { > > //print("ERROR ".mysql_errno().": > ".mysql_error()."<br>\n$sql<br>\n"); > > //} > > $count = mysql_result($result1,0,"count(*)"); > > > > $sql2 = "SELECT date_format(date, '%b. %D, > %Y') as date1 FROM Chart_Files where > > id = '$id' order by photoid desc limit 1"; > > $result2 = @mysql_query($sql2,$connection) > or die(" Couldn't execute query."); > > $row = mysql_fetch_array($result2); > > $date1 = $row['date1']; > > > > if ($count > 0) { > > > > $display_block .= "<tr><td nowrap align=\"left\"><b><a > > href=\"display_album.php?id=$id\">$title </a></b></td><td > > align=\"left\">$count</td><td align=\"right\" nowrap>$date1</td></tr>"; > > } > > } > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php