Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with transactions in PHP

Status
Not open for further replies.
deeciple

deeciple

Technical User
Mar 1, 2012
70
US
Hi All,

I am having a hard time getting SQL transactions to work in PHP. I am using the following code in my form's process.php file and I am being redirected to the success.html page but when I check the database, there is no data being written to the tables. If I remove the transaction and just insert to one table everything works fine. Any help will be much appreciated. Thanks in advance:

Code: 
<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>

<?php
	$DiscrepType = mysql_prep($_POST['List1']);
	$DiscrepDetail = mysql_prep($_POST['List2']);
	$Airdate = $_POST['Airdate'];
	$Description = mysql_prep($_POST['Description']);
	$Resolution = mysql_prep($_POST['Resolution']);
	$OnAirVariance = mysql_prep($_POST['OnAirVariance']);
	$EquipID = mysql_prep($_POST['EquipID']);
	$EquipLoc = mysql_prep($_POST['EquipLoc']);
	$StartTime = $_POST['StartTime'];
	$EndTime = $_POST['EndTime'];
	$MaterialID = $_POST['MaterialID'];
	$Title = $_POST['Title'];
?>
<?php
	$query = BEGIN;

			"INSERT INTO tblonairactivity (
				DiscrepType, DiscrepDetail, Airdate, Description, Resolution, OnAirVariance, EquipID, EquipLoc
			) VALUES (
				'{$DiscrepType}', '{$DiscrepDetail}', '{$Airdate}', '{$Description}', '{$Resolution}', '{$OnAirVariance}', '{$EquipID}', '{$EquipLoc}'
			)";

			"INSERT INTO tblaffectedprog (
				StartTime, EndTime, MaterialID, Title
			) VALUES (
				'{$StartTime}', '{$EndTime}', '{$MaterialID}', '{$Title}'
			)";

			COMMIT;
	$result = mysql_query($query, $connection);
	if ($result) {
		// Success!
		redirect_to("success.html");
	} else {
		// Display error message.
		echo "<p>Record creation failed.</p>";
		echo "<p>" . mysql_error() . "</p>";
	}
?>

<?php mysql_close($connection); ?>
 
Sort by date Sort by votes
Have had the same issue myself in the past. I believe you have to close then reopen the query between each table write.
 
Upvote 0 Downvote
Hi

Huh ? What are those string are doing there ? Currently they are just levitating in the air and nothing happens with them.
Code: 
[navy]$query[/navy] [teal]=[/teal] [green][i]"BEGIN"[/i][/green][teal];[/teal]
[navy]$result[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][navy]$query[/navy][teal],[/teal] [navy]$connection[/navy][teal]);[/teal]

[navy]$query[/navy] [teal]=[/teal] [green][i]"INSERT INTO tblonairactivity ( DiscrepType, DiscrepDetail, Airdate, Description, Resolution, OnAirVariance, EquipID, EquipLoc ) VALUES ( '{$DiscrepType}', '{$DiscrepDetail}', '{$Airdate}', '{$Description}', '{$Resolution}', '{$OnAirVariance}', '{$EquipID}', '{$EquipLoc}' )"[/i][/green][teal];[/teal]
[navy]$result[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][navy]$query[/navy][teal],[/teal] [navy]$connection[/navy][teal]);[/teal]

[navy]$query[/navy] [teal]=[/teal] [green][i]"INSERT INTO tblaffectedprog ( StartTime, EndTime, MaterialID, Title ) VALUES ( '{$StartTime}', '{$EndTime}', '{$MaterialID}', '{$Title}' )"[/i][/green][teal];[/teal]
[navy]$result[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][navy]$query[/navy][teal],[/teal] [navy]$connection[/navy][teal]);[/teal]

[navy]$query[/navy] [teal]=[/teal] [green][i]"COMMIT"[/i][/green][teal];[/teal]
[navy]$result[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][navy]$query[/navy][teal],[/teal] [navy]$connection[/navy][teal]);[/teal]

Feherke.
[link feherke.github.com/][/url]
 
Upvote 0 Downvote
see how you get on with this :

PHP: 
<?php require_once("includes/functions.php");

	$DiscrepType = mysql_prep($_POST['List1']);
	$DiscrepDetail = mysql_prep($_POST['List2']);
	$Airdate = $_POST['Airdate'];
	$Description = mysql_prep($_POST['Description']);
	$Resolution = mysql_prep($_POST['Resolution']);
	$OnAirVariance = mysql_prep($_POST['OnAirVariance']);
	$EquipID = mysql_prep($_POST['EquipID']);
	$EquipLoc = mysql_prep($_POST['EquipLoc']);
	$StartTime = $_POST['StartTime'];
	$EndTime = $_POST['EndTime'];
	$MaterialID = $_POST['MaterialID'];
	$Title = $_POST['Title'];

