query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
moiseszaragoza
Forum Commoner
Posts: 87
Joined: Sun Oct 03, 2004 4:04 pm
Location: Ft lauderdale
Contact:

query

Post by moiseszaragoza »

I need to join all the information of 2 tables into one query

Here are the table names with all the fields so, if you can help me to write a proper query

Table Name
orders

Fields

orders_id, * Primary KEY
customers_id,
customers_name,
customers_company,
customers_street_address,
customers_suburb,
customers_city,
customers_postcode,
customers_state,
customers_country,
customers_telephone,
customers_email_address,
customers_address_format_id,
delivery_name,
delivery_company,
delivery_street_address,
delivery_suburb, delivery_city,
delivery_postcode, delivery_state,
delivery_country, delivery_address_format_id,
billing_name, billing_company,
billing_street_address,
billing_suburb, billing_city,
billing_postcode, billing_state,
billing_country,
billing_address_format_id,
payment_method,
cc_type,
cc_owner,
cc_number,
cc_expires,
last_modified,
date_purchased,
orders_status,
orders_date_finished,
currency,
currency_value,





Table Name
orders_products

Fields
orders_products_id, Primary Key
orders_id, *Link KEY
products_id,
products_model,
products_name,
products_price,
final_price,
products_tax,
products_quantity,
ryanlwh
Forum Commoner
Posts: 84
Joined: Wed Sep 14, 2005 1:29 pm

Post by ryanlwh »

Code: Select all

SELECT * FROM orders,orders_products WHERE orders.orders_id=orders_products.orders_id
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

ryanlwh wrote:

Code: Select all

SELECT * FROM orders,orders_products WHERE orders.orders_id=orders_products.orders_id
or

Code: Select all

SELECT * FROM `orders`
INNER JOIN `orders_products` 
USING (`orders_id`)
Both are valid ways of doing it, I just like readability
User avatar
moiseszaragoza
Forum Commoner
Posts: 87
Joined: Sun Oct 03, 2004 4:04 pm
Location: Ft lauderdale
Contact:

Post by moiseszaragoza »

would it be to hard to add combine the were clause to say

were orders_status=1
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

The long method:

Code: Select all

SELECT * FROM orders INNER JOIN orders_products ON orders.orders_id = orders_products.orders_id WHERE orders.orders_status = '1';
:)
User avatar
moiseszaragoza
Forum Commoner
Posts: 87
Joined: Sun Oct 03, 2004 4:04 pm
Location: Ft lauderdale
Contact:

Post by moiseszaragoza »

Thanks.
that has been killing me for a month.

I so have to come here more often
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

SELECT * FROM `orders` AS `t1`
INNER JOIN `orders_products`
USING (`orders_id`) 
WHERE `t1`.`orders_status` =  '1'
:lol:
Post Reply