insert query vs update query: doing one or the other depending on what is added

Bhaal · February 16, 2007

I have a simple "add to cart" function that simply adds a record to a "cart" table.

But, I'd really like it to check and see if the item being added is already in the table. If it's not in the table, do an insert query; if it's already in the table, do an update query - and update the "quantity" field, by incrementing it by one.

Original insert query:

if($_POST["submit_x"])
{
$MyTotal = $_POST[itemPrice];

$q1 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
mysql_query($q1) or die(mysql_error());
}

Please don't laugh at the following, but here's what I've been trying:


if($_POST["submit_x"])
{
//get current table contents
$sql = "select * from cart where OrderID = '$PHPSESSID' ";
$rql = mysql_query($sql) or die(mysql_error());

while($aql = mysql_fetch_array($rql))
{
        $myItem = $aql[itemID];
$qty = $aql[itemQty];
$newQty = $qty++;
$ItemID = '$_POST[itemID]';

if($myItem == $ItemID){
	$q1 = "update cart set
			ItemQty = $newQty

			where OrderID = '$PHPSESSID' and ItemID = '$ItemID' ";
	mysql_query($q1) or die(mysql_error());
}

else {

        $MyTotal = $_POST[itemPrice];
	$q1 = "insert into cart set
			OrderID = '$PHPSESSID',
			ItemID = '$_POST[itemID]',
			ItemName = '$_POST[itemName]',
			ItemPrice = '$_POST[itemPrice]',
			ItemQty = '1',
			ItemType = '$_POST[itemType]',
			ItemTotal = '$MyTotal' ";
	mysql_query($q1) or die(mysql_error());
	}
}
}

This obviously doesn't work at all. Any help is very much appreciated...

sayedsohail · February 16, 2007

I am not sure this is the problem in your while loop you use $aql do you think it should be $sql.

while($aql = mysql_fetch_array($rql))

Any errors, can you please describe your problem, what is happening any error code etc.

regards

arifsor · February 16, 2007

why you put single quote '$_POST[itemID]'

try to remove it $_POST[itemID]

Bhaal · February 16, 2007

I removed the single quotes around $_POST[itemID] in the line: $ItemID = $_POST[itemID]; - but the result is the sameas before: nothing is saved to the table.

roopurt18 · February 16, 2007

Try it like this:

<?php
// Update SQL
$sql = "UPDATE ...";
$uq = mysql_query($sql);
if(!$uq){
  // Query failed, existing record must not exist
  // Insert SQL
  $sql = "INSERT INTO ...";
  $iq = mysql_query($sql);
  if(!$iq){
    echo "Error: Could not add your item to the cart.";
  }
}
?>

Or if you have the proper version of MySQL you can do an INSERT ... UPDATE query.

Bhaal · February 17, 2007

This is kind of working (thanks, roopurt18!):

if($_POST["submit_x"])
{
$sql = "select * from cart where OrderID = '$PHPSESSID' ";
$rql = mysql_query($sql) or die(mysql_error());

$rows = mysql_num_rows($rql);

     if($rows == '0')
     {
$MyTotal = $_POST[itemPrice];
$q1 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
	mysql_query($q1) or die(mysql_error());
     }

     else
     {
while($aql = mysql_fetch_array($rql))
{
	$myItem = $aql[itemID];
	$qty = $aql[itemQty];
	$newQty = $qty++;
	$ItemID = $_POST[itemID];

	if($myItem == $ItemID)
                 {
	     $q1 = "update cart set
	     ItemQty = $newQty
		where OrderID = '$PHPSESSID' and ItemID = '$ItemID' ";
		$uq = mysql_query($q1);
	}
}
    if(!$uq)
    {
         $MyTotal = $_POST[itemPrice];
	$q1 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
	mysql_query($q1) or die(mysql_error());

     }

    }
}

It IS preventing duplicates (YEAH!!!).

However - it's not updating the Quantity ($qty).

The first part sees if there are records in the cart - if not, do a simple insert.

If there are records, it loops through and tries to find dupes. If it finds dupes, it's supposed to do an update.

If it doesn't find dupes it's supposed to do the insert.

If the item exists, it's supposed to increment quantity by 1, hence the line:

