CODEWITHSUNDEEP
phpmysqlarrays
Jess McKenzie
Jess McKenzie

Reputation: 8385

A multi-dimensional array insert and update into the database

I have a multiple select drop down box that produces me an array like this:

 array(3) { [0]=> string(1) "1" [1]=> string(1) "6" [2]=> string(1) "7" }

What I cannot work out is how to get the multiple select drop down box to insert update and select from or into the database. I seem to only be getting one value

My current code situation is this -> And what the above var_dump is of:

SELECT * FROM {table} WHERE canid=? AND categoryid=?", array($emailCheck['id'], $fields['Occupation'][0]));

Update Statement: How could I implode this?

UPDATE {table} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array($emailCheck['id'], implode(',', $fields['Occupation']));

My DB Structure is:

enter image description here

Upvotes: 0

Views: 276

Answers (2)

Imre L
Imre L

Reputation: 6249

you have to dynamically generate the parameter placeholder for each value

$params = array_merge(array($emailCheck['id']), $fields['Occupation']);
$catPlaceholders = '?'.str_repeat(',?',count($fields['Occupation'])-1);

myFunction("SELECT * FROM {table} WHERE canid=? AND categoryid IN ("
        .$catPlaceholders.")", $params);

Upvotes: 0

haynar
haynar

Reputation: 6030

if I understood correctly and $fields['Occupation'] contains multiple selected values from dropdown, then use AND categoryid IN (?) and use implode to merge array into string like this: implode(',', $fields['Occupation'])

The complete code will look like this:

myFunction("SELECT * FROM {table} WHERE canid=? AND categoryid IN (?)", array($emailCheck['id'], implode(',', $fields['Occupation'])));

UPDATE

I think in this case, when you need to change these columns for each row independently, it would be better to loop over array in PHP and make separate queries:

for($i=0; $i<count($fields['Occupation']); $i++) {
    $id = $fields['Occupation'][$i];
    // here make query like before
    myFunction("UPDATE {table} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array($emailCheck['id'], $id));
}

Upvotes: 1

Related Questions