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
