php and Postgres question

[SeenGee]

I am trying to write a shopping basket using php (version 4.06) and postrgres, i'm only a beginner at php so i've seperated the script into processes and wrote a script for each one.
these are showjeans.php to display the items that can be selected, i've then created a script to add an item called addtobasket.php, one to update the quantity of the item called
update.php and one to delete an item called alterbasket.php.

the problem i am having is that when you add the same item twice it just adds another entry into the basket where i would like it to recognise the id and update the quantity already in
the basket. i hope this makes sense. i would really appreciate any help that can be offerred because i'm stuck in a rut with
this.

addtobasket.php:

<?php
$DBconnection = pg_connect("");

if (!$DBconnection)
    {
        print"<h3>Error connecting to the database!</h3>";
    }
    else
    {
        $productid = $HTTP_POST_VARS["productid"];
        $quantity = $HTTP_POST_VARS["quantity"];

    $addtobasket = "insert into basket values ($productid, $quantity)";
    $doAddToBasket = pg_exec($DBconnection, $addtobasket);

    if (!$doAddToBasket)
    {
        print "<h2>Error adding to the basket</h2>";
    }

    $selectBasketContents = "select basket.productid, basket.quantity, jeans.name, jeans.brand, jeans.price from basket, jeans where basket.productid=jeans.id";
    $getBasketContents = pg_exec($DBconnection, $selectBasketContents);

    if(!$getBasketContents)
    {
        print "<h2>Failed selecting basket contents</h2>";
    }
    else
    {
        $rowCount = pg_numrows($getBasketContents);

        print "<table>\n <th class=\"title\" colspan=\"7\">Basket Contents</th>";
        print"<tr><td class=\"title\">Product ID</td><td class=\"title\">Name</td><td class=\"title\"> Brand</td><td class=\"title\">Price</td><td class=\"title\">Quantity</td><td class=\"title\">Total Price</td><td class=\"title\">Delete</td></tr>\n";
        for($i; $i<$rowCount; $i++)
        {
            list($productid, $quantity, $name, $brand, $price) = pg_fetch_row($getBasketContents, $i);
            $sumCost = $quantity*$price;
            print "<tr><FORM ACTION=\"updatebasket.php\" METHOD=\"POST\"><td>$productid</td><input type=\"hidden\" name=\"productid\" value=\"$productid\"><td>$name</td><td>$brand</td><td>£$price</td><td><input type =\"TEXT\" name=\"quantity\" Size=\"4\" MAXLENGTH=\"5\" value=\"$quantity\"></td><td>£$sumCost</td><td><INPUT TYPE=\"submit\" VALUE=\"Update\"></td></FORM>";
	print "<td><FORM ACTION=\"alterbasket.php\" METHOD=\"POST\"><input type=\"hidden\" name=\"productid\" value=\"$productid\"><input type=\"hidden\" name=\"quantity\" value=\"$quantity\"><INPUT TYPE=\"submit\" VALUE=\"Delete\"></td></FORM></tr>";	
        }

        print"</table>";
    }
}
    pg_close();
?>

alterbasket.php

<?php
$DBconnection = pg_connect("");

if (!$DBconnection)
    {
        print"<h3>Error connecting to the database!</h3>";
    }
    else
    {
        $productid = $HTTP_POST_VARS["productid"];
        $quantity = $HTTP_POST_VARS["quantity"];

//print ":$productid: :$quantity:";

    $alterbasket = "delete from basket where productid=$productid AND quantity=$quantity";
    $doAlterBasket = pg_exec($DBconnection, $alterbasket);

    if (!$doAlterBasket)
    {
        print "<h2>Error deleting from the basket</h2>";
    }

    $selectBasketContents = "select basket.productid, basket.quantity, jeans.name, jeans.brand, jeans.price from basket, jeans where basket.productid=jeans.id";
    $getBasketContents = pg_exec($DBconnection, $selectBasketContents);

    if(!$getBasketContents)
    {
        print "<h2>Failed selecting basket contents</h2>";
    }
    else
    {
        $rowCount = pg_numrows($getBasketContents);

        print "<table>\n <th class=\"title\" colspan=\"7\">Basket Contents</th>";
        print"<tr><td class=\"title\">Product ID</td><td class=\"title\">Name</td><td class=\"title\"> Brand</td><td class=\"title\">Price</td><td class=\"title\">Quantity</td><td class=\"title\">Total Price</td><td class=\"title\">Delete</td></tr>\n";
        for($i; $i<$rowCount; $i++)
        {
            list($productid, $quantity, $name, $brand, $price) = pg_fetch_row($getBasketContents, $i);
            $sumCost = $quantity*$price;
            print "<tr><FORM ACTION=\"updatebasket.php\" METHOD=\"POST\"><td>$productid</td><input type=\"hidden\" name=\"productid\" value=\"$productid\"><td>$name</td><td>$brand</td><td>£$price</td><td><input type =\"TEXT\" name=\"quantity\" Size=\"4\" MAXLENGTH=\"5\" value=\"$quantity\"></td><input type=\"hidden\" name=\"quantity\" value=\"$quantity\"><td>£$sumCost</td><td><INPUT TYPE=\"submit\" VALUE=\"update\"></td></form>";
	print "<td><FORM ACTION=\"alterbasket.php\" METHOD=\"POST\"><input type=\"hidden\" name=\"productid\" value=\"$productid\"><input type=\"hidden\" name=\"quantity\" value=\"$quantity\"><INPUT TYPE=\"submit\" VALUE=\"Delete\"></td></FORM></tr>";
        }

        print"</table>";
    }
}
    pg_close();
