advice on small shopping cart, sql reordering items

craftymind

For my shopping cart I'm holding the ids of products selected in an array set as a session variable

$_SESSION['shop_array']

the code for displaying the products is

<?
include "inc_connect.php";
$values = join(",", $_SESSION['shop_array']);
$query = "SELECT id,title FROM resources_catalogue_list WHERE id IN ($values)";
$result = mysql_query($query) or die ("Error in query: $query.<br>".mysql_error());
while($data = mysql_fetch_array($result)){
?>
<tr> 
  <td class="tdCart"><? echo $data['title']; ?></td>
  <td align="center" class="tdCart"><input name="quantity" type="text" value="1" size="2" maxlength="2"></td>
  <td align="right" class="tdCart"><a href="resources_order?remove=<? $data['id']; ?>">remove</a></td>
</tr>
<?
}
?>

so if shop_array contains (4,2,9) it is joined as a string and placed in the sql query within the IN() parameter. This all works great but the output is reordered to 2,4,9 but I'd rather display the checkout cart in the order that they added it to there cart 4,2,9.
Am I able to correctly reorder it the way I'm currently working this, or will I need to change it to something like this.

<?
include "inc_connect.php";
foreach($_SESSION['shop_array'] as $value){
	$query = "SELECT id,title FROM resources_catalogue_list WHERE id='$value'";
	$result = mysql_query($query) or die ("Error in query: $query.<br>".mysql_error());
	$data = mysql_fetch_array($result);
?>
<tr> 
  <td class="tdCart"><? echo $data['title']; ?></td>
  <td align="center" class="tdCart"><input name="quantity" type="text" value="1" size="2" maxlength="2"></td>
  <td align="right" class="tdCart"><a href="resources_order?remove=<? $data['id']; ?>">remove</a></td>
</tr>
<?
}
?>

Or is that way putting too much load on calls to the database especially with a large shopping cart order?

y6y6y6

Do you have an auto-incrimenting column in the shopping cart table? Like a unique ID? If so you could do a join on the shopping cart and resources_catalogue_list tables and order by the shopping cart ID.

craftymind

I'm not sure I understand your line of thought?
I only have one table that contains products which is resources_catalogue_list. In there is all the information for each product including description and prices. This table is auto-incremented and I reference each product by it's id. When I add a product to the cart, I'm adding that id to the session array.

Are you saying I should have a shopping cart table as well that stores $_SESSION['shop_array']?

y6y6y6

Oh. I get it. No, I always (well, usually) store the cart in a database table.

If you are using a session var my solution won't work. And I can't think of a way to properly order the recordset with just the one table. You might be able to set the query up in such a fashion that it would do that, but for the life of me I can't think of how.

trooper

To order the products in the order in which they were added you could add another var to the shop_array session

call it something like "cart_entry". Then the next time a product is added to the cart simply get the existing value and increment by one. Then you can use that to do your order by

HTH

craftymind

I see what you mean now y6y6y6, does this mean you write a sessionid to the database for each customer and insert columns into that table when someone adds an item to their cart? I'm still learning how people build carts like this.

trooper, naturally the order that they show up in the array would be the output order, so I don't think I need another session variable to describe order. But setting up the sql query is what I'm curious about, because the only order I know of is ORDER BY but that doesn't work in this case.

y6y6y6

you could add another var to the shop_array session

How would that help order the recordset from the database query?

trooper

Apologies chaps i mis read the post believing that it was to be ordered by the session and not the db

😕

trooper

Aany particular reason why you want to order them by say

4 2 9 and not any of the other ways?

trooper

Actually my previous post about adding another var to the shop_array session wasn't 100% useless 😉

Simply add another field to your DB and use that as a marker of what product was selected 1st , 2nd etc then you can use that field to use as the order by cirteria

y6y6y6

does this mean you write a sessionid to the database for each customer

Well, no. This is more than I wanted to do, and this is just scribbling, but something like:

//--- when the items goes into the cart ------
$query = "insert into cart_table (ItemID,AddTime,Qty) ";
$query .= "values ($ItemID,NOW(),$Qty)";
$rsInsert = mysql_db_query($db_name,$query);

//--- Then to get the cart items ------

$query = "select * from resources_catalogue_list,cart_table WHERE ";
$query .= "resources_catalogue_list.id='$value' ";
$query .= "and cart_table.ItemID='$value' ";
$query .= "order by cart_table.ID";

$rsSelect = mysql_db_query($db_name,$query);

//--- where cart_table has an autoincrimenting ID column ---

Simply add another field to your DB and use that as a marker of what product was selected 1st

So...... a column for every customer?

craftymind

trooper, 4,2,9 represents the order that those ids were added to the cart(item id 4, item id 2, item id 9), I think it's typical to list out the items in a persons shopping cart in the order that they added those items.

y6y6y6, thanks for the help!