Jump to content

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

tooNight

By tooNight
in MySQL Help

 Share

Recommended Posts

tooNight Newbie

tooNight

Posted
Posted
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 Advanced Member

fenway

Posted
Posted
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.
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.