[COLOR=red]	require("includes/connection.php");[/color]
	$query = "INSERT INTO tblonairactivity (
				DiscrepType, DiscrepDetail, Airdate, Description, Resolution, OnAirVariance, EquipID, EquipLoc
			) VALUES (
				'{$DiscrepType}', '{$DiscrepDetail}', '{$Airdate}', '{$Description}', '{$Resolution}', '{$OnAirVariance}', '{$EquipID}', '{$EquipLoc}'
			)";

[COLOR=red]	if (!mysql_query($sql,$connection))[/color]
[COLOR=red]	{ die('Error: ' . mysql_error()); }[/color]
[COLOR=red]	mysql_close($connection);[/color]

[COLOR=red]	require("includes/connection.php");[/color]
	$query = "INSERT INTO tblaffectedprog (
				StartTime, EndTime, MaterialID, Title
			) VALUES (
				'{$StartTime}', '{$EndTime}', '{$MaterialID}', '{$Title}'
			)";


	if (!mysql_query($sql,$connection))
	{ die('Error: ' . mysql_error()); }
	mysql_close($connection);

		redirect_to("success.html");
?>
 
Upvote 0 Downvote
Hi max2474,

Thanks for your reply. What exactly do you mean by "close then reopen the query between each table write"? I am not familiar with SQL and just learning PHP.

Thank you,

Ken
 
Upvote 0 Downvote
Hi

max2474, and where would be the transactions there ? ( Hint : uncommitted transactions are rolled back if the connection gets closed. )

Feherke.
[link feherke.github.com/][/url]
 
Upvote 0 Downvote
Am not far in front of you... have been posting a few questions myself..lol

My understanding is that when you connect to a table in the database, you cant just switch to a different table. You need to stop the connection (sql query) completely and start it again to connect to the next table.

Try the code above and see what happens.
 
Upvote 0 Downvote
feherke -
That statement just about proves that im still learning myself lol, as I have absolutely no idea :/
 
Upvote 0 Downvote
Hi

max2474, in a few points :
[ul]
[li]Connection to the SQL database server usually is never closed in PHP scripts. ( They are closed automatically anyway when the script terminates. )[/li]
[li]Connections to the SQL database server can even be persistent, in which case ( however transparent to the script ) a given number of connections are kept open and next scripts will receive an already open connection. ( This may improve speed by eliminating connection time. )[/li]
[li]Database transaction are a way to group together SQL statements to make sure none of them gets executed if another one is not. ( For example when moving $100 from your bank account to Tek-Tips bank account you want to be sure the $100 is not subtracted from your account if adding it to Tek-Tips account failed for any reason. )[/li]
[/ul]

Feherke.
[link feherke.github.com/][/url]
 
Upvote 0 Downvote
OK... I do understand that both sides of the transaction should complete at the same time.

I do however, have a script in my current project very similar to the one above and could not get the 2nd database to write either, unless i closed the connection first.

I am guessing that both deeciple and myself are a little bit behind when it comes to comprehending and solutionising the problems you are talking about.
 
Upvote 0 Downvote
Hi Feherke,

Thank you for the link to the wiki. I still don't know why the transaction is not writing to my database. Can you help?

Kind regards,

Ken
 
Upvote 0 Downvote
Hi

Ken said:
I still don't know why the transaction is not writing to my database.
Click to expand...
Tried the code fragment I posted on 15 May 12 12:51 ? If yes, show us your modified code so we get up to date with your tries.


Feherke.
[link feherke.github.com/][/url]
 
Upvote 0 Downvote
Hi Feherke,

I tried your suggestion but did not have any luck. Here is my modified code:

Code: 
<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>

<?php
	$DiscrepType = mysql_prep($_POST['List1']);
	$DiscrepDetail = mysql_prep($_POST['List2']);
	$Airdate = $_POST['Airdate'];
	$Description = mysql_prep($_POST['Description']);
	$Resolution = mysql_prep($_POST['Resolution']);
	$OnAirVariance = mysql_prep($_POST['OnAirVariance']);
	$EquipID = mysql_prep($_POST['EquipID']);
	$EquipLoc = mysql_prep($_POST['EquipLoc']);
	$StartTime = mysql_prep($_POST['txtStartTime']);
	$EndTime = mysql_prep($_POST['txtEndTime']);
	$MaterialID = mysql_prep($_POST['txtMaterialID']);
	$Title = mysql_prep($_POST['txtTitle']);
