CODEWITHSUNDEEP
phpautomation
amir
amir

Reputation: 21

Auto increment id

This is query where i want to insert new data to database. Before this id is auto by using old system and now i make a new form by php. This is my query and now i want make auto increment id.How to make a auto increment id.Please Help me if anyone know.

$sql="INSERT INTO $tbl_name(id, name, icno, email, applyIntake_id, modeOfStudy_id, 
        choice1_id, dob, nationalityType, gender_id, race_id, highestEducation_id,
        address1, address2, postcode, city, state_id, permanent_address1,
        permanent_address2, permanent_postcode, permanent_city, permanent_state,
        telephoneNo, mobileNo)
    VALUES('$name', '$icno', '$email', '$id', '$applyIntake_id', '$modeOfStudy_id',
        '$choice1_id', '$dob', '$nationalityType', '$gender_id', '$race_id', 
        '$highestEducation_id', '$address1', '$address2', '$postcode', '$city', 
        '$state_id', '$permanent_address1', '$permanent_address2', 
        '$permanent_postcode', '$permanent_city', '$permanent_state', 
        '$telephoneNo', '$mobileNo')";
$result=mysql_query($sql);

Upvotes: 2

Views: 638

Answers (4)

Tom
Tom

Reputation: 11

Amir you have 3 errors in above insert.

  1. The columns count match but you try to insert into #tbl_name(email) value of $id
  2. Please set the id field as autoincrement the answer is in above posts.
  3. I think when preparing PHP $sql variable you have to use "." as string operator to get proper SQL INSERT statement with data from your variables - currently the below INSERT will insert instead of your name variable $name as string into DB.

Exmaple :

$name = 'Tom'; $email = '[email protected]'

$sql = "insert into table(name,email) VALUES (".$name.",".$email.");";

Then use following insert but apply the example of string join above - it should work: 

$sql="INSERT INTO $tbl_name(name, icno, email, applyIntake_id, modeOfStudy_id, 
    choice1_id, dob, nationalityType, gender_id, race_id, highestEducation_id,
    address1, address2, postcode, city, state_id, permanent_address1,
    permanent_address2, permanent_postcode, permanent_city, permanent_state,
    telephoneNo, mobileNo)
VALUES('$name', '$icno', '$email', '$applyIntake_id', '$modeOfStudy_id',
    '$choice1_id', '$dob', '$nationalityType', '$gender_id', '$race_id', 
    '$highestEducation_id', '$address1', '$address2', '$postcode', '$city', 
    '$state_id', '$permanent_address1', '$permanent_address2', 
    '$permanent_postcode', '$permanent_city', '$permanent_state', 
    '$telephoneNo', '$mobileNo')";

$result=mysql_query($sql);

Upvotes: 1

tailor
tailor

Reputation: 376

ALTER TABLE document MODIFY COLUMN document_id INT AUTO_INCREMENT;

There are a couple of reasons that your SQL might not work. First, you must re-specify the data type (INT in this case). Also, the column you are trying to alter must be indexed (it does not have to be the primary key, but usually that is what you would want). Furthermore, there can only be one AUTO_INCREMENT column for each table. So, you may wish to run the following SQL (if your column is not indexed):

ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY;

You can find more information in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for the modify column syntax and http://dev.mysql.com/doc/refman/5.1/en/create-table.html for more information about specifying columns.

Upvotes: 0

Prashanth
Prashanth

Reputation: 1304

Try this for MYSQL DB..

ALTER TABLE
    `TABLE_SCHEMA`.`TABLE_NAME` 
CHANGE COLUMN
    `ID` `ID` INT(6) NOT NULL 
AUTO_INCREMENT;

Upvotes: 4

Theolodis
Theolodis

Reputation: 5102

Ok, one thing first: you should escape your parameters! See here.

To solve your problem: you can define a field in the database as auto_increment, what will automatically increment the id if left empty.

Upvotes: 1

Related Questions