PDO update table using array

Question

I'm learning and new in PDO. The usage of array in PDO causes difficulty for me. I'm developing simple web application using PDO syntax. Everything is going on smoothly, but I can't update multiple values from single submit button.

HTML FORM

    <td>
        <input type="number" name="roll[]" value="<?php echo $roll?>">
    </td>
    <td>
        <input type="text" name="name[]" value="<?php echo $name?>">
    <td>

I can print data.

if(isset($_POST['submit'])){
        $name = $_POST['name'];
        $roll = $_POST['roll'];
        foreach( $roll as $key => $n ){
            print "The name is ".$name[$key]." and email is ".$roll[$key].", thank you\n";
        }
    }

But I can't update multiple value at once. Perhaps it is because of lack of knowledge in terms of combination of array in PDO. I've searched in internet. But I found only advance question and answers. I can't found any example or discussion topics in this matter. I am sure I am 100% wrong, so following code doesn't work. Please help how to update using array

PHP CODE

if(isset($_POST['submit'])){
    $name = $_POST['name'];
    $roll = $_POST['roll'];

    foreach( $roll as $key => $n ){
        $sql = "UPDATE student SET name=:name WHERE roll=:roll";
        $query = $con->prepare($sql);
        $query->bindparam(':roll', $roll[$key]);
        $query->bindparam(':name', $name[$key]);
        $query->execute();
    }
}

What exactly happens when you run this loop? And you only have to prepare and bind the parameters once. I don't see if it would make a difference, but have you tried the same loop using bindValues()? And more importantly, do you have any error handling, is PDO set up to throw exceptions? — jeroen
– jeroen, Commented Jan 29, 2018 at 12:26

Masivuye Cokile · Accepted Answer · 2018-01-29 12:41:16Z

2

How about you prepare the statement outside the loop, then bind the values within the loop and execute.

<?php


if(isset($_POST['submit'])){
    $name = $_POST['name'];
    $roll = $_POST['roll'];


     $sql = "UPDATE student SET name=:name WHERE roll=:roll";
     $query = $con->prepare($sql);

    foreach($roll as $key => $n){
        $query->bindParam(':roll', $n[$key]);
        $query->bindParam(':name', $name[$key]);
        $query->execute();
    }
}

answered Jan 29, 2018 at 12:41

Masivuye Cokile

4,7733 gold badges22 silver badges35 bronze badges

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

Comments

Sanjit Bhardwaj · Accepted Answer · 2018-01-29 12:23:20Z

-1

Use for loop for this operation as there are no associative arrays involved

if(isset($_POST['submit'])){

$name = array();// initialize it first for a good coding standard
$roll = array();// initialize it first for a good coding standard
$name = $_POST['name'];
$roll = $_POST['roll'];

for($i=0;$i<count($name);$i++){ // considering the array elements of roll are equal to name elements 
    $sql = "UPDATE student SET name=:name WHERE roll=:roll";
    $query = $con->prepare($sql);
    $query->bindparam(':roll', $name[$i]);
    $query->bindparam(':name', $roll[$i]);
    $query->execute();
}
}

answered Jan 29, 2018 at 12:23

Sanjit Bhardwaj

8937 silver badges14 bronze badges

3 Comments

jeroen Over a year ago

As the print works, the loop does not seem to be the problem. And why would it anyway?

Sanjit Bhardwaj Over a year ago

why would we use a foreach loop as there are no associative arrays?

jeroen Over a year ago

The question is why the result would be any different; how would it solve the problem?

Collectives™ on Stack Overflow

PDO update table using array

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related