1 Replies - 4215 Views - Last Post: 11 August 2009 - 05:57 AM Rate Topic: -----

#1 pjamrisk   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 02-March 09

Using a select query to insert values froma temp table

Post icon  Posted 11 August 2009 - 04:39 AM

Hello everyone,
Have not posted in a while since I been doing better at my PHP coding over here, but as always I found another roadblock. I been developing a shopping cart in PHP with a little of ajax (trying to stay away for intranet policy issues). But anyways, at the end of my cart, I have the selected items in the temporary table with a named Work order via a session (static Value generated by another system). So I query the temp table with the username as a filter, then inside a while loop I try to run the insert for each line. Now, I know this is not the most efficient way, but the amount of transaction parts is not going to be bigger than 5 at a time, or more than 6 simultaneusly. But at the time of execution it drops me with a sql query error for some reason that does not make sense at all.
either way, here is the code.
session_start();

//Define Session Variables
$User = $_SESSION['UserID'];

//Set the Static Variables
$TDID = '5'; //Checkout is 2 in the database
//$mysqldate = date( 'YYYY/mm/dd HH:ii:ss', $phpdate ); //will do once inser works
//$phpdate = strtotime( $mysqldate ); // will do once insert works

//Connection script to the server
$weberp = mysql_connect($hostname_weberp, $username_weberp, $password_weberp)
or die('Could not connect: ' . mysql_error());

//select database
mysql_select_db($database_weberp, $weberp);

//Select The database
$bool = mysql_select_db($database_weberp, $weberp);
if ($bool === False){
		print "can't find $database_weberp";
}

$query  = "SELECT
  TempCart.ProductID,
  TempCart.UnitsCheckOut,
  TempCart.UserID,
  TempCart.PartName,
  TempCart.PartNumber,
  TempCart.WOReference,
  TempCart.VISA,
  TempCart.PONumber,
  TempCart.PMNumber
FROM
  TempCart
WHERE
  TempCart.UserID = '$User'";

//process the result
$result = mysql_query($query, $weberp);
//check for errors on the result
if(!$result){
	die("SQL Query ERROR! " . mysql_error()); // if the result is not a proper result then we output the error
}

while($row = mysql_fetch_array($result)){
	//Set each line to a variable
	$ProductID = $row['ProductID'];
	$PartName = $row['PartName'];
	$PartNumber = $row['PartNumber'];
	$WONumber = $row['WOReference'];
	$UnitsCheck_In_Out = $row['UnitsCheckOut'];
	
	//Run the insert
	$InsertFinal = "INSERT INTO
							  PartsTransactions(
							  ProductID,
							  TDID,
							  UnitsCheck_In_Out,
							  UserId,
							  WONumber)
							VALUES(
							  '$ProductID',
							  '$TDID',
							  '$UnitsCheck_In_Out',
							  '$User',
							  '$WONumber')";
	$result = mysql_query($InsertFinal, $weberp); // This is the Standard Search
	if(!$result){
		die("SQL Insert Query ERROR! this is the printout <br> $InsertFinal <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
	}
	}

//Create Delete Query
$DELETE  = "DELETE
FROM
  TempCart
WHERE
  TempCart.UserID = '$User'";

//Execute Delete Query
$result = mysql_query($DELETE, $weberp); // This is the Standard Search
if(!$result){
	die("SQL Query ERROR! this is the printout <br> $DELETE <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
}
//unset Sessions
unset($_SESSION['POWOPMVS']);
?>
<html>
		<script type='text/javascript'>
			function delayer(){
				window.location = '<?php echo "SearchStockParts1.php" ?>'
			}
		</script>
		<body onload="setTimeout('delayer()', 500)">
		<h2>Please wait you are being sent back!</h2>
</body>
</html>
<?php
mysql_close($weberp);
?>



Any helping hand would be appreciated. Thanks,
Patrick J.

Is This A Good Question/Topic? 0
  • +

Replies To: Using a select query to insert values froma temp table

#2 pjamrisk   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 38
  • Joined: 02-March 09

Re: Using a select query to insert values froma temp table

Posted 11 August 2009 - 05:57 AM

Silly me. I found my mistake. I needed to rename the variable $result to another name for each query. no wonder why I was not able to realize the error name yesterday while working at it. Here is the cleaned code for future reference for anyone else out there.
Patrick J.
session_start();

//Define Session Variables
$User = $_SESSION['UserID'];
//Set the Static Variables
$TDID = '5'; //Checkout is 2 in the database
$mysqldate = date( 'YYYY/mm/dd HH:ii:ss', $phpdate );
$phpdate = strtotime( $mysqldate );


//Connection script to the server
$weberp = mysql_connect($hostname_weberp, $username_weberp, $password_weberp)
or die('Could not connect: ' . mysql_error());

//select database
mysql_select_db($database_weberp, $weberp);

//Select The database
$bool = mysql_select_db($database_weberp, $weberp);
if ($bool === False){
		print "can't find $database_weberp";
}

$query  = "SELECT
  TempCart.ProductID,
  TempCart.UnitsCheckOut,
  TempCart.UserID,
  TempCart.PartName,
  TempCart.PartNumber,
  TempCart.WOReference,
  TempCart.VISA,
  TempCart.PONumber,
  TempCart.PMNumber
FROM
  TempCart
WHERE
  TempCart.UserID = '$User'";

//process the result
$result = mysql_query($query, $weberp);
//check for errors on the result
if(!$result){
	die("SQL Query ERROR! " . mysql_error()); // if the result is not a proper result then we output the error
}

while($row = mysql_fetch_array($result,MYSQL_ASSOC)){
	//Set each line to a variable
	$ProductID = $row['ProductID'];
	$PartName = $row['PartName'];
	$PartNumber = $row['PartNumber'];
	$WONumber = $row['WOReference'];
	$UnitsCheck_In_Out = $row['UnitsCheckOut'];
	
	//Run the insert
	$InsertFinal = "INSERT INTO
							  PartsTransactions(
							  ProductID,
							  TDID,
							  UnitsCheck_In_Out,
							  UserId,
							  WONumber)
							VALUES(
							  '$ProductID',
							  '$TDID',
							  '$UnitsCheck_In_Out',
							  '$User',
							  '$WONumber')";
	$InsertResult = mysql_query($InsertFinal, $weberp); // This is the Standard Search
	if(!$InsertResult){
		die("SQL Insert Query ERROR! this is the printout <br> $InsertFinal <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
	}
	}

//Create Delete Query
$DELETE  = "DELETE
FROM
  TempCart
WHERE
  TempCart.UserID = '$User'";

//Execute Delete Query
$resultDelete = mysql_query($DELETE, $weberp); // This is the Standard Search
if(!$resultDelete){
	die("SQL Query ERROR! this is the printout <br> $DELETE <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
}
//unset Sessions
unset($_SESSION['POWOPMVS']);


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1