Why are scripts unable to execute queries?

I have a simple script to get results from a database.

$query = "select * from tablename where name like \"%$trimmed%\"  
  order by name"; 

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Unable to execute query.");

PHP:

Sometimes the page echos "Unable to execute query" and then when I refresh the page it works fine. Why does this occur?

It is annoying as pages get indexed with "Unable to execute query" written on them.

I have replaced the die error message with <meta http-equiv=\"refresh\" content=\"0\"> as a temporary fix at the moment.

 

Try doing a

mysql_error()

PHP:

to see what the problem is

 

I agree with dct. Replace

$result = mysql_query($query) or die("Unable to execute query.");

Code (markup):

with

$result = mysql_query($query) or die("$query failed - ". mysql_error());

Code (markup):

By printing the query out as well as the error message you can see if the query is sometimes mis-formed.

 

The error message only shows approx 1 in 50 times with the script - the rest of the time it works fine.

If the error message show I just refresh the page and the query string does not change but the query will execute fine.

 

Then change the script, run it as many times as it takes to get a decent error message. You could also get it to email you with the message provided by mysql_error() when it fails.

 

and what is the message that mysql_error() provides? That's what we need to know to help you.

 

From what you say about Google indexing error messages, I'm assuming that the script returns an error when it runs on your ISPs server, not a local development server.

Have you considered the possibility that you are hosted on an overloaded/overworked server that fails 1 time out of 50?

Still, the error message is what you need at this stage.

 

i think its with ur mysql server, get a better one which has a nice uptime.

 

How many rows are in the table? Is the name field indexed? This query could be walking the table row by row and choking mysql.

I've seen a 100K row table walk crash mysql many times. Imagine if 5 or 20 users hit that page at once and cause all those cuncurrent walks.

 

I have solved the problem (not quite sure how though).

I used to use the variable $s=0 for the first page and $s=10 to show the second page of results etc.

When I set up the script to email the query string to me when it failed I found that the referrer information eg www.google.com?q=xxxxxxxx was being passed into my query instead of the $s variable.

I changed the s to be z and the problem has gone away.

Thanks for the help everyone.

 

may be u had been using another $s which caused a variable over write. good to know u figured it out.