[RESOLVED] Foreach with INSERT query Help

focus310

Hi,

I'm trying to learn about arrays and how to access them. I have a form which allows a person to select multiple items. When I do a "foreach" and echo the results of the form, the results appear correctly.

When I try to insert the results into a table, I receive the following error message:
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 'York)' at line 1

Can someone help me with this INSERT query?

Here is the code for the form:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<title></title>
</head>
<body>
<form action="checkbox-test.php" method="post">
<p>Select City:</p>
<p><input type="checkbox" name="place[]" value="New York">New York</p>
<p><input type="checkbox" name="place[]" value="Chicago">Chicago</p>
<p><input type="checkbox" name="place[]" value="London">London</p>
<p><input type="checkbox" name="place[]" value="San Francisco">San Francisco</p>
<p><input type="submit" value="submit"></p>

</form>
</body>
</html>

====
Here is the PHP script which processes the form:

<?php


require_once ('mysql_connect.php'); 

foreach($_POST['place'] as $pvalue){
$query = "INSERT INTO checkbox_test(place) VALUES ($pvalue)";
$result = mysql_query($query) or die(mysql_error()); 

//echo "$pvalue <br>";
}
?>

==
My table name is checkbox_test and it contains two fields. The first field is ID which is primary and auto-incremented. The second field is place.

Thanks for the help.

reddrum

I think the refrence to place should be place[0], place[1] ect.?


$i = 0;

foreach($_POST['place[$i]' as $pvalue){

$query = "INSERT INTO checkbox_test(place[$i]) VALUES ($pvalue)";

$result = mysql_query($query) or die(mysql_error());

++$i
}

NogDog

You need to quote string literals in SQL just as you do in PHP. Also, you should never trust external data, so at the very least escape the inputs:

$pvalueSanitized = mysql_real_escape_string($pvalue);
$query = "INSERT INTO checkbox_test(place) VALUES ('$pvalueSanitized')";

focus310

Hi,

Placing quotes did the trick.

Thanks.

reddrum

I think I get it now but, what is the "checkbox_test(place)" a field?

focus310

Hi,

checkbox_test is the name of the table
(place) is the name of the field

My table contains two fields: id which is the primary key and it auto-increments and place which stores the values.

reddrum

So checkbox_text(place) is the same as checkbox_text.place?

focus310

Yes, it is the same though I do not write my queries that way.

To insert my data I use the following syntax:
INSERT INTO <tablename> (field1, field2, field3) VALUES (value1, value2,value3)

By writing the query this way, you reference the table once. If you get an error message regarding your query, it is a lot easier to troubleshoot this way. The field names are lined up with the values for those fields. Otherwise, if you use the syntax you mentioned and let say you have 50 fields, you are typing the table 50 times (once for each field). This can lead to a lot of typos and other mistakes.

Also, when I need to query more than one table together, rather than typing the full table name with the field (<tablename.field>), I create an alias for the table.

So let say you have two tables, employee and department. To query against these tables I would write something like this:

SELECT a.employee_id, a.employee_name, b.department 
FROM employee a, department b
WHERE a.employee_id = b.employee_id;

You see that using the aliases of "a and b" for the tables requires less typing and it is easier to read.

This is my personal preference. I've coded my queries this way for years and haven't gotten into any issues.

Then coding is a matter of preference and what you are comfortable in doing.

reddrum

Good stuff!
Thanks Focus310!

focus310

Anytime. I hope it will come in handy for you.