Form Send Saving to Database

[chadsten]

Ok guys. I have a form page that writes data to the selected row on the table. I am getting the following 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 'usage, apt_date_1, apt_time_1, apt_place_1, apt_auto_1, apt_ref_1, apt_results_' at line 32'

Here are the scripts:


FORM PAGE:

<?// START OF DATABASE QUERIES=============

$ready_form_data="SELECT *
FROM properties
WHERE lead_name='$lead_name'";
$get_data = mysql_query($ready_form_data) or die (mysql_error());
$data= mysql_fetch_array($get_data) or die(mysql_error());


// END OF DATABASE QUERIES=============?>

<form method="post" action="blank.php?type=write_data&amp;lead_name=<? echo $lead_name; ?>">
<div id="leftColumn" class="t1">
CHUNK OMITTED FOR SPACE, NOTHING IMPORTANT

<label>State:
<select name="state" id="state" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="State" dir="ltr" value="<? echo $data['state']; ?>">
LIST OMITTED FOR SPACE
</select>
</label>

<label>Zip:
<input type="text" size="17" name="zip" id="zip" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['zip']; ?>" />
</label>
<br /><br />
</div>

<div id="contactInfo">
<div class="h1" align="center">Contact Information</div><br />
<div id="leftContact" align="right">
OMMITED FOR SPACE
</div>
<div style="clear:both;"></div>
<br /><br />
</div>
<div id="saleBox">

<div class="h1" align="center">Sale Information</div><br />
<div id="saleTopRow">
<label>Sold?:
<select name="is_sold" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Is Sold" dir="ltr" value="<? echo $data['sold']; ?>">
<option value="y">Yes</option>
<option value="n">No</option>
</select>
</label>
OMITTED FOR SPACE
<br /><br />
</div>


<div id="interestBox">

<div class="h1" align="center">Interest Information</div><br />
<label>Acreage:
<select name="acreage" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Acreage Range" dir="ltr" value="<? echo $data['acreage']; ?>" >
<option value="any">Any Size</option>
<option value="1">1 or below</option>
<option value="2">1.1 - 2</option>
<option value="3">2.1 - 3</option>
<option value="4">3.1 - 4</option>
<option value="5">4.1 - 5</option>
<option value="6">5.1 or above</option>
</select>
</label>
<label>Price:
<select name="price" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Price Range" dir="ltr" value="<? echo $data['price']; ?>" >
<option value="any">Any Price</option>
<option value="10">10,000 or below</option>
<option value="20">10,001 - 20,000</option>
<option value="30">20,001 - 30,000</option>
<option value="40">30,001 - 40,000</option>
<option value="50">40,001 - 50,000</option>
<option value="60">50,001 or above</option>
</select>
</label>
<br /><br />
<label>Looking Since:
<input type="text" size="25" name="looking_since" id="looking_since" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['looking_since']; ?>" />
</label>
<br /><br />
<label>Type:
<select name="lot_type" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Type" dir="ltr" value="<? echo $data['lot_type']; ?>" >
<option value="Level">Level</option>
<option value="Views">Views</option>
<option value="Open">Open</option>
<option value="Mtns">Mtns</option>
<option value="Water">Water</option>
<option value="Woods">Woods</option>
<option value="Other">Other</option>
</select>
</label>
<label>Usage:
<select name="usage" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Usage" dir="ltr" value="<? echo $data['usage']; ?>" >
<option value="Build">Build</option>
<option value="Fish">Fish</option>
<option value="Hunt">Hunt</option>
<option value="Ski">Ski</option>
<option value="Invest">Invest</option>
<option value="Retire">Retire</option>
<option value="Vacation">Vacation</option>
<option value="Other">Other</option>
</select>
</label>
<br /><br />
</div>

