Foreign ID doesn't increment with the other tables.

[Namtip]

SET UP: Windows vista

# XAMPP 1.7.3,

# Apache 2.2.14 (IPv6 enabled) + OpenSSL 0.9.8l

# MySQL 5.1.41 + PBXT engine

# PHP 5.3.1

# phpMyAdmin

 

Problem: The name_id column from the card_numbers table doesn't increment at all. Even if if I put in two rows of data I'm left with the name_id not increasing.

 

Main Goal: I want that column to increment with the same values as the other tables.

 

Code for Mysql database:

<?php
require 'db.inc.php';

$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

$query = 'CREATE TABLE IF NOT EXISTS subscriptions (
			name_id						INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
			name						VARCHAR(50)	NOT NULL,
			email_address				VARCHAR(50),
			membership_type				VARCHAR(50),

	PRIMARY KEY (name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

// create the user information table
$query = 'CREATE TABLE IF NOT EXISTS site_user_info (
			name_id						INTEGER UNSIGNED NOT NULL,
			terms_and_conditions		VARCHAR(50) NOT NULL,
			name_on_card				VARCHAR(50),
			credit_card_number			VARCHAR(50),

	FOREIGN KEY (name_id) REFERENCES subscriptions(name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

$query = 'CREATE TABLE IF NOT EXISTS card_numbers (
			name_id					INTEGER UNSIGNED NOT NULL,
			credit_card_expiration_data	VARCHAR(50),

	FOREIGN KEY (name_id) REFERENCES subscriptions(name_id)
    )
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

echo 'Success!';
?>

 

Code of the data being inserted into mysql from forms:

<?php   

//let's start our session, so we have access to stored data
session_start();

session_register('membership_type');
session_register('terms_and_conditions');

include 'db.inc.php';

$db = mysql_connect('localhost', 'root', '') or 
    die ('Unable to connect. Check your connection parameters.');
mysql_select_db('ourgallery', $db) or die(mysql_error($db));
  

//let's create the query
$query = sprintf("INSERT INTO subscriptions (
		name_id, name, email_address, membership_type)
	VALUES ('%s','%s','%s','%s')",
name_id,
mysql_real_escape_string($_SESSION['name']),
mysql_real_escape_string($_SESSION['email_address']),
mysql_real_escape_string($_SESSION['membership_type']));

//let's run the query
$result = mysql_query($query, $db) or die(mysql_error($db));

$name_id = mysql_insert_id();

$query = sprintf("INSERT INTO site_user_info (
		name_id, terms_and_conditions, name_on_card,
		credit_card_number	)
	VALUES ('%s','%s','%s','%s')",
mysql_insert_id(),
mysql_real_escape_string($_SESSION['terms_and_conditions']),
mysql_real_escape_string($_POST['name_on_card']),
mysql_real_escape_string($_POST['credit_card_number']));

//let's run the query
$result = mysql_query($query, $db) or die(mysql_error($db));

$name_id = mysql_insert_id();

$query = sprintf("INSERT INTO card_numbers (
		 name_id, credit_card_expiration_data)
	VALUES ('%s','%s')",
mysql_insert_id(),
mysql_real_escape_string($_POST['credit_card_expiration_data']));

$result = mysql_query($query, $db) or die(mysql_error($db));
echo '$result';

?>

 

Should I be JOINING these tables together? I don't want the IDs to get muddled up otherwise it will screw up my database.

[jcbones]

Try:

 

$query = sprintf("INSERT INTO site_user_info (
name_id, terms_and_conditions, name_on_card,
credit_card_number
)	
VALUES (LAST_INSERT_ID(),'%s','%s','%s')",
mysql_real_escape_string($_SESSION['terms_and_conditions']),
mysql_real_escape_string($_POST['name_on_card']),
mysql_real_escape_string($_POST['credit_card_number']));

 

Do the same for "card_numbers".  This should fix the last inserted ID from the insert into "subscriptions" into the next two tables.

[Namtip]

Thanks that works great