MySQL Syntax Error, can't seem to figure it out

Question

Ok, so I am building a database for a real estate company, and just got started with PHP about 3 or 4 months ago. I am having this error, "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 'VALUES ('3605 2nd Street', '', 'gfnfgnbfgn', 'MD', '21230', 'dfgbfgnbg', '', '',' at line 2".

Here is the code involved, there is a form, and I use the POST method to relay the info to this page.

<?php
session_start();
require('connect.php');
$address = $_POST["Address"];
$apt = $_POST["Apt #"];
$city = $_POST["City"];
$state = $_POST["State"]; 
$zip = $_POST["Zip"];
$tenant1 = $_POST["Tenant1"];
$tenant1phone = $_POST["Tenant 1 Phone"];
$tenant1email= $_POST["Tenant 1 Email"];
$tenant2 = $_POST["Tenant 2"];
$tenant2phone = $_POST["Tenant 2 Phone"];
$tenant2email= $_POST["Tenant 2 Email"];
$tenant3 = $_POST["Tenant 3"];
$tenant3phone = $_POST["Tenant 3 Phone"];
$tenant3email= $_POST["Tenant 3 Email"];
$daterented = $_POST["Date Rented"];
$rent = $_POST["Rent"];
$leaseexp= $_POST["Lease Expiration"];
$datedue = $_POST["Date Due"];
$vacant = $_POST["Vacant"];
$rentpaid= $_POST["Rent Paid"];
$amountdue = $_POST["Amount Due"];
$fee = $_POST["Fee"];
$secdeposit= $_POST["Security Deposit"];
$leadcert = $_POST["Lead Cert"];
$leadcertdate= $_POST["Lead Cert Date"];
$repairlimit= $_POST["Repair Limit"];
$key = $_POST["Key #"];
$bedrooms= $_POST["Bedrooms"];
$bathrooms = $_POST["Bathrooms"];
$heat = $_POST["Heat"];
$appliances= $_POST["Appliances"];
$owner = $_POST["Owner"];
$owneraddress = $_POST["Owner Address"];
$ownercity = $_POST["Owner City"];
$ownerstate= $_POST["Owner State"];
$ownerzip = $_POST["Owner Zip"];
$ownerphone1 = $_POST["Owner Phone 1"];
$ownerphone2 = $_POST["Owner Phone 2"];
$ownerother= $_POST["Owner Other"];
$owneremail = $_POST["Owner Email"];

$sql = ("INSERT INTO info (Address, Apt #, City, State, Zip, Tenant 1, Tenant 1 Phone,     
Tenant 1 Email, Tenant 2, Tenant 2 Phone, Tenant 2 Email, Tenant 3, Tenant 3 Phone,   
Tenant 3 Email, Date Rented, Rent, Lease Expiration, Date Due, Vacant, Rent Paid,   
Amount Due, Fee, Security Deposit,  Lead Cert, Lead Cert Date, Repair Limit, Key #, 
Bedrooms, Bathrooms,  Heat, Appliances, Owner, Owner Address, Owner City, Owner State, 
Owner Zip, Owner Phone 1, Owner Phone 2, Owner Other, Owner Email)

VALUES ('$address', '$apt', '$city', '$state', '$zip', '$tenant1', '$tenant1phone',   
'$tenant1email', '$tenant2', '$tenant2phone', '$tenant2email', '$tenant3', 
'$tenant3phone', '$tenant3email', '$daterented', '$rent', '$leaseexp', '$datedue', 
'$vacant', '$rentpaid', '$amountdue', '$fee', '$secdeposit', '$leadcert', 
'$leadcertdate', '$repairlimit', '$key', '$bedrooms', '$bathrooms', '$heat', 
'$appliances', '$owner', '$owneraddress', '$ownercity', '$ownerstate', '$ownerzip', 
'$ownerphone1', '$ownerphone2', '$ownerother', '$owneremail')");

var_dump($sql);
mysql_query($sql)or die(mysql_error());
if(mysql_affected_rows()>=1){
echo "<p>Entry Added</p>";
echo "<a href='index.php'>Home</a>";
}else{
echo "<p>Entry Not Updated</p>";
}

?>

I have pretty much the exact same issue on this page, which edits a row

