Strange Looping adding additional table

[WorldDrknss]

I have a strange problem with the while statement making unnecessary loops.

 

The following code produces a semi-correct result. It displays how many items are in the session, but doesn't display the price or description.

SELECT pid, image FROM prints WHERE prints.cid = 1 AND prints.pid IN (31,15,22,29,18) ORDER BY prints.pid DESC

results:

 

1_12993017344d71c56615688.jpg		$		$0.00
1_12993017314d71c5639b261.jpg		$		$0.00
1_12993017264d71c55e70176.jpg		$		$0.00
1_12993017174d71c55548d52.jpg		$		$0.00
1_12993015844d71c4d03883e.jpg		$		$0.00

 

 

changing that code to something like:

 

SELECT pid, image, eid, description FROM prints, products WHERE prints.cid = 1 AND prints.pid IN (31,15,22,29,18) ORDER BY prints.pid DESC

 

will loop through how many entries are in the product tables.

an 8x10 = eid 3 so the while statement loops 3 time. How do I prevent this?

This one has all the information, but duplicated results

 

results:

 

1_12993017344d71c56615688.jpg	5x7 Standard Glossy Print	$		$0.00
1_12993017344d71c56615688.jpg	4x6 Standard Glossy Print	$1.10		$1.10
1_12993017344d71c56615688.jpg	8x10 Standard Glossy Print	$		$0.00
1_12993017314d71c5639b261.jpg	5x7 Standard Glossy Print	$2.00		$2.00
1_12993017314d71c5639b261.jpg	4x6 Standard Glossy Print	$		$0.00
1_12993017314d71c5639b261.jpg	8x10 Standard Glossy Print	$		$0.00
1_12993017264d71c55e70176.jpg	8x10 Standard Glossy Print	$		$0.00
1_12993017264d71c55e70176.jpg	5x7 Standard Glossy Print	$		$0.00
1_12993017264d71c55e70176.jpg	4x6 Standard Glossy Print	$1.10		$1.10
1_12993017174d71c55548d52.jpg	8x10 Standard Glossy Print	$3.00		$3.00
1_12993017174d71c55548d52.jpg	5x7 Standard Glossy Print	$		$0.00
1_12993017174d71c55548d52.jpg	4x6 Standard Glossy Print	$		$0.00
1_12993015844d71c4d03883e.jpg	4x6 Standard Glossy Print	$1.10		$1.10
1_12993015844d71c4d03883e.jpg	8x10 Standard Glossy Print	$		$0.00
1_12993015844d71c4d03883e.jpg	5x7 Standard Glossy Print	$		$0.00

 

the ones with prices are suppose to be the only ones listed.

 

Here is the query:

 

$query = "SELECT pid, image, eid, description FROM prints, products WHERE prints.cid = ".$chk[0]." AND prints.pid IN (";foreach($_SESSION['cart'] as $pid => $value): $rpid = explode("-", $pid); $query .= $rpid[0].','; endforeach;
						$query = substr($query, 0, -1) . ') ORDER BY prints.pid DESC';
						$process = $db->sql_query($query);

 

and here is what the session looks like:

 

Array ( [31-1] => Array ( [quantity] => 1 [price] => 1.10 [type] => 4x6 ) [15-1] => Array ( [quantity] => 1 [price] => 1.10 [type] => 4x6 ) [22-1] => Array ( [quantity] => 1 [price] => 1.10 [type] => 4x6 ) [29-2] => Array ( [quantity] => 1 [price] => 2.00 [type] => 5x7 ) [18-3] => Array ( [quantity] => 1 [price] => 3.00 [type] => 8x10 ) )

 

 

[fenway]

Post the actual, raw query, not php code.

[WorldDrknss]

When I echo the query it returns:

 

SELECT pid, image, eid, description FROM prints, products WHERE prints.cid = 1 AND prints.pid IN (31,15,22,29,18) ORDER BY prints.pid DESC

 

Echoing $process returns:

 

Resource id #6

 

[WorldDrknss]

I can ignore the problem by checking if the price is empty like so:

if(!empty($_SESSION['cart'][$row['pid']."-".$row['eid']]['price']) || $_SESSION['cart'][$row['pid']."-".$row['eid']]['price'] != ""):

but this is a work around and not a solution.

[DavidAM]

Since your first query does not SELECT the price or description, it is not surprising that it does not display the price or description.

 

In your second query:

SELECT pid, image, eid, description 
FROM prints, products 
WHERE prints.cid = 1 
AND prints.pid IN (31,15,22,29,18) 
ORDER BY prints.pid DESC

