[Resolved] PHP, MYSQL and drop down menus

[djwiltsh]

Hi everyone

I have a website for a music shop using a MYSQL database of products. The make and model colums are indexed. One page in the site allows admin users to modify product details. It has a drop down menu that is populated from the database like so:

$sql = "SELECT pid, make, model, new FROM products order by make, model";

// Returns result
$mysql_result=mysql_query($sql,$connection);
echo"Query run";

// Counts the number of rows
$num_rows=mysql_num_rows($mysql_result);

// Tests if the database is empty
if ($num_rows == 0) {
	echo"<CENTER>You need to add some products first!</CENTER>";
}

// If there are records....
else {
	// Form to pick the product to be ammended
	echo "<FORM method=post action=\"view_record.php\">";
	echo "Please select a product <BR>";
	echo "<select name=\"record\">";

// Loop to populate the menu 
while ($row=mysql_fetch_array($mysql_result)) {
	$pid=$row["pid"];
	$make=$row["make"];
	$model=$row["model"];
	$new=$row["new"];
	// Display options in the pulldown box
	echo "<option value=\"$pid\">$new $make $model</option>";
	} // end while

echo "</select>";
} // end else

echo "<BR><BR>";
echo "<input type=\"submit\" value=\"View the record\"></FORM>";
mysql_close($connection);

The problem is that sometimes this menu will be populated really quickly. Other times, the page will be blank for ages, and when I hit stop in the browser, only a few records are shown in the drop down. There are currently 124 products in the database.

What is going on?

Many thanks

Daniel

[sfullman]

Let the query run, no matter how long it takes, and make sure you have all your items.

You might also try running the exact same query in phpMyAdmin to compare, the newest version tells how long the query took.

If you stop the process and see in source code fromthe browser that the it basically "stopped" at the dropdown list (never finishing with the </select> tag), but the items you have so far are the same as the full list's, then your code is executing right.

If the list is the same each time, albeit slow, there's nothing wrong with your query.

Make sure you have proper indexes on the table.

Also, to see the query happen and spill data real-time, consider temporarily removing the <se.ect></select> tags, and instead of <option> just output the string with a <br>, that way you can watch the output as it happens.

Server issues, load issues, a number of things, and unfortunately you'll have to troubleshoot each of those possibilities.

Then good news is that you'll learn something in the process :-)

Sam Fullman

[djwiltsh]

Thanks for the reply

I have done what you suggested. The list is generated but it seems to stop at random points, and no amount of waiting makes the list finish.

What does this indicate?

Thanks

Daniel

[sfullman]

did you try it in phpMyAdmin? does it work there?

I suggest you create a new page with just that query, leve out the <select> tag and <option> tag and go with a <br> as before. Then:

is there a way you can structre the query that DOES work?

can you create another table with similar data that does work?

Honestly, I've never heard of a query failing mid-try, sounds a little wierd. You might try creating a new table, that would be the clincher. If you have another server, try it on that server.

It's called troubleshooting and often it will either drive you to insanity or make you a person with latitude and an attitude that accepts obstacles :-)

Sam

[djwiltsh]

I don't have phpMyadmin installed so I can't try it there.

However, I have just been trying it at home rather than at work with some suprising results.

Over a broadband connection it works with no problems. However, I also tried it with a dialup here and again there were no problems. At work we have freeserve anytime dialup access, so perhaps the problem is here? The thing is, my dialup at home actually connected slower than freeserve at work, yet there were no problems with the query!

Daniel

[sfullman]

once again, that's what it's called. I suggest you do a search for and download the latest phpMyAdmin, it really will improve your ability to do this stuff

Sam