Queries causing page to load slowly

micmac

Hi all,
Can anyone tell me of a good solution for the following.
I currently have an admin area page that allows the admin staff to modify what title, text etc. for a particular movie is displayed on say the "New Release" page.
This all worked fine when there was only a few movies in the DB but now that there are over 3000 the time taken to load the admin page is too long.
The problem lies within one of my queries.

<?
$query7 = "SELECT movie_id,movie_title FROM movie ORDER BY movie_title";
$result7 = mysql_query($query7);
while($row = mysql_fetch_array($result7)){
$movie_id = $row["movie_id"];
$movie_title = stripslashes($row["movie_title"]);

$query8 = "SELECT t_home_box_img FROM t_home_box";
$result8 = mysql_query($query8);
while($row = mysql_fetch_array($result8)){
$t_home_box_img = $row["t_home_box_img"];
?>
                            <option value="<? echo $movie_id ?>" <? if($t_home_box_img == $movie_id) echo "selected"; ?>><? echo $movie_title ?></option>
                            <?
}
}
?>

You will notice that $query7 has to get every movie_id and movie_title from the DB. To make matters worse i have about 5 of these queries on the same page.
Can anyone suggest a solution to this...would a function be the way to go?

Cheers,
micmac

steve8557

Hi micmac,

WOW! 3000 entries in an option list, how do people manage?

Perhaps a better way would be to allow the admins to do a search on the title first to slim down the result set.

I couldn't make out what you were trying to do with the inner result set while. Is is possible for me to see the page you're talking about?

Steve

micmac

Hi Steve,
Thanks for the reply...i've also attached the page.
There must be a better way...i'm just not sure which way to go.

Cheers,
micmac

steve8557

Hi micmac,
Looking at the file and considering the usability of the admin function I would definately go for the initial search.

I'd handle this by allowing users to choose, from an option list, the type of movie to edit (new release, pre-order etc.) then have them type some or all of the title of the move they need to edit.

This would provide a short list of available movies to edit. I think this will be more user friendly and faster to return the results.

You could LIMIT the result set in the initial query and paginate the whole thing.

Hope that helps.

Regards
Steve

barand

What is t_home_box table. It looks like you loop through the whole of that table for every row you read in query7

Weedpacket

Originally posted by barand
What is t_home_box table. It looks like you loop through the whole of that table for every row you read in query7

True; and it's the same query every time, which seems a bit redundant.

Actually, I'm pretty worried about the option list those two loops are building: it looks as though every movie will be listed as many times as there are 't_home_box_img's. So if there are 3000 movies and 8 of those t_home_box_img thingies, that's 24,000 options in the list. With I guess 8 of them selected.

micmac

Hi guys,
Thanks for the replies.
Still alittle unsure which way to go though.
Can you suggest a method that will stop these redundant loops?

Cheers,
micmac