You are selecting from TWO tables, but you have not described any relation between them. As a result, you are getting a Cartesian product -- every row in your "products" table is matched to every row from "prints" that satisfies the WHERE clause. You need to JOIN the tables in some way.

 

Without knowing your table structure and how the two relate I can only guess at the JOIN. Perhaps, if the "eid" is in both tables, you could write the query like this:

SELECT pid, image, products.eid, description 
FROM prints JOIN products ON prints.eid = products.eid
WHERE prints.cid = 1 
AND prints.pid IN (31,15,22,29,18) 
ORDER BY prints.pid DESC

 

But, since you did not report an error from the select, it would appear that "eid" is NOT actually in both tables. So figure out how they relate and apply the appropriate join.

[WorldDrknss]

@David, there isn't a way to associate them, so I will provide the entire script:

 

Add To Cart:

 

$pid = $_POST['image']."-".$_POST['size'];
if(isset($_SESSION['cart'][$pid])):
    $_SESSION['cart'][$pid]['quantity']++;
    echo "ok";
else:
    $chkq = $db->sql_query("SELECT pid FROM prints WHERE pid='".$_POST['image']."'");
    if($db->sql_numrows($chkq) == 1):
        $query = $db->sql_query("SELECT eid, price FROM products WHERE eid='".$_POST['size']."'");
if($db->sql_numrows($query) == 1):
     list($eid, $price) = $db->sql_fetchrow($query);
    $_SESSION['cart'][$pid] = array('quantity' => 1, 'price' => $price, 'type' => $eid);
    echo "ok";
else:
    echo "<p class='paragraphText'>An Error Has Occured</p>";
         endif;
else:
echo "<p class='paragraphText'>An Error Has Occured</p>";
endif;

 

View Cart:

 

$query = "SELECT pid, image, eid, description FROM prints, products WHERE prints.cid = ".$chk[0]." AND prints.pid IN (";foreach($_SESSION['cart'] as $pid => $value): $rpid = explode("-", $pid); $query .= $rpid[0].','; endforeach;
$query = substr($query, 0, -1) . ') ORDER BY prints.pid DESC';
$process = $db->sql_query($query);

while($row = $db->sql_fetchrow($process)):
    $subtotal = $_SESSION['cart'][$row['pid']."-".$row['eid']]['quantity'] * $_SESSION['cart'][$row['pid']."-".$row['eid']]['price'];
    $total += $subtotal;
    echo "<tr>
    <td align=\"left\"><img src=\"/clients/".base64_decode($chk[5])."/thumbnails/{$row['image']}\" /></td>
    <td align=\"left\" style=\"display:table-cell; vertical-align:top\">"./*$_SESSION['cart'][$row['pid']."-".$row['eid']]['type']*/$row['description']."</td>
    <td align=\"left\" style=\"display:table-cell; vertical-align:top\">\$".$_SESSION['cart'][$row['pid']."-".$row['eid']]['price']."</td>
    <td align=\"left\" style=\"display:table-cell; vertical-align:top\"><input type=\"text\" size=\"3\" name=\"qty[".$row['pid']."-".$row['eid']."]\" value=\"".$_SESSION['cart'][$row['pid']."-".$row['eid']]['quantity']."\" /></td>
    <td align=\"left\" style=\"display:table-cell; vertical-align:top\">$" . number_format($subtotal, 2) . "</td>
endwhile;

 

Table Structure:

 

CREATE TABLE registration (
rid INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(16) NOT NULL,
    PRIMARY KEY (rid)
) ENGINE=MyISAM;

