Updating multiple entries in MySQL database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
amjohnno
Forum Newbie
Posts: 6
Joined: Tue Apr 25, 2006 1:26 am

Updating multiple entries in MySQL database

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
amjohnno
Forum Newbie
Posts: 6
Joined: Tue Apr 25, 2006 1:26 am

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
amjohnno
Forum Newbie
Posts: 6
Joined: Tue Apr 25, 2006 1:26 am

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
amjohnno
Forum Newbie
Posts: 6
Joined: Tue Apr 25, 2006 1:26 am

Post by amjohnno »

Thanks for the help. It must be something else then. :)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
amjohnno
Forum Newbie
Posts: 6
Joined: Tue Apr 25, 2006 1:26 am

Post by amjohnno »

I shall have to try it later when I get back from work. :) Thanks again.
amjohnno
Forum Newbie
Posts: 6
Joined: Tue Apr 25, 2006 1:26 am

Post by amjohnno »

Fantastic; I've got it all sorted now. :) . Your code was a big help in sorting it out. Cheers.
Post Reply