?>
<?php
	$query = "BEGIN";
	$result = mysql_query($query, $connection);

	$query = "INSERT INTO tblonairactivity (
			DiscrepType, DiscrepDetail, Airdate, Description, Resolution, OnAirVariance, EquipID, EquipLoc
			) VALUES (
			'{$DiscrepType}', '{$DiscrepDetail}', '{$Airdate}', '{$Description}', '{$Resolution}', '{$OnAirVariance}', '{$EquipID}', '{$EquipLoc}'
			)";
	$result = mysql_query($query, $connection);

	$query = "INSERT INTO tblaffectedprog (
			StartTime, EndTime, MaterialID, Title
			) VALUES (
			'{$StartTime}', '{$EndTime}', '{$MaterialID}', '{$Title}'
			)";
	$result = mysql_query($query, $connection);
	$query = "COMMIT";
	$result = mysql_query($query, $connection);
	if ($result) {
		// Success!
		redirect_to("success.html");
	} else {
		// Display error message.
		echo "<p>Record creation failed.</p>";
		echo "<p>" . mysql_error() . "</p>";
	}
?>

<?php mysql_close($connection); ?>
 
Upvote 0 Downvote
As I am reading up on sql transactions and beginning to understanding your posts, it is dawning on me that I need to rewrite all my code... :( There is tones...

May even - by default - solve the question I posted myself :)

Thanks for the help feherke.

Didnt mean to butt in on your question Ken.. Guess its not always a good idea for noobs to help other noobs...lol







 
Upvote 0 Downvote
Feherke,

Also, the table tblAffectedProg is on the many side of a relationship with table tblOnAirActivity. Is there something to this?

Ken
 
Upvote 0 Downvote
Hi

On your insistence I put together a minimal test case to be sure I wrote it correctly. ( My transaction-related knowledge mostly comes from other databases, not MySQL. ) Well, it works for me.
PHP: 
[COLOR=darkgoldenrod]mysql_connect[/color][teal]([/teal][green][i]'localhost'[/i][/green][teal],[/teal] [green][i]'master'[/i][/green][teal],[/teal] [green][i]'ofpuppets'[/i][/green][teal]);[/teal]
[COLOR=darkgoldenrod]mysql_select_db[/color][teal]([/teal][green][i]'deeciple'[/i][/green][teal]);[/teal]

[navy]$value[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]rand[/color][teal]();[/teal]
[b]echo[/b] [green][i]"Value to insert is $value.\n"[/i][/green][teal];[/teal]

[COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][green][i]'begin'[/i][/green][teal]);[/teal]
[COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][green][i]"insert into tblonairactivity values ( $value )"[/i][/green][teal]);[/teal]
[COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][green][i]"insert into tblaffectedprog values ( $value )"[/i][/green][teal]);[/teal]
[COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][green][i]'commit'[/i][/green][teal]);[/teal]
Note that the use of transactions requires InnoDB storage engine. With other engines the transactions are ignored, but the other statements still get executed.

