Delete inside a select

From my previous post, a simpler problem

Page 1 of 1

6 Replies - 1228 Views - Last Post: 12 August 2009 - 03:34 AM Rate Topic: -----

#1 pjamrisk  Icon User is offline

  • New D.I.C Head

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

Delete inside a select

Post icon  Posted 11 August 2009 - 08:33 AM

Hello again everyone,
I needed to add a small query to the previous code I posted help for, but this time it is to update the inventory as parts get check out.
The full code is this:
session_start();

//Define Session Variables
$User = $_SESSION['UserID'];
//Set the Static Variables
$TDID = '5'; //Checkout is 2 in the database
$mysqldate = date('Y-m-d H:i:s');

//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 Parts update
	$PartsUpdate = "UPDATE
						stockParts
					SET
						UnitsOnHand = UnitsOnHand-'$UnitsCheck_In_Out'
					Where
						PartNumber = '$ProductID'
					";
	$InsertPartsUpdate = mysql_query($PartsUpdate, $weberp); // This is the Standard Search
	if(!$InsertPartsUpdate){
		die("SQL Insert Query ERROR! this is the printout <br> $InsertPartsUpdate <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
	}
	
	//Run the insert Query
	$InsertFinal = "INSERT INTO
							  PartsTransactions(
							  ProductID,
							  TDID,
							  UnitsCheck_In_Out,
							  UserId,
							  WONumber,
							  TransactionDateTime)
							VALUES(
							  '$ProductID',
							  '$TDID',
							  '$UnitsCheck_In_Out',
							  '$User',
							  '$WONumber',
							  '$mysqldate'
							   )";
	$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 of Temptable 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']);



but my problem is in the math query for the update:
	//Run the Parts update
	$PartsUpdate = "UPDATE
						stockParts
					SET
						UnitsOnHand = UnitsOnHand-'$UnitsCheck_In_Out'
					Where
						PartNumber = '$ProductID'
					";
	$InsertPartsUpdate = mysql_query($PartsUpdate, $weberp); // This is the Standard Search
	if(!$InsertPartsUpdate){
		die("SQL Insert Query ERROR! this is the printout <br> $InsertPartsUpdate <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
	}


the page does not return any errors, and when I print the query to see the output is showing the correct values to do the math. I tried the query in mysql and it works as it should. But when executed in PHP it simply does not update the values in the database.
Any ideas anyone?

Thanks,
Patrick J

Is This A Good Question/Topic? 0
  • +

Replies To: Delete inside a select

#2 mocker  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 50
  • View blog
  • Posts: 466
  • Joined: 14-October 07

Re: Delete inside a select

Posted 11 August 2009 - 08:53 AM

Not sure if this is causing the issue
UnitsOnHand = UnitsOnHand-'$UnitsCheck_In_Out'


should remove the single quotes, since subtracting a string from a number doesn't usually work too well.

same with
PartNumber = '$ProductID'



Single quotes tell mysql that it needs to look for a string, not a number
Was This Post Helpful? 0
  • +
  • -

#3 pjamrisk  Icon User is offline

  • New D.I.C Head

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

Re: Delete inside a select

Posted 11 August 2009 - 09:09 AM

Dear Mocker,
I did try the same without quotes with the same result, is like it does not execute the transaction, but like I said I get no errors, it simply works.
Was This Post Helpful? 0
  • +
  • -

#4 pjamrisk  Icon User is offline

  • New D.I.C Head

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

Re: Delete inside a select

Posted 11 August 2009 - 09:53 AM

A Small Update, I made a mistake on the title of the Post, it should have been update, not delete.
Still having the same problem, I have not been able to figure it out yet. Still looking around and doing testing. If someone figures it out, I will be around.

Thanks,
Patrick J
Was This Post Helpful? 0
  • +
  • -

#5 pjamrisk  Icon User is offline

  • New D.I.C Head

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

Re: Delete inside a select

Posted 11 August 2009 - 12:11 PM

OK,
I have isolated the update query by itself inside the while loop in the meantime to check if it gets executed at all in the code, but the result is still like nothing has happened. No errors thrown either so I have no clue what is going on.
Here is the modified code so far.

session_start();

//Define Session Variables
$User = $_SESSION['UserID'];
//Set the Static Variables
$TDID = '5'; //Checkout is 2 in the database
$mysqldate = date('Y-m-d H:i:s');

//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 Query
	$InsertFinal = "INSERT INTO
							  PartsTransactions(
							  ProductID,
							  TDID,
							  UnitsCheck_In_Out,
							  UserId,
							  WONumber,
							  TransactionDateTime)
							VALUES(
							  '$ProductID',
							  '$TDID',
							  '$UnitsCheck_In_Out',
							  '$User',
							  '$WONumber',
							  '$mysqldate'
							   )";
	$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
	}
}*/
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 Parts update
	$PartsUpdate = "UPDATE
						stockParts
					SET
						UnitsOnHand = UnitsOnHand - $UnitsCheck_In_Out
					Where
						PartNumber = '$ProductID'
					";
	$InsertPartsUpdate = mysql_query($PartsUpdate, $weberp); // This is the Standard Search
	if(!$InsertPartsUpdate){
		die("SQL Insert Query ERROR! this is the printout <br> $InsertPartsUpdate <br> " . mysql_error($weberp) ); // if the result is not a proper result then we output the error
	}
}

//Create Delete of Temptable 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']);



Why is the math not working. I even tried to use a static number on that update query and still nothing.
Patrick J
Was This Post Helpful? 0
  • +
  • -

#6 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: Delete inside a select

Posted 11 August 2009 - 10:08 PM

no sure but change your query to this:
$PartsUpdate = "UPDATE stockParts SET `UnitsOnHand` = `UnitsOnHand`-$UnitsCheck_In_Out Where `PartNumber` = $ProductID";
Was This Post Helpful? 0
  • +
  • -

#7 pjamrisk  Icon User is offline

  • New D.I.C Head

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

Re: Delete inside a select

Posted 12 August 2009 - 03:34 AM

Dear Noorahmad,
I did try that as well and no luck. For some reason it simply skips the query. I ended up trying the query statically as well, meaning all the values via the double quotes and still got nothing running. I have never encountered this problem before.
Patrick J
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1