PHP/MySQL Head-Spinning Query - Please Help

[frank5050]

PHP/mysql newb here and I'd appreciate any help.

I have 3 mysql tables that I'm trying to query via a join query.

The 3 tables are:

order

order_option

order_product


Order 4 as seen in shopping cart before going in database looks like this:

(



order_id is shared field in all tables
order_product_id is shared field in order_option and order_product tables.

In a single query,

For order_id : 4

I would like to retrieve invoice_id from table order and,

I would like to retrieve from tables order_product and order_option the following info in the following manner:

**************************************************
PRODUCT: LG 1200 Unlock Code
Network: U.S.A. - AT&T
IMEI: 565656565656565
IMEI: 343434355666666
Network: U.S.A. - T-Mobile
IMEI: 767676766766776


PRODUCT: HTC 2223 Unlock Code
Network: U.S.A. - SunCom
IMEI: 383838383839405
Network: U.S.A. - AT&T
IMEI: 123456789123456


***************************************************

It's important to note that the value of the field 'name' in table order_option is always either Network or IMEI.


My current query is shown below:

SELECT * FROM table.order AS o INNER JOIN table.order_option AS p, table.order_product AS r where o.order_id = p.order_id AND o.order_id = r.order_id";


With the above query all I get is a list of everything in the tables. That's a lot of unorganized info on a page and not concatenated or bunched together. How do I bunch together and organize the information based on order/product/options as I stated above?

I'm a newb but I'm thinking concat may be needed as well as possibly distinct in order to organize the options under individual products based on IMEI and Network. (that being said, I'm no specialist and i could be totally off).

I am really stuck and I'd appreciate help from some of the gurus in here.

Thanks

[harkly]

You can't do this

SELECT * FROM table.order AS o

and expect to pull things out individually. I would not do use "AS" if not needed.

Check out this website for learning MySql, they will show you how to "Join" http://sql.learncodethehardway.org/book/

Here is a Select I did with multiple tables, userID is the "shared field"

PHP Code:

$search = "SELECT
user.userID,
user.first_name,
user.gender,
about_me.headline,
about_me.description,
pets.dog,
pets.cat,
pets.fish

FROM user
LEFT JOIN about_me
ON user.userID = about_me.userID

LEFT JOIN pets
ON user.userID = pets.userID

WHERE user.userID = '$userID'";
$result = $mysqli->query($search);

while ($r=$result->fetch_assoc()) {
$userID=$r["userID"];
$dog=$r["dog"];
$cat=$r["cat"];

// put all your code in the 'while' which will make it lope until the array is finished

echo "$userID has $dog and $cat"; // this line will repeat for as many $userID
} 


Hope this helps