mysql_insert_id() - Insert Newly Created Incremented ID Into Another Table Problem

tooNight · March 19, 2006

Hi there,

I am creating a login page where racket players can sign up to the site and then be placed on a world rankings website. I am trying when the players sign up and inout information in the players table that the playerID created can then be used in playerRankings so that playerRankingsID and pRplayerID can be the same number. Player ID is auto incrementing, did some reserach on the internet and found mysql_insert_id() which needs to be put directly below the insert query.

The only problem is when i echo $pID it is always 0 and the query $insertIntoRankings does not run and no fields are created in this table but the $insert query above works correctly.

Just wondering if anyone could give me some feedback and tell me where I am going wrong, I am a relative newcomer to php and enjoying it so far.

Cheers

Leon

[a href=\"http://www.squashcommunities.com/library/register.php\" target=\"_blank\"]Registration Page Here[/a]

register.php
[code=php:0]<?php
require('db_connect.php'); // database connect script.
?>

<?php

if (isset($_POST['submit'])) { // if form has been submitted
/* check they filled in what they supposed to,
passwords matched, username
isn't already taken, etc. */

if (!$_POST['emailU'] | !$_POST['passwd'] | !$_POST['passwd_again'] ) {
die('You did not fill in a required field.');
}

// check if username exists in database.

if (!get_magic_quotes_gpc()) {
$_POST['emailU'] = addslashes($_POST['emailU']);
}

$name_check = $db_object->query("SELECT email FROM players WHERE email = '".$_POST['emailU']."'");

if (DB::isError($name_check)) {
die($name_check->getMessage());
}

$name_checkk = $name_check->numRows();

if ($name_checkk != 0) {
die('Sorry, the email address: <strong>'.$_POST['emailU'].'</strong> is already taken, please make sure you havent already registered');
}

// check passwords match

if ($_POST['passwd'] != $_POST['passwd_again']) {
die('Passwords did not match.');
}

// check e-mail format

if (!preg_match("/.*@.*..*/", $_POST['emailU']) | preg_match("/(<|>)/", $_POST['emailU'])) {
die('Invalid e-mail address.');
}

// no HTML tags in username, website, location, password

$_POST['emailU'] = strip_tags($_POST['emailU']);
$_POST['passwd'] = strip_tags($_POST['passwd']);

// check show_email data

if ($_POST['show_email'] != 0 & $_POST['show_email'] != 1) {
die('Nope');
}

// now we can add them to the database.
// encrypt password

$_POST['passwd'] = md5($_POST['passwd']);

if (!get_magic_quotes_gpc()) {
$_POST['passwd'] = addslashes($_POST['passwd']);
$_POST['emailU'] = addslashes($_POST['emailU']);
}

$regdate = date('d m, Y');

$insert = "INSERT INTO players (
playerID,
email,
password,
regdate,
playerName,
address1,
address2,
town,
county,
postcode,
country,
telephoneNum,
show_email,
last_login)
VALUES (
NULL,
'".$_POST['emailU']."',
'".$_POST['passwd']."',
'$regdate',
'".$_POST['name']."',
'".$_POST['address1']."',
'".$_POST['address2']."',
'".$_POST['town']."',
'".$_POST['county']."',
'".$_POST['postCode']."',
'".$_POST['country']."',
'".$_POST['teleNum']."',
'".$_POST['show_email']."',
'Never')";

$pID = mysql_insert_id();

$insertIntoRankings = "INSERT INTO playerRankings (
pRankingsID,
pRplayerID,
pRankingScore)
VALUES (
".$pID.",
".$pID.",
0,
0)";

$add_member = $db_object->query($insert);

$add_to_rankings = $db_object->query($insertIntoRankings);

if (DB::isError($add_member)) {
die($add_member->getMessage());
}

$db_object->disconnect();
?>
<h1>Registered</h1>

<p>Thank you, your information has been added to the database, you may now <a href="login.php" title="Login">log in</a>.</p>

<?php
} else { // if form hasn't been submitted
?>
<html>
<head>
<script type="text/javascript" src="countrysAndCounties.js"></script>
<title>Register</title>
</head>
<body>
<h1>Register</h1>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<table align="center" border="1" cellspacing="0" cellpadding="3">
<tr><td>Email*:</td><td>
<input type="text" name="emailU" maxlength="100">
</td></tr>
<tr><td>Password*:</td><td>
<input type="password" name="passwd" maxlength="50">
</td></tr>
<tr><td>Confirm Password*:</td><td>
<input type="password" name="passwd_again" maxlength="50">
</td></tr>
<tr><td>Name</td><td>
<input type="text" name="name" maxlength="100">
</td></tr>
<tr><td>address1</td><td>
<input type="text" name="address1" maxlength="150">
</td></tr>
<tr><td>address2</td><td>
<input type="text" name="address2" maxlength="150">
</td></tr>
<tr><td>Town</td><td>
<input type="text" name="town" maxlength="150">
</td></tr>
<tr><td>Post Code</td><td>
<input type="text" name="postCode" maxlength="150">
</td></tr>
<tr><td>Country</td><td>
<select id='countrySelect' name='country' onchange='populateState()'></select>
</td></tr>
<tr><td>County</td><td>
<select id='stateSelect' name='county'></select>
<script type="text/javascript">initCountry('US');</script>
</td></tr>
<tr><td>telephoneNum</td><td>
<input type="text" name="teleNum" maxlength="150">
</td></tr>
<tr><td>Show E-Mail?</td><td>
<select name="show_email">
<option value="1" selected="selected">Yes</option>
<option value="0">No</option></select>
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="submit" value="Sign Up">
</td></tr>
</table>
</form>
</body>
</html>
<?php

}

?>[/code]

db_connect.php
[code=php:0]<?php

//require the PEAR::DB classes.

require_once 'DB.php';

$db_engine = 'mysql';
$db_user = '*******';
$db_pass = '**********';
$db_host = '***************';
$db_name = '***********';

$datasource = $db_engine.'://'.
$db_user.':'.
$db_pass.'@'.
$db_host.'/'.
$db_name;

$db_object = DB::connect($datasource, TRUE);

/* assign database object in $db_object,

if the connection fails $db_object will contain

the error message. */

// If $db_object contains an error:

// error and exit.

if(DB::isError($db_object)) {
die($db_object->getMessage());
}

$db_object->setFetchMode(DB_FETCHMODE_ASSOC);

// we write this later on, ignore for now.

include('check_login.php');

?>[/code]

fenway · March 19, 2006

Well, I've always used the MySQL function LAST_INSERT_ID() instead. But the PHP function would return nothing if either the column wasn't auto-increment, you used IGNORE/ON DUPLICATE KEY UPDATE, or if there was an error in your INSERT.

Sign In

mysql_insert_id() - Insert Newly Created Incremented ID Into Another Table Problem

Recommended Posts

tooNight

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information