Some Advise On Editing This Code Please....

roldahayes · September 28, 2012

Hi,

I have a very basic shopping cart that takes its information from a database and displays the products.

I am trying to edit this code so that when a customer buys a certain item, the basket then gives them some more products that are related to what they are purchasing.

The products are currently taken from these feilds:

Car_Make, Model, Car_ID, Prod_Type, Priority section, Prod_Model, Prod_Make, Product_Desc, Prod_Model, Prod_ExVat, Post_ID, Link image_name, Image_Van

an example row of data, inserted into the database would be:

all, not specific, def, VR, NA, HD1, AR, HEAVY DUTY System HD1, HD1, 258.4, NA, NA, NA, NA,

and I have now added the extra fields here:

Prod_Model_EX1, Product_Desc_EX1, Prod_ExVat_EX1

and the data in that row is:

HD1-RM3, 3mm Ribbed Matting for HD1, 14.4

So basically, when a customer buys an "HD1" and it appears in the basket, I would like another row to appear offering the "HD1-RM3" with a quantity box next to it...

Any help here would be much appreciated!

Basket Code:

<?php
//session_start ();
//include header code
include_once("head.php");
// use the user_connection include file's connectDB function
include_once("usr_conn.php");
if(!connectDB())
{
echo "<p>Unable To Connect To Database</p>";
return;
}

if (isset ($_GET['src']))
{
$_SESSION['returnTo'] = $_GET['src'];
}
// assign variables
//echo "ref1:" . $HTTP_REFERER . "<br>";
//$temp = (string)$HTTP_REFERER;
//$urlref2 = substr($temp,0,6);
//echo "ref:" . $urlref;

if(isset($_GET)) {
foreach($_GET as $get_clean) {
 $key = key($_GET);
 $_GET[''.$key.''] = trim(htmlentities(strip_tags($_GET[''.$key.''])));
 next($_GET);
}
}
$urlref = $HTTP_REFERER;
$prodID = $_GET['productID'];
$delete = $_GET ['delete'];
$quantity = $_GET['quantity'];
$updateQuantity = $_GET['updateQuantity'];
$update = $_GET ['update'];
$currency = "£";
// maximum querys per user basket
$MAXBASKETQUERY = 25;
// start the html table

?>

