• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

MYSQL database using auto increment

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
0
Hidesign
Asked:
Hidesign
  • 8
  • 4
  • 3
  • +1
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Hidesign,

The auto_increment is pretty basic.  You can expect that it works just fine.

What symptoms are you seeing?


Kent
0
 
HidesignAuthor Commented:
I created a form where users enter their info and we get an email in addition to inserting into the databse. Every day, we get a few records that come through in the email but are not in the database.
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Inserts can fail for any number of reasons.  Violation of referential constraints, duplicate key, lock contention/timeout, session timeout, etc.

How much data is in the database?
How many transactions is the database handling?
Are the inserts "simple" or does the process join to other tables?


Kent
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
leakim971PluritechnicianCommented:
Hello Hidesign,

Do you have some php code to display ?
Table schema ?

Regards.
0
 
Ray PaseurCommented:
I think we would need to see your code to help much more here.  It is not a problem with auto_increment, that is for sure.  Here is how to do an insert - check the code, comments and man page references.

Best regards, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES (\"$safe_username\")";
// 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
if (!$res)
{
   // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
   $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);

Open in new window

0
 
leakim971PluritechnicianCommented:
@Hidesign, $5 on constraints! You follow me ? LOL
0
 
HidesignAuthor Commented:
Here is the code for insert. This may be a silly questions, but can I generate the sql for the create table fom phpmyadmin - thanks everyone

mysql_query("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 ('$_POST[lastname]','$_POST[firstname]','$_POST[email]','$_POST[birthdate]','$_POST[phone]','$_POST[address]','$_POST[city]','$_POST[state]','$_POST[zip]','$_POST[county]','$_POST[sex]','$_POST[doctorname]','$_POST[doctoraddress]','$_POST[doctorphone]','$_POST[changes]','$_POST[which_breast]','$_POST[symptom1]','$_POST[symptom2]','$_POST[symptom3]','$_POST[symptom4]','$_POST[symptom5]','$_POST[symptom6]','$_POST[symptom7]','$_POST[symptom8]','$_POST[symptom9]','$_POST[when_breast_screening]','$_POST[screening_type]','$_POST[fibrocystle_changes]','$_POST[had_biopsy]','$_POST[biopsy_age]','$_POST[history_breast_cancer]','$_POST[relation]','$_POST[side_family]','$_POST[relative_age]','$_POST[insurance]','$_POST[income]','$_POST[per]','$_POST[supports]','$_POST[heardabout]','$_POST[experience]','$_POST[comments]')");
0
 
HidesignAuthor Commented:
Most of the records do get inserted. For example, about 15 records were inserted successfully and 2 records never made it to the database
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Can you post a sample of a record that doesn't get inserted?
0
 
Ray PaseurCommented:
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);

Open in new window

0
 
HidesignAuthor Commented:
I will be trying this updated code and will post what happens - thanks
0
 
HidesignAuthor Commented:
I am using a a timestamp field. Would this cause a problem?
0
 
Ray PaseurCommented:
You're not using a TIMESTAMP definition on an AUTO_INCREMENT field are you?
0
 
HidesignAuthor Commented:
Ray_Paseur: - no the auto_increment is a separate field. Different users are entering the data on the form iinto the database so its hard to track why a record fails.
0
 
Ray PaseurCommented:
"hard to track why a record fails." - Understood.  Let's take the data and error visualization steps I showed above, then let's run your tests again and look at the inputs and outputs.  I am fairly sure we can skin this cat!  Best, ~Ray
0
 
HidesignAuthor Commented:
I was away for a few days and will trying the suggestions today - thanks
0
 
HidesignAuthor Commented:
My call was failing because of an apostaphe in one of the fields. I used this:  
$safe_post[$key] = mysql_real_escape_string($val);
 and it worked great - thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now