By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,676 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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
<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:

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 '<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

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

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('<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

P: n/a
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

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.