Hey guys,

the code below is where I am having the problem.
The error I am getting when trying to insert the record is

Could not enter data: Unknown column '3' in 'field list'

<?php 
session_start();
include("db_connect.php");
if(($_SESSION['user_id']) && ($_SESSION['access_level'] == 1 ) )
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<head>
    <title>Teaching Int Limited - Add Education</title>
    <meta name="title" content="Teaching Int Limited">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="description" content="Providing Quality English Teachers to Schools and Organisations">
    <meta name="Abstract" content="Providing Quality English Teachers to Schools and Organisations">
    <meta name="keywords" content="TEFL,english,teacher">
    <meta name="Robots" content="index, follow">
    <meta name="Distribution" content="Global">
    <meta name="Revisit-After" content="30 days">
    <meta name="Rating" content="General">
    <meta name="Reply-to" content="info@teaching-int.com">
    <meta name="Owner" content="Teaching Int Limited">
    <meta name="Author" content="Carter Langley">
    <meta name="copyright" content="Teaching Int Limited - 2013">
    <link rel="shortcut icon" href="images/favicon.ico">
    <link href='http://fonts.googleapis.com/css?family=Great+Vibes' rel='stylesheet' type='text/css'>
    <link rel="stylesheet" type="text/css" href="form.css">
    <link rel="stylesheet" type="text/css" href="header.css">
    <link rel="stylesheet" type="text/css" href="site.css">
</head>
<body>
<?php include("header.php"); ?>

