Page 1 of 1

Updating multiple entries in MySQL database

Posted: Tue Apr 25, 2006 1:38 am
by amjohnno
How would I go about updating all rows in a table in MySQL through PHP where one field equals 0? I've actually tried the following SQL in phpMyAdmin and it works fine, but I can't seem to get it to work through my PHP script.

Code: Select all

$final_sql = "UPDATE reminders SET checked = 0 WHERE checked = 1";
$final_nt = mysql_query($final_sql) or die(mysql_error());
I thought it would work fine, but there does seem to be a problem. As I said, the SQL did work when I tried it through phpMyAdmin. It's probably so simple and staring me right in the face!

Thanks

Posted: Tue Apr 25, 2006 1:53 am
by feyd
What are you getting that doesn't work? Just so you know, you're SQL updates all records where "checked" is one, not zero.

Posted: Tue Apr 25, 2006 1:58 am
by amjohnno
Sorry, yes that's what I meant. :oops:

It's supposed to alter all those equalling 1 and change them to 0. As I said, the SQL seemed to work fine in phpMyAdmin, but won't in my php script. Whenever I run it, for some reason it simply won't update anything.

Posted: Tue Apr 25, 2006 2:03 am
by s.dot
If you've executed the script in PHPMyAdmin... then there wouldn't be anything to update, as the fields are already set to what you want via the query through PHPMyAdmin.

Unless you were doing sample data or the likes.

Posted: Tue Apr 25, 2006 2:13 am
by amjohnno
scottayy wrote:If you've executed the script in PHPMyAdmin... then there wouldn't be anything to update, as the fields are already set to what you want via the query through PHPMyAdmin.

Unless you were doing sample data or the likes.
I've been reseting the data whilst testing. But does the code look ok? Should it be working?

Posted: Tue Apr 25, 2006 2:19 am
by s.dot
Providing you have the correct database connection, and the table `reminders` exists in that database, and those field names are correct, and mysql_error()) isn't giving you an error, then yes.. it looks perfect.

Posted: Tue Apr 25, 2006 2:20 am
by amjohnno
Thanks for the help. It must be something else then. :)

Posted: Tue Apr 25, 2006 2:23 am
by s.dot
Try this piece of code:

Code: Select all

$numr = mysql_query("SELECT count(*) FROM `reminders` WHERE `checked` = 1") or die(mysql_error());
$num = mysql_result($numr,0);

echo '<p>Number To Correct: '.$num.'</p>';

mysql_query("UPDATE `reminders` SET `checked` = 0 WHERE `checked` = 1") or die(mysql_error());

echo '<p>Query to correct successfully executed.</p>';

$numr = mysql_query("SELECT count(*) FROM `reminders` WHERE `checked` = 1") or die(mysql_error());
$num = mysql_result($numr,0);

echo '<p>Number To Correct After Query Executed: '.$num.'</p>';
After running that code, the result you should expect would look like

Code: Select all

Number To Correct: 124

Query to correct successfully executed.

Number To Correct After Query Executed: 0

Posted: Tue Apr 25, 2006 2:26 am
by amjohnno
I shall have to try it later when I get back from work. :) Thanks again.

Posted: Tue Apr 25, 2006 3:40 pm
by amjohnno
Fantastic; I've got it all sorted now. :) . Your code was a big help in sorting it out. Cheers.