?
Solved

MYSQL database using auto increment

Posted on 2010-01-08
17
Medium Priority
?
547 Views
Last Modified: 2013-12-12
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
Comment
Question by:Hidesign
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26213749
Hi Hidesign,

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

What symptoms are you seeing?


Kent
0
 

Author Comment

by:Hidesign
ID: 26213926
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26214007

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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 83

Expert Comment

by:leakim971
ID: 26214800
Hello Hidesign,

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

Regards.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26226752
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
 
LVL 83

Expert Comment

by:leakim971
ID: 26227769
@Hidesign, $5 on constraints! You follow me ? LOL
0
 

Author Comment

by:Hidesign
ID: 26290054
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
 

Author Comment

by:Hidesign
ID: 26290058
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 26292463

Can you post a sample of a record that doesn't get inserted?
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 26292838
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
 

Author Comment

by:Hidesign
ID: 26294861
I will be trying this updated code and will post what happens - thanks
0
 

Author Comment

by:Hidesign
ID: 26297855
I am using a a timestamp field. Would this cause a problem?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26298036
You're not using a TIMESTAMP definition on an AUTO_INCREMENT field are you?
0
 

Author Comment

by:Hidesign
ID: 26298172
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26302981
"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
 

Author Comment

by:Hidesign
ID: 26354602
I was away for a few days and will trying the suggestions today - thanks
0
 

Author Closing Comment

by:Hidesign
ID: 31674762
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question