CREATE TABLE customers (
id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(20) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    address VARCHAR(40) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zip VARCHAR(10) NOT NULL,
    phone VARCHAR(14) NOT NULL,
    email VARCHAR(40) NOT NULL,
    password VARCHAR(40) NOT NULL,
    salt VARCHAR( NOT NULL,
    directory VARCHAR(255) default NULL,
    rdate TIMESTAMP,
    PRIMARY KEY (id),
    KEY email_pass(email, password)
) ENGINE=MyISAM;

CREATE TABLE prints (
pid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    cid INT(5) UNSIGNED NOT NULL,
    image VARCHAR(50) NOT NULL,
    directory VARCHAR(255) NOT NULL,
    PRIMARY KEY (pid)
) ENGINE=MYISAM;

CREATE TABLE thumbnails (
tid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
rid INT(10) UNSIGNED NOT NULL,
    cid INT(5) UNSIGNED NOT NULL,
    image VARCHAR(50) NOT NULL,
width VARCHAR(10) NOT NULL,
height VARCHAR(10) NOT NULL,
    directory VARCHAR(255) NOT NULL,
    PRIMARY KEY (tid)
) ENGINE=MYISAM;

CREATE TABLE products (
eid INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    prod VARCHAR(40) NOT NULL,
    price decimal(6,2) NOT NULL,
    description VARCHAR(255) default NULL,
    instock TINYINT unsigned NOT NULL,
    PRIMARY KEY (eid),
    KEY price (price)
) ENGINE=MyISAM;

CREATE TABLE orders (
oid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    cid INT(5) UNSIGNED NOT NULL,
    total decimal(10,2) NOT NULL,
    ordate TIMESTAMP,
    PRIMARY KEY (oid),
    KEY customerid (cid),
    KEY orderdate (ordate)
)ENGINE=InnoDB;

CREATE TABLE order_contents (
ocid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    oid INT(10) UNSIGNED NOT NULL,
    pid INT(10) UNSIGNED NOT NULL,
    quanity TINYINT UNSIGNED NOT NULL DEFAULT 1,
    price decimal(6,2) NOT NULL,
    ship_date DATETIME default NULL,
    PRIMARY KEY (ocid),
    KEY orderid (oid),
    KEY printid (pid),
    KEY shipped (ship_date)
)ENGINE=InnoDB;

 

When a user uploads a file the information is stored in the prints db as print id, customer id, image, directory.

The products table are the current supplies that I have in stock such as 4x6,5x7,8x10,8.5x11,11x14,13x19,6x8C, and 10x15C

 

[WorldDrknss]

Do you think it would be better to do:

 

$total = 0;
foreach($_SESSION['cart'] as $pid =>$value):
  //mysql query statements
   $prid = explode("-", $pid);
   $query = "SELECT pid, image  FROM prints WHERE pid = ".$prid[0].";
   list($prid, $image) = $db->sql_fetchrow($query);
   $query = "SELECT eid, price, description  FROM prints WHERE eid = ".$value['type'].";
   list($eid, $price, $description) = $db->sql_fetchrow($query);
   $subtotal = $_SESSION['cart'][$prid."-".$eid]['quantity'] * $_SESSION['cart'][$prid."-".$eid]['price'];
   $total += $subtotal;
endforeach;

 

or

 

foreach($_SESSION['cart'] as $pid =>$value):
  //mysql query statements
   $prid = explode("-", $pid);
   $query = $db->sql_query("SELECT pid, images, products.eid, products.price, products.description FROM prints, products WHERE pid='".$prid[0]."' AND products.eid = '".$value['type']."'");
   list($pid, $images, $product, $price, $description) = $db->sql_fetchrow($query);
   $subtotal = $_SESSION['cart'][$pid."-".$product]['quantity'] * $_SESSION['cart'][$pid."-".$product]['price'];
   $total += $subtotal;
endforeach;

[WorldDrknss]

It works using the following code:

 

foreach($_SESSION['cart'] as $pid =>$value):
								$prid = explode("-", $pid);
								$query = $db->sql_query("SELECT prints.pid, prints.image, products.eid, products.price, products.description FROM prints, products WHERE prints.pid='".$prid[0]."' AND products.eid = '".$value['type']."'");
								list($pid, $images, $product, $price, $description) = $db->sql_fetchrow($query);
								$subtotal = $_SESSION['cart'][$pid."-".$product]['quantity'] * $_SESSION['cart'][$pid."-".$product]['price'];
								$total += $subtotal;
								echo "<tr>
									<td align=\"left\"><img src=\"/clients/".base64_decode($chk[5])."/thumbnails/{$images}\" /></td>
									<td align=\"left\" style=\"display:table-cell; vertical-align:top\">{$description}</td>
									<td align=\"left\" style=\"display:table-cell; vertical-align:top\">\$".$_SESSION['cart'][$pid."-".$product]['price']."</td>
									<td align=\"left\" style=\"display:table-cell; vertical-align:top\"><input type=\"text\" size=\"3\" name=\"qty[".$pid."-".$product."]\" value=\"".$_SESSION['cart'][$pid."-".$product]['quantity']."\" /></td>
									<td align=\"left\" style=\"display:table-cell; vertical-align:top\">$" . number_format($subtotal, 2) . "</td>
									</tr>\n";
							endforeach;