$qty = $aql[itemQty];

$newQty = $qty++;

My guess is that it's not firing the update portion of the query at all.

I can't see the flaw in the logic. Any clues?

Thanks again - this is already a huge leap forward.

sspoke · February 17, 2007

that ++ thing isn't that good its ok ya.. but its a chance of luck

lets say if someone does 2 php interpreter's at some close times like milliseconds

1 php php interpreter does qty++; line and starts going down.. near update query while the other php interpreter is doing the same thing so you got 2 php interpreters near the update query with same qty I would make a function or something in MySQL for that not PHP

Bhaal · February 17, 2007

Alright sspoke - good advice, but much easier said than done, for a newbie like me at least.

"...make a function or something in MySQL for that not PHP..."

I don't think I've ever attempted anything like that - is that like a stored procedure in SQLServer? Does MySQL support functions on the database level??

sspoke · February 17, 2007

ya exactly stored procedures

than in PHP to SQL you would just call EXEC sp_IncreaseQty '$PHPSESSID'

the coding for stored producures is very different to what im used to do so IdK really how it works just know how to call them with EXECUTE

CREATE PROCEDURE sp_IncreaseQty

ya ??? lol

Bhaal · February 17, 2007

Well, I still don't understand why the update query portion of this code isn't firing. Any ideas about that?

redarrow · February 17, 2007

post your current code please cheers

Bhaal · February 17, 2007

Code hasn't changed since I last posted it:

if($_POST["submit_x"])
{
$sql = "select * from cart where OrderID = '$PHPSESSID' ";
$rql = mysql_query($sql) or die(mysql_error());

$rows = mysql_num_rows($rql);

     if($rows == '0')
     {
$MyTotal = $_POST[itemPrice];
$q1 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
	mysql_query($q1) or die(mysql_error());
     }

     else
     {
while($aql = mysql_fetch_array($rql))
{
	$myItem = $aql[itemID];
	$qty = $aql[itemQty];
	$newQty = $qty++;
	$ItemID = $_POST[itemID];

	if($myItem == $ItemID)
                 {
	     $q1 = "update cart set
	     ItemQty = $newQty
		where OrderID = '$PHPSESSID' and ItemID = '$ItemID' ";
		$uq = mysql_query($q1);
	}
}
    if(!$uq)
    {
         $MyTotal = $_POST[itemPrice];
	$q1 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
	mysql_query($q1) or die(mysql_error());

     }

    }
}

redarrow · February 17, 2007

there u go

<?php

if($_POST["submit_x"])
{
$sql = "select * from cart where OrderID = '$PHPSESSID' ";
$rql = mysql_query($sql) or die(mysql_error());

$rows = mysql_num_rows($rql);

     if($rows == '0')
     {
$MyTotal = $_POST[itemPrice];
$q1 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
	mysql_query($q1) or die(mysql_error());
     }

     else
     {
while($aql = mysql_fetch_array($rql))
{
	$myItem = $aql[itemID];
	$qty = $aql[itemQty];
	$newQty = $qty++;
	$ItemID = $_POST[itemID];

	if($myItem == $ItemID)
                 {
	     $q2 = "update cart set
	     ItemQty = $newQty
		where OrderID = '$PHPSESSID' and ItemID = '$ItemID' ";
		$uq = mysql_query($q2);
	}
}
    if(!$uq)
    {
         $MyTotal = $_POST[itemPrice];
	$q3 = "insert into cart set
		OrderID = '$PHPSESSID',
		ItemID = '$_POST[itemID]',
		ItemName = '$_POST[itemName]',
		ItemPrice = '$_POST[itemPrice]',
		ItemQty = '1',
		ItemType = '$_POST[itemType]',
		ItemTotal = '$MyTotal' ";
	mysql_query($q3) or die(mysql_error());

     }

    }
}
?>

Bhaal · February 17, 2007

Thanks redarrow.

I made one other change that seems to work - instead of:

$newQty = $qty++;

I'm using:

$newQty = $qty + 1;

...which works in a testing environment...

THANKS ALL! I truly appreciate it!

(Mark this one: resolved)

Sign In

insert query vs update query: doing one or the other depending on what is added

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information