<?php
// check if user has no cookie set
if ($userID == "")
{
?>
					 <table border="0" align="center" cellpadding="0" cellspacing="0">
						 <tr class="stdtable" align="center">
						 <td colspan="6">No Product Querys made yet </td>
						 </tr>
					 </table>
					 <p>
						 <?php
return;
}
//if the updated quantity is 0 or blank remove item from basket
if ($updateQuantity == '0'){
$delete = 'yes';
}
if (($update == 'yes')&& ($updateQuantity == '')){
$delete = 'yes';
}
// if delete parameter set to yes run the delete code
if ($delete == 'yes')
{
// if prodID is set, delete that specific product from this userID's basket
if ($prodID != "")
{
$sqlquery = "DELETE FROM basket WHERE userID = '" . $userID . "' AND productID = '" . $prodID . "'";
$result = mysql_query($sqlquery);
if ($result)
{
?>
					 </p>
					 <p>  </p>
					 <table border="0" align="center" cellpadding="15" cellspacing="0">
						 <tr class="stdtable" align="center">
						 <td width="564" colspan="6"><font size="5" face="Arial, Helvetica, sans-serif"><strong>Item

							 Deleted. Click <a href="hdbasket.php"><font color="#990000">Here</font></a> to refresh the basket </strong></font></td>
						 </tr>
					 </table>
					 <?php
}
else
{
?>
					 <table border="0" align="center" cellpadding="0" cellspacing="0">
						 <tr class="stdtable" align="center">
						 <td width="368" colspan="6">Unable To Delete <br />
							 Item Click <a href="hdbasket.php">Here</a> to refresh

							 the basket </td>
						 </tr>
					 </table>
					 <?php
 }
// close the wykes database connection
mysql_close();
return;
}
}
//update the shopping basket quantity
if ($update == 'yes')
{
// if prodID is set, delete that specific product from this userID's basket
if ($prodID != "")
{
$sqlquery = "UPDATE basket SET quantity = '" . $updateQuantity . "' WHERE productID = '" . $prodID . "'AND userID = '" . $userID . "'";
$result = mysql_query($sqlquery);
if ($result)
{
?>
					 <table border="0" align="center" cellpadding="0" cellspacing="0">
						 <tr class="stdtable" align="center">
						 <td colspan="6">Your basket has been updated</td>
						 </tr>
					 </table>
					 <?php
}
else
{
?>
					 <table border="0" align="center" cellpadding="0" cellspacing="0">
						 <tr class="stdtable" align="center">
						 <td colspan="6">Your basket has NOT been updated</td>
						 </tr>
					 </table>
					 <?php
 }
// close the wykes database connection
//mysql_close();
//return;
}
}
// find the number of rows in this userID's basket
$sqlquery = "SELECT * FROM basket WHERE userID = '" . $userID . "'";
$result = mysql_query($sqlquery);
if (!$result)
{
echo "<p><font class=error>Could not find any entrys for this Basket</small></p>";
mysql_close();
return;
}
else
$rowCount = mysql_num_rows($result);
// if prodID is set new product is passed in, add/update the userID's basket
if ($prodID != "")
{
// find if the prodID already exists in this userID's basket
$sqlquery = "SELECT * FROM basket WHERE userID = '" . $userID . "' AND productID = '" . $prodID . "'";
$result = mysql_query($sqlquery);
$rowCount = mysql_num_rows($result);
// if no matches insert the product into the userID's basket
if ($rowCount == 0)
{
// find number of items in basket
$sqlquery = "SELECT * FROM basket WHERE userID = '" . $userID . "'";
$result = mysql_query($sqlquery);
$rowCount = mysql_num_rows($result);
// check if the maxquery's has been reached
if ($rowCount > ($MAXBASKETQUERY - 1))
{
echo ("<p><font class=error>Only $MAXBASKETQUERY overall orders are allowed, your product could not be added to basket</font></p>");
}
else
{
$expiretime = time() + 7200;
$sqlquery = "INSERT INTO basket" . $basketFields . "VALUES ('" . $prodID . "', '1', '" .$userID . "', '" . $expiretime . "')";
$result = mysql_query($sqlquery);
if (!$result)
echo "<font class=error><p>Could not add item to Basket</p></font>";
}
}
}
// select the userID's basket query and the Product Reference relating to each of the basket's productID's
$sqlquery = "SELECT products.Prod_REF, basket.productID, basket.quantity, products.Prod_Make, products.Prod_Model, products.Prod_Type, products.Car_Make, products.Car_Model, products.Price_ExVat, products.Post_ID, Product_Desc FROM basket INNER JOIN products ON basket.productID = products.Prod_ID WHERE ((basket.userID) = '" . $userID . "')";
$result = mysql_query($sqlquery);
$rowCount = mysql_num_rows($result);
// echo $sqlquery; // *debug
// assign the table headers
//$dbFields = array( "Reference", "Product Query");
// check if no entries in basket
if (!$result || (mysql_num_rows($result) == 0)){
?>
					 <table border="0" align="center" cellpadding="0" cellspacing="0">
						 <tr class="stdtable" align="center">
						 <td colspan="6" class="error">No Product Queries

							 In Basket. </td>
						 </tr>
					 </table>
					 <?php
}
else
//*************display contents of basket//////////////////////////////////////////////////////
{
// echo each header from array
//foreach ($dbFields as $headIndex)
// echo an extra blank header for the delete item column
// fetch each row as an associative array
$counter = 1;
$price = 0;
//set default postage value outside loop
$postagerate = 10.00;
while ($row = mysql_fetch_assoc($result))
{
//decide which postage value is the highest and use that to calculate overall price
//get the postage values for each product
$sqlpostquery = "SELECT * FROM postage WHERE Post_ID = '" . htmlspecialchars($row['Post_ID']) . "'";
//get the postage values from the database
$postresult = mysql_query($sqlpostquery);
$rowpost = mysql_fetch_assoc($postresult);
// check if postage value was available
if ($postresult || !(mysql_num_rows($postresult) == 0))
{
 $rawpostage = htmlspecialchars($rowpost['Post_Cost']) ? htmlspecialchars($rowpost['Post_Cost']) : 0.00;
 //get the lowest postage rate.
 if ($postagerate > $rawpostage)
 {
 $postagerate = $rawpostage;
 }
}
else
{
 $postagerage = 0.00;
}
//round postage rate of 2 decimal places
$postagerate = $postagerate;
//release the postage resultset array
mysql_free_result($postresult);
echo "<form action=hdbasket.php method=get name=form".$counter.">
 <input name=update type=hidden value=yes>
 <input name=productID type=hidden value=". $row['productID'] ."><tr class=stdtable>";
echo("<td align=center> " . htmlspecialchars($row['Car_Model']) . "</td>");
echo("<td align=center> " . htmlspecialchars($row['Prod_REF']) . "</td>");
echo("<td align=left>" . htmlspecialchars($row['Product_Desc']) . "</td>");
//echo("<td align=center>". htmlspecialchars($row['Prod_REF']) ."</td>");
// for the final column echo an hyperlink to delete the product entry
//settype($row["Price_ExVat"], "integer");
echo "<td align=center><input name=updateQuantity onchange=submit(); type=text size=2 value=". htmlspecialchars($row['quantity'])."></td><td align=center>". $currency . number_format(htmlspecialchars($row['Price_ExVat']), 2) ."</td><td align=center>". $currency . number_format(calcVAT($row["Price_ExVat"]), 2) ."</td><td align=center>". $currency . number_format((calcVAT (htmlspecialchars($row['Price_ExVat'] * $row['quantity']))), 2) ."</td>";
echo "<td align=center><a href=\"hdbasket.php?delete=yes&productID=" . $row['productID'] . "\"><img src=images/remove.gif border=0></a></td>";
echo "</tr></form>";
$counter ++;
//get a cumulative value of the price as items are added to the basket and multiply by quantity as we go.
$price = $price + (calcVAT (htmlspecialchars($row['Price_ExVat']))) * htmlspecialchars($row['quantity']);
}
//assign subtotal and round to 2 decimal places
$subtotal = $price;
$total = $subtotal + $postagerate;
//pick overall postage type
if ($postagerate == 5)
$postage = 3;
else if ($postagerate == 9)
$postage = 2;
else if ($postagerate == 10)
$postage = 1;
}
//update shopper table with new/changed info
$sqlshopper = "SELECT * FROM shopper WHERE User_ID = '" . $userID . "'";
//echo "query: " . $sqlshopper;
$result = mysql_query($sqlshopper);
$rowCount2 = mysql_num_rows($result);
//add shopper
if ($rowCount2 == 0)
{
 $sqladd = "INSERT INTO shopper" . $shopperFields . "VALUES ('" . $userID . "', '" . $total . "', '" . $postage . "')";
 //echo "noshopper: " . $sqladd;
 $shopadd = mysql_query($sqladd);
 if (!$shopadd)
 echo "<font class=error><p>Your basket has not been processed</p></font>";
}
else
{
 //update details
 $sqlupdate = "UPDATE shopper SET Basket_total = '" . $total . "', Postage = '" . $postage . "' WHERE user_ID = '" . $userID . "'";
 //echo "shopper: " . $sqlupdate;
 $shopupdate = mysql_query($sqlupdate);
 if (!$shopupdate)
 echo "<font class=error><p>Your basket has not been updated</p></font>";
}
// finish table
?></td>
				 </tr>
				 <?php
