This code is complete and fully functional, though I would appreciate any critisism on how I may improve
performance as it does have an effect on my database which is quite large. On average I have about 1200
members online that are constantly accessing this code and at times it does slow the site up quite a bit.
Any help would be appreciated.
There are two tables that I am joining, profile and images. The profile table contains signup dates of each
member, along with the state they reside in and their particular sex (male or female). The second table
images, contains each member's photo along with two additional fields proofread and ppp which tells us if
their profile and photos have been approved to be viewed by everyone. I have a table where I this script
will display two rows of five photos of the most recent members to join. The scrip works perfectly, but I
feel there could be more effective ways of doing this. Any help or advice would be appreciated.
Thanks,
Lord Rogaine
<?php
//Calling up the image info but sorting from profile table first to get list of names of members that may have photos in images table. Because there are so many members in each state and of each sex I am forcing my first criteria for sorting to be the signup date which should be within the most recent 10 days. I figure this will cut out quite a bit of unnessary sorting later when I sort by state then by sex. If I'm wrong for doing this please let me know.
$query = "SELECT straight_join img_id, img_file, img_type, img_height, img_width, img_bytes, img_alt, images.username, images.proofread, ppp from profile USE INDEX (signup_date), images WHERE TO_DAYS(NOW()) -
TO_DAYS(signup_date) <= '10' and state ='$LSstate' and sex='$sex' and profile.username = images.username and images.ppp ='1' and images.proofread ='1' order by signup_date desc limit 0,10";
// execute the query
$result = mysql_query($query);
// get the number of results
$num_rows = mysql_num_rows($result);
//two rows of five photos with each person's name underneath the photo with a link to their own personal profile
echo "<tr>";
$numInRow = 5; // this determines the amount of results per row
for ($i=0; $i<$num_rows; $i++){
if ($i % $numInRow == 0) { print ("<tr>"); }
// fetch row
$img_info = mysql_fetch_array($result);
// start table row
echo '<td valign=bottom align=center>';
$IMGusername = $img_info["username"];
// generates the image with their name underneath and a link to their personal profile
echo "<a href=memberpage.php?memberusername=$IMGusername>";
echo img_tag($img_info["img_file"], array("x"=>"100", "y"=>"100"));
echo "<br>";
echo "$IMGusername";
echo "</a>";
if ($i % $numInRow == $numInRow-1) { print ("</tr>"); }
}
?>
