Help with SQL query

[ianric]

Hi

 

In the account section, you can view your previous orders. I have a test user / login and added titles to my cart which are waiting to be processed (not done that bit yet). So I login, goto account, it shows the previous oder so I pick the view button. It is displayed OK but I want to display a custom field as well.

 

I have found the SQL query in includes/classes/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 a custom field from my products table so after products_name I added products_title (does exist and works elsewhere in the site) and get the error

 

1054 - Unknown column 'products_title' in 'field list'

 

select orders_products_id, products_id, products_name, products_title, products_model, products_price, products_tax, products_quantity, final_price from orders_products where orders_id = '11'

 

I changed the products_title to p.products_title but I get a similar error.

 

Can someone point me in the right direction, can I join 2 queries together like query3=query1 . query2??

 

thanks

 

ian

[wheeloftime]

Hi

 

In the account section, you can view your previous orders. I have a test user / login and added titles to my cart which are waiting to be processed (not done that bit yet). So I login, goto account, it shows the previous oder so I pick the view button. It is displayed OK but I want to display a custom field as well.

 

I have found the SQL query in includes/classes/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 a custom field from my products table so after products_name I added products_title (does exist and works elsewhere in the site) and get the error

 

1054 - Unknown column 'products_title' in 'field list'

 

select orders_products_id, products_id, products_name, products_title, products_model, products_price, products_tax, products_quantity, final_price from orders_products where orders_id = '11'

 

I changed the products_title to p.products_title but I get a similar error.

 

Can someone point me in the right direction, can I join 2 queries together like query3=query1 . query2??

 

thanks

 

ian

You have to add the field also to the orders_products table and besides that make sure the field is being filled when an order is processed so the data is put into the orders_products table.

[ianric]

You have to add the field also to the orders_products table and besides that make sure the field is being filled when an order is processed so the data is put into the orders_products table.

 

Hi Howard

 

Thanks for the reply. I had a play with the tables but got lost so I left it til yesterday. I knew it could be done but I forgot how. I've managed it by changing the default sql from

 

$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 . "'");

 

to

 

$orders_products_query = tep_db_query("SELECT orders_products_id, orders_products.products_id AS P_ID, products_name, orders_products.products_model AS P_Model, orders_products.products_price AS P_Price, products_tax, orders_products.products_quantity AS P_Qty, final_price, products.products_title FROM " . TABLE_ORDERS_PRODUCTS . "," . TABLE_PRODUCTS . " WHERE orders_products.products_id = products.products_id");

 

I had to use aliases because of an ambiguous error. I modified the array and added my custom field

 

		$this->products[$index] = array('qty' => $orders_products['P_Qty'],
								'id' => $orders_products['products_id'],
									'name' => $orders_products['products_name'],
									'title' => $orders_products['products_title'],
									'model' => $orders_products['products_model'],
									'tax' => $orders_products['products_tax'],
									'price' => $orders_products['P_Price'],
									'final_price' => $orders_products['final_price']);

 

and... hey presto! it works in account_history_info.php and in order_confirmation.

 

I am so good I amaze myself sometimes. :D

 

Cheers

 

Ian

[wheeloftime]

Hi Howard

 

Thanks for the reply. I had a play with the tables but got lost so I left it til yesterday. I knew it could be done but I forgot how. I've managed it by changing the default sql from

 

$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 . "'");

 

to

 

$orders_products_query = tep_db_query("SELECT orders_products_id, orders_products.products_id AS P_ID, products_name, orders_products.products_model AS P_Model, orders_products.products_price AS P_Price, products_tax, orders_products.products_quantity AS P_Qty, final_price, products.products_title FROM " . TABLE_ORDERS_PRODUCTS . "," . TABLE_PRODUCTS . " WHERE orders_products.products_id = products.products_id");

 

I had to use aliases because of an ambiguous error. I modified the array and added my custom field

 

		$this->products[$index] = array('qty' => $orders_products['P_Qty'],
								'id' => $orders_products['products_id'],
									'name' => $orders_products['products_name'],
									'title' => $orders_products['products_title'],
									'model' => $orders_products['products_model'],
									'tax' => $orders_products['products_tax'],
									'price' => $orders_products['P_Price'],
									'final_price' => $orders_products['final_price']);

 

and... hey presto! it works in account_history_info.php and in order_confirmation.

 

I am so good I amaze myself sometimes. :D

 

Cheers

 

Ian

Way to go Ian :thumbsup:

[Velveeta]

Hi Howard

 

Thanks for the reply. I had a play with the tables but got lost so I left it til yesterday. I knew it could be done but I forgot how. I've managed it by changing the default sql from

 

$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 . "'");

 

to

 

