Insert into mysql failing

Question

CarterLangley 0 Newbie Poster

10 Years Ago

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>";
}
?>

mysql php

3 Contributors
6 Replies
190 Views
1 Hour Discussion Span
Latest Post 10 Years Ago Latest Post by CarterLangley

pzuurveen 90 Posting Whiz in Training

10 Years Ago

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

diafol

10 Years Ago

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.

Edited 10 Years Ago by diafol

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

CarterLangley 0 Newbie Poster · Answer 1 · 2014-03-12T10:39:19+00:00

CarterLangley 0 Newbie Poster

10 Years Ago

Thanks guys, got it working!

CarterLangley 0 Newbie Poster · Answer 2 · 2014-03-12T10:50:57+00:00

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?

diafol · Answer 3 · 2014-03-12T11:00:39+00:00

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')";

CarterLangley 0 Newbie Poster · Answer 4 · 2014-03-12T11:28:54+00:00

CarterLangley 0 Newbie Poster

10 Years Ago

Cheers buddy will check it out