How do I use mysql_insert_id() in conjuntion with sprintf() to link tables?

[Namtip]

Set up

Windows Vista

* XAMPP 1.7.3, including:

* Apache 2.2.14 (IPv6 enabled) + OpenSSL 0.9.8l

* MySQL 5.1.41 + PBXT engine

* PHP 5.3.1

* phpMyAdmin

 

Ultimate objective: I want to insert session data into multiple tables whilst ensuring that the data is in the appropriate column.

 

Problem: I managed to insert the data into the correct tables and column, but after reading various forums I have been given the impression that if I was to have multiple site users the data's columns could get muddled up if they execute the script at the same time (hope I'm making sense, say if I'm not). The suggested method was mysql_insert_id() but I do not know how to make this work with in conjunction with sprintf().

 

As I said the script worked before I added the code with the star by it in an attempt to reach my ultimate objective.

<?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, email_address, membership_type)
	VALUES ('%s','%s','%s')",
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));

*mysql_real_escape_string($_SESSION['name']) = mysql_insert_id($db);*

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

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

*mysql_real_escape_string($_SESSION['credit_card_number']) = mysql_insert_id($db);*

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

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

?>

 

All the other facets of the script work but I get the following error message with the above script: Fatal error: Can't use function return value in write context in C:\x\xampp\htdocs\form_process.php on line 27

 

But it's not so much the error message its the ultimate objective. Any help appreciated.

[Namtip]

bump

[Psycho]

I don't see what sprintf() has anything to do with your problem. Just use mysql_insert_id() where you would you need the ID of the recently created record. The problem seems to be your queries.

 

Example:

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

The query is set to only insert three values, but you have five values!

 

Ok, forgetting about the expiration date, I will assume that you want to insert the ID from the previously inserted record into the subscription table. You should NOT be using the "name" field to link records - you have to use an auto-incrementing ID field to use mysql_insert_id(). So, let's assume the sunscriptions table does have an auto-incrementing field called "subcription_id". The above query might look something like this

$query = sprintf("INSERT INTO site_user_info
                      (subscription_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']),
                 );

 

Remember, mysql_insert_id() will be the last inserted record for the currently running process.

[Namtip]

Thank you mjdamato! I've got a new problem now