<?php
if(isset($_POST['add']))
{
$person_id = (int)$_POST['person_id'];
$university_name = protect($_POST['university_name']);
$college_name = protect($_POST['college_name']);
$school_name = protect($_POST['school_name']);
$degree_name = protect($_POST['degree_name']);
$diploma_name = protect($_POST['diploma_name']);
$school_level = protect($_POST['school_level']);
$tefl_school = protect($_POST['tefl_school']);
$tesol_school = protect($_POST['tesol_school']);
$celta_school = protect($_POST['celta_school']);
$other_details = protect($_POST['other_details']);
$degree_start = protect($_POST['degree_start']);
$degree_end = protect($_POST['degree_end']);
$college_start = protect($_POST['college_start']);
$college_end = protect($_POST['college_end']);
$school_start = protect($_POST['school_start']);
$school_end = protect($_POST['school_end']);
$other_start = protect($_POST['other_start']);
$other_end = protect($_POST['other_end']);

$sql = "INSERT INTO `teaching`.`education_detail` (`education_detail_id`, `person_id`, `university_name`, `college_name`, `school_name`, `degree_name`, `diploma_name`, `school_level`, `tefl_school`, `tesol_school`, `celta_school`, `other_details`, `degree_start`, `degree_end`, `college_start`, `college_end`, `school_start`, `school_end`, `other_start`, `other_end`)"
        . "VALUES (NULL, `$person_id`, `$university_name`, `$college_name`, `$school_name`, `$degree_name`, `$diploma_name`, `$school_level`, `$tefl_school`, `$tesol_school`, `$celta_school`, `$other_details`, `$degree_start`, `$degree_end`, `$college_start`, `$college_end`, `$school_start`, `$school_end`, `$other_start`, `$other_end`)";
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully. Redirecting in 2 seconds.\n";
mysql_close($conn);
header('Refresh: 2; URL=edit.php');
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="100%" align="left" border="0" cellspacing="1" cellpadding="2">
<?php
$ops = '';
$sql_select = "select * from `person` order by `last_name` asc ";
$retval_selectperson = mysql_query( $sql_select, $conn );
if(! $retval_selectperson ) { die('Could not select data: ' . mysql_error()); }
while($row = mysql_fetch_assoc($retval_selectperson)) {
    $ops .=  "<option value='{$row['person_id']}'>{$row['first_name']} {$row['middle_name']} {$row['last_name']}</option>";
}
?>
<tr>
<td>
<label for="person_id">Person</label>
</td>
<td>
<select name="person_id" id="person_id">
<option value="">--- Select a Person ---</option>
<?php echo $ops;?>
</select>
</td>
</tr>
<tr>
<td><label for="university_name">University Name</label></td>
<td><input name="university_name" type="text" id="university_name"></td>
</tr>
<tr>
<td><label for="college_name">College Name</label></td>
<td><input name="college_name" type="text" id="college_name"></td>
</tr>
<tr>
<td><label for="school_name">School Name</label></td>
<td><input name="school_name" type="text" id="school_name"></td>
</tr>
<tr>
<td><label for="degree_name">Degree Name</label></td>
<td><input name="degree_name" type="text" id="degree_name"></td>
</tr>
<tr>
<td><label for="diploma_name">Diploma Name</label></td>
<td><input name="diploma_name" type="text" id="diploma_name"></td>
</tr>
<tr>
<td><label for="school_level">School Level</label></td>
<td><input name="school_level" type="text" id="school_level"></td>
</tr>
<tr>
<td><label for="tefl_school">TEFL School</label></td>
<td><input name="tefl_school" type="text" id="tefl_school"></td>
</tr>
<tr>
<td><label for="tesol_school">TESOL School</label></td>
<td><input name="tesol_school " type="text" id="tesol_school"></td>
</tr>
<tr>
<td><label for="celta_school">CELTA School</label></td>
<td><input name="celta_school " type="text" id="celta_school"></td>
</tr>
<tr>
<td><label for="other_details">Other Details</label></td>
<td><textarea name="other_details" rows="10" cols="50" id="other_details"></textarea></td>
</tr>
<tr>
<td><label for="degree_start">Degree Start Date (yyyy-mm-dd)</label></td>
<td><input name="degree_start " type="text" id="degree_start"></td>
</tr>
<tr>
<td><label for="degree_end">Degree End Date (yyyy-mm-dd)</label></td>
<td><input name="degree_end" type="text" id="degree_end"></td>
</tr>
<tr>
<td><label for="college_start">College Start Date (yyyy-mm-dd)</label></td>
<td><input name="college_start" type="text" id="college_start"></td>
</tr>
<tr>
<td><label for="college_end">College End Date (yyyy-mm-dd)</label></td>
<td><input name="college_end" type="text" id="college_end"></td>
</tr>
<tr>
<td><label for="school_start">School Start Date (yyyy-mm-dd)</label></td>
<td><input name="school_start" type="text" id="school_start"></td>
</tr>
<tr>
<td><label for="school_end">School End Date (yyyy-mm-dd)</label></td>
<td><input name="school_end" type="text" id="school_end"></td>
</tr>
<tr>
<td><label for="other_start">Other Start Date (yyyy-mm-dd)</label></td>
<td><input name="other_start" type="text" id="other_start"></td>
</tr>
<tr>
<td><label for="other_end">Other End Date (yyyy-mm-dd)</label></td>
<td><input name="other_end" type="text" id="other_end"></td>
</tr>
<tr>
<td></td>
<td>
<input name="add" type="submit" id="add" value="Add Education Details">
</td>
</tr>
</table>
</form>
</body>
</html>
<?php
}
}
else
{
    echo "<script type=\"text/javascript\">
    alert(\"You don't belong here!\");
    window.location=\"index.php\"</script>";
}
?>

change the ` (back tick) in the query value part to ' (single qoute)
stings are surounde by a single qoute.
Colomnames by a back tick

Member Avatar
diafol

Your js redirect should be a php redirect.

Also your form input names are the same as your DB fieldnames - try to make them a little different if possible - better security.

I'm assuming this is an admin form as you allow the user to choose from a list of people in order to insert a record to your DB. If this is not the case and this form is meant for logged in users, then forget the dropdown and just use the session user_id in the SQL.

I'm also assuming that protect is some sort of mysql_real_escape_string thingy.

I'm sure I've mentioned before, use mysqli/PDO - mysql has been given the last rites.

BTW an useful snippet for cleaning and extracting post data. Note that extracting data from $_GET and $_POST etc is dangerous, BUT if you filter the variables first, it should be OK.

if($_POST)
{
    //Filter Fields to Process...
    $fields = array('t1','t2','t3','t4');        
    $p = array_intersect_key($_POST, array_flip($fields));

    //Ensure Data is untainted
    $clean = array_map("protect", $p);

    //Extract variables - named after the items in $fields
    extract($clean);

    //check
    echo $t1;
}

The above is for a form with textboxes named 't1', 't2', etc. Also - this is for 'string' data. Validation for specific data types should be performed.

diafol,

Yep, this is an admin form. Only myself has access to this. Not yet on the internet, just running locally off an apache server.
Yes, protect is exactly that.
I will do a global search and replace for the mysql to mysqli and get them all changed in one fell swoop.
Your filtering variables looks nice, but how do I use that in the sql query?

Member Avatar
diafol

The extract() takes any array items and creates new variables named after the keys. Take a look at the manual for different options - be aware that you could overwrite variables with the same name if not careful!

I should point out that it's quick'n'dirty.

extract($clean);

$sql = "INSERT INTO tbl (f1,f2,f3,f4) VALUES ('$t1','$t2','$t3','$t4')";