PHP prepared statement array update

Question

This is a follow-up question to another post (link)

I'm trying to wrap my head around how I perform a mysql update using a prepared statement. I need to add the WHERE id = ?.. I'm confused as to how I'm getting that back from my array. Thanks!

My form:

<form action="update.php" method="post">

  <input type="hidden" name="id" value=""/>
  <input type="text" name="name" value=""/>
  <input type="text" name="age" value=""/>

  <input type="hidden" name="id" value=""/>
  <input type="text" name="name" value=""/>
  <input type="text" name="age" value=""/>

  <input type="submit" value="submit" name="submit" />
</form>

PHP:

// Create statement object
$stmt = $db->stmt_init();


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

// Create a prepared statement
if($stmt->prepare("UPDATE contact SET (name, age) VALUES (?, ?)")) {

    // Bind your variables to replace the ?s
    $stmt->bind_param('si', $name, $age);


    $returnedData = $_POST['data'];

for($i=0;$i<count($returnedData);$i+=3){
    $id = $returnedData[$i]['id']
    $name = $returnedData[$i+1]['name'];
    $age = $returnedData[$i+2]['age'];
    $stmt->execute();
}


    // Close statement object
    $stmt->close();
}

}

score 2 · Answer 1 · answered Jul 09 '13 at 21:13

The data from your form will appear in $_POST['id'], $_POST['name'] and $_POST['age'], but you're not using these variables in the code you have posted.

You're not actually setting a value for id in your form. Your hidden ipputs should look more like this:

  <input type="hidden" name="id" value="myID"/>

which will return $_POST['id'] == "myID"

Note also that your form is asking for two sets of these variables. You won't see the first set unless you modify the names somehow.

score 2 · Accepted Answer · answered Jul 09 '13 at 21:13

First, you need to use array-style names in the form:

<form action="update.php" method="post">

  <input type="hidden" name="id[]" value=""/>
  <input type="text" name="name[]" value=""/>
  <input type="text" name="age[]" value=""/>

  <input type="hidden" name="id[]" value=""/>
  <input type="text" name="name[]" value=""/>
  <input type="text" name="age[]" value=""/>

  <input type="submit" value="submit" name="submit" />
</form>

I don't know where you got $_POST['data'] from, that doesn't appear anywhere in your form. The keys of $_POST are the names of the input elements in the form.

And finally, if you don't include a WHERE clause in the query, you'll update every row in the table.

// Create a prepared statement
if($stmt->prepare("UPDATE contact SET (name, age) VALUES (?, ?) WHERE id = ?")) {

    // Bind your variables to replace the ?s
    $stmt->bind_param('sii', $name, $age, $id);

    for($i=0;$i<count($_POST['id']);$i++){
        $id = $POST['id'][$i]
        $name = $_POST['name'][$i];
        $age = $_POST['age'][$i];
        $stmt->execute();
    }

    // Close statement object
    $stmt->close();
}

Would you take a look at my follow up question.. I'm not quite wrapping my head around my errors. Thanks! http://stackoverflow.com/questions/17575653/php-prepared-statement-insert-from-a-loop-of-data — user1040259, Jul 10 '13 at 17:16

PHP prepared statement array update

2 Answers2