How do I auto increment a primary key?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

How do I auto increment a primary key?

Post by 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?? :(
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: How do I auto increment a primary key?

Post by Darhazer »

do not pass value for member_id in the query or pass null
Post Reply