[SOLVED] Problem with really basic php mysql query

[bizkid]

I have a very simple php/mysql query I'm using in an ajax script to populate a field from a database. (It basically presents address information for the sales rep assigned to a particular zip code when the user enters a zip code in a text field).

 

Heres the query script (this is the entire page called by the ajax script.)

 

<?php require('Connections/server.php'); ?>

<?php

$q=$_GET["q"];

 

$sql="SELECT * FROM reps WHERE zipCode = '".$q."' LIMIT 1";

 

$result = mysql_query($sql);

 

while($row = mysql_fetch_array($result))

{

echo $row['county_code'] . "," . $row['rep_code'] . "," . $row['repName'] . "," . $row['repAddress'] . "," . $row['repPhone'];

}

 

mysql_close($server);

?>

 

At any rate, I run this page repeatedly with no changes (passing the same value for "q" in through URL every time). Sometimes the query works, sometimes it produces the following error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource.

 

Can't for the life of me figure out why the exact same script works sometimes and not others. I even rebooted the server.  I swear this thing worked perfectly yesterday.

 

Any help would be appreciated.

 

[Mchl]

Change:

 

$result = mysql_query($sql); 

 

to

 

$result = mysql_query($sql) or trigger_error("Query error: ".mysql_error(). " Query: $sql", E_USER_ERROR);  

 

paste any MySQL errors that are reported when query fails.

[bizkid]

I made the change you suggested, and I get this result:

 

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in [filename] on line 11

Result=Resource id #3

Error code=17,1,John Doe,1000 North Main, Springfield IL XXXXX,xxx-xxx-xxxx

 

I see the error code contains the information I was trying to get.  (I changed the actual file path and name to [filename] for brevity.)

 

The page was actually working for awhile and then stopped again.  This is really flaky. 

 

Any thoughts?

[Mchl]

It seems you're having problems with connecting to database server..

[bizkid]

I was thinking that it probably wasn't going to be a scripting error. 

 

The solution may be well beyond the scope of this forum.  The testing server setup is a little unusual, in that the database server and the webserver are running in the same Debian Lenny VirtualBox Guest on a Windows Vista Host.  The setup normally works really well (and surprisingly fast.)  Normally I don't have problems at all, but even a virus scan on the host could slow up response from the guest.

 

Is there any way to script in a delay, a wait or a reconnect attempt into my script?  I've learned a lot about PHP in the last six months (starting at never having programmed anything before), but I still have a long way to go.

[Mchl]

Try setting

mysql.connect_timeout = -1

in php.ini.

(-1 means the connection will never time out, which might have some other undesired effects. You can put any ither number of seconds instead)

[bizkid]

I appreciate your help.  Oddly enough everything's working fine right now.  If the problem crops up again, I'll do a little research to what other complications might come from your suggested php.ini modification, and maybe give that a try.

 

Thanks again.