?>
				 </table>
				 <table width="100%" border="0" cellspacing="0" cellpadding="1">
				 <tr>
					 <td width="69%"><div align="right">
					 <table width="100%" border="0" cellspacing="0" cellpadding="5">
						 <tr>
						 <td width="90%"><div align="right">
							 <input type="image" name="update" src="images/update2.gif" />
						 </div></td>
						 <td width="10%"><div align="right"></div></td>
						 </tr>
					 </table>
					 <a href="http://www.roof-rack.co.uk/checkout.php"><img src="images/proceed.gif" alt="CLICK HERE TO SUPPLY PAYMENT AND SHIPPING INFORMATION" width="212" height="39" border="0" /></a> </div></td>
					 <td width="31%"><table width="161" border="1" cellpadding="1" cellspacing="0" bordercolor="#FFFFFF">
					 <tr bordercolor="#FFFFFF">
						 <td width="55%" height="25" align="right" bgcolor="#EBEBEB" class="small"><font color="#000000"><strong>SUB

						 TOTAL</strong></font></td>
						 <td width="45%" height="25" align="center" bgcolor="#EBEBEB" class="header"><?php
 echo $currency;
 echo number_format($subtotal, 2);
 ?></td>
					 </tr>
					 <tr bordercolor="#FFFFFF">
						 <td height="25" align="right" class="small"><font color="#000000">POSTAGE</font></td>
						 <td height="25" align="center" class="header"><?php
 echo $currency;
 echo number_format($postagerate, 2);
 ?></td>
					 </tr>
					 <tr bordercolor="#FFFFFF" bgcolor="#EAEADB">
						 <td height="25" align="right" class="small"><font color="#000000"><strong>TOTAL</strong></font></td>
						 <td height="25" align="center" class="header"><?php
 echo $currency;
 echo number_format($total, 2);
 ?>

