MYSQL database using auto increment

Question:

I created a mysql/php ssystem. There is one table and I am using the auto increment feature. In most case the record inserts correctly but there are case where the record does not insert. Could this be because the auto increment does not always work correctly and if it doens’t the mysql call fails. The version of MYSQL is: is 5.0.86

Thanks

Solution:

Couple of things that you need to do…

1. Separate the query construction from the query execution.  This lets you print out the query as it is executed and you can see the issues that may arise.  Example

mysql_query(“INSERT…”); // WRONG

$sql = “INSERT…”;
echo $sql; // NOW WE CAN SEE THE FULLY RESOLVED QUERY BEFORE EXECUTION
mysql_query($sql); // CORRECT

2. You need to filter and escape the input.  Using the unedited and unescaped POST data is a way to ensure that disaster is not left to chance.  Read the man page here and adjust your code accordingly:

http://us.php.net/manual/en/function.mysql-real-escape-string.php

3. You must test the queries for success. MySQL is not a black box – it can and does fail, sometimes for reasons outside of your program code.  When that happens, most mysql functions return FALSE and you can find the reasons in mysql_errno() and mysql_error().

Not sure about the answer to the phpMyAdmin question, but it can both create tables and show the create table statements, so the answer is probably “yes.”

Obviously I cannot test your code, but this shows what you should be doing with that insert query.

HJTH, ~Ray

// ESCAPE THE INPUT VALUES – SEE ALSO PHP FUNCTION filter_var();
$safe_post = array();
foreach ($_POST as $key => $val)
{
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_post[$key] = mysql_real_escape_string($val);
}

// CONSTRUCT THE QUERY USING SAFE VALUES
$sql = “INSERT INTO sceentable

(

lastname,
firstname,
email,
birthdate,
phone,
address,
city,
state,
zip,
county,
sex,
doctorname,
doctoraddress,
doctorphone,
changes,
which_breast,
symptom1,
symptom2,
symptom3,
symptom4,
symptom5,
symptom6,
symptom7,
symptom8,
symptom9,
when_breast_screening,
screening_type,
fibrocystle_changes,
had_biopsy,
biopsy_age,
history_breast_cancer,
relation,
side_family,
relative_age,
insurance,
income,
per,
supports,
heardabout,
experience,
comments

) VALUES (

‘{$safe_post["lastname"]}’,
‘{$safe_post["firstname"]}’,
‘{$safe_post["email"]}’,
‘{$safe_post["birthdate"]}’,
‘{$safe_post["phone"]}’,
‘{$safe_post["address"]}’,
‘{$safe_post["city"]}’,
‘{$safe_post["state"]}’,
‘{$safe_post["zip"]}’,
‘{$safe_post["county"]}’,
‘{$safe_post["sex"]}’,
‘{$safe_post["doctorname"]}’,
‘{$safe_post["doctoraddress"]}’,
‘{$safe_post["doctorphone"]}’,
‘{$safe_post["changes"]}’,
‘{$safe_post["which_breast"]}’,
‘{$safe_post["symptom1"]}’,
‘{$safe_post["symptom2"]}’,
‘{$safe_post["symptom3"]}’,
‘{$safe_post["symptom4"]}’,
‘{$safe_post["symptom5"]}’,
‘{$safe_post["symptom6"]}’,
‘{$safe_post["symptom7"]}’,
‘{$safe_post["symptom8"]}’,
‘{$safe_post["symptom9"]}’,
‘{$safe_post["when_breast_screening"]}’,
‘{$safe_post["screening_type"]}’,
‘{$safe_post["fibrocystle_changes"]}’,
‘{$safe_post["had_biopsy"]}’,
‘{$safe_post["biopsy_age"]}’,
‘{$safe_post["history_breast_cancer"]}’,
‘{$safe_post["relation"]}’,
‘{$safe_post["side_family"]}’,
‘{$safe_post["relative_age"]}’,
‘{$safe_post["insurance"]}’,
‘{$safe_post["income"]}’,
‘{$safe_post["per"]}’,
‘{$safe_post["supports"]}’,
‘{$safe_post["heardabout"]}’,
‘{$safe_post["experience"]}’,
‘{$safe_post["comments"]}’

)”;

// RUN THE QUERY
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS AND PRESENT THEM
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
$errmsg = mysql_errno() . ‘ ‘ . mysql_error();
echo “<br/>QUERY FAIL: “;
echo “<br/>$sql <br/>”;
die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED – PER THE DB CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($db_connection);