Forum Moderators: coopster
I have the following line of Sql from order.php,
$orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "'");
i want to add to it the ability to query for the product manaufacturers name, i think i need the syntax for the following rules.
1. ) lookup the product_model from products_table to retrieve the manufacturers_id
2.) Use the manufacturers_id to lookup manufacturers_table to retrieve manufacturers_name.
Could someone pls advise the correct syntax to perform such a query?
Many thnaks
Marc
$orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price, manufacturers_id, manufacturers_name from " . TABLE_ORDERS_PRODUCTS . " LEFT JOIN manufacturers_table ON (" . TABLE_ORDERS_PRODUCTS . ".manufacturers_id = manufacturers_table.manufacturers_id) where orders_id = '" . (int)$order_id . "'");
Your assumption of table_orders_products is not correct, it is table_products - therefore do i simply replace the TABLE_ORDERS_PRODUCTS with TABLE_PRODUCTS?
thanks again.
Marcus
$orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price, manufacturers_id, manufacturers_name from " . TABLE_ORDERS_PRODUCTS . " LEFT JOIN manufacturers_table ON (" . TABLE_ORDERS_PRODUCTS . ".manufacturers_id = manufacturers_table.manufacturers_id) where orders_id = '" . (int)$order_id . "'");
$orders_products_query = tep_db_query("select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price, manufacturers_id, manufacturers_name from " . TABLE_ORDERS_PRODUCTS . " LEFT JOIN table_products ON (" . TABLE_ORDERS_PRODUCTS . ".manufacturers_id = table_products.manufacturers_id) LEFT JOIN manufacturers_table ON (table_products.manufacturers_id = manufacturers_table.manufacturers_id) where orders_id = '" . (int)$order_id . "'");
1052 - Column: 'products_id' in field list is ambiguous
select orders_products_id, products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price, manufacturers_id, manufacturers_name from orders_products LEFT JOIN products ON (orders_products.manufacturers_id = PRODUCTS.manufacturers_id) LEFT JOIN manufacturers ON (products.manufacturers_id = manufacturers.manufacturers_id) where orders_id = '14199'
[TEP STOP]
any ideas?
thanks for your help on this.
Marcus
orders_products.products_id
my query syntax is:
$orders_products_query = tep_db_query("select orders_products_id, products.products_id, products_name, orders_products.products_model, orders_products.products_price, products_tax, orders_products.products_quantity, final_price, products.manufacturers_id, manufacturers_name from " . TABLE_ORDERS_PRODUCTS . " LEFT JOIN products ON (" . TABLE_ORDERS_PRODUCTS . ".manufacturers_id = PRODUCTS.manufacturers_id) LEFT JOIN manufacturers ON (products.manufacturers_id = manufacturers.manufacturers_id) where orders_id = '" . (int)$order_id . "'");
i now get :
1054 - Unknown column 'orders_products.manufacturers_id' in 'on clause'
select orders_products_id, products.products_id, products_name, orders_products.products_model, orders_products.products_price, products_tax, orders_products.products_quantity, final_price, products.manufacturers_id, manufacturers_name from orders_products LEFT JOIN products ON (orders_products.manufacturers_id = PRODUCTS.manufacturers_id) LEFT JOIN manufacturers ON (products.manufacturers_id = manufacturers.manufacturers_id) where orders_id = '14199'
NOTE: The manufacturers_id is in the products table - i tried changing the TABLE_ORDERS_PRODUCTS to TABLE_PRODUCTS - it didnt like it....
so just to recap, i take the products in table orders_products and lookup the manufacturers_id in the table products, i then need the manufacturers_name from the table manufacturers.
NOTE:
Tables products and manufacturers have the manufacturers_id in common.
Tables orders_products and products have the model in common.
?
I used a LEFT JOIN because it will return NULL values if there are no relative rows in the JOINed table. INNER JOIN will return rows for ONLY those that match in all tables.
How does this effect the returned array?
What's the practical difference between returning a NULL value and returning no row?
Thanks.
Ok, the syntax seems to be fine. When i feed this into my array, it doesnt display anything - i dont think it picked up the value.
$orders_products_query = tep_db_query("select orders_products_id, products.products_id, products_name, orders_products.products_model, orders_products.products_price, products_tax, orders_products.products_quantity, final_price, products.manufacturers_id, manufacturers_name from orders_products LEFT JOIN products ON (orders_products.orders_products_id = products.products_id) LEFT JOIN manufacturers ON (products.manufacturers_id = manufacturers.manufacturers_id) where orders_id = '" . (int)$order_id . "'");
while ($orders_products = tep_db_fetch_array($orders_products_query)) {
$this->products[$index] = array('qty' => $orders_products['products_quantity'],
'name' => $orders_products['products_name'],
'model' => $orders_products['products_model'],
'manufacturer' => $orders_products['?'],
'tax' => $orders_products['products_tax'],
'price' => $orders_products['products_price'],
'final_price' => $orders_products['final_price']);
where the? is i tried the 'manufacturer_name', any other field such as 'products_name' works great!
I'm not so sure the sql syntax is taking a field such as the products_model from the orders_products table and looking up the manufacturers_id associated with this record , this is contained within the products table. Once i have this manufacturers_id for the product in question i can query for the manufacturers_name within the manufacturers table.
Hope this makes sense, god your gonna wish you never bothered...! ;-)
thanks for your patients.
Marcus
StupidScript asked:How does this effect the returned array?
What's the practical difference between returning a NULL value and returning no row?
With a LEFT JOIN, the returned array will contain NULL values for any products ordered that did not have a related manufacturer's row in the other tables. If we used an INNER JOIN, we wouldn't return the row at all, and that means we would no longer have that item in the products ordered and the customer wouldn't have it in their cart! That would be bad. We still want to sell the item even though we can't offer manufacturing information.
Take these two tables, as an example:
Table: t1
+----+--------+
¦ id ¦ text ¦
+----+--------+
¦ 1 ¦ First ¦
¦ 2 ¦ Second ¦
¦ 3 ¦ Third ¦
¦ 4 ¦ Four ¦
+----+--------+
Table: t2
+----+--------+
¦ id ¦ text ¦
+----+--------+
¦ 1 ¦ First ¦
¦ 4 ¦ Four ¦
+----+--------+
SELECT * FROM t1 LEFT JOIN t2 USING (id);Hope that clears things up for you.
--
-- Returns:
--
+----+--------+------+------+
¦ id ¦ text ¦ id ¦ text ¦
+----+--------+------+------+
¦ 1 ¦ First ¦ 1 ¦ First¦
¦ 2 ¦ Second ¦ NULL ¦ NULL ¦
¦ 3 ¦ Third ¦ NULL ¦ NULL ¦
¦ 4 ¦ Four ¦ 4 ¦ Four ¦
+----+--------+------+------+
--
-- whereas an INNER JOIN returns only the matching rows...
--
SELECT * FROM t1 INNER JOIN t2 USING (id);
--
-- Returns:
--
+----+--------+------+------+
¦ id ¦ text ¦ id ¦ text ¦
+----+--------+------+------+
¦ 1 ¦ First ¦ 1 ¦ First¦
¦ 4 ¦ Four ¦ 4 ¦ Four ¦
+----+--------+------+------+
Have you tried echoing the query out to the browser to see if the query statement looks good first? Then maybe even copy and paste that to a command line to see what data gets returned by MySQL? Try it. This is also a good practice -- build your query statement as a separate variable so that you can echo it when you are debugging your scripts...
$sql = "select orders_products_id, products.products_id, products_name, orders_products.products_model, orders_products.products_price, products_tax, orders_products.products_quantity, final_price, products.manufacturers_id, manufacturers_name from orders_products LEFT JOIN products ON (orders_products.orders_products_id = products.products_id) LEFT JOIN manufacturers ON (products.manufacturers_id = manufacturers.manufacturers_id) where orders_id = '" . (int)$order_id . "'";
// exit($sql); // Uncomment this line to troubleshoot sql statement
$orders_products_query = tep_db_query($sql);
while ($orders_products = tep_db_fetch_array($orders_products_query)) {
Best Regards
Marcus
