How to debug PHP's mysql_query() function?

[John Moore]

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("UP DATE 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("UP DATE about SET category_id=60 WHERE about_id=5") or
trigger_error(" SQL", E_USER_ERROR);

I've also done: $update = mysql_query("Bl ah blah blah"); and testing
$update gives me nothing. No errors, no update.

Thanks,

J Moore

[Oli Filth]

Re: How to debug PHP's mysql_query() function?

John Moore said the following on 02/11/2005 18:39:[color=blue]
> 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("UP DATE 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?[/color]

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

[John Moore]

Re: How to debug PHP's mysql_query() function?

On Wed, 02 Nov 2005 19:09:25 GMT, in comp.lang.php Oli Filth
<catch@olifilth .co.uk> wrote:

[color=blue]
>
>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?[/color]

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("UP DATE about SET category_id=63 WHERE about_id=5") or
trigger_error(m ysql_error(), E_USER_ERROR);

I'm running it exactly like that.

I also tried it like this:

$update = mysql_query("UP DATE about SET category_id=63 WHERE
about_id=5") or trigger_error(m ysql_error(), E_USER_ERROR);
if (!$update) {
echo 'Error: ' . mysql_error();
}
echo '<hr>Info: ';
mysql_info($upd ate);
echo '<hr>';

mysql_info returns a warning:

Warning: mysql_info() expects parameter 1 to be resource, boolean
given in c:\Path\to\scri pt.php on line 384

Thanks,

J Moore

[Malcolm Dew-Jones]

Re: How to debug PHP's mysql_query() function?

John Moore (nobody@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("UP DATE 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.

[Colin McKinnon]

Re: How to debug PHP's mysql_query() function?

John Moore wrote:
[color=blue]
>
> $update = mysql_query("UP DATE about SET category_id=63 WHERE
> about_id=5") or trigger_error(m ysql_error(), E_USER_ERROR);
> if (!$update) {
> echo 'Error: ' . mysql_error();
> }
> echo '<hr>Info: ';
> mysql_info($upd ate);
> echo '<hr>';
>
> mysql_info returns a warning:
>
> Warning: mysql_info() expects parameter 1 to be resource, boolean
> given in c:\Path\to\scri pt.php on line 384
>[/color]

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...accor ding 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.

[John Moore]

Re: How to debug PHP's mysql_query() function?

On 2 Nov 2005 12:16:30 -0700, in comp.lang.php
yf110@vtn1.vict oria.tc.ca (Malcolm Dew-Jones) wrote:
[color=blue]
>
>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.[/color]

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($in sert_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($se lect_query) or die(mysql_error ());
$new_id = mysql_result($s elect_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($up date_sql) or die(mysql_error ());

// update the category totals
include_once('i nc/about_inc2.php' );
update_cat_tota l('remove', $_SESSION['category_id']);
update_cat_tota l('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','hi de') 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_tota l($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($qu ery) or trigger_error(" SQL", E_USER_ERROR);
if ($result) {
$total_pages = mysql_result($r esult, 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($qu ery) 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

[John Moore]

Re: How to debug PHP's mysql_query() function?

On Wed, 02 Nov 2005 21:04:54 GMT, in comp.lang.php Colin McKinnon
<colin.thisisno tmysurname@ntlw orld.deletemeun lessURaBot.com> wrote:

[color=blue]
>
>Well, you're running it on MS-Windows - thats not good for starters.
>[/color]

Thanks, but it behaves the same way in Slackware9.1, PHP 4.3.2,
mysql4.0.13, Apache1.3.24
[color=blue]
>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...accor ding 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.
>[/color]

This gives me something to go on- I really appreciate it and will
check it out.


J Moore

[Justin Koivisto]

Re: How to debug PHP's mysql_query() function?

John Moore wrote:[color=blue]
> 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("UP DATE 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("UP DATE about SET category_id=60 WHERE about_id=5") or
> trigger_error(" SQL", E_USER_ERROR);
>
> I've also done: $update = mysql_query("Bl ah blah blah"); and testing
> $update gives me nothing. No errors, no update.[/color]

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 - justin@koivi.co m

http://koivi.com