MySQL multiple joins - how do i display the data? (using PHP)

Question

Ok, i know this is stupid but I'm stuck and I need a kick in the head. I have 3 tables (orders, orderitems, products) and I'm using 2 left outer joins in a query to display the orders history in the clients section:

SELECT * FROM orderitems oi, orders o
LEFT OUTER JOIN product p ON p.dbarcode = oi.orderitems_item
WHERE o.order_code = oi.orderitems_ordercode 
    AND oi.orderitems_ordercode = '".$_GET['ordercode']."'`

I can display a row for each product in the table order_items but I can't seem to display (echo) the total amount which is held in the field orders.order_amount, i presume it has to do with the fact that the field has multiple instances or whatsoever. How do I display data as a result of several tables joined?

Edit for Quassnoi:

I loop through the results and create a table row for each one and, after the loop, i want to display the general total:

  <? do { ?>
  <tr>
    <td><? echo $row_OrderItems['dbarcode']; ?></td>
    <td><? echo $row_OrderItems['name']; ?></td>
    <td><? echo $row_OrderItems['orderitems_quantity']; ?></td>
    <td><? echo $row_OrderItems['price']; ?></td>
  </tr>
  <? } while ($row_OrderItems = mysql_fetch_assoc($OrderItems)); ?>

  <tr>
    <td colspan="5">Total order amount (incl. shipping and handling): &euro; <? echo $row_OrderItems['order_amounteuro']; ?></td>
  </tr>

Would adding o.order_amount AS order_amount in the SELECT help? — Marco
– Marco, Commented Apr 14, 2011 at 11:49
Yes, I've realised that the reason I cant display the total is because it's outside the loop, however i need to display it there and not inside the repeated region. Any ideas? — bikey77
– bikey77, Commented Apr 14, 2011 at 13:45

Community · Accepted Answer · 2017-02-08 14:07:40Z

3

o.order_amount will be repeated in every row, just take the first one.

And, of course, never use this:

oi.orderitems_ordercode = '".$_GET['ordercode']."'`

without mysql_real_escape_string.

edited Feb 8, 2017 at 14:07

CommunityBot

11 silver badge

answered Apr 14, 2011 at 11:51

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bikey77 Over a year ago

Yes, I do sanitize the inputs, I just used the above as an example! I loved the cartoon strip though! :)

bikey77 Over a year ago

Forgot to ask though, HOW do I display only the first instance? If i simply echo out the field ie echo $row_orderinfo('order_amout') it shows nothing although the data has been fetched (i can see it with print_r)

Quassnoi Over a year ago

@bikey77: please post a sample of your code (as an update to your original post)

bumperbox · Accepted Answer · 2011-04-14 19:57:21Z

1

<? do { ?>
  <tr>
    <td><? echo $row_OrderItems['dbarcode']; ?></td>
    <td><? echo $row_OrderItems['name']; ?></td>
    <td><? echo $row_OrderItems['orderitems_quantity']; ?></td>
    <td><? echo $row_OrderItems['price']; ?></td>
  </tr>
  <? 
$total = $row_OrderItems['order_amounteuro']; // store the total, before you loop past the last row
} while ($row_OrderItems = mysql_fetch_assoc($OrderItems)); ?>

  <tr>
    <td colspan="5">Total order amount (incl. shipping and handling): &euro; <? echo $total; ?></td>
  </tr>

edited Apr 14, 2011 at 19:57

answered Apr 14, 2011 at 11:53

bumperbox

10.2k6 gold badges46 silver badges66 bronze badges

1 Comment

bikey77 Over a year ago

Yes, I have, i'm using print_r and I can see all the data correctly.

JamesHalsall · Accepted Answer · 2011-04-14 11:52:58Z

0

Just an FYI, this part of your statement:

SELECT * FROM orderitems oi, orders o 
LEFT OUTER JOIN product p ON p.dbarcode = oi.orderitems_item

is fetching everything from orderitems, orders and products. Prefix the * with the table you want all the fields from, or rewrite it to fetch specific fields.

answered Apr 14, 2011 at 11:52

JamesHalsall

13.5k4 gold badges43 silver badges68 bronze badges

1 Comment

bikey77 Over a year ago

I get your point but doesnt the condition "AND oi.orderitems_ordercode = '".$_GET['ordercode']."'" do the job and just pull the specific records from the database?

Collectives™ on Stack Overflow

MySQL multiple joins - how do i display the data? (using PHP)

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related