What's wrong with this code?
12-02-2005, 10:43 PM
|
What's wrong with this code?
|
Posts: 626
|
Can someone help me with this code please?
I am getting the following error when trying to save a client:
"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 ' aaaa, NULL, aaaaa, NULL, NULL, 1900-01-01, xxx-xxx-xxx, Driver\'s License, NULL' at line 2'
Here is code for saveclient.php:
Code:
<?php
$conn = mysql_connect('localhost', 'root', 'password') or die(mysql_error());
mysql_select_db("Clientbase") or die(mysql_error());
if (!array_key_exists('InsuranceClient', $_POST)) { $InsuranceClient = 0;} else {$InsuranceClient = 1; }
if (!array_key_exists('SolicitInsurance', $_POST)) { $SolicitInsurance = 0;} else {$SolicitInsurance = 1;}
if (!array_key_exists('MutualFundClient', $_POST)) {$MutualFundClient = 0; } else {$MutualFundClient = 1;}
if (!array_key_exists('SolicitMutualFunds', $_POST)) { $SolicitMutualFunds = 0; } else { $SolicitMutualFunds = 1;}
if (!array_key_exists('MortgageClient',$_POST)) { $MortgageClient = 0; } else {$MortgageClient = 1;}
if (!array_key_exists('$SolicitMortgages', $_POST)) { $SolicitMortgages = 0; } else {$SolicitMortgages = 1;}
if (!array_key_exists('$Smoker', $_POST)) { $Smoker = 0; } else {$Smoker = 1;}
if (!array_key_exists('$PrimaryContact', $_POST)) { $PrimaryContact = 0; } else {$PrimaryContact = 1;}
foreach($_POST as $key => $value) {
// if ((gettype($value) == "string") && !NULL) {
// $value = "'".addslashes($value)."'";
// }
if (empty($value)) { $value = 'NULL'; }
switch($key) {
case "ClientNumber" : $ClientNumber = $value; break;
case "ContactStatus" : $ContactStatus = $value; break;
/* case "InsuranceClient" : $InsuranceClient = $value; break;
case "SolicitInsurance" : $SolicitInsurance = $value; break;
case "MutualFundClient" : $MutualFundClient = $value; break;
case "SolicitMutualFunds" : $SolicitMutualFunds = $value; break;
case "MortgageClient" : $MortgageClient = $value; break;
case "SolicitMortgages" : $SolicitMortgages = $value; break;
*/ case "Title" : $Title = $value; break;
case "FirstName" : $FirstName = $value; break;
case "MiddleName" : $MiddleName = $value; break;
case "LastName" : $LastName = $value; break;
case "GreetingName" : $GreetingName = $value; break;
case "MailingName" : $MailingName = $value; break;
case "SIN" : $SIN = $value; break;
case "IDType" : $IDType = $value; break;
case "IDNumber" : $IDNumber = $value; break;
// case "Smoker" : $Smoker = $value; break;
// case "PrimaryContact" : $PrimaryContact = $value; break;
case "HomeAddress1" : $HomeAddress1 = $value; break;
case "HomeAddress2" : $HomeAddress2 = $value; break;
case "HomeCity" : $HomeCity = $value; break;
case "HomeProv" : $HomeProv = $value; break;
case "HomePostal" : $HomePostal = $value; break;
case "HomePhone" : $HomePhone = $value; break;
case "CellPhone" : $CellPhone = $value; break;
case "Email" : $Email = $value; break;
case "WorkAddress1" : $WorkAddress1 = $value; break;
case "WorkAddress2" : $WorkAddress2 = $value; break;
case "WorkCity" : $WorkCity = $value; break;
case "WorkProv" : $WorkProv = $value; break;
case "WorkPhone" : $WorkPhone = $value; break;
case "WorkPostal" : $WorkPostal = $value; break;
case "WorkExt" : $WorkExt = $value; break;
case "WorkFax" : $WorkFax = $value; break;
case "Income" : $Income = $value; break;
case "Employer" : $Employer = $value; break;
case "Occupation" : $Occupation = $value; break;
case "BirthMonth" : $MM = $value; break;
case "BirthDay" : $DD = $value; break;
case "BirthYear" : $YYYY = $value; break;
}
}
$DOB = date($YYYY.'-'.$MM.'-'.$DD);
$sql = "INSERT INTO Clientbase (ClientNumber, ContactStatus, InsuranceClient, SolicitInsurance, MutualFundClient, SolicitMutualFunds, MortgageClient, SolicitMortgages, Title, FirstName, MiddleName, LastName, GreetingName, MailingName, DOB, SIN, IDType, IDNumber, Smoker, PrimaryContact, HomeAddress1, HomeAddress2, HomeCity, HomeProv, HomePostal, HomePhone, CellPhone, Email, WorkAddress1, WorkAddress2, WorkCity, WorkProv, WorkPhone, WorkPostal, WorkExt, WorkFax, Income, Employer, Occupation)
VALUES ($ClientNumber, $ContactStatus, $InsuranceClient, $SolicitInsurance, $MutualFundClient, $SolicitMutualFunds, $MortgageClient, $SolicitMortgages, $Title, $FirstName, $MiddleName, $LastName, $GreetingName, $MailingName, $DOB, $SIN, $IDType, $IDNumber, $Smoker, $PrimaryContact, $HomeAddress1, $HomeAddress2, $HomeCity, $HomeProv, $HomePostal, $HomePhone, $CellPhone, $Email, $WorkAddress1, $WorkAddress2, $WorkCity, $WorkProv, $WorkPostal, $WorkPhone, $WorkExt, $WorkFax, $Income, $Employer, $Occupation)";
mysql_query($sql) or die(mysql_error());
echo "Done.";
echo "<META HTTP-EQUIV=Refresh CONTENT=\"1; URL=./\">";
?>
Here is the addclient.php:
Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Client Database</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body style="font-family:Arial, Helvetica, sans-serif; font-size:12px; font-weight:bold;">
<?php
$conn = mysql_connect('localhost','root','password') or die('Sorry couldn\'t connect to database');
mysql_select_db("Clientbase");
$query = 'SELECT * FROM Clientbase';
$results = mysql_query($query,$conn) or die(mysql_error());
$num_fields = mysql_num_fields($results) - 1;
$drvstr = "Driver's License";
$addclient_header =<<<ADDHEADER
<form method="POST" action="saveclient.php">
<table cellspacing="0" cellpadding="0" width="90%" border="1" align="center">
<tr>
<th height="30px" colspan="2" style="background-color:#000000; color:#FFFFFF; font-size:14px;">
Add a Client
</th>
</tr>
ADDHEADER;
$addclient_page =<<<ADDPAGE
<tr>
<td width="70%" height="90px" style="background-color:#A0A0A0;">
Client Number: <input type="text" size="10" name="ClientNumber">
Insurance
Client Status:
<select name="ContactStatus">
<option value="Client">Client</option>
<option value="Ex-Client">Ex-Client</option>
<option value="Prospect">Prospect</option>
<option value="Co-Signer">Co-Signer</option>
</select>
</td>
<td width="30%" height="90px">
<table cellpadding="0" cellspacing="0" width="100%" height="90px" style="border:1px solid #0000AA;">
<tr>
<th bgcolor="#0000AA" width="60%" style="color:yellow; border:1px solid #0000AA;">Product Type</th>
<th bgcolor="#0000AA" width="20%" style="color:yellow; border:1px solid #0000AA;">Client?</td>
<th bgcolor="#0000AA" width="20%" style="color:yellow; border:1px solid #0000AA;">Solicit?</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="60%" align="center" style="color:#000000; border:1px solid #0000AA;">Insurance Products</td>
<td bgcolor="#FFFFFF" width="20%" align="center" style="color:#000000; border:1px solid #0000AA;">
<input type="checkbox" name="InsuranceClient" checked>
</td>
<td bgcolor="#FFFFFF" width="20%" align="center" style="color:#000000; border:1px solid #0000AA;">
<input type="checkbox" name="SolicitInsurance" checked>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="60%" align="center" style="color:black; border:1px solid #0000AA;">Mutual Funds</td>
<td bgcolor="#FFFFFF" width="20%" align="center" style="color:black; border:1px solid #0000AA;">
<input type="checkbox" name="MutualFundClient" checked>
</td>
<td bgcolor="#FFFFFF" width="20%" align="center" style="color:black; border:1px solid #0000AA;">
<input type="checkbox" name="SolicitMutualFunds" checked>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="60%" align="center" style="color:black; border:1px solid #0000AA;">Mortgages</td>
<td bgcolor="#FFFFFF" width="20%" align="center" style="color:black; border:1px solid #0000AA;">
<input type="checkbox" name="MortgageClient" checked>
</td>
<td bgcolor="#FFFFFF" width="20%" align="center" style="color:black; border:1px solid #0000AA;">
<input type="checkbox" name="SolicitMortgages" checked>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" height="30px" align="center" style="background-color:white;">
<input type="radio" name="Title" value="Mr." checked>Mr.
<input type="radio" name="Title" value="Mrs.">Mrs.
<input type="radio" name="Title" value="Miss.">Miss.
<input type="radio" name="Title" value="Ms.">Ms.
<input type="radio" name="Title" value="Dr.">Dr.
</td>
</tr>
<tr>
<td colspan="2" height="30px" align="center" style="background-color:white;">
First Name: <input type="text" name="FirstName" size="20">
Middle Name: <input type="text" name="MiddleName" size="15">
Last Name: <input type="text" name="LastName" size="20">
Greeting Name: <input type="text" name="GreetingName" size="15">
</td>
</tr>
<tr>
<td colspan="2" height="30px" align="center" style="background-color:white;">
Primary Contact <input type="checkbox" name="PrimaryContact">
Mailing Name <input type="text" name="MailingName" size="50">
Birthdate:
<select name="BirthMonth">
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<select name="BirthDay">
<option value="01" selected>01</option><option value="02">02</option>
<option value="03">03</option><option value="04">04</option>
<option value="05">05</option><option value="06">06</option>
<option value="07">07</option><option value="08">08</option>
<option value="09">09</option><option value="10">10</option>
<option value="11">11</option><option value="12">12</option>
<option value="13">13</option><option value="14">14</option>
<option value="15">15</option><option value="16">16</option>
<option value="17">17</option><option value="18">18</option>
<option value="19">19</option><option value="20">20</option>
<option value="21">21</option><option value="22">22</option>
<option value="23">23</option><option value="24">24</option>
<option value="25">25</option><option value="26">26</option>
<option value="27">27</option><option value="28">28</option>
<option value="29">29</option><option value="30">30</option>
<option value="31">31</option>
</select>
<input type="text" name="BirthYear" value="1900" size="4">
</td>
</tr>
<tr>
<td colspan="2" height="30px" align="center" style="background-color:white;">
SIN <input type="text" name="SIN" value="xxx-xxx-xxx" size="12">
Identification Type
<select name="IDType">
<option>$drvstr</option>
<option>Passport</option>
<option>Birth Certificate</option>
<option>Permanent Resident Card</option>
</select>
ID Number <input type="text" name="IDNumber" size="30">
Smoker <input type="checkbox" name="Smoker">
</td>
</tr>
<tr>
<td colspan="2" height="20px" align="center" style="background-color:white;">
</td>
</tr>
<tr>
<td colspan="2" height="20px" align="center" style="background-color:white;">
<table width="100%" cellspacing="0" cellpadding="0" style="border:2px solid #f88334">
<tr>
<th width="50%" height="30px" align="center" style="background-color:#F88334; font-size:20px;">
Home
</th>
<th width="50%" height="30px" align="center" style="background-color:#F88334; font-size:20px;">
Work
</th>
</tr>
<tr>
<td width="50%" height="30px" align="left" valign="top" style="border-right:2px solid #F88334; padding-left:18px;">
<br>
Address 1 <input type="text" name="HomeAddress1" size="50"><br>
Address 2 <input type="text" name="HomeAddress2" size="50"><br>
City
<input type="text" name="HomeCity" size="20">
Province <input type="text" name="HomeProv" size="2"><br>
Postal
<input type="text" name="HomePostal" size="7"><br>
<br>
Phone
<input type="text" name="HomePhone" size="16">
Cell Phone <input type="text" name="CellPhone" size="16"><br>
Email
<input type="text" name="Email" size="50">
</td>
<td width="50%" height="30px" align="left" style="padding-left:18px;">
<br>
Employer
<input type="text" name="Employer" size="50"><br>
Occupation <input type="text" name="Occupation" size="50"><br>
<br>
Address 1 <input type="text" name="WorkAddress1" size="50"><br>
Address 2 <input type="text" name="WorkAddress2" size="50"><br>
City
<input type="text" name="WorkCity" size="20">
Province <input type="text" name="WorkProv" size="2"><br>
Postal
<input type="text" name="WorkPostal" size="7"><br>
<br>
Phone
<input type="text" name="WorkPhone" size="14"> x <input type="text" name="WorkExt" size="5">
Fax <input type="text" name="WorkFax" size="14"><br>
Income
<input type="text" name="Income" size="10"><br>
<br>
</td>
</tr>
</table>
</td>
</tr>
ADDPAGE;
$addclient_footer =<<<FOOTER
<tr>
<td colspan="2" align="right" style="background-color:#DDDDDD;">
<font style="font-size:4px;"><br></font>
<input type="submit" value="Save"> <br>
<font style="font-size:4px;"><br></font>
</td>
</tr>
</table>
</form>
FOOTER;
echo $addclient_header;
echo $addclient_page;
echo $addclient_footer;
?>
</body>
</html>
|
|
|
|
|
12-03-2005, 01:15 AM
|
|
Posts: 169
|
The error is that you are not having quotation around values in insert query where the type is varchar or text or date.
For example in the below piece of code,
Quote:
case "ClientNumber" : $ClientNumber = $value; break;
case "ContactStatus" : $ContactStatus = $value; break;
|
if ContactStatus is type varchar in your mysql database, than you can change the above code to,
Code:
case "ContactStatus" : $ContactStatus = '.$value.'; break;
see the added single quotation around '.$value .'
You need to change all the varchar, text or date types by putting quotations around them.
__________________
Please login or register to view this content. Registration is FREE
TOP 10 WEB HOSTS COMPARED
BEST RESELLER HOSTING
|
|
|
|
|
12-06-2005, 10:39 PM
|
|
Posts: 626
|
Thank you for youor help.
|
|
|
|
|
« Reply to What's wrong with this code?
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
