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
