update multiple rows in table mysql

Question

im wondering if someone could point me in the right direction as im trying to update multiple rows but for some reason everytime time i do the update, variable id_employee is set with the same value for all the rows.. that´s what i figured out when i var_dumped $_POST. The rest of fields are fine.. Here is a complete view of my code. http://pastie.org/5478920, also this is what i get when i var_dump http://pastie.org/5478980

    $query = "update project_staff
    set
    id_employee=?
    where
    id_project=?
    and
    id_projectemployee=?
    ";

    $stmt = $this->dbh->prepare($query);

    for ($i = 0; $i < count($_POST['id_employee']); $i++){

    $employee = $_POST['id_employee'][$i];

    $stmt->bindValue(1, $employee, PDO::PARAM_INT);
    $stmt->bindValue(2, $_POST["id"], PDO::PARAM_INT);
    $stmt->bindValue(3, $_POST["idstaff"], PDO::PARAM_INT);

    $stmt->execute();

    }

    echo("Project" . " " . $_POST["nom"] . " ". "has been updated");

"variable id_employee is set with the same value for all the rows.. that´s what i figured out when i var_dumped $_POST" - i.e this is NOT a MySQL issue but rather a PHP one. How are you fetching the values for the id_employee[] array? — Matanya
– Matanya, Commented Dec 4, 2012 at 14:33
In the loop you can see in the first lines pastie.org/5478920 — adavia
– adavia, Commented Dec 4, 2012 at 14:42

Hugo Delsing · Accepted Answer · 2012-12-04 14:43:11Z

1

You are setting the bind param to an explicit data type PDO::PARAM_INT, but values FROM $_POST are always string.

You should change and validate your POST input to be INT first and change it to INT or use PDO::PARAM_STR

//UPDATE

Looking at your code you set the value of the where clause to the content of $_POST['id'] AND $_POST["idstaff"]. So in every run trough your foreach you are requesting the same value. So the $employee is update on every run, but the where clause is always the same. this would look like:

//example
//$_POST['id'] = 20;
//$_POST['idstaff'] = 40;
//$_POST['id_employee'][0] = 1;
//$_POST['id_employee'][1] = 2;
//$_POST['id_employee'][2] = 3;

//run 1
$employee = $_POST['id_employee'][0]; //1
update project_staff set id_employee=1 where id_project=20 and id_projectemployee=40

//run 1
$employee = $_POST['id_employee'][1]; //2
update project_staff set id_employee=2 where id_project=20 and id_projectemployee=40

//run 1
$employee = $_POST['id_employee'][2]; //3
update project_staff set id_employee=3 where id_project=20 and id_projectemployee=40

so you are overwriting previous changes on every run.

edited Dec 4, 2012 at 14:43

answered Dec 4, 2012 at 14:24

Hugo Delsing

14.3k5 gold badges50 silver badges76 bronze badges

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

4 Comments

adavia Over a year ago

Hello.. I dont know what do you mean by "always string". Values are always Integers and the the only input here is the name that have an input hidden behind sending the id value

adavia Over a year ago

Thanks Hugo.. but how come im getting different values in id_projectemployee when i var_dump $_POST.. should be looping with those values instead the same one.. or im wrong?

Hugo Delsing Over a year ago

You are adding multiple instances of idstaff in foreach ($datos as $staff). So you are getting multiple results back. Just not the correct value linked to the correct member. You could alter input type="hidden" name="idstaff" to something like input type="hidden" name="idstaff_<?=idemployee?>" and then in your mysql statement use $_POST["idstaff_".$employee];

adavia Over a year ago

Is idemployee a variable? sorry to ask

Collectives™ on Stack Overflow

update multiple rows in table mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related