<?php
session_start();
require('connect.php');
$id = $_GET["id"];
$address = $_POST["Address"];
$apt = $_POST["Apt #"];
$city = $_POST["City"];
$state = $_POST["State"];
$zip = $_POST["Zip"];
$tenant1 = $_POST["Tenant 1"];
$tenant1phone = $_POST["Tenant 1 Phone"];
$tenant1email= $_POST["Tenant 1 Email"];
$tenant2 = $_POST["Tenant 2"];
$tenant2phone = $_POST["Tenant 2 Phone"];
$tenant2email= $_POST["Tenant 2 Email"];
$tenant3 = $_POST["Tenant 3"];
$tenant3phone = $_POST["Tenant 3 Phone"];
$tenant3email= $_POST["Tenant 3 Email"];
$daterented = $_POST["Date Rented"];
$rent = $_POST["Rent"];
$leaseexp= $_POST["Lease Expiration"];
$datedue = $_POST["Date Due"];
$vacant = $_POST["Vacant"];
$rentpaid= $_POST["Rent Paid"];
$amountdue = $_POST["Amount Due"];
$fee = $_POST["Fee"];
$secdeposit= $_POST["Security Deposit"];
$leadcert = $_POST["Lead Cert"];
$leadcertdate= $_POST["Lead Cert Date"];
$repairlimit= $_POST["Repair Limit"];
$key = $_POST["Key #"];
$bedrooms= $_POST["Bedrooms"];
$bathrooms = $_POST["Bathrooms"];
$heat = $_POST["Heat"];
$appliances= $_POST["Appliances"];
$owner = $_POST["Owner"];
$owneraddress = $_POST["Owner Address"];
$ownercity = $_POST["Owner City"];
$ownerstate= $_POST["Owner State"];
$ownerzip = $_POST["Owner Zip"];
$ownerphone1 = $_POST["Owner Phone 1"];
$ownerphone2 = $_POST["Owner Phone 2"];
$ownerother= $_POST["Owner Other"];
$owneremail = $_POST["Owner Email"];

$sql = "UPDATE info
SET 
Address='$address', 
Apt #='$apt', 
City='$city',
State='$state', 
Zip='$zip',
Tenant 1='$tenant1', 
Tenant 1 Phone='$tenant1phone',
Tenant 1 Email='$tenant1email',
Tenant 2='$tenant2', 
Tenant 2 Phone='$tenant2phone',
Tenant 2 Email='$tenant2email', 
Tenant 3='$tenant3', 
Tenant 3 Phone='$tenant3phone',
Tenant 3 Email='$tenant3email',  
Date Rented='$daterented',
Rent='$rent', 
Lease Expiration='$leaseexp',
Date Due='$datedue', 
Vacant='$vacant',
Rent Paid='$rentpaid', 
Amount Due='$amountdue',
Fee='$fee', 
Security Deposit='$secdeposit',
Lead Cert='$leadcert', 
Lead Cert Date='$leadcertdate',
Repair Limit='$repairlimit', 
Key #='$key',
Bedrooms='$bedrooms', 
Bathrooms='$bathrooms',
Heat='$heat', 
Appliances='$appliances',
Owner='$owner', 
Owner Address='$owneraddress', 
Owner City='$ownercity',
Owner State='$ownerstate', 
Owner Zip='$ownerzip',
Owner Phone 1='$ownerphone1', 
Owner Phone 2='$ownerphone2', 
Owner Other='$ownerother',
Owner Email='$owneremail'
WHERE id='$id'";
var_dump($sql);
mysql_query($sql)or die(mysql_error());
if(mysql_affected_rows()>=1){
echo "<p>Entry Updated</p>";
echo "<a href='index.php'>Home</a>";
}else{
echo "<p>Entry Not Updated</p>";
}
?>

I've looked it over like 15 times, and can't seem to find the syntax error. The more I look at it, the more it looks like a giant wall of text. Maybe someone more experienced can take a look at it, and see it immediately. I have used almost the same code in a different site, without any problems, the only difference is that this has way more variables. Thanks in advance, this site and the great people on it have been a lifesaver in learning PHP!

Populate your query with a loop, not all by hand. It's cleaner, shorter, and less prone to errors. — Waleed Khan, Sep 06 '12 at 13:26
# is used to comment a line in MySQL, maybe you should remove those. — Henrique Ordine, Sep 06 '12 at 13:30
**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Sep 06 '12 at 13:32

score 1 · Answer 1 · edited Sep 06 '12 at 13:42

1

You need to wrap all the column names with a space in them with `` quotes.

Like this:

`Owner Address`

Best thing would probably be to rename all the column names with spaces, to replace the space with an underscore.

edited Sep 06 '12 at 13:42

fancyPants

50,732
33
89
96

answered Sep 06 '12 at 13:30

Kao

2,242
3
22
31

score 0 · Answer 2 · answered Sep 06 '12 at 13:38

This example:

select apt # from mytable

will be interpreted as select apt, everything including and after the # until EOL will be interpreted as a comment. You will notice SO's syntax highlighting picks this up too.

This would work:

select `apt #` from mytable

Or even:

select `apt #` from `mytable`

MySQL Syntax Error, can't seem to figure it out

2 Answers2