Getting an Auto Incremented Primary Key

WDGirlie · September 13, 2012

Hello again!

I need to create a set of web pages that registers a person for a conference. I have my first 3 pages with forms and input boxes. The 4th page needs to show all the information that was entered in the previous pages. The fifth confirms everything has been saved to the database.

My issue, is once I go to the second part of my form, I need the auto_incremented primary key from the previous page to insert into the table.

I read up on the mysqli_insert_id, but what from I read, it's suppose to be used RIGHT AFTER the previous insert statement. The MYSQL statement is on another page. Here is the first page: (Goes through okay)

<?php
session_start();	
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Conference Registration</title>
</head>

<body>

<?php 
$DBConnect = @mysql_connect("localhost", "root", "");
	if ($DBConnect === FALSE)
		echo "<p>Unable to connect to the database server.</p>" . "<p>Error Code " . mysql_errno($DBConnect) . ": " . mysql_errno($DBConnect) . "</p>";
	else {
		$DBName = "Conference";
		if (!@mysql_select_db($DBName, $DBConnect)) {
			$SQLstring = "CREATE DATABASE $DBName";
			$QueryResult = @mysql_query($SQLstring, $DBConnect);
			if ($QueryResult === FALSE)
				echo "<p>Unable to execute the query.</p>" . "<p>Error Code " . mysql_errno($DBConnect) . ": " . mysql_error($DBConnect) . "</p>";
		}

		$TableName = "users";
		$SQLString = "SHOW TABLES LIKE '$TableName' ";
		$QueryResult = @mysql_query($SQLstring, DBConnect);
			if (@mysql_num_rows($QueryResult) == 0) {
			$SQLstring = "CREATE TABLE $TableName(UserID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Address VARCHAR(40), City VARCHAR(30), State VARCHAR(2), Phone VARCHAR(10), email VARCHAR(40))";
			$QueryResult = @mysql_query($SQLstring, $DBConnect);
			if ($QueryResult === FALSE)
				echo "<p>Unable to create the table.</p>" . "<p>Error Code " . mysql_errno($DBConnect) . ": " . mysql_error($DBConnect) . "</p>";

		}	
	}			
mysql_close($DBConnect);
?>
<h1>Please fill out the following forms to register</h1>
<hr  />

<form action="" method="post">
<p>Name: <input type="text" name="uName" /></p>
<p>Address: <input type="text" name="uAddress" /></p>
<p>City: <input type="text" name="uCity" /></p>
<p>State: <input type="text" name="uState" size="2"/></p>
<p>Telephone: <input type="text" name="uPhone" /></p>
<p>Email: <input type="text" name="email" /></p>

</form>
<a href="CompanyForm.php" <?php SID;?> >Next</a>
</body>
</html>

This is the second page: (the one getting the error)