trq · September 28, 2012

Do you have a question?

roldahayes · September 28, 2012

Yes,

How would I go about modifying the basket code to display the extra database data that I have added?

White_Lily · September 28, 2012

So basically you want something more or less along the lines of a related products/pages box?

roldahayes · September 28, 2012

That is correct,

I've added the extra products to the database but just need to work out how to get them to display correctly.

Christian F. · September 28, 2012

You need to have a second table, that defines the relations between two (or more) products. Then select the related items based upon the associations from this many-to-many relations table.

roldahayes · September 28, 2012

Thats maybe a little more advanced than I need.

I just need it to display an extra product when the original is selected.

i.e

When Part Number "HD1" is purchased, the line "HD1-RM3" will be displayed underneath....

I've attached a screen grab of the database to see if it helps with explaining....

roldahayes · September 28, 2012

Maybe another way I could do this...?

The code for the buy button is below.

Could this be adjusted so that two "$strProd_REF" can be selected?

That way, I could just add another line to the database with the option in it?

** Clutching at straws here...! **

<?php
//the folowing lines contain the code that should be used with each link that you want to create change the values of $strProd_REF $strCar_ID as you need to
//--------------'LINK CODE'---------------------
$strProd_REF = "HD1";
$strCar_ID = "def";
$sqlSelect = "SELECT Prod_ID FROM products WHERE Prod_REF = '" . $strProd_REF . "' AND Car_ID = '" . $strCar_ID . "'
";
// assign the basic sqlquery
$sqlquery = $sqlSelect;
//get the result set
$result = mysql_query($sqlquery);
while ($row = mysql_fetch_assoc($result))
{
 echo "<a href=\"basket.php?src=".urlencode($_SERVER['REQUEST_URI'])."&productID=" . $row["Prod_ID"] . "\"><img src=2003/buy.gif width=68 height=17 border=0></a>";
//end make while
}
$row = "";
mysql_free_result($result);
//--------------'END LINK CODE'---------------------
?>

White_Lily · September 28, 2012

What christian said is what my old CMS was, i had two tables, one for pages/products and another for related pages/products, i do suggest this option as it has worked for me for a very ong time and no-one has complained of it yet

roldahayes · September 29, 2012

Ok,

Could anyone point me in the right direction to go about doing this please.

jcbones · September 29, 2012

Normalized data will always give normal results. Abnormalized data, will always give abnormal results.

Things you need to look at:

Database Normalization

Join queries

Looking at your current table, it should probably be 3 tables.

Sign In

Some Advise On Editing This Code Please....

Recommended Posts

roldahayes

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

White_Lily

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

Christian F.

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

White_Lily

Link to comment

Share on other sites

roldahayes

Link to comment

Share on other sites

jcbones

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information