MySQL and PHP multiple checklist db insert

Question

I am trying to insert the value of this multiple checklist into the db column. This code not working. Can anyone spot the problem?

My database consists of a table called "colors" and one column called "color".

<?php
// connect to database
require "mysql_connect.php";
?>


<?php
// get value from the form
$color = $_POST['color'];

foreach($_POST['color'] as $colors){
$insert = mysql_query("INSERT INTO colors (color) VALUES ('$color')");
}
?>

<form action="add_color.php" method="post" enctype="multipart/form-data" name="colorform"     id="colorform">

<input type="checkbox" name="color[]" value="black" /> Black
<input type="checkbox" name="color[]" value="red" /> Red
<input type="checkbox" name="color[]" value="blue" /> Blue
<input type="checkbox" name="color[]" value="white" /> White

<input name="submit" type="submit" value="Add color" />

</form>

Thanks

Are you trying to insert into 4 different tables ($colors)? And where is the $color value defined? It seems that you may just have your variable names mixed up. — swayne
– swayne, Commented Mar 7, 2012 at 15:33

Noor · Accepted Answer · 2012-03-07 15:46:47Z

This is a nice way to add your colors

<?php

        require "mysql_connect.php";
    // connect to database
    $colors=array();
    // get value from the form
     if (isset($_POST['color']))  $colors = $_POST['color'];

    foreach($colors as $color)
    {
        mysql_query ("INSERT INTO colors ('color') VALUES ('$color')");
    }
?>

<form action="add_color.php" method="post" enctype="multipart/form-data" name="colorform"     id="colorform">

<input type="checkbox" name="color[]" value="black" /> Black
<input type="checkbox" name="color[]" value="red" /> Red
<input type="checkbox" name="color[]" value="blue" /> Blue
<input type="checkbox" name="color[]" value="white" /> White

<td><input name="submit" type="submit" value="Add color" />
</form>

if (isset($_POST['color'])) This condition is important because it will prevent an indexing error in case the array is empty

$colors=array(); Also, do declare your variables to prevent getting undeclared varibles, previously, in your code, this will happen if the user does not specify any color

Remember PHP is server-side and thus getting errors on PHP create loopholes for attacks. Try to read about PHP Best Practices, Its very impotant

Hopes it helps :-)

Alan Moore · Accepted Answer · 2012-03-07 15:50:41Z

1

I would also suggest that you sanitize your from inputs before inserting into your database. You don't mention what type your color column is, could be a mismatch there as well.

When you say INSERT INTO $colors -- is that what you mean? Your table name is variable? You should probably have a proper table name in place of $colors.

In addition, you have used $color which I don't see defined, you probably meant to use $colors so it should be more like this:

INSERT INTO tblColors (color) VALUES ('$colors')

To check your return value to see what error you're getting:

$query = "INSERT INTO tblColors (color) VALUES ('$colors')";
$insert = mysql_query($query) or die("A MySQL error has occurred.<br />Your Query: " . $query . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());

edited Mar 7, 2012 at 15:50

answered Mar 7, 2012 at 15:28

Alan Moore

6,5756 gold badges58 silver badges69 bronze badges

6 Comments

Sarah Hamed Over a year ago

My table name is "colors" I meant "colors" instead of "$colors" (typing error). I changed the name as you suggested but it is still not working :(

Alan Moore Over a year ago

I would suggest echoing your query strings to make sure you are putting what you think you're putting in. I'm assuming you've setup your database, and your database connection correctly. You might also check for mysql errors on your query as well.

Sarah Hamed Over a year ago

My database consists of a table called "colors" and one column called "color".

Alan Moore Over a year ago

What is the type of color column?

Alan Moore Over a year ago

Have you echoed your $colors to see what you're getting there? I think your best bet at this time is to check your return value to determine the error.

|

Odinn · Accepted Answer · 2012-03-07 15:28:16Z

0

$insert = mysql_query("INSERT INTO $colors (color) VALUES ($color)");

Change it to:

$insert = mysql_query("INSERT INTO colors_table_name (color) VALUES ($color)");

Also, please check the return value of insert, maybe you are getting errors? First obvious problem was that the table name was being replaced with the color because of the variable, is this the desired effect?

answered Mar 7, 2012 at 15:28

Odinn

8085 silver badges23 bronze badges

2 Comments

Karoly Horvath Over a year ago

problem spotted, but that's still an SQL injection vulnerability

Sarah Hamed Over a year ago

I changed the name as you suggested but it is still not working :(

Husain Basrawala · Accepted Answer · 2012-03-07 15:28:26Z

0

<?php
// connect to database
require "mysql_connect.php";
?>


<?php
// get value from the form
$colors = $_POST['color'];

foreach($colors as $color){
    $insert = mysql_query("INSERT INTO colors (color) VALUES ($color)");
}


<form action="add_color.php" method="post" enctype="multipart/form-data" name="colorform"     id="colorform">

<input type="checkbox" name="color[]" value="black" /> Black
<input type="checkbox" name="color[]" value="red" /> Red
<input type="checkbox" name="color[]" value="blue" /> Blue
<input type="checkbox" name="color[]" value="white" /> White

<td><input name="submit" type="submit" value="Add color" />

</form>

answered Mar 7, 2012 at 15:28

Husain Basrawala

1,75115 silver badges22 bronze badges

Comments

Marc B · Accepted Answer · 2012-03-07 16:00:29Z

0

You've got your variables backwards, SQL syntax errors, SQL injection vulnerabilities, and a total lack of error handling

$color = $_POST['color'];   <---stuff the POST data array into $color

foreach($_POST['color'] as $colors){   <--- loop over the POST data directly

$insert = mysql_query("INSERT INTO colors (color) VALUES ($color)");
                                                          ^^^^^^---insert the array
                                                          ^^^^^^---no quotes

You use $colors (with an S) to store the individual colors, but then insert $color, which is an array.

Never assume that a query has suceeded. If you'd have the bare minimum or die(...) error handling, you've have seen why your queries were failing:

foreach($_POST['color'] as $color) {
    $safe_color = mysql_real_escape_string($color);
    $result = mysql_query("INSERT INTO colors (color) VALUES ('$safe_color');") or die(mysql_error());
}

edited Mar 7, 2012 at 16:00

answered Mar 7, 2012 at 15:46

Marc B

362k44 gold badges433 silver badges508 bronze badges

5 Comments

Sarah Hamed Over a year ago

Thanks that sorted out some problems. Now my problem is when I echo $color it only prints out one value. For example, if I check black and red, it only prints red. If I select black and red and blue, it only prints blue. But it does not add anything to the database either way.

Marc B Over a year ago

Where are you doing the echo? Inside the loop? It should print every color that. Make sure that you are getting the right values from the form by doing a var_dump($_POST) somewhere and see what shows up.

Sarah Hamed Over a year ago

After selecting the first 3 colors and submitting I got this: ["color"]=> array(3) { [0]=> string(5) "black" [1]=> string(3) "red" [2]=> string(4) "blue" }

Marc B Over a year ago

Ok. that's what you should be getting. So most likely you've got an error in the loop, or are doing the output in the wrong spot so you only see a single value.

Sarah Hamed Over a year ago

Ah. Now it did store in the database. But it only stored 1 color which is "black". It was meant to store black, red, blue.

Collectives™ on Stack Overflow

MySQL and PHP multiple checklist db insert

5 Answers 5

Comments

6 Comments

2 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

6 Comments

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related