?>

updatebasket.php

<?php
$DBconnection = pg_connect("");

if (!$DBconnection)
    {
        print"<h3>Error connecting to the database!</h3>";
    }
    else
    {
        $productid = $HTTP_POST_VARS["productid"];
        $quantity = $HTTP_POST_VARS["quantity"];

//print ":$productid: :$quantity:";

    $updatebasket = "update basket set quantity=$quantity where productid=$productid";
//print $updatebasket; exit;
    $doUpdateBasket = pg_exec($DBconnection, $updatebasket);

    if (!$doUpdateBasket)
    {
        print "<h2>Error updating the basket</h2>";
    }

    $selectBasketContents = "select basket.productid, basket.quantity, jeans.name, jeans.brand, jeans.price from basket, jeans where basket.productid=jeans.id";
    $getBasketContents = pg_exec($DBconnection, $selectBasketContents);

    if(!$getBasketContents)
    {
        print "<h2>Failed selecting basket contents</h2>";
    }
    else
    {
        $rowCount = pg_numrows($getBasketContents);

        print "<table>\n <th class=\"title\" colspan=\"7\">Basket Contents</th>";
        print"<tr><td class=\"title\">Product ID</td><td class=\"title\">Name</td><td class=\"title\"> Brand</td><td class=\"title\">Price</td><td class=\"title\">Quantity</td><td class=\"title\">Total Price</td><td class=\"title\">Delete</td></tr>\n";
        for($i; $i<$rowCount; $i++)
        {
            list($productid, $quantity, $name, $brand, $price) = pg_fetch_row($getBasketContents, $i);
            $sumCost = $quantity*$price;
            print "<tr><FORM ACTION=\"updatebasket.php\" METHOD=\"POST\"><td>$productid</td><input type=\"hidden\" name=\"productid\" value=\"$productid\"><td>$name</td><td>$brand</td><td>£$price</td><td><input type =\"TEXT\" name=\"quantity\" Size=\"4\" MAXLENGTH=\"5\" value=\"$quantity\"></td><td>£$sumCost</td><td><INPUT TYPE=\"submit\" VALUE=\"Update\"></td></FORM>";
	print "<td><FORM ACTION=\"alterbasket.php\" METHOD=\"POST\"><input type=\"hidden\" name=\"productid\" value=\"$productid\"><input type=\"hidden\" name=\"quantity\" value=\"$quantity\"><INPUT TYPE=\"submit\" VALUE=\"Delete\"></td></FORM></tr>";
        }

        print"</table>";
    }
}
    pg_close();
?>

--------------------------------------------------------------------------------
Thanks a lot for takin the time to look through my code, much appreciated.

[zhabala]

In the script named addtobasket you have to check, whether the record with the same id exists in the basket, when yes, then you have to update the quantity, when no you can insert new record.

Zdenek

[SeenGee]

i am fairly new to sql aswell, could someone give me an idea how this code would look.
Thanks for your time.

[zhabala]

Here is modification of addtobasket.php. It is written by heart, not tested on the computer. Only part when I did modifications is displayed.

<?php
$DBconnection = pg_connect("");

if (!$DBconnection)
    {
        print"<h3>Error connecting to the database!</h3>";
    }
    else
    {
        $productid = $HTTP_POST_VARS["productid"];
        $quantity = $HTTP_POST_VARS["quantity"];

    $updbasket = "UPDATE basket SET quantity = quantity + $quantity WHERE productid = $productid";
    $doUpdateBasket  = pg_Exec($DBconnection, $updbasket);
    if (!$doUpdateBasket)
    {
        print "<h2>Error updating to the basket</h2>";
    }
    $affected = pg_affected_rows($doUpdateBasket);

    if ( $affected == 0 ) {
         $addtobasket = "insert into basket values ($productid, $quantity)";
         $doAddToBasket = pg_exec($DBconnection, $addtobasket);

         if (!$doAddToBasket)
         {
             print "<h2>Error adding to the basket</h2>";
         }

    }

    $selectBasketContents = "select basket.productid, basket.quantity, jeans.name, jeans.brand, jeans.price from basket, jeans where basket.productid=jeans.id";
    $getBasketContents = pg_exec($DBconnection, $selectBasketContents);

    if(!$getBasketContents)
    {
        print "<h2>Failed selecting basket contents</h2>";
    }

[SeenGee]

thanks a lot for your help, much appreciated.