Page 2 of 3

Posted: Tue Jan 10, 2006 9:54 pm
by Jim_Bo

Code: Select all

SELECT 
   * 
FROM 
   `ordercontent` o, 
   `cart` c 
WHERE 
   o.`orderid` = c.`orderid` AND 
   c.`userid` = '{$_SESSION['userid']}' 
ORDER BY 
   c.`orderid` DESC, 
   o.`contentid` DESC 
GROUP BY 
   o.`contentid`
Produces error:

Code: Select all

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY o.`contentid`' at line 12

?


Thanks

Posted: Tue Jan 10, 2006 10:31 pm
by RobertGonzalez
GROUP BY essentially performs a SELECT DISTINCT on the grouped by field. Everything before the GROUP BY clause will be overridden by the GROUP BY. What you will get is a result set that will have a listing of unique, individual contentid data.

I'm not sure that you can do what you want with one query. Basically you want a result set where the data is shown in groups (by contentid), then within the groups you want to show all records related to that contentid, is that correct?

Posted: Wed Jan 11, 2006 1:02 am
by Jim_Bo
I think thats it ..

2 tables .. ordercontent holds the purchased items - price, description, orderid, quantity and userid:

Code: Select all

CREATE TABLE cart (
  orderid int(25) NOT NULL auto_increment,
  userid int(25) NOT NULL default '',
  total decimal(11,2) NOT NULL default '0',
  orderdate datetime NOT NULL default '0000-00-00 00:00:00',
  approved varchar(5) NOT NULL default '',
  PRIMARY KEY (orderid)
) TYPE=MyISAM;

CREATE TABLE ordercontent (
  contentid int(25) NOT NULL auto_increment,
  orderid int(25) NOT NULL default '',
  userid int(25) NOT NULL default '',
  itemdesc text NOT NULL default '',
  subtotal decimal(11,2) NOT NULL default '0',
  qty int(25) NOT NULL default '',
  PRIMARY KEY (contentid)
) TYPE=MyISAM;
So im trying to display all the data from ordercontent with the same orderid's into a group along with the matching orderid in the cart table .. as shown in the picture No 2 above


Thanks

Posted: Wed Jan 11, 2006 11:14 am
by RobertGonzalez
OK, I get it. You can run two queries fairly easily and I would guess without a lot of overhead. The first query selects all contentid's. The second query grabs all orders. Loop through the contentid array, then for each iteration, loop through the order array echoing out only those array values that match the contentid you are on in the first loop.

Code: Select all

<?php
// Content query
$sql = "SELECT * FROM ordercontent ORDER BY contentid ASC";
if (!$result = mysql_query($sql))
{
    die("Could not query content table: " . mysql_error());
}
while ($row = mysql_fetch_array($result))
{
    $ordercontent[] = $row;
}

// Cart query
$sql = "SELECT * FROM cart ORDER BY orderid ASC";
if (!$result = mysql_query($sql))
{
    die("Could not query cart table: " . mysql_error());
}
while ($row = mysql_fetch_array($result))
{
    $cart[] = $row;
}

// Run the content loop
for ($i = 0; $i < count($ordercontent); $i++)
{
    $order_id = $ordercontent[$i]['orderid'];
    echo "Item description is " . $ordercontent[$i]['itemdesc'] . ".<br />";

    // Run the cart loop
    for ($j = 0; $j < count($cart); $j++)
    {
        if ($cart[$j]['orderid'] == $order_id)
        {
            echo "The user id is " . $cart[$j]['userid'] . ".<br />";
        }
    }
}
?>
Of course this is not exactly what you want, but it should be easily adaptable to what you want. Try it and see if it comes close.

Posted: Wed Jan 11, 2006 5:04 pm
by Jim_Bo
edited*

Code: Select all

CREATE TABLE cart (
  orderid int(25) NOT NULL auto_increment,
  userid int(25) NOT NULL default '0',
  total decimal(11,2) NOT NULL default '0.00',
  orderdate datetime NOT NULL default '0000-00-00 00:00:00',
  approved varchar(5) NOT NULL default '',
  PRIMARY KEY  (orderid)
) TYPE=MyISAM;



INSERT INTO cart (orderid, userid, total, orderdate, approved) VALUES (1,1,'345.00','2006-01-08 01:17:33','No');
INSERT INTO cart (orderid, userid, total, orderdate, approved) VALUES (2,1,'375.00','2006-01-08 01:28:15','No');
INSERT INTO cart (orderid, userid, total, orderdate, approved) VALUES (3,1,'240.00','2006-01-08 02:10:47','No');
INSERT INTO cart (orderid, userid, total, orderdate, approved) VALUES (4,1,'342.00','2006-01-08 02:47:12','No');

CREATE TABLE ordercontent (
  contentid int(25) NOT NULL auto_increment,
  orderid int(25) NOT NULL default '0',
  userid int(25) NOT NULL default '0',
  itemdesc text NOT NULL,
  subtotal decimal(11,2) NOT NULL default '0.00',
  qty int(25) NOT NULL default '0',
  PRIMARY KEY  (contentid)
) TYPE=MyISAM;



INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (1,1,1,'Slim Shady - ','15.00',0);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (2,1,1,'Elite Jacket - ','30.00',0);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (3,2,1,'Slim Shady - Bla BLa','15.00',10);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (4,2,1,'Elite Jacket - Test Test \" Bla','30.00',10);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (5,3,1,'Slim Shady - Bla BLa','15.00',4);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (6,3,1,'Elite Jacket - Test Test \" Bla','30.00',6);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (7,4,1,'Slim Shady - Bla BLa','15.00',4);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (8,4,1,'Elite Jacket - Test Test \" Bla','30.00',6);
INSERT INTO ordercontent (contentid, orderid, userid, itemdesc, subtotal, qty) VALUES (9,4,1,'Cher Cher - Bla BLa','34.00',3);

Thanks

Posted: Wed Jan 11, 2006 5:11 pm
by feyd
are the last 3 inserts intended for the second table?

Posted: Wed Jan 11, 2006 5:18 pm
by RobertGonzalez
Sorry, had it backwards. Run the cart query first then loop the content query inside of it. Try this...

Code: Select all

<?php
// Cart query
$sql = "SELECT * FROM cart ORDER BY orderid ASC GROUP BY orderid";
if (!$result = mysql_query($sql))
{
    die("Could not query cart table: " . mysql_error());
}
while ($row = mysql_fetch_array($result))
{
    $cart[] = $row;
}

// Content query
$sql = "SELECT * FROM ordercontent ORDER BY contentid ASC";
if (!$result = mysql_query($sql))
{
    die("Could not query content table: " . mysql_error());
}
while ($row = mysql_fetch_array($result))
{
    $ordercontent[] = $row;
}


// Run the cart loop
for ($i = 0; $i < count($cart); $i++)
{
    $order_id = $cart[$i]['orderid'];
    echo "Shopping cart order id is $order_id.<br />";

    // Run the order content loop
    for ($j = 0; $j < count($ordercontent); $j++)
    {
        if ($ordercontent[$j]['orderid'] == $order_id)
        {
            echo "The item description is " . $ordercontent[$j]['itemdesc'] . ".<br />";
        }
    }
}
?>
All this will do is run the loops to see if they throw one piece of information from each of the queries, grouped the way you want. To put them in tables just add the appropriate mark up.