<?php 
session_start();
require_once("UserForm.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Company Information</title>
</head>

<body>
<?php
$DBConnect = @mysql_connect("localhost", "root", "");
	if ($DBConnect === FALSE)
		echo "<p>Unable to connect to the database server.</p>" . "<p>Error Code " . mysql_errno($DBConnect) . ": " . mysql_errno($DBConnect) . "</p>";
	else {
		$DBName = "Conference";
		if (!@mysql_select_db($DBName, $DBConnect)) {
			$SQLstring = "USE $DBName";
			$QueryResult = @mysql_query($SQLstring, $DBConnect);
			if ($QueryResult === FALSE)
				echo "<p>Unable to execute the query.</p>" . "<p>Error Code " . mysql_errno($DBConnect) . ": " . mysql_error($DBConnect) . "</p>";
		}

		$TableName = "company";
		$SQLString = "SHOW TABLES LIKE '$TableName' ";
		$QueryResult = @mysql_query($SQLstring, DBConnect);
			if (@mysql_num_rows($QueryResult) == 0) {
			$SQLstring = "CREATE TABLE $TableName(CompanyID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, UserID " . mysqli_insert_id('UserID') . " ,CompanyName VARCHAR(100), Address VARCHAR(40), City VARCHAR(30), State VARCHAR(2), Phone VARCHAR(10), position VARCHAR(40))";
			$QueryResult = @mysql_query($SQLstring, $DBConnect);
			if ($QueryResult === FALSE)
				echo "<p>Unable to create the table.</p>" . "<p>Error Code " . mysql_errno($DBConnect) . ": " . mysql_error($DBConnect) . "</p>";

		}	
	}			
mysql_close($DBConnect);
?>

<h1>Please fill out the following forms to register</h1>
<hr  />

<form action="" method="post">
Company Name: <input type="text" name="cName" />
Address: <input type="text" name="cAddress" />
City: <input type="text" name="cCity" />
State: <input type="text" name="cState" size="2"/>
Telephone: <input type="text" name="cPhone" />
Position: <input type="text" name="position" />

</form>
<a href="SeminarForm.php" <?php SID; ?> >Next</a>
</body>
</html>

The error is: Warning: mysqli_insert_id() expects parameter 1 to be mysqli, string given.

I know I need a variable to put into the function(not a string like I have), but how can I declare a variable on a previous auto_incremented primary key?

Please, someone point me in the right direction!

Thanks,

WDGirlie

mod edit: code in code tags

PFMaBiSmAd · September 13, 2012

In the future, please post your code using the forum's

bbcode tags (the # edit form button.) I modified your post this time.

Jessica · September 13, 2012

Stop suppressing errors, and start capturing and displaying them.

Why are you CREATING A NEW TABLE??

Edit: also turn on error reporting, you have a ton of errors.

kicken · September 13, 2012

Your code does not make much sense. Why are you creating tables? You should create the tables before hand using something like phpmysqladmin or mysql command line tool. To add data to them (such as when a person submits your form) you want to run an INSERT INTO with the new data.

Two additional notes for when you get there:

1) Your using mysql_* functions for your queries and connection, which means you need to be using mysql_insert_id, not mysqli_insert_id.

2) You have to call mysql_insert_id right after you run the INSERT INTO query that would generate the ID. You cannot do it on another page or after you do other queries. You'll need to get the generated ID on the first page then save it in the session or pass it as a URL parameter to access it on the other pages.

PFMaBiSmAd · September 13, 2012

Edit: In addition to what kicken and jesirose posted above ^^^ while I was typing this -

A few suggestions -

1) Remove all the @ error suppressors from your code and never put any in any code you are writing. You have at least one error in your code concerning the database connection link variable that should be producing two php errors that would be pointing out the problem statement so that you could find and fix it. You should always have php's error_reporting set to E_ALL (or even better a -1) and on a development system, display_errors should be ON, on a live server display_errors should be OFF and log_errors should be ON.

2) You should not be attempting to create a database and database tables 'on the fly' as a user submits data. You should be creating the database and any tables your application needs, up front, either using an installation script or directly by using any available database management tool. The main reason is that the database user you use for an application should only have the minimum permissions necessary that the application needs, so that any security holes in your code won't permit a hacker to create, drop, or alter your tables or database.

3) You don't need all the extra code to test if a database or table exists before creating it. Just use the IF NOT EXISTS syntax in the CREATE statements.

4) Your mysqli_insert_id() statement is trying to use the mysqli (with an i) extension. All the rest of your code is using the mysql (no i) extension. You cannot mix statements from the two different extensions. Also, php is actively discouraging using the mysql (no i) extension for writing new code. You should be learning and using the mysqli (with an i) extension.

5) The place you have the mysqli_insert_id() statement, inside the CREATE TABLE query, makes no sense. In fact, there's no INSERT query anywhere in the posted code, so there is nothing for a mysqli_insert_id() statement to get the last insert id from.

6) Since the posted code doesn't show where you are inserting data to get the last insert id from and where you are trying to use that value, its not possible to directly solve your current problem.

WDGirlie · September 14, 2012

Thanks to everyone who responded. I will take your advice and take care of all my errors.

You have given great detail, which I appericate.

Thanks to everybody again!

Sign In

Getting an Auto Incremented Primary Key

Recommended Posts

WDGirlie

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

kicken

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

WDGirlie

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information