How to loop through an update query in pdo?

PHP
1

Could anyone please tell me what is wrong with this code?

<?php

$dbname = "mysql:host=localhost;dbname=online_examination";

$username = "root";

$password = "";

$conn = new PDO ($dbname, $username, $password);

$questionId = $_POST['question_id'];

$optionTitle_1 = $_POST['option_title_1'];

$optionTitle_2 = $_POST['option_title_2'];

$optionTitle_3 = $_POST['option_title_3'];

$optionTitle_4 = $_POST['option_title_4'];

$optionTitle = array($optionTitle_1, $optionTitle_2, $optionTitle_3, $optionTitle_4);

foreach ($optionTitle as $option) 

    { 

        $sql = "UPDATE `option_table` SET option_title = {$option} WHERE question_id = {$questionId}";

        $stmt = $conn->prepare($sql);

        $stmt->execute();

    }

?>
2

First you have your array(s) setup wrong (?) and second I would look in to implode - https://www.php.net/manual/en/function.implode.php. You should also be using prepared statements.

Here’s how I do it in OOP, but you might get an idea on how to do it the procedural way.

public function update(): bool
{
    /* Initialize an array */
    $attribute_pairs = [];

    /* Create the prepared statement string */
    foreach (static::$params as $key => $value)
    {
        if($key === 'id') { continue; } // Don't include the id:
        $attribute_pairs[] = "{$key}=:{$key}"; // Assign it to an array:
    }

    /*
     * The sql implodes the prepared statement array in the proper format
     * and updates the correct record by id.
     */
    $sql  = 'UPDATE ' . static::$table . ' SET ';
    $sql .= implode(", ", $attribute_pairs) . ' WHERE id =:id';

    /* Normally in two lines, but you can daisy chain pdo method calls */
    Database::pdo()->prepare($sql)->execute(static::$params);

    return true;

}
1 Like
3

Try using Php errors and what is wrong is shown in the verbose output:

<?php declare(strict_types=1); // applies t this file only
// following two lines should be set in php.ini
    error_reporting(-1);
    ini_set('display_errors', 'true');

$PWORD   = 'password goes here';
$DBNAME  = 'database goes here';
$TBLNAME = 'tablename goes here';

$dbname     = "mysql:host=localhost;dbname=$DBNAME";
$username   = "root";
$password   = "$PWORD";
$conn       = new PDO ($dbname, $username, $password);

$questionId     = $_POST['question_id']     ?? 'first';
$optionTitle_1  = $_POST['option_title_1']  ??  NULL;
$optionTitle_2  = $_POST['option_title_2']  ?? 'second';
$optionTitle_3  = $_POST['option_title_3']  ??  NULL;
$optionTitle_4  = $_POST['option_title_4']  ?? 'third';

$optionTitle = array(
                $optionTitle_1, 
                $optionTitle_2, 
                $optionTitle_3, 
                $optionTitle_4
            );

foreach ($optionTitle as $key => $option) 
{ 
  echo '<hr>$key:  ' .$key .' ==> ' .$option .'<br>';  

  if($option) :  
    $sql = "
        UPDATE 
            `$TBLNAME` 
        SET 
            `hash` = '{$option}' 
        WHERE 
            `vendor` >= '0' 
        " ;
        echo $sql;
        try {
            $stmt = $conn->prepare($sql);
            $ok   = $stmt->execute();
            fred($ok, '$ok');
        } catch(Exception $e) {
            fred($e);
        }//
  endif;  
}//

//====================================================================
function fred($val='No param???', $name='No $name???')
{
    echo '<pre style="width:88%; margin: 2em auto; border: dotted 1px RED">';
        echo '$name => ' .$name;
        print_r($val);
    echo '</pre>';    
}

// NOT REQUIRED AND CAN CAUSE WHITE_SPACE PROBLEMS ? >
1 Like
4

Perhaps you could share what it does that you don’t want it to, or what it does not do that you do want.

One of the issues is you’re not quoting the option-title in your query string. I see that you prepare() the query, but you don’t use parameters with it, rather you concatenate the various values into the query. If you used parameters, you wouldn’t need to be worrying about whether quotes are needed around values because that would be handled for you.

I can’t help thinking that if you used an array for the option titles in your form you could save the hassle of turning them into an array. I forget the exact syntax, but I think it’s no more complex than using name=option_value[] in the input - the square-brackets on all four inputs will send you an array of values instead of four separately-named ones.

1 Like
5

It is indeed

6

With the array index being the option id’s (1…4) so that you can associate the submitted value with the correct row it belongs to.

@tarunrathore03041980, if you are at the point of editing/updating existing data, you would have retrieved the existing values and used them to populate the form field values. Your option_table should have columns for an - id (auto-increment integer primary index), question_id, option_id, and option_title. The question_id/option_id columns would be defined as a unique composite index so that only one combination of each question_id/option_id can be inserted into the table. If there are four option/choices for a question, there would be four rows in the option_table. The update query would have a WHERE clause matching both the question_id and the option_id. You would use a foreach(){} loop to loop over and get both the key (option_id) and the value from the submitted array of data.

1 Like
7

Can we just… take a moment to point out that the OP’s query is clobbering the data as it goes through the loop?

question_id doesnt change.
The column being updated doesnt change.

So each execution of the query is going to edit the same row(s), and update the same field…

If you’re trying to edit multiple fields in a single row, you don’t need a loop.
If you’re trying to edit a single field in different rows, you need to change the WHERE clause on each iteration of the loop.

3 Likes
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.