PHP MySQL Transaction problem
Posted: Fri Dec 02, 2011 4:43 pm
Hi Everyone,
I got a PHP / MySQL Transaction to function as a transaction, but I have some behavior I can't figure out. In the following script I insert data into the “events” table and then I grab the insert ID using mysqli_insert_id(). The subsequent queries do not insert this ID into the other tables; they insert a zero instead. Since I've registered a session variable and given it the last insert ID's value, it should be inserted into the other tables.
Farther down the script, I rename the uploaded photos and they end up with the proper number / letter combination. Does anyone know why this is happening? I've tried everything I can think of to solve the problem. Thanks much in advance!
Cheers,
Rick
I got a PHP / MySQL Transaction to function as a transaction, but I have some behavior I can't figure out. In the following script I insert data into the “events” table and then I grab the insert ID using mysqli_insert_id(). The subsequent queries do not insert this ID into the other tables; they insert a zero instead. Since I've registered a session variable and given it the last insert ID's value, it should be inserted into the other tables.
Code: Select all
<?php
// SET THE SESSION VARIABLE
session_register('insert');
// CODE HERE TO VARIFY USER
// CODE TO CREATE VARIBLE NAMES, FILTER INPUT AND CREATE END DATE
// DO PHP / MySQL TRANSACTION
$query1 = ("INSERT INTO events VALUES ('".$user_id."', NULL, 04, NOW(), NOW(), '".$end_date."')");
$query2 = ("INSERT INTO equip_info VALUES (NULL, '".$_SESSION['insert']."' , 04, '".$make."',
'".$model."', '".$style."', '".$year."', '".$descrp."',
'".$settings."', 'No', 'Yes', NOW(), '".$user_name."')");
$query3 = ("INSERT INTO pay_info VALUES(NULL, '".$_SESSION['insert']."',
'Payment and number', 19.99, Now(), '".$user_name."')");
$query4 = ("INSERT INTO photo_bin VALUES (NULL, '".$_SESSION['insert']."',
CONCAT('".$_SESSION['insert']."', 'a.jpg'), CONCAT('".$_SESSION['insert']."', 'b.jpg'),
CONCAT('".$_SESSION['insert']."', 'c.jpg'), CONCAT('".$_SESSION['insert']."', 'd.jpg'),
CONCAT('".$_SESSION['insert']."', 'e.jpg'))");
// Start the transaction
mysqli_query($conn, "START TRANSACTION");
$result1 = mysqli_query($conn, $query1);
$insert = mysqli_insert_id($conn);
$_SESSION['insert'] = $insert;
echo " From middle of queries - the ID is ".$_SESSION['insert']."<br />"; // ECHO FOR TESTING PURPOSES
$result2 = mysqli_query($conn, $query2);
$result3 = mysqli_query($conn, $query3);
$result4 = mysqli_query($conn, $query4);
if(!$result1 || !$result2 || !$result3 || !$result4) {
mysqli_rollback($conn);
// Dispaly the error message
no_insert_qry();
} else {
mysqli_commit($conn);
// PROCESS THE PHOTOS FOR AND MOVE THEM TO THE FOLDER
// Check for user_file errors
userfile_error();
// Check for black listed extensions
black_list();
// Check for propper mime type
mimeType();
// Move the images to the uploads folder in TFC site
move_imgs();
// Change the name of each photo
renameFile(); // THIS USES $_SESSION['insert'] AND RENAMES THE FILES CORRECTLY
// display the page name in the tab
do_html_header('Equipment Info Insert Page');
// display the banner and sidebars
display_banner();
login_l_sidebar();
login_r_sidebar();
// Attempt to tell user what the title of the ad is
insert_sucsess($title);
}
// CODE HER TO FINISH OUT THE PAGE
?>Cheers,
Rick