Insert Array and Non-Array Variables from Checkbox List

Question

I'm trying to code a simple grocery list web app for my family to use where the user would check the items they want to add to their list and then have it added to a table for future retrieval. But I am running into trouble while trying to insert an array along with the username into a MySQL database. I've searched around for an answer, but cannot find one that satisfies the following:

Inserts both an array and a non-array.
Does not use deprecated mysql_ functions.

Ideally, I would love to use PDO since I have some other queries using PDO. I know the array is being passed because I print it out successfully on the page on which I wish to execute the query. Below is what I have so far.

HTML Form (the checkbox values are populated from another MySQL table):

<div class="ui-body">
<? while($row = $resultRedMeat->fetch(PDO::FETCH_ASSOC)){
echo '<label><input type="checkbox" id="groceryListItem[]" name="groceryListItem[]" value="'.$row['food'].'">'.$row['food'].'</label>';
}?>
</div>

PHP Code to Insert

$groceryListItems = $_POST['groceryListItem'];    
$q = $db->prepare("INSERT INTO `grocerylist` 
                         SET `username` = $username, `food` = ?");
foreach((array)$groceryListItems as $groceryListItem){
            $q ->execute($username, array($groceryListItem['groceryListItem']));
            }

The errors I get in my error log are:

PHP Warning: Illegal string offset 'groceryListItem'
PHP Warning: PDOStatement::execute() expects at most 1 parameter, 2 given

While all the values being passed are from a database and there is no user entry, if you guys think it is still good to protect against injection, I wouldn't mind some advice on that, too, in this context.

Thanks so much for your help!

Dan · Accepted Answer · 2015-11-11 20:21:25Z

1

A couple things.

Your $groceryListItems isn't structured the way you think. Use print_r($groceryListItems) to view the array and figure out how you can access the element you want.

Re. Your Comment:

print_r($grocery_list_items);
Array ( [0] => Ground Beef (Extra Lean),
        [1] => Flank Steak,
        [2] => Roast )

If you loop through this array;

$foreach( $grocery_list_items as $key=>$value ){
    //$key will be 0 then 1 the 2
    //$value will be Ground Beef (Extra Lean) then Flank Steak then Roast
}

In your query you have SET username = $username but leter you try to pass username as a paramter. Replace $username with ?

PDO::executeexpects a single argument that is an array of paramters to pass to the query. Read me So combine $username and the greoceryitem into one array to pass to execute.

Also, your query is mixing UPDATE and INSERT syntax. If you are trying to run an INSERT query it should be;

INSERT INTO `grocerylist` (`username`,`food`) VALUES (?,?);

edited Nov 11, 2015 at 20:21

answered Nov 11, 2015 at 18:33

Dan

11.2k5 gold badges26 silver badges36 bronze badges

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

3 Comments

TheAleMaster Over a year ago

Ugh. Embarrassed about my SQL statement Apologies for that. The array looks like this: Array ( [0] => Ground Beef (Extra Lean) [1] => Flank Steak [2] => Roast ). I'm not really sure what I expect it to look like, to be honest. This one of my first go arounds with arrays. So for the PDO statement, it looks like I need to create an array from $username and my existing array of $groceryListItems?

Dan Over a year ago

See my comment. It should help. basically once you are in the foreach loop, grocery_list_item is just a string.

TheAleMaster Over a year ago

Thanks for the guidance. With it, I arrived at the following, which works: $q = $db->prepare("INSERT INTO grocerylist (username,food) VALUES (?,?)"); foreach ($groceryListItems as $key=>$groceryListItem){ $listInsert = array($username, $groceryListItem); $q -> execute ($listInsert); }

Collectives™ on Stack Overflow

Insert Array and Non-Array Variables from Checkbox List

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related