using arrays in mysql queries

[Marked]

Hi guys,

Im writing a script that inserts info into a certain category based on categories chosen from a form. But it has to be inserted into multiple categories.

Here's the code:
$category = $_POST['categoryid'];

$sqlInsertCategory = "INSERT INTO `jos_zoo_core_category_item` (
`catalog_id` ,
`category_id`,
`item_id`
)
VALUES (1, $category[$i], $item_id);";

for ($i = 0; $i < sizeof($category); $i++) {
mysql_query($sqlInsertCategory) or die(mysql_error());
}

Okay, so when this is run, there is a syntax error, which i know is this bit: "$category[$i]".

The script runs the query for each category chosen(if the user selects 2 categories, 2 queries will be run). Changing the value of the category chosen inside the query is what i cant do. In normal php $category[$i]works, but not in a mysql query. Is there some way i can store $category[$i] inside a normal value so i can use it the same way in the query?

Thanks in advance, and sorry if my post is hard to understand. This is my first post, and first script.

[jatar_k]

Welcome to WebmasterWorld Marked,

array notation as such never works inside double quotes, just concatenate it

$sqlInsertCategory = "INSERT INTO `jos_zoo_core_category_item` (`catalog_id` ,`category_id`, `item_id`) VALUES (1, " . $category[$i] . ", $item_id);";

[Marked]

Thanks for your reply jatar_k,

I played around with this for a bit. I still cannot get it working...

Using: VALUES (1, " . $category[$i] . ", $item_id);";
I get a SQL syntax error.

Using: VALUES (1, '" . $category[$i] . "', $item_id);";
(added ''), it just returns 0 each time, and of course that gives a duplicate entry error. I think that's because numeric values arn't supposed to be contained in single quotation marks, but if that were so, wouldn't i get a syntax error?

I tested the code using echo $category[$i]; and it returns the values as it should, so the problem lies within returning the values in the query.

[omoutop]

try single and double quotes like this:
'" .$category[$i]."'

if this still doesn't work, go to the easy solution
$a = $category[$i];
insert/update $a

[Marked]

This is weird,

I tested both your suggestions.

Using " . $category[$i] . " or $a = $category[$i]; both return the values they should, but for some strange reason I am still getting a syntax error. Its strange because I printed the value, it comes up just as it should. For example, in one of the tests the value for the first query to be run was 5. If it returns 5, its exactly the same as if I typed in 5 into the query in the first place, so why the syntax error?

[jatar_k]

it would help if you posted the error, the error usually points to where the problem starts. Is it the same error every time, at the same place? all of our suggestions are correct given what we know, so we are obviously missing something.

the rule is

if the column is int, no single quotes around the value but I believe you can quote it without error
if it is pretty much anything else then quote it

also you can echo your query before even sending it to mysql, that allows you to take a look at it and also test the actual query outside of the code, either at the command line or in some mysql gui type thing.

[Marked]

Ah, sorry about that. I printed the query before it was ran, this is what came up:

5
INSERT INTO `jos_zoo_core_category_item` ( `catalog_id` , `category_id`, `item_id` ) VALUES (1,,174);
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 '174)' at line 6

5 is the variable($category[$i]), which obviously can be printed, but it does not show up in the query. As you can see, it is just left blank.

Any ideas why the variable isn't returning the value in the query? I thought it could be my mysql version(im using xampp), or an error in the code that runs the query. But i have no idea

[jatar_k]

so this echoes what?

$sqlInsertCategory = "INSERT INTO `jos_zoo_core_category_item` (`catalog_id` ,`category_id`, `item_id`) VALUES (1, " . $category[$i] . ", $item_id)";
echo '<p>the var is: ',$category[$i],'<p>and the query is: ',$sqlInsertCategory;

and this?

$a = $category[$i];
$sqlInsertCategory = "INSERT INTO `jos_zoo_core_category_item` (`catalog_id` ,`category_id`, `item_id`) VALUES (1, $a, $item_id)";
echo '<p>the var is: ',$category[$i],'<p>a is: ',$a,'<p>and the query is: ',$sqlInsertCategory;

[Marked]

the first one:
the var is: 6

and the query is: INSERT INTO `jos_zoo_core_category_item` (`catalog_id` ,`category_id`, `item_id`) VALUES (1, , 176)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 ' 176)' at line 1

And the second:
the var is: 6

a is: 6

and the query is: INSERT INTO `jos_zoo_core_category_item` (`catalog_id` ,`category_id`, `item_id`) VALUES (1, , 180)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 ' 180)' at line 1

[jatar_k]

well that definitely makes no sense at all, the vars are set if you tested exactly what I posted without anything else in the middle between the query construction and the echoes

no clue frankly, something's there that you aren't considering or noticing, or we aren't

i would remove the backticks, then recheck all varnames, maybe missing something there, maybe that var isn't being set in the right place, maybe it is blank

no clue

how long is the whole script? sticky it to me, I know there's something being missed.

[idfer]

Hi Marked, i'm not sure if you've already corrected it, but there's a logic error in the code posted originally: you're creating the SQL statement ($sqlInsertCategory) outside the for loop. At that point $i isn't defined and $category[$i] returns an empty string.

Make sure the assignment statement for $sqlInsertCategory is inside the for loop, e.g.:

for ($i = 0; $i < sizeof($category); $i++) {
$sqlInsertCategory = "INSERT INTO `jos_zoo_core_category_item` (
`catalog_id` ,
`category_id`,
`item_id`
)
VALUES (1, ".$category[$i].", $item_id)";
mysql_query($sqlInsertCategory) or die(mysql_error());
}

Better yet:

foreach ($category as $category_id) {
$sqlInsertCategory = "INSERT INTO `jos_zoo_core_category_item` (
`catalog_id` ,
`category_id`,
`item_id`
)
VALUES (1, $category_id, $item_id)";
mysql_query($sqlInsertCategory) or die(mysql_error());
}

Hope this helps.

[Marked]

idfer, your suggestion worked. The script works exactly as it should.

Thank you very much for your replies and helping me. jatar_k and idfer, I will give you credit on my website for your help.