Help with mySQL!

[Guest]

I need to create a "products_weight" column in the "orders_products" table, and fill it with the appropriate weights drawn from the "products" table.

 

What would be the syntax for this?

[idabagusmade]

You can use phpmyadmin or other mysql editor to create fields. To fill it with the appropriate product weights, you have to write php codes.

[Wendy James]

Why not just grab the information from the table that already has the information in it?

[Guest]

I agree with Wendy...just use the existing data.

 

Bobby

[Guest]

I agree with Wendy...just use the existing data.

 

Bobby

<{POST_SNAPBACK}>

 

OK, let me back up and explain myself so you guys can tell me what I need to learn. :)

 

In my invoice, I need the products to be ordered by weight (don't ask). The code that grabs the product information for the order appears to be this, located in /admin/includes/classes/order.php:

 

      $orders_products_query = tep_db_query("select orders_products_id, products_name, products_model, products_price, products_tax, products_quantity, final_price from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$order_id . "'");

 

So, I was thinking that if this table ("orders_products") had a products_weight column, with the appropriate weights pulled from the "products" table, then I could just add "order by products_weight" to this query and that would do the trick.

 

Does that make sense, or is there a better way to do it? Either way, I'm looking for the actual syntax... :)

 

Thanks!

[FalseDawn]

$orders_products_query = tep_db_query('SELECT OP.orders_products_id, OP.products_name, OP.products_model, OP.products_price, OP.products_tax, OP.products_quantity, OP.final_price FROM' . TABLE_ORDERS_PRODUCTS . ' OP JOIN ' . TABLE_PRODUCTS . ' P ON OP.products_id=P.products_id WHERE orders_id = ' . (int)$order_id . ' ORDER BY P.products_weight');

 

Or Descending by weight (same with DESC on the end!)

 

$orders_products_query = tep_db_query('SELECT OP.orders_products_id, OP.products_name, OP.products_model, OP.products_price, OP.products_tax, OP.products_quantity, OP.final_price FROM' . TABLE_ORDERS_PRODUCTS . ' OP JOIN ' . TABLE_PRODUCTS . ' P ON OP.products_id=P.products_id WHERE orders_id = ' . (int)$order_id . ' ORDER BY P.products_weight DESC');

[Guest]

Just figured it out.. now I know what a "left join" does. B)

[FalseDawn]

You don't need a left join unless you have products ordered that are no longer in your products table. In this case, you wouldn't be able to retrieve the weight.

[Guest]

FalseDawn gets extra points for knowing the difference between the various JOINs :)

 

Bobby

[♥bruyndoncx]

Since you can have really long signatures these days, might as well start a webring LOL

"This guy/girl knows osC and MySQL", if you link to the osC user name, you're not doing anything wrong haha

[Guest]

...a long signature and 25 cents gets a cup of coffee :)