Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How do I create new SQL queries for new box?


kdrew098

Recommended Posts

Posted

Hi,

 

I'm new to OSCommerce (and a bit rusty at programming). This site was started by another tech and left to me to finish. I figured out how to get the home page working, however, I'm requested to have one of the boxes (wha't new) show one line of products (shoes) while another box show apparel. IFrom reading the code available on what_new.php, I see that it's randomly searching products based on a product_ID. I'd like to have them search by categorie_id. Of course, I tried just changing the parameter to categorie_id, but it's apparently looking only at a products table. If I just had a better handle on the database, I think I could create a join between these two tables. Is there a better way to do this or can anyone advise me how to go about manipulating MySQL (I've done SQL on several databases several years ago, but am new to MySQL).

 

Any help would be appreciated!

 

Kevin Drew

Posted

select ... <see original> from products p, products_to_categories p2c where p.products_id = p2c.products_id and categories_id = X order by  ... <see original>

Just replace ... <see original> with the statements in the original query to get the desired result.

 

MySQL is pretty standard sql. (sql-92)

 

HTH

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted

Thank you!

 

I'm going to have to take time to learn some of the particurlars, like the way you did the join. I probably did not adapt your instructions properly. This is what it said before (whats_new.php):

 

if ($random_product = tep_random_select("select products_id, products_image, products_tax_class_id, products_price from " . TABLE_PRODUCTS . " where products_status = '1' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)) {

 

This is how I adapted your code to it (there were a few earlier unsuccessful tries as well):

 

if ($random_product = tep_random_select("select products_id, products_image, products_tax_class_id, products_price from products p, products_to_categories p2c where p.products_id = p2c.products_id and categories_id = '33' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)) {

 

This was the error received:

1052 - Column: 'products_id' in field list is ambiguous

 

products_id wasn't ambiguous before this statement, so there's something else wrong here.

 

KD

Any thoughts?

Posted

if ($random_product = tep_random_select("select p.products_id, products_image, products_tax_class_id, products_price from products p, products_to_categories p2c where p.products_id = p2c.products_id and categories_id = '33' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)) {

it doesn't know which products_id to take, as it is in products and products_to_categories table. We know they are the same but mysql doesn't ...

Added p. in the selection

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted

Thank you Carine!

 

That totally worked! Now I just have to figure out what categories to search. I'll look at your store and give you my humble feedback by tonight.

 

Thanks again!

 

Kevin Drew

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...