Filter one recordset using another.

[marcobraynio]

I have two tables called shopping_cart and products which have the following columns:

products - product_id, name, size, price, comments, stock

shopping_cart - cart_id, product_id, quantity, session_id

I want to be able to display the contents of my shopping cart on screen, using only the items in the table shopping_cart filtered by session_id but with all of the product information which is held in the products table.

I have created a record set to filter the shopping cart by session_id without a problem, but don't know how to pull the information from the products table for each entry?

Using PHP/MySQL - have very limited knowledge I'm afraid.

Thanks

Mark

[bregent]

Unless I am misunderstanding you, you don't need two recordsets for this. Just use a single recordset with SQL that joins the two tables.

[marcobraynio]

Thanks for the reply.  Could you tell me how to do that?  I really have no idea.

[David_Powers]

Do a Google search for MySQL join tutorial. You'll find lots of resources.

[marcobraynio]

Hi David  thanks for the reply, it's your fault I'm in this state after doing your tutorial, I thought I could change the world....

I am so very nearly there.

If I specify a username in my query it is working for me, but if I try to get the session variable to sit in it's place in the qhery it doesn't.  Here is the code generated in both instances:

Specified - username 'marcobraynio'

mysql_select_db($database_boutique_wines, $boutique_wines);
$query_getCart = "SELECT cart.product_id, cart.quantity, cart.session_id, product.price, product.winery, product.colour, product.grape, product.year FROM cart, product WHERE cart.session_id = 'marcobraynio' AND cart.product_id = product.product_id ";
$getCart = mysql_query($query_getCart, $boutique_wines) or die(mysql_error());
$row_getCart = mysql_fetch_assoc($getCart);
$totalRows_getCart = mysql_num_rows($getCart);?>

Pulling in the Session Variable in the place of 'marcobraynio' I get this which doesn't work.  I have started the session at the top of the page.

$colname_getCart = "-1";
if (isset($_SESSION['session_id'])) {
  $colname_getCart = $_SESSION['session_id'];
}
mysql_select_db($database_boutique_wines, $boutique_wines);
$query_getCart = sprintf("SELECT cart.product_id, cart.quantity, cart.session_id, product.price, product.winery, product.colour, product.grape, product.year FROM cart, product WHERE cart.session_id = %s AND cart.product_id = product.product_id ", GetSQLValueString($colname_getCart, "text"));
$getCart = mysql_query($query_getCart, $boutique_wines) or die(mysql_error());
$row_getCart = mysql_fetch_assoc($getCart);
$totalRows_getCart = mysql_num_rows($getCart);
?>

I don't understand the code generated here - DW has placed a %s where I thought $_SESSION['session_id'] would go.

Cheers

Mark

[David_Powers · Accepted solution]

marcobraynio wrote:

I don't understand the code generated here - DW has placed a %s where I thought $_SESSION['session_id'] would go.

The code that Dreamweaver has generated is correct. Dreamweaver uses the sprintf() function to insert the variable into the SQL query. Getting your head around sprintf() can be difficult to begin with. The function takes a minimum of two arguments. The first argument is the string that you want to insert variables into. Instead of inserting the variables directly in the string, you use placeholders that always begin with %. In this case %s represents a string.

The remaining argument(s) are the values you want to insert in place of the placeholders. If you look at the script generated by Dreamweaver, the second argument to sprintf() is GetSQLValueString($colname_getCart, "text"). GetSQLValueString() is a custom function created by Dreamweaver that checks the value of a variable, and makes it safe to insert in a SQL query. In your case, $colname_getCart is the equivalent of $_SESSION['session_id'].

There shouldn't be any problem using $_SESSION['session_id'] because PHP uses a different value to refer to the session ID (the internal one that identifies the session, not your variable). However, to avoid confusion, it would be better to use a different name, such as $_SESSION['user_id'].

If your query is working with a hard-coded name, but not with the session variable, it means that the session variable isn't being recognized on the page. Use echo to check whether the variable is being passed to the page.

if (isset($_SESSION['user_id'])) {
  echo $_SESSION['user_id'];
} else {
  echo 'Session variable does not exist';
}

[marcobraynio]

Hi David

That's great thanks.  Have managed to get it working perfectly.  I did have a bit of a problem due to SESSION_START(); appearing after the session variable was called but after putting it further up the code it is now doing what I want it to.  Thank's for your help - you really should do a tutorial on a shopping cart.  You're very good at it.

Thanks

Mark

[marcobraynio]

Hi David, it soesn't seem to be as cut and dried as I thought.  Since moving the session_start() to the top of the page I have lost my recordset in the DW workspace.  And there's an error on the repeat region.  It does still work however, but something isn't right.

<?php require_once('Connections/boutique_wines.php'); ?>
<?php session_start(); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;   
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_getCart = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_getCart = $_SESSION['MM_Username'];
}
mysql_select_db($database_boutique_wines, $boutique_wines);
$query_getCart = sprintf("SELECT cart_id, session_id FROM cart WHERE session_id = %s ORDER BY cart_id DESC", GetSQLValueString($colname_getCart, "text"));
$getCart = mysql_query($query_getCart, $boutique_wines) or die(mysql_error());
$row_getCart = mysql_fetch_assoc($getCart);
$totalRows_getCart = mysql_num_rows($getCart); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<h1>Cart</h1>

<?php do { ?>
  <p><?php echo $row_getCart['cart_id']; ?> <?php echo $row_getCart['session_id']; ?></p>
  <?php } while ($row_getCart = mysql_fetch_assoc($getCart)); ?>
</body>
</html>
<?php
mysql_free_result($getCart);
?>

[David_Powers]

There's nothing wrong with the code, but I suspect that Dreamweaver can't recognize the recordset because you have put <?php session_start(); ?> between the connection and the rest of the script. It shouldn't make any difference, and you might find that Dreamweaver recognizes it after closing down and relaunching. Alternatively, you could just move <?php session_start(); ?> above the connection.

[marcobraynio]

Thanks David