$orders_products_query = tep_db_query("SELECT orders_products_id, orders_products.products_id AS P_ID, products_name, orders_products.products_model AS P_Model, orders_products.products_price AS P_Price, products_tax, orders_products.products_quantity AS P_Qty, final_price, products.products_title FROM " . TABLE_ORDERS_PRODUCTS . "," . TABLE_PRODUCTS . " WHERE orders_products.products_id = products.products_id");

 

I had to use aliases because of an ambiguous error. I modified the array and added my custom field

 

		$this->products[$index] = array('qty' => $orders_products['P_Qty'],
								'id' => $orders_products['products_id'],
									'name' => $orders_products['products_name'],
									'title' => $orders_products['products_title'],
									'model' => $orders_products['products_model'],
									'tax' => $orders_products['products_tax'],
									'price' => $orders_products['P_Price'],
									'final_price' => $orders_products['final_price']);

 

and... hey presto! it works in account_history_info.php and in order_confirmation.

 

I am so good I amaze myself sometimes. :D

 

Cheers

 

Ian

 

Good job, I was going to add a reply if you'd added that new field to orders_products, that you could have done it with a join (which is how you ended up getting it done)... Anytime there's a table that already contains a field, and you want to add that field to a query that pulls from another table, and they share a common key (products_id), you should probably do it with a join rather than duplicating the data in the other table and changing the code to populate it... It all goes back to the rules of normalization, that you should isolate data in different tables that hold related info, and grab based on the key references between tables...

 

Richard.

[wheeloftime]

Good job, I was going to add a reply if you'd added that new field to orders_products, that you could have done it with a join (which is how you ended up getting it done)... Anytime there's a table that already contains a field, and you want to add that field to a query that pulls from another table, and they share a common key (products_id), you should probably do it with a join rather than duplicating the data in the other table and changing the code to populate it... It all goes back to the rules of normalization, that you should isolate data in different tables that hold related info, and grab based on the key references between tables...

 

Richard.

Normally I would agree but in this case I think the underlying reason for duplicating product fields lies in the fact that if you start cleaning your products you will loose the information needed for the orders...

[Velveeta]

Normally I would agree but in this case I think the underlying reason for duplicating product fields lies in the fact that if you start cleaning your products you will loose the information needed for the orders...

 

Ahhhhhhh yes, good point :)

 

Richard.

[ianric]

Ahhhhhhh yes, good point :)

 

Richard.

 

 

Hello again

 

It turns out that the SQL is wrong and it duplicates the data so I'm not so good afterall :blush:

 

So, how do I go about doing this.

 

You have to add the field also to the orders_products table and besides that make sure the field is being filled when an order is processed so the data is put into the orders_products table.

 

Thanks

 

Ian

[ianric]

Hello again

 

It turns out that the SQL is wrong and it duplicates the data so I'm not so good afterall :blush:

 

So, how do I go about doing this.

Thanks

 

Ian

Please please can someone help me on this one. All I want to do is to display a custom field in the products table on the account_history_info page. I have managed to do this for all the other pages but not this one. If the SQL query is too complex how do I program the button on the confirm order page, which displays the custom field, to insert the custom field into the orders_products table.

 

Many thanks

 

Ian

[Guest]

Having a very similar problem, see my forum posts here http://www.oscommerce.com/forums/index.php?showtopic=250421

 

So far I have generated my fields correctly and when an order is placed the correct table in my database is populated.

 

Only thing I'm stuck on is grabbing that data and getting it to display in my orders,invoice and packingslip pages.

 

Take a look at how I've done it so far, and maybe you can help me with getting it displayed.

[Velveeta]

Please please can someone help me on this one. All I want to do is to display a custom field in the products table on the account_history_info page. I have managed to do this for all the other pages but not this one. If the SQL query is too complex how do I program the button on the confirm order page, which displays the custom field, to insert the custom field into the orders_products table.

 

Many thanks

 

Ian

If you've already got this field as part of your $order class object, just find the section in checkout_process.php that makes the inserts into the orders_products table, and add your custom field there just like you did in the order class, something like this:

 

'orders_products_title' => $order->products[$i]['title'],

 

Then just set your order class's query function to pull that title field from the orders_products table as well, and use that in your account_history_info page...

 

Richard.

[ianric]

If you've already got this field as part of your $order class object, just find the section in checkout_process.php that makes the inserts into the orders_products table, and add your custom field there just like you did in the order class, something like this:

 

'orders_products_title' => $order->products[$i]['title'],

 

Then just set your order class's query function to pull that title field from the orders_products table as well, and use that in your account_history_info page...

 

Richard.

 

Thankyou, thankyou it now works :thumbsup: I would have never thought of checkout_success. I've been playing with the order class and account_history_info. In the checkout_process I modified the bestsellers query and added my custom field, then in the order class added the custom field to the SQL query and the array just below, then in account_history_info added the custom field to the output display.

 

I'm not a programmer but if someone gives me a place to start on most occasions I can sort it.

 

Ian