469,963 Members | 1,327 Online

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

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:

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:

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 3705
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:

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
On Wed, 02 Nov 2005 19:09:25 GMT, in comp.lang.php Oli Filth
<ca***@olifilth.co.uk> wrote:

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?

Like I said, I got rid of the function. Now I'm just dealing with this
snippet of code which won't execute:

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 '<hr>Info: ';
mysql_info($update); echo '<hr>'; 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 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 John Moore wrote:$update = mysql_query("UPDATE about SET category_id=63 WHERE
if (!$update) { echo 'Error: ' . mysql_error(); } echo '<hr>Info: '; mysql_info($update);
echo '<hr>';

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

C.
Nov 2 '05 #5
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 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.

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:

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) {
$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('<hr>No result from query: ' . $query . ' Error: ' . mysql_error() . '<hr>'); } } 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 On Wed, 02 Nov 2005 21:04:54 GMT, in comp.lang.php Colin McKinnon <co**********************@ntlworld.deletemeunlessU RaBot.com> 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 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. This gives me something to go on- I really appreciate it and will check it out. J Moore Nov 2 '05 #7 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

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.