Problem constructing an array for an SQL query

Question

I have a 1 dimensional array of strings ($answers). I want to store each string as a new record a long with an itemId (the same itemId for each answer) into my table "answers_tb".

I have tried.....

using a foreach to construct a new array 2 dimensional array, with the $answers array and the itemID and then imploding this new array into my query.

not working.

i get this error:

Column count doesn't match value count at row 1

here is my code:

$records = array();

foreach($answers as $item_id => $text_value) {
$records[] = '("' . $item_id . '","' . $text_value . '")';
}
$sql = "INSERT INTO answers_tb (item_id, text_value) VALUES('" . implode(',', $records)." ')";
mysql_query($sql) or die(mysql_error());

many thanks for getting this far, even if you cant help.

regards,

Your Common Sense · Accepted Answer · 2011-03-30 11:52:15Z

3

Aside from particular problem, just to teach you a lesson.
You are working on your task in wrong way, upside down.

To put it straight, you're writing a program that builds some text.
So, first of all, it would be extremely handy to know, what particular text you want to get as a result. Thus, you have to write desired SQL query by hand and run in SQL console to see if it works okay or has any errors. If you experience any problem on this stage, you may ask under mysql tag, what query you need.

The rest is going to be easy. Upon finishing your program just print it's result out, and compare with example you've got before. Then correct your code to make them match. Repeat.

Done.

Always do your job this way and you will have no problem.

edited Mar 30, 2011 at 11:52

answered Mar 30, 2011 at 11:44

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

4 Comments

dapperwaterbuffalo Over a year ago

i know what I want as a result. I understand I can, inefficiently loop through my 1d array and run a query for each row with the same itemID. But I understand this would be stupidly ineficient. What I am having a problem with is building a 2d array with it like so: $itemId | $answers[1] etc. I don't kow if there is a better way to do this, as I am a beginner, so I was hoping I could be pointed in the right direction. thanks anyway.

Your Common Sense Over a year ago

@buymypies i know what I want as a result. - excellent! now step 2: print result of your program out.

dapperwaterbuffalo Over a year ago

my 2d array is now successfully constructed. But I get this error with my query:

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 ''("12","YES"),("12","NO")' at line 1

Your Common Sense Over a year ago

@buymypies it's not array but query being desired result. so - print it out.

vbence · Accepted Answer · 2011-03-30 12:35:47Z

0

This should work:

$records = array ();
$itemid = 3; // or any arbitrary value

foreach ($answers as $text_value) {
    $records[] = '("' . $itemid . '","' . $text_value . '")';
}
$sql = "INSERT INTO answers_tb (item_id, text_value) VALUES " . implode(',', $records);
mysql_query ($sql)
    or die (mysql_error ());

With your current solution you create a query like:

INSERT INTO answers_tb (item_id, text_value) VALUES (('1', 'asd'),('2', 'bsd'))

It shoould look like:

INSERT INTO answers_tb (item_id, text_value) VALUES ('1', 'asd'),('2', 'bsd')

So you don't need the extra parens.

edited Mar 30, 2011 at 12:35

answered Mar 30, 2011 at 11:35

vbence

20.4k10 gold badges74 silver badges119 bronze badges

11 Comments

dapperwaterbuffalo Over a year ago

right, Im doing this wrong, I just want the item_id to be the same for all. so VALUES ('1, 'asd'),('1','bsd'). As currently I am now getting this error:

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 ''("0","YES"),("1","NO")' at line 1

Your Common Sense Over a year ago

@buymypies time to follow my advise, dude :) Always know what result you want to get and you'll be okay

vbence Over a year ago

@buymypies exactly what item_id you want to give to these records? Because now you use the indexes in the array as id's. Where does that id come from? You can use: $records[] = '("3","' . $text_value . '")'; for example, but where does 3 come from?

dapperwaterbuffalo Over a year ago

i think i know where I am going wrong. the itemId is stored in a variable $itemid. so like you've done there, instead of the 3, I need to put the $itemid. thanks

vbence Over a year ago

@buymypies Yep, that might work. I edited my answer to clear up some more confusion (for example foreach can be used with only 2 parameters). - Please consider voting up the answers you find useful or even accept the best one.

|

Collectives™ on Stack Overflow

Problem constructing an array for an SQL query

2 Answers 2

4 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest