Update database, in foreach loop?

Question

In a card game I'm creating, I need to update the database in a foreach loop as every 3 cards they checkbox it updates the database for the hands they've created.

The problem I'm having is that even though it seems to be right, it's not working.

if (isset($_POST['hand']) == true)
{
    if (sizeof($_POST['checkbox']) == 3)
    {
        foreach($_POST['checkbox'] as $checkbox)
        {
            $query = "UPDATE games SET ? = ? WHERE comp = 0";
            $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
            $stmt = $db->prepare($query);
            $stmt->bindValue(1, "ss$ver");
            $stmt->bindValue(2, $checkbox);
            $stmt->execute();
            $ver = $ver + 1;
            echo $checkbox.'<br/>';
        }
    }
    else
    {
        echo 'You must only select 3 cards.';
    }
}

$ver is so that the cards can be done in a foreach loop. As "ss$ver" / 'ss'.$ver increments each time the loop is looped, so that it follows suit to the next field title / column in the database. BUT the mySQL error I get is this.

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ss1' = '13' WHERE comp = 0' at line 1 in E:\wamp\www\index.php on line 126

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ss2' = '15' WHERE comp = 0' at line 1 in E:\wamp\www\index.php on line 126`

etc..

Any ideas on how I can either get around the foreach loop for the checkboxes or actually update in the loop?

Any help is appreciated !

And for the form

echo '<form input="index.php" method="post">';
foreach($fetch as $key => $value)
{
    $check = '<input class="check" type="checkbox" name="checkbox[]" value="'.$value.'">';
    echo $cards[$value].$check;
}

The problem is that you can't use variable binding on column/table names and alike, as there are not "data", thus not support binding. Congratulation on finding out. — Passerby
– Passerby, Commented Aug 22, 2013 at 3:09

Flexo - Save the data dump · Accepted Answer · 2013-09-04 06:51:29Z

1

if (isset($_POST['hand']) == true)
{
    if (sizeof($_POST['checkbox']) == 3)
    {
        foreach($_POST['checkbox'] as $checkbox)
        {
            $query = "UPDATE games SET ss$ver = ? WHERE comp = 0";
            $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
            $stmt = $db->prepare($query);
            // $stmt->bindValue(1, 'ss'.$ver);
            $stmt->bindValue(1, $checkbox);
            $stmt->execute();
            $ver = $ver + 1;
            echo $checkbox.'<br/>';
        }
    }
    else
    {
        echo 'You must only select 3 cards.';
    }
}

Cannot bind variables on colum/table names.

edited Sep 4, 2013 at 6:51

Flexo - Save the data dump♦

89.1k22 gold badges205 silver badges286 bronze badges

answered Aug 22, 2013 at 12:14

dan

558 bronze badges

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

1 Comment

Gerald Schneider Over a year ago

you should move $db->setAttribute() and $db->prepare() out of the loop, no need to set it over and over again.

Collectives™ on Stack Overflow

Update database, in foreach loop?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related