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
