Checking Existing Values on Table

[jrotunda85]

I want to add something to the script below that, prior to insert, check to see if the $CigarID already exists for the $UserID. If so, I want it to do an update and increase the $Quantity by whatever the user inputs. If the value doesn't exist, then insert the full statement. What's the best way to handle this in PHP??

<?PHP

session_start();

//Open Database
include 'dbconnection.php';

//Create Post Variables
$CigarID = mysql_real_escape_string($_POST['Cigar']);
$Quantity = mysql_real_escape_string($_POST['Quantity']);
$DatePurchased = mysql_real_escape_string($_POST['DatePurchased']);
$DatePurchasedFormatted = date("m/d/Y", strtotime($DatePurchased));
$PricePaid = mysql_real_escape_string($_POST['PricePaid']);
$UserID = $_SESSION["UserID"];

//Create SQL Statement Variable
$SQL = "INSERT INTO humidor VALUES (NULL,'$UserID','$CigarID', '$DatePurchasedFormatted','$Quantity','$PricePaid',Now())";

//Insert Entry
if (!mysql_query($SQL))
  {
  die('Error: ' . mysql_error());
  }
?>

[diafol]

Do a simple mysql select on the id and then a mysql_num_rows(), if > 0, then it already exists, so increment in your next sql, e.g. quantity = quantity + 1 via UPDATE query.
If it doesn't exist, simply INSERT a new record.

[jrotunda85]

Do a simple mysql select on the id and then a mysql_num_rows(), if > 0, then it already exists, so increment in your next sql, e.g. quantity = quantity + 1 via UPDATE query.
If it doesn't exist, simply INSERT a new record.

That's easy enough, I didn't know though if there was a way to do it in one line kind of like how you can create a new table using something like "IF NOT EXISTS..." :)

[jrotunda85]

Alright, one other quick question. I got the if statement set up and it's working perfectly when > 0 for inserting new entries; however, it doesn't add the new values together. I'm not getting any parsing errors but it's not going up. Any ideas?

if (mysql_num_rows($Result) > 0) {
	
	$SQL = "UPDATE humidor SET Quantity = Quantity + $Quantity WHERE UserID = $UserID AND CigarID = $CigarID";
	header("Location: /humidor/");
}

[diafol]

You've marked this as solved. Still an issue? If so, check to see that $Quantity is set.

Easy way -

echo $SQL;

see what it gives you. If it looks OK, copy and paste it into the query pane of phpmyadmin or your favourite mysql gui and see what it says.

[jrotunda85]

Ok, I checked to make sure the variables were parsing correctly in PHP and they generated this statement --

UPDATE humidor SET Quantity = Quantity + 5 WHERE UserID = 1 AND CigarID = 21

I run the exact statement through mySQL and the quantity updates, as it should. When I go back through the PHP though and try to run the same thing, it doesn't update?? :confused:

[jrotunda85]

I figured it out and I'm a moron - I forgot to execute the SQL Statement in the PHP code....duhhhhh!!! :|