Help With Database Query

[Leeb2]

I would like to change this query to sort the products by the product name in alphabetical order. I think it will need to get the product names from the other table in the database, and have the end part changed to sort by name.

 

Here is the original query:

 

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

The original code then gets the product name like this:

 

  while ($new_products = tep_db_fetch_array($new_products_query)) {
$new_products['products_name'] = tep_get_products_name($new_products['products_id']);

 

But I think I would need the get the name in the first query to be able to sort it there.

[Guest]

I would like to change this query to sort the products by the product name in alphabetical order. I think it will need to get the product names from the other table in the database, and have the end part changed to sort by name.

 

Here is the original query:

 

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

The original code then gets the product name like this:

 

  while ($new_products = tep_db_fetch_array($new_products_query)) {
$new_products['products_name'] = tep_get_products_name($new_products['products_id']);

 

But I think I would need the get the name in the first query to be able to sort it there.

 

 

I would just adjust the SQL statement from this:

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

to this:

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name as products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on pd.products_id=p.products_id where products_status = '1' and pd.language_id=1 order by products_name" . MAX_DISPLAY_NEW_PRODUCTS);

 

That's just what I would do though.

[Leeb2]

I would just adjust the SQL statement from this:

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

to this:

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name as products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on pd.products_id=p.products_id where products_status = '1' and pd.language_id=1 order by products_name" . MAX_DISPLAY_NEW_PRODUCTS);

 

That's just what I would do though.

 

BeauCowan,

 

That gives this error:

 

1054 - Unknown column 'products_name9' in 'order clause'

 

select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name as products_name from products p left join specials s on p.products_id = s.products_id left join products_description pd on pd.products_id=p.products_id where products_status = '1' and pd.language_id=1 order by products_name9

 

[TEP STOP]

[user99999999]

You need the limit there

 

products_name" . MAX_DISPLAY_NEW_PRODUCTS);

 

products_name limit " . MAX_DISPLAY_NEW_PRODUCTS);

[Leeb2]

You need the limit there

 

products_name" . MAX_DISPLAY_NEW_PRODUCTS);

 

products_name limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

Thanks user9999999 and BeauCowan. It works now.