Separate gold and rose gold products

PHP
1

hi all

i have product names like

gold spoon 1
gold spoon 2
rose gold spoon 1
rose gold spoon 2
rose gold spoon 3

I dont have color field inserted in database.

i want show “gold” and “rose gold” color products separately

if i write a query

$colors = array("gold","rose gold");
foreach($colors as $colr)
{
    $q = "select product_name from products_table where product_name LIKE '%".$colr."%'"
}

then all results of “rose gold” are displayed under “gold” color results also.

There are 6 gold color products and 10 rose gold color products

First all 16 products are shown under “gold” color

then again 10 products are shown under “rose gold” color products

But i want to show 6 gold color products separately and 10 rose gold color separately.

how to separate them ??

vineet

2

LIKE '“.$colr.”%"

without the first % that allows 'rose ’ in front of ‘gold’

3

hi felgall

it doesnt echo any result.

If i remove first “%” does it become case sensitive ??

vineet

4

I tried the query directly in phpmyadmin and it didnt return any rows

so case sensitivity is not the problem

problem is something else ?/

vineet

5

Are you sure product_name starts with ‘gold’ and ‘rose gold’ ?

6

not all start with color name.

some have color name in middle and some in end

vineet

7

in that case there is no way to separate out rose gold from gold as when searching for gold it is in the middle when rose is on the front.

8

can regexp be of any help or not ??

vineet

9

not if the values are in an array - the only way to distinguish the two is by including both in the database call so as to say ‘gold’ and not ‘rose gold’ which means hard coding them in the query.

You can’t have array entries that are fully contained in other array entries to use with like as the shorter one will always include all the longer ones that contain it.

10

As long as you have CREATE TABLE privileges I think you should take advantage of the Relational possibility.

Maybe having something like a product_id field
and a color field.

With proper design and indexing you could put more of the work on the database and lower the amount of work you place on the client.

1 Like
12

Without normalizing that structure filtering, and sorting against those product attributes is going to be a complex, inefficient process that does not scale well.

13

Here is a really easy way to do it.

attribute_sets

  • id
  • name
  • pk(id)
  • uk(name)

attribute_set_values

  • id
  • attribute_set_id
  • attribute_value
  • pk(id)
  • fk(attribute_set_id) => attribute_sets(id)
  • uk(attribute_set_id,attribute_value)

product_attributes

  • product_id
  • attribute_set_value_id
  • pk(product_id,attribute_set_value_id)
  • fk(product_id) => products(id)
  • fk(attribute_set_value_id) => attribute_set_values(id)

Than all you need to do is write a script to parse the existing products attributes into those tables,

You do that now you avoid a whole lot of issues and limitations in the future. One of which is your current problem turns into a simple join query rather than a convoluted string manipulation mess.

2 Likes
14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.