<div id="aptBox">
<div class="h1" align="center">Appointment Information</div><br />
<div id="leftApt">Appointment 1<br />
<label>Date:
<input type="text" size="20" name="apt_date_1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_date_1']; ?>" />
</label>
<br />
<label>Time:
<input type="text" size="20" name="apt_time_1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_time_1']; ?>" />
</label>
<br />
<label>Place:
<input type="text" size="20" name="apt_place_1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_place_1']; ?>" />
</label>
<br />
<label>Auto:
<input type="text" size="20" name="apt_auto_1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_auto_1']; ?>" />
</label>
<br />
<label>Rep:
<input type="text" size="20" name="apt_ref_1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_ref_1']; ?>" />
</label>
<br />
<label>Results:<br />
<select name="apt_results_1" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Results" dir="ltr" value="<? echo $data['apt_results_1']; ?>" >
<option value="1">Sale</option>
<option value="2">Show/No Sale</option>
<option value="3">Reset</option>
<option value="4">Cancelled</option>
</select>
</label>
</div>
<div id="rightApt">Appointment 2<br />
<label>Date:
<input type="text" size="20" name="apt_date_2" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_date_2']; ?>" />
</label>
<br />
<label>Time:
<input type="text" size="20" name="apt_time_2" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_time_2']; ?>" />
</label>
<br />
<label>Place:
<input type="text" size="20" name="apt_place_2" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_place_2']; ?>" />
</label>
<br />
<label>Auto:
<input type="text" size="20" name="apt_auto_2" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_auto_2']; ?>" />
</label>
<br />
<label>Rep:
<input type="text" size="20" name="apt_ref_2" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" value="<? echo $data['apt_ref_2']; ?>" />
</label>
<br />
<label>Results:<br />
<select name="apt_results_2" size="1" tabindex="<? echo ($tab_index+1); $tab_index++; ?>" title="Results" dir="ltr" value="<? echo $data['apt_results_2']; ?>">
<option value="1">Sale</option>
<option value="2">Show/No Sale</option>
<option value="3">Reset</option>
<option value="4">Cancelled</option>
</select>
</label>
</div>
<div style="clear:both;"></div>
<br /><br />
</div>
</div>
<div id="notesRow">
<div class="h1" align="center">Other Notes</div><br />
Notes:<br />
<textarea name="notes" cols="100" rows="10" class="<? echo $input_class; ?>" value="<? echo $data['notes']; ?>">
</textarea>
</div>
<div id="goButton" class="t1">
<input name="submit" type="submit" value="Submit Now!" />
</div>

</form>


HERE IS THE FILE THAT WRITES TO THE DB:

<?
$insert="INSERT INTO properties (notes,
acreage,
price,
last_apt,
sold,
phone_number,
date_created,
name_one,
name_two,
relationship,
address,
city,
state,
zip,
home_phone,
cell_phone,
work_phone,
fax, email,
url,
purchase_date,
purchase_rep_1,
purchase_rep_2,
purchase_lot,
purchase_price,
purchase_project,
rep_name,
ext,
source,
ref_by,
looking_since,
lot_type,
usage,
apt_date_1,
apt_time_1,
apt_place_1,
apt_auto_1,
apt_ref_1,
apt_results_1,
apt_date_2,
apt_time_2,
apt_place_2,
apt_auto_2,
apt_ref_2,
apt_results_2)
VALUES
('$_POST[notes]',
'$_POST[acreage]',
'$_POST[price]',
'$_POST[last_apt]',
'$_POST[is_sold]',
'$_POST[phone_number]',
'$_POST[date_created]',
'$_POST[name_one]',
'$_POST[name_two]',
'$_POST[relationship]',
'$_POST[address]',
'$_POST[city]',
'$_POST[state]',
'$_POST[zip]',
'$_POST[home_phone]',
'$_POST[cell_phone]',
'$_POST[work_phone]',
'$_POST[fax]',
'$_POST[email]',
'$_POST[url]',
'$_POST[purchase_date]',
'$_POST[purchase_rep_1]',
'$_POST[purchase_rep_2]',
'$_POST[purchase_lot]',
'$_POST[purchase_price]',
'$_POST[purchase_project]',
'$_POST[rep_name]',
'$_POST[ext]',
'$_POST[source]',
'$_POST[ref_by]',
'$_POST[looking_since]',
'$_POST[lot_type]',
'$_POST[usage]',
'$_POST[apt_date_1]',
'$_POST[apt_time_1]',
'$_POST[apt_place_1]',
'$_POST[apt_auto_1]',
'$_POST[apt_ref_1]',
'$_POST[apt_results_1]',
'$_POST[apt_date_2]',
'$_POST[apt_time_2]',
'$_POST[apt_place_2]',
'$_POST[apt_auto_2]',
'$_POST[apt_ref_2]',
'$_POST[apt_results_2]')
WHERE lead_name='$_GET[lead_name]'";


mysql_query($insert) or die (mysql_error());
?>


Please help me somebody...I spent a while on this, and really want to move forward. Thanks a bunch! ( Chazzy, maybe you can help? :) )

[chazzy]

1. try changing all of these

'$_POST[apt_auto_2]',

to something like
'".$_POST['apt_auto_2']."',

2. Insert doesn't have a where clause.

3. try printing out the sql first


or die(" Failed to insert : ".mysql_error()." SQL: ".$insert);

[chadsten]

I actually just stumbled on UPDATE tablename SET column=value, column2=value2, etc...so I hope that I have resolved it. I will post back shortly with my results. You rock Chazzy!

[chazzy]

just as a comment (more design than anything), but that table's way too big.

[chadsten]

just as a comment (more design than anything), but that table's way too big.


Yeah, it is pretty large. The thing is, the way I have done permissions, the associated user(s) are stored on each row...but I am open to ANY ideas that will make this/me better or more efficient. How would I reduce the size of the table while still retaining my function? Source code available if needed. And thanks for all the help man, you are helping me conquer one of the big things in web.

[chazzy]

for one, you need to work on normalizing the data model. it looks like a lot of the data should be in other tables (such as a table for appointments, a table for contact info... etc)

the other thing is that your query is open to attacks. you should validate your data before you do an update.