[RESOLVED] help with inserting data into mysql

dcampbell18

Hello,

Pretty new at PHP and I'm having a heck of a time trying to get data inserted into the mysql database. I'm just learning, so I created a very basic form of 4 checkboxes called color[] and the values are red, blue, green, yellow. Then a submit button at the bottom of the checkboxes. I'm trying to figure out how to get which ever colors the user checks off to insert into my database each in their own row. I've played with implode and can get all the data in one row separated by commas, but I'd really like to get 'red' in one row, then 'blue' in the next row, etc. depending on what the user checks off. I've looked at so many examples on the web, that I think I am more confused now than I was when I started😕 ...Can anyone please help me? Here is what I have so far - it doesn't work though. Thanks so much!

<?php

$color = $_POST['color'];
$self = $_SERVER['PHP_SELF'];

#connect to mysql
$conn=@mysql_connect( "localhost", "root", "password" )
	or die( "Error connecting to MySQL" );

#select specified database
$rs = @mysql_select_db( "test", $conn )
	or die( "Error connecting to db" );

$array = array($_POST['color']);

for($i=0;$i<count($array);$i++){ 

$query = "insert into colors set color = '.$array[$i]'"; 
$rs = mysql_query($query)
	or die ("could not execute query"); 
} 


?>

bradgrafelman

To reduce overhead, I would INSERT the rows using one query, like so:

if(!empty($_POST['color']) && is_array($_POST['color'])) {
	$_POST['color'] = array_map('mysql_real_escape_string', $_POST['color']);
	$colors = implode('\'), (\'', $_POST['color']);

$query = 'INSERT INTO colors (color) VALUES (\'' . $colors . '\')';
$rs = mysql_query($query);
} else {
	// they didn't specify a color, or there was a problem with the data type
}

Note that I used [man]array_map/man to apply [man]mysql_real_escape_string/man to all of the elements in the $_POST['color'] array. You should never place user-supplied data directly into a SQL query without sanitizing it first, else you're leaving yourself vulnerable to SQL injection attacks.

dcampbell18

It works! I can't thank you enough! One question though: Once the color is added to the database, I cannot reload the form and add another row with that color name. For example: Initially, I checked off red and blue. Red and blue were added to the database. Then I checked off red blue and yellow. Nothing was added to the database. If I just check off green and yellow, they are added. My real form (after I understand this better) will have a list of checkboxes that the user will check off jobs that they have completed. They will do this every day and sometimes may complete the same job on two different days. How can I get this to allow duplicates in the database? (on the real one, there will be an id field that auto-increments to keep records unique). Thank you so much! You have restored my hope in this project!

dcampbell18

I added the 'ID' field to my colors table and it is accepting all entries now! Thank you soooooo much! I truly appreciate it! You don't know how long I've been working on this (too embarrassed to say)! Thanks!

bradgrafelman

Glad I could help; don't forget to mark this thread resolved.

chriscapetown

Please respond in this thread rather than having the same topic in multiple threads. -- MOD

Hey, this topic is solved but it applies to what I want to do.

I have a similar procedure, need to put different checkbox values in different corresponding rows. This part of things is fine.

Thing is, I have about 5 pages, first page has a couple of fields and all the other pages have different checkboxes on them, pages going in sequence, like you can see in the link below:

http://taxmon.designrobot.co.za/dialup/step1.html

The method im thinking of, is to insert the field values into the table, and then use an update query on the same ID for the checkboxes, but not sure how to go about it!

Really desperate to get this working, would really appreciate any advice or comments.

Thanks alot
Chris