Update multiple rows mysql

Question

Ok I'm sure this can be done I saw something similar on this post Updating multiple rows in MySQL

So what I need to make sure of is how I would wright it for mine. I have a database that lists a primary location for a staff member as well as their other associated office locations. I want to be able to update the table that holds that information for a single staff member, but I won't know exactly how many times I need to loop through.

So say I have Sally and she is associated with 2 office locations St. Paul and Chicago. Then I have Peter who is associated with only New York.

Table would be as follows

For Sally Primary key 101
LocationID 1 StaffID 1 PrimaryLoc 1

Primary key 102
LocationID 2 StaffID 1 PrimaryLoc 0

for Peter Primary key 103
LocationID 3 StaffID 2 PrimaryLoc 1

I know to insert one would be

    UPDATE loc_tbl SET locationID=%s, PrimaryLoc=%s WHERE staffID=%s

so if I want to loop through this would I use a for loop like this? the ... is for the section where it is pulling stuff from a few text boxes.

    for($i=$totalRows; $i>=0;$i--){
    UPDATE loc_tbl SET locationID=%s, PrimaryLoc=%s WHERE staffID=%s  ....
    }

Or is there a better way to do this?

You may need to give more details here, what are you trying to update locationID and PrimaryLoc to? — Zack Newsham, Sep 30 '13 at 00:48
Do you want to update **all** staff members or only some of them. If only some, do you know the exact criteria? Your `UPDATE` statement will do all the looping on the database side. — PM 77-1, Sep 30 '13 at 00:52
Sorry I want to be able to change the primary location and it would be for a single staff member at a time. — puddleJumper, Sep 30 '13 at 01:59

score 0 · Answer 1 · answered Sep 30 '13 at 01:58

0

MySql doesn't support multiple queries as stated in the post you referenced: mysql_query() sends a unique query (multiple queries are not supported).

When you delete, add or update in phpmyadmin it does this using multiple queries, why should we do any different? sql can perform something like 100,000 queries/sec! ref: http://yoshinorimatsunobu.blogspot.co.uk/2010/10/using-mysql-as-nosql-story-for.html

I'm assuming you would be throwing data at this from a form array?

$id = ('101','201','301');
$users = array('sally','john','james');
$office = array('Bristol','Bath','London');
$assistant = array('John','Ellen','Richard');

$count = count($users);

for($i=0; $i<$count; $i++) {
    $query = "UPDATE table SET 
    user = '$users[$i]', 
    assistant = '$assistant[$i]',
    office = $office[$i],
    WHERE id = '$id[$i]'";

    mysqli_query($link, $query);
}

This works for me and would be completed in a fraction of a second.

answered Sep 30 '13 at 01:58

Edward

1,806
5
26
36

sorry I didn't respond soon I got sick right after I posted this and am a little woozy. I actually don't need to update all of them I just need to update 1 person at a time, so I guess I could delete and re-add the spaces I want updated but how would you write it if you don't know the number of office the person has? – puddleJumper Oct 01 '13 at 17:11
I don't understand, if you need to update a single entry in a database, you would do just one update query. How is your table laid out? If its in two tables maybe something like $query = "UPDATE table SET name = $name WHERE id = (SELECT id FROM table WHERE staffID = '$staffID')"; ?? – Edward Oct 01 '13 at 18:07
it wouldn't necessarily be a single entry it is a single staff member who can have multiple entries into the table I'm trying to edit. I got it fixed anyway I used a delete/add back to the database for it to work. – puddleJumper Oct 13 '13 at 20:58

Update multiple rows mysql

1 Answers1