424,676 Members | 1,669 Online
Need help? Post your question and get tips & solutions from a community of 424,676 IT Pros & Developers. It's quick & easy.

# How to debug PHP's mysql_query() function?

 P: n/a Hi I posted three days ago about a function I wrote which kept refusing to run a mysql_query. Well I did away with the function, and hard coded the variables into the query itself. Here's what I have now: mysql_query("UPDATE about SET category_id = 58 WHERE about_id = 5") or die('error: ' . mysql_error()); Just running mysql_query, with no logic, I still dont get an update, and the script doesn't die. What other things should I be looking at? Here's another variation of the same thing I've tried: mysql_query("UPDATE about SET category_id=60 WHERE about_id=5") or trigger_error("SQL", E_USER_ERROR); I've also done: $update = mysql_query("Blah blah blah"); and testing$update gives me nothing. No errors, no update. Thanks, J Moore Nov 2 '05 #1
7 Replies

 P: n/a John Moore said the following on 02/11/2005 18:39: Hi I posted three days ago about a function I wrote which kept refusing to run a mysql_query. Well I did away with the function, and hard coded the variables into the query itself. Here's what I have now: mysql_query("UPDATE about SET category_id = 58 WHERE about_id = 5") or die('error: ' . mysql_error()); Just running mysql_query, with no logic, I still dont get an update, and the script doesn't die. What other things should I be looking at? Without seeing the function, it's difficult to say! Have you tried getting the function to echo the query string that it creates, and comparing that to what you expect? -- Oli Nov 2 '05 #2

 P: n/a On Wed, 02 Nov 2005 19:09:25 GMT, in comp.lang.php Oli Filth wrote: Without seeing the function, it's difficult to say!Have you tried getting the function to echo the query string that itcreates, and comparing that to what you expect? Like I said, I got rid of the function. Now I'm just dealing with this snippet of code which won't execute: mysql_query("UPDATE about SET category_id=63 WHERE about_id=5") or trigger_error(mysql_error(), E_USER_ERROR); I'm running it exactly like that. I also tried it like this: $update = mysql_query("UPDATE about SET category_id=63 WHERE about_id=5") or trigger_error(mysql_error(), E_USER_ERROR); if (!$update) { echo 'Error: ' . mysql_error(); } echo '
Info: '; mysql_info($update); echo ' '; mysql_info returns a warning: Warning: mysql_info() expects parameter 1 to be resource, boolean given in c:\Path\to\script.php on line 384 Thanks, J Moore Nov 2 '05 #3  P: n/a John Moore (no****@nomail.com) wrote: : Hi : I posted three days ago about a function I wrote which kept refusing : to run a mysql_query. : Well I did away with the function, and hard coded the variables into : the query itself. Here's what I have now: : mysql_query("UPDATE about SET category_id = 58 WHERE about_id = 5") or : die('error: ' . mysql_error()); Are you sure you have a record with about_id = 5? It is not an error to update zero rows. A successful update of zero rows is documented to return TRUE from mysql_query. You need to check the row count after running the query. But I don't have an example handy of doing that, so you'll have to look it up. I suspect that google can find examples of php mysql updates and checking the row count afterwards. -- This programmer available for rent. Nov 2 '05 #4  P: n/a John Moore wrote:$update = mysql_query("UPDATE about SET category_id=63 WHERE about_id=5") or trigger_error(mysql_error(), E_USER_ERROR); if (!$update) { echo 'Error: ' . mysql_error(); } echo ' Info: '; mysql_info($update); echo '
'; mysql_info returns a warning: Warning: mysql_info() expects parameter 1 to be resource, boolean given in c:\Path\to\script.php on line 384 Well, you're running it on MS-Windows - thats not good for starters. Try switching on the replication log in mysql and see if it changes size / timestamp when you run the query (you can also convert the log to a readable format - RTFM for more details). mysql_info(), like all the mysql_ fns should use the default...according to the manual (same for both _info & _query): : If by chance no connection is found or established, : an E_WARNING level warning is generated. ....but of course you read that already. It seems most peculiar that the script is getting that far the way you've written it (the connection exists at line 1, becuase you're not getting a fatal error, yet nothing is (allegedly) getting updated, then there is no connection at line 5. I guess your installation might be fscked. C. Nov 2 '05 #5

 P: n/a On 2 Nov 2005 12:16:30 -0700, in comp.lang.php yf***@vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote: Are you sure you have a record with about_id = 5? It is not an error toupdate zero rows. A successful update of zero rows is documented toreturn TRUE from mysql_query.You need to check the row count after running the query.But I don't have an example handy of doing that, so you'll have to look itup. I suspect that google can find examples of php mysql updates andchecking the row count afterwards. Well thanks for trying. I will try your suggestion for checking the row count. Maybe it will help if I put this into some context. In this script there are 5 queries, and the third one refuses to run or kill the script. I've tested this over and over, within a function and outside of the function. I've done var_dumps and echoed out the queries and variables at every step of the script, but still the third query refuses to run. It's a simple update query which works perfectly at the command line: UPDATE about SET category_id=65 WHERE about_id=5; Here it is in the context of my current version of the script: // create the category $cat_name =$_SESSION['cat_name']; $insert_query = "INSERT INTO about_category (category_id, cat_name, total_pages) VALUES ('', '$cat_name', 0)"; $insert_result = mysql_query($insert_query) or die(mysql_error()); // get the new category_id $select_query = "SELECT category_id AS new_id FROM about_category WHERE cat_name ='$cat_name'"; $select_result = mysql_query($select_query) or die(mysql_error()); $new_id = mysql_result($select_result, 0, 'new_id'); // This is the query that refuses to run // update the page $update_sql = "UPDATE about SET category_id=65 WHERE about_id=5";$update_result = mysql_query($update_sql) or die(mysql_error()); // update the category totals include_once('inc/about_inc2.php'); update_cat_total('remove',$_SESSION['category_id']); update_cat_total('add', $new_id); I have this written as a set of procedures without any logic because I thought it would be easier to see why the third query won't run. Once I get this bug fixed I'll go back and add some logic to test all the results, and put the code back into a function. Here is the layout of the two tables; about and about_category: CREATE TABLE about ( about_id int(10) unsigned NOT NULL auto_increment, category_id int(10) unsigned NOT NULL default '0', name varchar(35) NOT NULL default '', display enum('show','hide') NOT NULL default 'show', text text NOT NULL, meta_data_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (about_id) ) TYPE=MyISAM; CREATE TABLE about_category ( category_id int(10) unsigned NOT NULL auto_increment, cat_name varchar(35) NOT NULL default '', total_pages tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (category_id) ) TYPE=MyISAM; Also, the final two updates take place in another function, which works every time: function update_cat_total($task, $category_id) { // get the current count for total pages$query = "SELECT total_pages FROM about_category WHERE category_id = '$category_id'";$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR); if ($result) { $total_pages = mysql_result($result, 0, 'total_pages'); // $task may be 'add' or 'remove' switch ($task) { case 'add': $expression =$total_pages + 1; break; case 'remove': if ($total_pages > 0) {$expression = $total_pages - 1; } else {$expression = 0; } break; } $query = "UPDATE about_category SET total_pages =$expression WHERE category_id = '$category_id'";$result = mysql_query($query) or trigger_error("SQL", E_USER_ERROR); if ($result) { return true; } else { return false; } } else { die('
