Inserting an array into the database

Question

I have am creating a check function that enables me to check that data with the specific id and categoryid is already present in the database:

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

Line in question:

$occCheck = Jojo::selectQuery("SELECT * FROM {refocus_candidate_category} WHERE canid=? AND categoryid=?", array($emailCheck['id'], $fields['Occupation']));

Why am I getting this Error and how do I resolve:

Unknown column 'Array' in 'where clause'

Full Check Function:

    if($occCheck != FALSE)
    {
        Jojo::updateQuery("UPDATE {refocus_candidate_category} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array($emailCheck['id'], $fields['Occupation']));
    }else{
        Jojo::insertQuery("INSERT INTO {refocus_candidate_category} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?", array($emailCheck['id'], $fields['Occupation']));
    }

@Shades88 Why am I getting the error & how do I fix

Jess McKenzie
– Jess McKenzie

2012-09-20 18:38:47 +00:00
Commented Sep 20, 2012 at 18:38 — Jess McKenzie
– Jess McKenzie, Commented Sep 20, 2012 at 18:38
check my answer, hope that might help

Shades88
– Shades88

2012-09-20 18:46:05 +00:00
Commented Sep 20, 2012 at 18:46 — Shades88
– Shades88, Commented Sep 20, 2012 at 18:46

Pops · Accepted Answer · 2012-09-20 18:52:55Z

1

It looks $fields['Occupation'] is an array and it's going to be replaced by categoryid=>?< category ID. What about doing it step-by-step for each category make a question to the database?

OR

$occCheck = Jojo::selectQuery("SELECT * FROM {refocus_candidate_category}
                               WHERE canid=".$emailCheck['id']."
                               AND categoryid in
                                 (".implode(",",$fields['Occupation']).")");

if $emailCheck['id'] is not an array

edited Sep 20, 2012 at 18:52

Pops

31k37 gold badges139 silver badges153 bronze badges

answered Sep 20, 2012 at 18:41

Vaclav Svara

3593 silver badges6 bronze badges

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

2 Comments

Jess McKenzie Over a year ago

I get Input Array does not match ?: UPDATE refocus_candidate_category SET canid='11', categoryid=Array WHERE canid= UPDATE refocus_candidate_category SET canid=?, categoryid=? WHERE canid=? AND categoryid=? Array ( [0] => 11 [1] => Array ( [0] => 1 [1] => 6 [2] => 7 ) ) 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 '' at line 1array(5)

Vaclav Svara Over a year ago

Jess: SO as I wrote, use where operator IN and implode array to set of Ids

Shades88 · Accepted Answer · 2012-09-20 18:40:50Z

0

It's not exactly clear what you are trying to do in your queries, as you are writing these strange values SET canid=?.

But the problem you are facing is, that you are concatinating entire array in your SQL query string. try to echo an array then you will see it getting printed as Array. That's the source of your problem query gets expanded like someField=Array. Now that Array is not even in single quotes, so mysql is assuming it to be a column. Thus your error.

Remove that array($emailCheck['id'], $fields['Occupation']). And just set $emailCheck['id'], $fields['Occupation'][0]. your field['Occupation'] is and entire array so choose some as per your need.

answered Sep 20, 2012 at 18:40

Shades88

8,42826 gold badges95 silver badges135 bronze badges

10 Comments

Jess McKenzie Over a year ago

Hey, I am now getting Input Array does not match ?: SELECT * FROM refocus_candidate_category WHERE canid='11' AND categoryid= SELECT * FROM refocus_candidate_category WHERE canid=? AND categoryid=? why? Also it did insert but only 1 value

Shades88 Over a year ago

it looks like that function internally does something to array arguments passed to it. then try array($emailCheck['id'], $fields['Occupation'][0])

Jess McKenzie Over a year ago

I now get Input Array does not match ?: UPDATE refocus_candidate_category SET canid='11', categoryid='1' WHERE canid= UPDATE refocus_candidate_category SET canid=?, categoryid=? WHERE canid=? AND categoryid=? Array ( [0] => 11 [1] => 1 )

Shades88 Over a year ago

try array($emailCheck['id'], $fields['Occupation'][0], $emailCheck['id'], $fields['Occupation'][0]).... just a random guess. The reason for this guess is that, there are 4 placeholders in your sql query string. your function may be trying to replace them with input array elements. In input array we specified only 2, lets try putting 4

Jess McKenzie Over a year ago

INSERT INTO refocus_candidate_category SET canid=?, categoryid=? WHERE canid=? AND categoryid=? Array ( [0] => 11 [1] => 8 [2] => 11 [3] => 8 ) 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 'WHERE canid='11' AND categoryid='8'' at line 1

|

Peter Sobot · Accepted Answer · 2012-09-20 18:40:16Z

0

Is there a specific reason you're passing an Array to the ...Query( functions rather than separate arguments? PHP is passing the one parameter through to the database - which interprets it as a string - so your query is actually searching for the word "Array" instead.

Try this instead:

$occCheck = Jojo::selectQuery("SELECT * FROM {refocus_candidate_category}".
                              " WHERE canid=? AND categoryid=?",
                              $emailCheck['id'], $fields['Occupation']);

answered Sep 20, 2012 at 18:40

Peter Sobot

2,55721 silver badges20 bronze badges

2 Comments

Jess McKenzie Over a year ago

I need to check if there are any occupation options that match my id - If there is I would like to insert new options else insert the lot.

Jess McKenzie Over a year ago

I get Input Array does not match ?: SELECT * FROM refocus_candidate_category WHERE canid='11' AND categoryid= SELECT * FROM refocus_candidate_category WHERE canid=? AND categoryid=?

JvdBerg · Accepted Answer · 2012-09-20 18:42:03Z

0

You have to make up your mind what Occupation you want to query for:

$occCheck = Jojo::selectQuery("
   SELECT * FROM {refocus_candidate_category} WHERE canid=? AND categoryid=?",
   array($emailCheck['id'], $fields['Occupation'][1]));

In this case i used occupation with index 1, and that will result in occupation 6 selected.

answered Sep 20, 2012 at 18:42

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

1 Comment

Jess McKenzie Over a year ago

I need to check if they all are present

Collectives™ on Stack Overflow

Inserting an array into the database

4 Answers 4

2 Comments

10 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

10 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related