Posted: Wed Jan 11, 2006 5:51 pm
by Jim_Bo
Doh .. Screwed that up :(

Have edited the table data above to the correct dump ..

Sorry

Thanks

Posted: Thu Jan 12, 2006 12:17 am
by Jim_Bo
Here is what I have .. seems to do the job intended:

Code: Select all

<?php

if (isset($_GET['pending'])) { 
 	
	$sql = mysql_query("SELECT * FROM cart WHERE approved = 'no' ORDER BY orderid ASC") or die(mysql_error());  
		while ($row = mysql_fetch_array($sql)) { 
			$cart[] = $row;
} 

	$sql = mysql_query("SELECT * FROM ordercontent ORDER BY contentid ASC") or die(mysql_error()); ; 
		while ($row = mysql_fetch_array($sql)) { 
    		$ordercontent[] = $row; 
} 
 
// Run the cart loop 
	foreach ($cart as $data) { 
    	$order_id = $data['orderid']; 
		$total = $data['total'];    
?>

<table width="98%" align="center" cellpadding="0" cellspacing="0">
 <tr>
  <td colspan="3"><b>Date Ordered:</b> <?php echo $data['orderdate']; ?></td>
  <td width="13%"><div align="right"><b>Order No:</b> <?php echo $data['orderid']; ?></div></td>
 </tr>
 <tr>
  <td colspan="4">&nbsp;</td>
 </tr>
 <tr>
  <td width="70%"><b>Description</b></td>
  <td width="9%"><div align="right"><b>Price </b></div></td>
  <td width="8%"><div align="right"><b>Qty</b></div></td>
  <td><div align="right"><b>Sub Total</b></div></td>
 </tr>
<?php // Run the order content loop 
	foreach ($ordercontent as $data) {
		if ($data['orderid'] == $order_id) { 
			$itemtotal = $data['subtotal'] * $data['qty'];
?>
  <tr>
  <td><?php echo $data['itemdesc']; ?></td>
  <td><div align="right"><?php echo number_format($data['subtotal'], 2); ?></div></td>
  <td><div align="right"><?php echo $data['qty']; ?></div></td>
  <td><div align="right"><?php echo number_format($itemtotal, 2); ?></div></td>
 </tr><?php } } ?>
 <tr>
  <td colspan="2">&nbsp;</td>
  <td><div align="right"><b>Total</b></div></td>
  <td><div align="right"><font color="red"><b><?php echo number_format($total, 2); ?></b></font></div></td>
 </tr>
</table><hr />

<?php	
 } 
} 
?>

The result I was after:

http://www.temp.computerfusion.co.nz/in ... ng=pending


Is that plausable code?

Also how come I have to call on:

$total = $data['total'];

and not just

$data['total'];

?


Thanks[/url]

Posted: Thu Jan 12, 2006 12:32 am
by RobertGonzalez
$data['total'] should work fine. If it can read into $total then it should echo by itself. Glad to see that it is working though. Have you tried echoing out $data['total'] before it is read into total? See what comes out at that point.

Posted: Thu Jan 12, 2006 1:12 am
by Jim_Bo
It wont echo anywhere after:

Code: Select all

<?php // Run the order content loop 
	foreach ($ordercontent as $data) {
		if ($data['orderid'] == $order_id) { 
			$itemtotal = $data['subtotal'] * $data['qty'];
?>
Without setting a variable above it for $data['total'] .. ?


Thanks

Posted: Thu Jan 12, 2006 1:49 am
by RobertGonzalez
Try removing the square brackets from $cart[] and $ordercontent[]. I set them that way in my code because I was walking them with a for loop. The way I made the arrays makes them multidimensional, so in your foreach loop the $data var becomes an array. If you reduce the dimensions of the original arrays it should work fine in your foreach.

Posted: Thu Jan 12, 2006 2:02 am
by Jim_Bo
u mean change:

$cart[] = $row;

to

$cart = $row;

?

That makes an error ..

All the variables from ordercontent table display ok using $row['bla'] etc .. I dont get why the cart table variables dont do the same? .. they both using foreach?

Posted: Thu Jan 12, 2006 2:27 am
by RobertGonzalez
Alright, change it back to what worked, but change $data in your second foreach loop to something else. See if that does anything. I am about to call it a night, but I will be checking this board tomorrow morning.

Posted: Thu Jan 12, 2006 2:51 pm
by Jim_Bo
This is what I have got now:

Code: Select all

<center><a href="../index.php?pages=invoices">Past Invoices</a> | <a href="../index.php?pages=invoices&pending=pending">Pending Orders</a></center><br />

<?php

if (isset($_GET['pending'])) { 
		$approved = 'No';
		$empty = "No Invoices Pending";
}else{
		$approved = 'Yes';
		$empty = "You have never purchased anything";
}

	$sql = mysql_query("SELECT * FROM cart WHERE approved = '$approved' AND userid = '".$_SESSION['userid']."' ORDER BY orderid DESC") or die(mysql_error());  
		while ($row = mysql_fetch_array($sql)) {			
			$cart[] = $row;
} 

	$sql = mysql_query("SELECT * FROM ordercontent WHERE userid = '".$_SESSION['userid']."' ORDER BY contentid DESC") or die(mysql_error()); ; 
		while ($row = mysql_fetch_array($sql)) { 
    		$num_rows = mysql_num_rows($sql);
			$ordercontent[] = $row; 
}

	foreach ($cart as $data) { 
    	$order_id = $data['orderid']; 
		$total = $data['total'];
?>

<table width="99%" border="0" align="center" cellpadding="0" cellspacing="0" style="margin: 3px; border: 1px solid #000000;">
 <tr>
  <th colspan="5">&nbsp;</th>
 </tr>
 <tr>
  <td width="49%"><b>Date:</b> <?php echo $data['orderdate']; ?></td>
  <td colspan="3"><b>Payment Via:</b> Credit Card</td>
  <td width="13%"><div align="right"><b>Order No:</b> <?php echo $data['orderid']; ?></div></td>
 </tr>
 <tr>
  <td colspan="5">&nbsp;</td>
 </tr>
 <tr>
  <td colspan="2"><b>Description</b></td>
  <td width="9%"><div align="right"><b>Price </b></div></td>
  <td width="8%"><div align="right"><b>Qty</b></div></td>
  <td><div align="right"><b>Sub Total</b></div></td>
 </tr>
 <?php foreach ($ordercontent as $data) {
		if ($data['orderid'] == $order_id) {
			$itemtotal = $data['subtotal'] * $data['qty']; ?>
  <tr>
  <td colspan="2"><?php echo stripslashes($data['itemdesc']); ?></td>
  <td><div align="right"><?php echo number_format($data['subtotal'], 2); ?></div></td>
  <td><div align="right"><?php echo $data['qty']; ?></div></td>
  <td><div align="right"><?php echo number_format($itemtotal, 2); ?></div></td>
 </tr><?php } } ?>
 <tr>
  <td colspan="3">&nbsp;</td>
  <td><div align="right"><b>Total</b></div></td>
  <td><div align="right"><font color="red"><b><?php echo number_format($total, 2); ?></b></font></div></td>
 </tr>
</table>

<?php } ?>
I want to share the form for 2 links ..

Pending and complete .. I have put in a if statement at the top for:

if (isset($_GET['pending'])) {

I tried using $num_rows = mysql_num_rows($sql); to check for NULL results .. but if there are no results I get the error:

Invalid argument supplied for foreach() ....


How can I sort that?


Thanks