php mysql - update multiple rows in a table

Question

Ok, i have a checklist system that i am working on. There is a page that pulls data from the database and displays it as a checklist. I am trying to create a button that when pushed will reset the database to a certain state that i want. Basically, i have a button that sends an ajax callout to a php page that executes an UPDATE query. This query is as follows:

UPDATE $table SET value='$value', comments='$comments', editedBy='$editedBy', editedDate='$editedDate' WHERE projectId='$projectId';

I set the variables first of course, that's not my question. Just pretend they have data. My question is how can i repeat this query so that every row table x that has a projectId of n is updated? I'm guessing this involves a for loop?

SIDE NOTE: Since this query is just setting the value to false and making the comments, editedBy, and editedDate fields blank for every row in table x that has a projectId of n, is there a better way of doing this other than the UPDATE query?

Thanks for any help!

I'm assuming that the problem here is that you do not know all the rows table X has which makes it troublesome to update or am I wrong? — Michael J.V.
– Michael J.V., Commented Apr 20, 2011 at 14:53
thanks @edem, as you and @Jesse said, i'll just give that query a try on its own. — RyanPitts
– RyanPitts, Commented Apr 20, 2011 at 15:00

Jesse Bunch · Accepted Answer · 2011-04-20 14:56:12Z

2

As long as you don't sepcify a LIMIT in your UPDATE query, it will update every row it finds that satisfies your where clause.

Now, if you're updating the projects table and projectID is your primary key, you'll need to run a loop to update other projectIDs. If you're not updating the project table, then your update query will update any record that has a foreign key match to the project ID you specified.

Does that help?

answered Apr 20, 2011 at 14:56

Jesse Bunch

6,6694 gold badges39 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RyanPitts Over a year ago

thanks! I was thinking that might be the case but i didn't want to just run the query and screw up the database..wouldn't be the end of the world, just more work. Thanks for the help. I'll give it a try.

RyanPitts Over a year ago

yea, it worked! Guess i knew what i was doing after all. Thanks for confirming it for me. Now, is there a better way of removing data from a column on a specific row other than updating the row with a blank value? Or does this suffice?

Jesse Bunch Over a year ago

Depending on your constraints, you could set the value = NULL in your SQL query. However, a blank value should suffice in most applications.

Collectives™ on Stack Overflow

php mysql - update multiple rows in a table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related