No result from query: ' . $query . ' Error: ' . mysql_error() . ' '); } } I may not be able to code php very well, but I make a damn good plate of spaghetti. J Moore Nov 2 '05 #6  P: n/a On Wed, 02 Nov 2005 21:04:54 GMT, in comp.lang.php Colin McKinnon wrote: Well, you're running it on MS-Windows - thats not good for starters. Thanks, but it behaves the same way in Slackware9.1, PHP 4.3.2, mysql4.0.13, Apache1.3.24 Try switching on the replication log in mysql and see if it changes size /timestamp when you run the query (you can also convert the log to areadable format - RTFM for more details).mysql_info(), like all the mysql_ fns should use the default...according tothe manual (same for both _info & _query):: If by chance no connection is found or established,: an E_WARNING level warning is generated....but of course you read that already. It seems most peculiar that thescript is getting that far the way you've written it (the connection existsat line 1, becuase you're not getting a fatal error, yet nothing is(allegedly) getting updated, then there is no connection at line 5. I guessyour installation might be fscked.C. This gives me something to go on- I really appreciate it and will check it out. J Moore Nov 2 '05 #7  P: n/a John Moore wrote: Hi I posted three days ago about a function I wrote which kept refusing to run a mysql_query. Well I did away with the function, and hard coded the variables into the query itself. Here's what I have now: mysql_query("UPDATE about SET category_id = 58 WHERE about_id = 5") or die('error: ' . mysql_error()); Just running mysql_query, with no logic, I still dont get an update, and the script doesn't die. What other things should I be looking at? Here's another variation of the same thing I've tried: mysql_query("UPDATE about SET category_id=60 WHERE about_id=5") or trigger_error("SQL", E_USER_ERROR); I've also done:$update = mysql_query("Blah blah blah"); and testing $update gives me nothing. No errors, no update. after the query, run something like:$rows = mysql_affected_rows() if var_dump(mysql_query(...)) shows boolean true, then the query was a success, then if \$rows > 0, something was changed in the database, if -1, the query failed, and 0 indicates a successful query with nothing updated. if 0, perhaps the row already had those values or no rows have the about_id = 5... Also, I have a tendency to include the link identifier when using the database functions just so that I know exactly which connection I am working with (even if there is only one in the script) - it just removes some more unknowns for debug time. -- Justin Koivisto, ZCE - ju****@koivi.com http://koivi.com Nov 2 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.