Help displaying products by name instead of category #

[sackling]

I have my store setup right now by use of various custom made php pages. within the page there is this code:

 

<?php

 $products_new_array = array();

   $products_new_query_raw = "select p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status from products p, products_description pd, products_to_categories p2c where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id  and p2c.categories_id in(63) order by pd.products_name";
//echo $products_new_query_raw;
 //echo $products_new_query_raw;
 $products_new_split = new splitPageResults($products_new_query_raw, 30);

 if (($products_new_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>

 

p2c.categories_id in(63)decides which products are shown based on their category ID number. I would like to change this on some pages however to show based on the product name. I need products that have Gold + Earrings to be displayed for instance.

 

I think this would be the best way for me to have this setup as making a new category with the gold earrings would entail doubles of a lot of products in different categories. And since the naming scheme is already in place I think this would work out well.

 

So What I'm looking for is the command that I would need to change p2c.categories_id in(63) to

 

is there a p2c.products_name(gold+earrings) or some such?

 

Just to be clear I am not talking about sorting by the name with those keywords. I want to only display products with those keywords. The sorting I have read about and am able to change easily.

 

Any help is appreciated. Thank you.

[acbatchelor]

replace:

p2c.categories_id in(63)

 

with:

pd.products_name LIKE('%gold%') and pd.products_name LIKE('%earrings%')

 

the "LIKE" operator in SQL means to find the word in parenthesis for the products_name.

 

the percent signs are wildcards. that means that the full name does not have to be exactly match the word "gold", it just has to contain the word "gold". (like "24K Gold Earrings")

 

without the percent signs, a product named "24K Gold Earrings" would not display.

 

 

repeat the statement for earrings and then it will only contain the results with gold and earrings in it.

 

note: i am not an expert, but this should work. i tested it on my test site to make sure i knew what i was doing.

[sackling]

replace:

p2c.categories_id in(63)

 

with:

pd.products_name LIKE('%gold%') and pd.products_name LIKE('%earrings%')

 

the "LIKE" operator in SQL means to find the word in parenthesis for the products_name.

 

the percent signs are wildcards. that means that the full name does not have to be exactly match the word "gold", it just has to contain the word "gold". (like "24K Gold Earrings")

 

without the percent signs, a product named "24K Gold Earrings" would not display.

 

 

repeat the statement for earrings and then it will only contain the results with gold and earrings in it.

 

note: i am not an expert, but this should work. i tested it on my test site to make sure i knew what i was doing.

 

Wow works perfectly. Thanks so much!

[sackling]

Sorry just one more question regarding this.. Is there any way to include negative keywords as well? for so for instance include the words would be "earrings" and "-silver" for everything with earrings that didnt have silver to be included?