A multi-dimensional array insert and update into the database

Question

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

This depends on your database schema. I suggest you post that. — Jason McCreary
– Jason McCreary, Commented Sep 21, 2012 at 0:02
@JasonMcCreary Done but still unsure about the update/insert statements — Jess McKenzie
– Jess McKenzie, Commented Sep 21, 2012 at 0:34

haynar · Accepted Answer · 2012-09-21 17:31:49Z

1

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));
}

edited Sep 21, 2012 at 17:31

answered Sep 21, 2012 at 0:06

haynar

6,0517 gold badges37 silver badges53 bronze badges

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

6 Comments

Jess McKenzie Over a year ago

Thanks, How would I do my update/insert statements? I have updated my question with my update statement, I am getting d.pr/i/Y5y7

haynar Over a year ago

it depends what you want to do in update/insert statement, I mean what values should be set to canid=?, categoryid=?. sorry for late reply

Jess McKenzie Over a year ago

No problem im not well so hence why I am struggling. canid = given by the database this works fine but all I am doing is basically saying if the cat id is found with the user can id then do no reenter this information but update it. If the can id was not found then insert. I need one canid catid per row

haynar Over a year ago

sorry, I was at work... if you have to change canid and catid per row, I think better would be to loop over array and make separate queries

Jess McKenzie Over a year ago

wait can we just do the insert statement but I need to check if there is no double ups - from previous entries with that id

|

Imre L · Accepted Answer · 2012-09-21 17:52:10Z

0

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);

edited Sep 21, 2012 at 17:52

answered Sep 21, 2012 at 17:46

Imre L

6,29126 silver badges32 bronze badges

Collectives™ on Stack Overflow

A multi-dimensional array insert and update into the database

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related