Hi everyone I'm trying to run this script but keep getting the following mysql error message
'MySQL 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 'add, town, county, pcode, telephone, email, enrolled, company, cost) VALUES ('Fi' at line 1'
I've looked for hours to try and figure out what the problem is without success. Here is the complete code for the page...
<?php # index.php
// This is the page where you can add clients
// Set the page title and include the html header
$page_title="Add New Clients";
include ('header.html');
// Include config file...
require_once('_inc/config.inc.php');
// If no first name session variable exists, redirect the user...
if (!isset($_SESSION['fname'])) {
// Start defining the URL.
$url = 'http://' . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']);
// Check for a trailing slash.
if ((substr($url, -1) == '/') OR (substr($url, -1) == '\\') ) {
$url = substr ($url, 0, -1); // Chop off the slash.
}
// Add the page.
$url .= '/index.php';
ob_end_clean(); // Delete the buffer.
header("Location: $url");
exit(); // Quit the script.
} else {
require_once ('_inc/ecg_con.php'); // Connect to the database.
echo 'This is the \'Add Clients Page\', you can add new clients here.';
if (isset($_POST['submitted'])) { // Handle the form.
// Set form variables
$fn = escape_data($_POST['fname']);
$ln = escape_data($_POST['lname']);
$ad = escape_data($_POST['ad']);
$add = escape_data($_POST['add']);
$town = escape_data($_POST['town']);
$cty = escape_data($_POST['county']);
$pc = escape_data($_POST['pcode']);
$tel = escape_data($_POST['tel']);
$e = escape_data($_POST['email']);
$cmpy = escape_data($_POST['company']);
$enrolled = escape_data($_POST['enrolled']);
$course = escape_data($_POST['course']);
$cost = escape_data($_POST['cost']);
$errors = array(); // Initialise the errors array
// Check for a first name.
if (empty($_POST['fname'])) {
$errors[] = 'You forgot to enter the clients first name.';
} else {
$fn = escape_data($_POST['fname']);
}
// Check for a last name.
if (empty($_POST['lname'])) {
$errors[] = 'You forgot to enter the clients last name.';
} else {
$ln = escape_data($_POST['lname']);
}
// Check for an address.
if (empty($_POST['ad'])) {
$errors[] = 'Please enter at least the first line of the clients address.';
} else {
$ad = escape_data($_POST['ad']);
}
// Check for an town.
if (empty($_POST['ad'])) {
$errors[] = 'Please enter at the clients town of residence.';
} else {
$town = escape_data($_POST['town']);
}
// Check for a post code.
if (empty($_POST['pcode'])) {
$errors[] = 'Please enter the clients post code.';
} else {
$pc = escape_data($_POST['pcode']);
}
// Check for an email address and that it's in the correct format.
if (preg_match ('/^[[:alnum:]][a-z0-9_\.\-]*@[a-z0-9\.\-]+\.[a-z]{2,4}$/', stripslashes(trim($_POST['email'])))) {
$e = escape_data($_POST['email']);
} else {
$errors[] = 'enter a valid email address.';
}
// Check for a cost.
if (empty($_POST['cost'])) {
$errors[] = 'Please enter the cost of the course.';
} else {
$cost = escape_data($_POST['cost']);
}
// Check for a post code.
if (empty($_POST['enrolled'])) {
$errors[] = 'Please enter the date the course was taken.';
} else {
$enrolled = escape_data($_POST['enrolled']);
}
// Check for a post code.
if (empty($_POST['course'])) {
$errors[] = 'Please enter the name of the course.';
} else {
$course = escape_data($_POST['course']);
}
if (empty($errors)) { // If everything's OK.
// Add the client.
$query = "INSERT INTO clients (course, fname, lname, ad, add, town, county, pcode, telephone, email, enrolled, company, cost) VALUES ('$course', '$fn', '$ln', '$ad', '$add', '$town', '$cty', '$pc', '$tel', '$e', '$enrolled', '$cmpy', '$cost' )";
$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
if (mysql_affected_rows() == 1) { // If it ran OK.
// Finish the page.
echo "<h3>Thank you for using this system.</h3><p><b>You have added:</p><font color=\"#008000\">$fn $ln<br> <br />with the following address:<br />$ad, $add<br />Town: $town<br />County: $cty<br />Postcode: $pc<br />Telephone: $tel<br />Email: $e<br />Date course taken: $enrolled, Company: $cmpy, Cost: $cost<br /><a href='add_client.php'>Add Another client</a> or return to <a href='index.php'>Admin Page</a><br><br></font>";
include ('./includes/footer.html'); // Include the HTML footer.
exit();
} else { // If it did not run OK.
echo '<h1>Already registered</h1>
<p id="error">The client is already registered</p>'; // Debugging message.
include ('./includes/footer.html');
exit();
}
} else { // Report the errors.
echo '<h1>Error!</h1>
<p id="error">The following error(s) occurred:<br />';
foreach ($errors as $msg) { // Print each error.
echo " - $msg<br />\n";
}
echo '</p><p>Please try again.</p>';
}// mysql_close(); // Close the database connection.
} // End of the main Submit conditional.
}
?>
<form action="add_client.php" method="post">
<fieldset>
<table width="200" border="0" cellspacing="5" cellpadding="5">
<tr>
<th><h2>Add A Client</h2></th>
</tr>
<tr>
<td><strong>*First Name:</strong><br /><input type="text" name="fname" size="30" maxlength="30" value="<?php if (isset($_POST['fname'])) echo $_POST['fname']; ?>" /></td>
<td><strong>Company:</strong><br /><input type="text" name="company" size="30" maxlength="30" value="<?php if (isset($_POST['company'])) echo $_POST['company']; ?>" /></td>
</tr>
<tr>
<td><strong>*Last Name:</strong><br /><input type="text" name="lname" size="30" maxlength="30" value="<?php if (isset($_POST['lname'])) echo $_POST['lname']; ?>" /></td>
<td><strong>Town:</strong><br /><input type="text" name="town" size="30" maxlength="30" value="<?php if (isset($_POST['town'])) echo $_POST['town']; ?>" /></td>
</tr>
<tr>
<td><strong>*Address Line 1:</strong><br /><input type="text" name="ad" size="30" maxlength="30" value="<?php if (isset($_POST['ad'])) echo $_POST['ad']; ?>" /></td>
<td><strong>County:</strong><br /><input type="text" name="county" size="30" maxlength="30" value="<?php if (isset($_POST['county'])) echo $_POST['county']; ?>" /></td>
</tr>
<tr>
<td><strong>Address Line 2:</strong><br /><input type="text" name="add" size="30" maxlength="30" value="<?php if (isset($_POST['add'])) echo $_POST['add']; ?>" /></td>
<td><strong>*Postal Code:</strong><br /><input type="text" name="pcode" size="30" maxlength="30" value="<?php if (isset($_POST['pcode'])) echo $_POST['pcode']; ?>" /></td>
</tr>
<tr>
<td><strong>Telephone:</strong><br /><input type="text" name="tel" size="30" maxlength="30" value="<?php if (isset($_POST['tel'])) echo $_POST['tel']; ?>" /></td>
<td><strong>*Email:</strong><br /><input type="text" name="email" size="30" maxlength="30" value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>" /></td>
</tr>
<tr>
<td><strong>*Date Course Taken:</strong><br /><input type="text" name="enrolled" size="30" maxlength="30" value="<?php if (isset($_POST['enrolled'])) echo $_POST['enrolled']; ?>" /><br /><span style="font-size:smaller">Please enter date in the following format: YYYY/mm/dd</span></td>
<td><strong>*Course Name:</strong><br /><input type="text" name="course" size="30" maxlength="30" value="<?php if (isset($_POST['course'])) echo $_POST['course']; ?>" /></td>
</tr>
<tr>
<td><strong>*Cost of the course:</strong><br /><input type="text" name="cost" size="30" maxlength="30" value="<?php if (isset($_POST['cost'])) echo $_POST['cost']; ?>" /></td>
<td><strong>Fields marked with an asterisk* are required.</strong></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="submit" value="Add Client" />
<input type="hidden" name="submitted" value="TRUE" /></td>
</tr>
</table>
</fieldset>
<p></p>
</form>
<?php
// Include the footer file.
include ('footer.html');
?>
If anyone could give me a clue as to where I'm going wrong I'd really appreciate it, many thanks for your time.
