How do I auto increment a primary key?

[drayarms]

The following code is part of a script that registers members on a site. 

 

 

<?php

 

//address error handling

 

ini_set ('display_errors', 1);

error_reporting (E_ALL & ~E_NOTICE);

 

 

 

// Check if he wants to register:

if (!empty($_POST[username]))

{

// Check if passwords match.

if ($_POST[password] != $_POST[password2])

exit("Error - Passwords don't match. Please go back and try again.");

 

// Assign some variables.

$date = mktime(0,0,0,date("m"),date("d"),date("Y"));

$ip = $_SERVER[REMOTE_ADDR];

 

require_once("config.php");

 

// Register him.

$query = mysql_query("INSERT INTO members

(member_id, username, firstname, lastname, password, register_date, ip)

VALUES (0, '$_POST[username]','$_POST[firstname]','$_POST[lastname]','$_POST[password]','$date','$ip')")

or die ('<p>Could not register user becasue: <b>' .mysql_error().'</b>.</p>');

 

echo "Welcome". ucfirst$_POST[username]. "! You've been successfully reigstered! <br /><br />

        Please login <a href='login.php'><b>here</b></a>.";

 

exit();

}

 

?>

 

 

 

And here is the corresponding table which receives and stores the data in a mysql database:

 

 

 

<?php

 

//This file installs tables into database. Needs the connect or config script to access the database.

 

//address error handling

 

ini_set ('display_errors', 1);

error_reporting (E_ALL & ~E_NOTICE);

 

 

//include the config file

require_once("config.php");

 

//Define the query.

 

$query = "CREATE TABLE members (

 

member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,

username VARCHAR( 50 ) NOT NULL UNIQUE,

firstname VARCHAR( 50 ) NOT NULL ,

lastname VARCHAR( 50 ) NOT NULL ,

title VARCHAR(10),

password VARCHAR( 50 ) NOT NULL ,

primary_email VARCHAR(100),

secondary_email VARCHAR(100),

register_date VARCHAR( 50 ) NOT NULL ,

ip VARCHAR( 50 ) NOT NULL ,

UNIQUE (username)

     

          )";

 

//Run the query.

 

if (@mysql_query ($query)) {

  print '<p>The table has been created.</p>';

} else {

  die('<p>Could not create the table becasue: <b>' .mysql_error().'</b>.</p><p>The query being run was: '.$query.'</p>');

}

 

mysql_close();// Close the database connection.

 

 

?>

 

 

 

As you can see, the value for member_id which is the primary key for the members table into which the member's information is being inserted, is 0.  From my understanding,  the first member to register would be assigned the default id 0 and subsequent members would be assigned, 1, 2, 3, etc.  I created two members and when I ran a select query to test my results, Both members had a member_id of 0.  Where did I go wrong here? Can anyone pinpoint the problem?? 

[gristoi]

You just need to remove the members_id from the insert query.

Example:

  Insert into members ( username, firstname, lastname, password, register_date, ip) VALUES ( username, firstname, lastname, password, register_date, ip)

 

The members_id field will auto increment upon insertion for you