PHP MySQL Query - Display all items within the same order

PHP
1

Hi

I am having some problems with my mysql database and php queries.

I have a simple table (‘database_table’) that stores order details from my online gift shop. The table is set up as follows:

id | order_code | item_code | item_description | name

1 | 56743 | 56AAY | Fruit Bowl | Joe
2 | 56743 | 518VB | Aftershave | Joe
3 | 88932 | 11TQW | Red Mug | Mary
etc…

Each item (defined by item_code and item_description) within the order will be added to a separate row.

On orders such as ‘56743’ (Joe), there are two entries as they have ordered 2 items within the 1 order.

Now my problem is that i want to set up a query that pulls order ‘56743’ from the database and displays all the items within the order.

I have the following PHP code for my query:


$query="SELECT * FROM database_table WHERE order_code='56743'";	
$result=mysql_query($query);
mysql_close();

while($row = mysql_fetch_array($result)) {
   echo "
   <p>Your Order:</p>
   <p>Your Name: ".$row['name']."</p>
   <p>Your Order Code:".$row['order_code']."</p>
   <p>Your Item Codes:".$row['item_code']."</p>
   <p>Your Item Descriptions:".$row['item_description']."</p>";
}

This outputs the following:

Your Order:
Your Name: Joe
Your Order Code: 56743
Your Item Codes: 56AAY
Your Item Descriptions: Fruit Bowl
Your Order:
Your Name: Joe
Your Order Code: 56743
Your Item Codes: 518VB
Your Item Descriptions: Aftershave

But i only want it to output the ‘item_code’ and ‘item_description’ more than once if the order has several items, The ‘name’ and ‘order_code’ i only want to be displayed once (so as below:)

Your Order:
Your Name: Joe
Your Order Code: 56743
Your Item Codes: 56AAY
Your Item Codes: 518VB
Your Item Descriptions: Fruit Bowl
Your Item Descriptions: Aftershave

I am quite new to mysql so apologies if this isn’t explained to well and for the length of it!

Thanks in advance for any help

John

2

I think there are some potential problems with your table design but that is another issue. To help solve your immediate need what you can do is as you loop through $result, store the current values of $row[‘name’] and $row[‘item_code’] in a variable called $current_name and $current_item for example. Then on each iteration through the loop if the values of name or item_code are the same as $current_name or $current_item, don’t echo them out. If the name and item_code are different to the current values, reset the current values to the new name and item_code and then echo the new name and item_code.

hth

3

Thanks for the reply Shastah, sorry i am very new to setting up mySQL databases and tables, can you recommend me or show me any links on how i should set up my table design correctly?

4

Oddball25, if you got this far it means you already understand at least the basics of some code you run into. First, you could try SitePoint’s PHP & MySQL book, but it’s always a good idea to take a look at other open source projects similar to what you want to do. Just take a look at their MySQL tables and other stuff you’re interested in.

See this article: 8 Best Open Source Shopping Carts

5

the “name” column depends on the order, and not on the order item

further, the item description depends on the item, not on the order item

you need three tables – orders, items, and order_items