adding multiple select results to mysql

[1internet]

I am trying to add multple selections from a drop down box to a mysql database. The user makes multple selections, and they become an array, then I implode the array to a string and add it to the database. But also if the page has an error, I want to keep those selection values there when the page refreshes.

 

Here is my my form:

 

<select name="category[]" multiple="multiple">

<option disabled <?php if($category == 0) {echo 'selected="selected"';} ?> value="select">Select a Category</option>

<option disabled >-------------------------------</option>

<?php

$cateogry = is_array($category) ? $category : explode(',',$category);

$categoryResult = mysql_query("SELECT * FROM `blog_categories` ORDER BY name", $connect);

while($categoryRow = mysql_fetch_assoc($categoryResult)){

$categoryId = $categoryRow['id'];

$categoryName = $categoryRow['name'];

if ($id != $categoryId) {

?>

<option value="<?php echo $categoryRow['id']; ?>" <?php if (in_array($categoryId, $category)) echo 'selected="selected"';?>><?php echo $categoryName; ?></option>

<?php }} ?>

</select>

 

And here is the code for adding to the database, I run it through the foreach loop to sanitize it, I then implode it to a string and add it to the database:

 

if(isset($_POST['submitPage'])) {

$category = $_POST['category'];

foreach ($category as $cat) {

$cat = (int)($cat);

$category[] = $cat;

}

if($category == 0) {

$error[] = 'Please select a category';

}

$category = implode(',', $category);

if(count($error) == 0) {

$addResult = mysql_query("INSERT INTO `blog`(`category`)

VALUES ('$category')", $connect);

}

 

I have been testing lots of different variations, and searching how this is done, but I just can't work it out. Also when I do manage to add something to the variable it seems to add the value twice. And when the page refreshes the drop down list items are all errors,

in_array() expects parameter 2 to be array, string given in add-blog.php on line 142

 

So how do you add multiple drop down lists to a database properly?

Edited January 6, 2013 by 1internet

[PFMaBiSmAd]

So, you marked the thread as being solved. Does that mean you don't need any help with this?

[1internet]

Sorry, I thought I hard worked it out, but after more and more thorough testing, I think I would rather bang my head against the wall!!!

So how do you add multiple options to a database? What are the proper methods for sanitization, etc?

[scootstah]

You have a few issues with your code. The biggest of which is that you are trying to use $category as both an int and an array at the same time. For example, you do foreach ($category as $cat) {, and then if($category == 0) {. That logic should have taken place before the foreach - since if $category is not an array, you'll get an error trying to use it in a foreach.

 

You have a typo on this line, possibly leading to your "in_array" error: $cateogry = is_array($category) ? $category : explode(',',$category);

 

Your database query is wrong. When you want to insert multiple rows, you need multiple sets. That would look like: INSERT INTO table (column) VALUES (set1), (set2), (set3)

 

Currently, yours would look like: INSERT INTO table (column) VALUES (set1,set2,set3).

 

See the difference? To fix this you'd need to:

Change

$category[] = $cat;

to

category[] = "($cat)";

 

Change

VALUES ('$category')", $connect);

to

VALUES $category", $connect);

Edited January 7, 2013 by scootstah