MYSQL command

[sLaV-]

Hi,

 

So havn't worked with MYSQL for at least 5 years now forgot all about it....

Can someone help me with the following:

 

I have a drop down box and want to input all the products in that dropdown box with items in a particular category, so for instance I have a Intel CPU drop down box that will display all the products that are in our database in the category CPU->Intel CPU .... The things I need to extract from the database are the product ID, product image, product name and product price.

 

 

 

Cheers!

[♥geoffreywalton]

Guess this link will help refresh your memory.

 

http://www.w3schools.com/sql/sql_select.asp

[sLaV-]

Guess this link will help refresh your memory.

 

http://www.w3schools.com/sql/sql_select.asp

 

Just having a hard time working out how the whole database works with osCommerce...just need the SQL command to get the product ID, product name, product price and product image from a particular categorie

 

 

Cheers

[dmnalven]

You don't run SQL commands directly using PHP. You set up a query result in SQL then use that resource in another database manipulation function, of which there are several.

 

Please review the osC code (beginning with index.php) to gain a knowledge of how PHP and osC in particular handle database calls.

 

In the 'extras' directory of the osC download there is a database relationship table.

[sLaV-]

You don't run SQL commands directly using PHP. You set up a query result in SQL then use that resource in another database manipulation function, of which there are several.

 

Please review the osC code (beginning with index.php) to gain a knowledge of how PHP and osC in particular handle database calls.

 

In the 'extras' directory of the osC download there is a database relationship table.

 

Yep that's fine I can work with the results once I get them...I'm just after the actual SQL command to run to get the results first...

[sLaV-]

Yep that's fine I can work with the results once I get them...I'm just after the actual SQL command to run to get the results first...

 

Or can anyone help me with the PHP code of getting all the products from a particular categorie and populating a drop down box with the results??

 

 

Cheers.

[dmnalven]

And what is the problem that you are having with the well thought out, tested and reusable select statements in index.php?

[dmnalven]

Ahhh... Now we're getting somewhere.

 

At the end of includes/functions/html_output.php you will find the function tep_draw_pull_down_menu . If you populate an array with your choices and pass it to this function with your additional arguments you will get your menu. You may want to copy, rename and edit the function as I ended up modifying it once for another use because I never could figure out what the 'id' key was used for (possibly a countries list that I did want to disturb).

[sLaV-]

Ok i've narrowed it down to the following MYSQL call:

 

select p.products_id, pd.products_name, p.products_price, p.products_image from products p, products_description pd, products_to_categories p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and p2c.categories_id = '48'

 

This works and displays the correct details for products in subcategory 48 ... If I have more than on category of products I wish to display ... how do I go about this?? I tried changing the 48 to the category number of the parent (as 48 and 49 are the subcategories and they are the ones I want to display) ... but when I input the parent category number at the end I get a blank result :(

 

So for example my parent category is 47, my sub categories are 48 and 49...I want to display all the products in the category 47 (so all products in both sub categories 48 and 49) ...

 

Anyone??

 

 

Cheers

[dmnalven]

Simply change: and p2c.categories_id = '48'

to: and (p2c.categories_id = '48' or p2c.categories_id = '49')

[sLaV-]

Simply change: and p2c.categories_id = '48'

to: and (p2c.categories_id = '48' or p2c.categories_id = '49')

 

LEGEND thanks mate :)