The next step in debugging would be to copy that [tt]if [teal]([/teal][navy]$result[/navy][teal])[/teal] [teal]{[/teal] [gray]/*...*/[/gray] [teal]}[/teal][/tt] check after every [tt]mysql_query()[/tt] function call. Then make sure you have debugging turned on while testing:
Code: 
[url=http://php.net/ini_set/]ini_set[/url]('[url=http://php.net/errorfunc.configuration/#ini.display-errors]display_errors[/url]','on');
[url=http://php.net/error_reporting]error_reporting[/url]([url=http://php.net/errorfunc.constants/#errorfunc.constants.errorlevels.e-all]E_ALL[/url]);

Ken said:
Also, the table tblAffectedProg is on the many side of a relationship with table tblOnAirActivity. Is there something to this?
Click to expand...
Sorry, I not understand this. If you mean there are foreign key constraints, that may affect transactions depending on the set isolation level.


Feherke.
[link feherke.github.com/][/url]
 
Upvote 0 Downvote

deeciple said:
Also, the table tblAffectedProg is on the many side of a relationship with table tblOnAirActivity. Is there something to this?
Click to expand...

feherke said:
Sorry, I not understand this. If you mean there are foreign key constraints, that may affect transactions depending on the set isolation level.
Click to expand...

Thank you for continuing to work with me on this Feherke.

You are correct. There are foreign key constraints on the table tblAffectedProg (delete-restrict and update-cascade). I will do a test later with a clone of this table having the foreign key constraints removed and see if I get better reults. I will also try your recommendations above and post back with my results.

I don't understand the benefit of creating these table relationships. I come from an MS Access background and I am new to web developement and trying to learn on my own. In Access you use table relationships so that when running queries the child records will automatically be returned with their parent records. Also, (at least in Access) when creating child records, the value of the linked parent field is automatically entered in the linked child record. I thought that it would work the same way for mySQL and maybe it does, I just need to read up some more.

Kind regards,

Ken
 
Upvote 0 Downvote
Hi

Ken said:
I don't understand the benefit of creating these table relationships.
Click to expand...
As our fellow member SQLSister's signature quotes :
ESquared said:
NOTHING is more important in a database than integrity.
Click to expand...
Constraints are serving that purpose. Foreign keys ensure no data exists without all its dependencies. They can reduce the amount of consistency checks, but not the other operations.

Ken said:
Also, (at least in Access) when creating child records, the value of the linked parent field is automatically entered in the linked child record.
Click to expand...
For that the closest in SQL is the view. They are mostly used for [tt]select[/tt]ing only, but is possible to write Updatable and Insertable Views.


Feherke.
[link feherke.github.com/][/url]
 
Upvote 0 Downvote
Hi All,

Happy to report that I have it working! By assigning each query to a variable and using an If statement I was able to get transactions working. I don't know why it would not work the other way but I am happy it is working :)

Now to my next challenge lol.

Thanks for all your input everyone. Here is the working code:
Code: 
<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>

<?php
	$DiscrepType = mysql_prep($_POST['List1']);
	$DiscrepDetail = mysql_prep($_POST['List2']);
	$Airdate = $_POST['Airdate'];
	$Description = mysql_prep($_POST['Description']);
	$Resolution = mysql_prep($_POST['Resolution']);
	$OnAirVariance = mysql_prep($_POST['OnAirVariance']);
	$EquipID = mysql_prep($_POST['EquipID']);
	$EquipLoc = mysql_prep($_POST['EquipLoc']);
	$StartTime = mysql_prep($_POST['txtStartTime']);
	$EndTime = mysql_prep($_POST['txtEndTime']);
	$MaterialID = mysql_prep($_POST['txtMaterialID']);
	$Title = mysql_prep($_POST['txtTitle']);
?>
<?php
	$query1 = "INSERT INTO tblonairactivity (
			DiscrepType, DiscrepDetail, Airdate, Description, Resolution, OnAirVariance, EquipID, EquipLoc
			) VALUES (
			'{$DiscrepType}', '{$DiscrepDetail}', '{$Airdate}', '{$Description}', '{$Resolution}', '{$OnAirVariance}', '{$EquipID}', '{$EquipLoc}'
			)";

	$query2 = "INSERT INTO tblaffectedprog (
			tblOnAirActivityID, StartTime, EndTime, MaterialID, Title
			) VALUES (
			LAST_INSERT_ID(), '{$StartTime}', '{$EndTime}', '{$MaterialID}', '{$Title}'
			)";
	
	$result = FALSE;
	if (mysql_query('BEGIN')) {
	    if (mysql_query($query1) &&
	        mysql_query($query2))
	        $result = mysql_query('COMMIT'); // both queries looked OK, save
	    else
	        mysql_query('ROLLBACK'); // problems with queries, no changes.
	    }
	if ($result) {
		// Success!
		redirect_to("success.html");
	} else {
		// Display error message.
		echo "<p>Record creation failed.</p>";
		echo "<p>" . mysql_error() . "</p>";
	}
?>

<?php mysql_close($connection); ?>

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

cdlvj
  • Question
PHP Debugging
Replies
0
Views
99
cdlvj
cdlvj
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
365
SashaBuilder3
SashaBuilder3
PeDa
  • Locked
  • Question
Unwanted spaces in php HTML mail 1
Replies
4
Views
306
PeDa
PeDa
Mandy_crw
  • Locked
  • Question
How to send sms in PHP using usb gsm modem?
Replies
1
Views
329
vishvajit
vishvajit
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
402
EdLentz
EdLentz

Part and Inventory Search

Sponsor

Back
Top