Jump to content

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

Bhaal

By Bhaal
in PHP Coding Help

 Share

Recommended Posts

Bhaal Member

Bhaal

Posted
Posted

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...

Link to comment
Share on other sites
roopurt18 Advanced Member

roopurt18

Posted
Posted

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.

Link to comment
Share on other sites
Bhaal Member

Bhaal

Posted
  • Author
Posted

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.

 

 

 

 

 

 

Link to comment
Share on other sites
sspoke Member

sspoke

Posted
Posted

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

Link to comment
Share on other sites
Bhaal Member

Bhaal

Posted
  • Author
Posted

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??

Link to comment
Share on other sites
sspoke Member

sspoke

Posted
Posted

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

Link to comment
Share on other sites
Bhaal Member

Bhaal

Posted
  • Author
Posted

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());

     }

    }
}

Link to comment
Share on other sites
redarrow Advanced Member

redarrow

Posted
Posted

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());

     }

    }
}
?>

Link to comment
Share on other sites
Bhaal Member

Bhaal

Posted
  • Author
Posted

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)

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.