Member Avatar for 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());
  }
?>
  • 2 Contributors
  • 6 Replies
  • 130 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by jrotunda85

Dani AI

Generated

A simple, safe pattern is to make the pair (UserID, CigarID) unique and do an "upsert" in one statement instead of SELECT then UPDATE. That avoids the read-then-write race and keeps the code compact. @Member120589's select-then-update idea works, but a one-query approach is cleaner. 's final post shows the classic gotcha — make sure the query actually executes before redirecting.

Add a UNIQUE index on the two columns:

ALTER TABLE humidor
  ADD UNIQUE KEY uk_user_cigar (UserID, CigarID);

Then use a parameterized INSERT with ON DUPLICATE KEY UPDATE. Example (use PDO or mysqli prepared statements; this is PDO-style):

$sql = "INSERT INTO humidor (UserID, CigarID, DatePurchased, Quantity, PricePaid, Created)
        VALUES (:uid, :cid, :date, :qty, :price, NOW())
        ON DUPLICATE KEY UPDATE
          Quantity = Quantity + :qty_up,
          DatePurchased = :date_up,
          PricePaid = :price_up";

$stmt = $pdo->prepare($sql);
$stmt->execute([
  ':uid'=> $userId, ':cid'=> $cigarId,
  ':date'=> $dateFormatted, ':qty'=> $quantity, ':price'=> $pricePaid,
  ':qty_up'=> $quantity, ':date_up'=> $dateFormatted, ':price_up'=> $pricePaid
]);

Notes and cautions:

  • Format dates for MySQL DATE columns as Y-m-d (e.g., date('Y-m-d', strtotime($inputDate))).
  • Use prepared statements (PDO/mysqli) instead of deprecated mysql_* functions to prevent injection and ensure compatibility with modern PHP.
  • ON DUPLICATE KEY UPDATE avoids the two-statement timing window; with InnoDB it behaves safely under concurrency. If you need multi-step changes, wrap them in transactions or use SELECT ... FOR UPDATE.
  • Always check the execute/query return value and the database error message before redirecting.

For reference on the MySQL upsert behavior see the MySQL manual: INSERT ... ON DUPLICATE KEY UPDATE.

Recommended Answers

All 6 Replies

Member Avatar for Member #120589

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.

Member Avatar for 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..." :)

Member Avatar for 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/");
}
Member Avatar for Member #120589

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.

Member Avatar for 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:

Member Avatar for jrotunda85

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.