Problem with LIKE selection in query 2

[electricphp]

I have this query:

"SELECT * FROM ads WHERE cats LIKE'%$find%'"

lets say that $find is "Wines and Cheeses"

for some reason, it is selecting records that only have "wines" in them, so it will select "wines and beers"

How can I change this query so it only selects records that have the full string "Wines and Cheeses" in them

 

[r937]

there is something you're not telling us, because what you say it's doing is impossible

please test your query outside of php (with hardcoded values for $find), and you will see what i mean





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[electricphp]

what are you talking about,

ok here is the hardcoded query, my question still remains:

SELECT * FROM ads WHERE cats LIKE'%Wines and Cheeses%'

 

[whosrdaddy]

what Rudy wants to make clear is that your query is incorrect.

this will work:

SELECT * FROM ads 
WHERE cats LIKE'%Wines%' AND cats LIKE '%Cheeses%'

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

sorry misread your question.

you really want "wines and cheeses" in one string.

but with your query it is impossible to get 'wines and beers' as a result, that is what rudy was referring to...
so a bug in your php logic?

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[vacunita]

What he means is that a query like the one you are showing, can never produce the output you describe. The LIKE clause searches for the complete string. In your case 'Wines and Cheeses'. In your example the string may be preceded by something else and followed by something else.

So if there was a value such as:
'I like Wines and Cheeses very much'

It would be returned by your query as well.

The only way the query would return 'Wines and beers' along side 'Wines and Cheeses' is if your search string was either '%Wines%' or '%Wines and %' but not '%Wines and Cheeses'%'

I suggest you echo out the query prior to PHP (maybe PERL) running it to make sure it looks like you think it looks at that point.






----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[electricphp]

Maybe the fact that the values in the database are in serialized format has something to do with this, for example:

field cats in table ads contains this:

a:3:{i:0;s:13:"Wines and Cheeses";i:1;s:15:"Wines and Cookies";i:2;s:9:"Chocolate";}

what I meant to say before was, when I run this query:

SELECT * FROM ads WHERE cats LIKE'%Wines%'

the above record is returned

I only want the above record to be returned if the query is:

SELECT * FROM ads WHERE cats LIKE'%Wines and Cheeses%'

Hope this helps

How can I reformat my query to work properly. This is what I'm using now:

"SELECT * FROM ads WHERE cats LIKE'%$find%'"

 

[r937]

Maybe the fact that the values in the database are in serialized format has something to do with this

it sure does


any chance you can change that? it's really a very poor design

for one thing, it requires a table scan, so it will not scale (i.e. it will get slower and slower the more rows you have)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vacunita]

Ahh see, you neglected to tell us the information was serialized. and that you weren't searching for "Wines and Cheeses" but rather just "Wines".


If you want to prevent 'Wines' from returning 'Wines and Cheeses' or 'Wines and beers', then you need to include the surrounding quotes so that you limit the search. Of course you'll need to escape them.

"SELECT * FROM ads WHERE cats LIKE'%[red]\"[/red]$find[red]\"[/red]%'"

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

If you do not know for sure what query is sent to the server (or are just curious), you can enable the query log in MySQL. I would suggest that for any development machine. Then, use a log viewer like BareTail (Windows) or Multitail (Linux) to see the queries scroll past. Both log viewers have colouring-by-content, so you can easily make SELECTs, UPDATEs and INSERTs stand out if you want. Note that this does not solve your question in itself, but it can give you a good insight into what is going on.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[electricphp]

I think that did it, thanks guys. Boy, I gotta write a script that will update the db to comma separated from serialized.

If the values were comma separated, how would my query have to change to select correctly.

 

[tsuji]

I think what the op's meant to get done is this effective sql.
[1] returning anything containing wines but not "wines and cheeses" combination:
[tt][blue] SELECT * FROM ads WHERE cats REGEXP '^.*Wines.*$' AND cats NOT REGEXP '^.*?Wines and Cheeses.*$';[/blue][/tt]
which effective meant to return anything like
'...wines...'
or
'...wines and cookies...'
etc etc ...
but excluding any column containing particle like
'...wines and cheeses...'

[2] And then to return Wines and Cheeses, the sql would be simply:
[tt][blue] SELECT * FROM ads WHERE cats REGEXP '^.*?Wines and Cheeses.*$'[/blue][/tt]

 

[r937]

comma-separated and serialized both suffer from the problem of not being in first normal form

comma-separated is also a very poor design

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[electricphp]

so you're saying it's better to have each entry in its own record.

If I do that then it makes individual queries more complex and difficult to debug, with unnecessary joins.

If you have a limited number of categories, then I don't see why its bad to just parse through the serialized values.

 

[r937]

more complex? actually, it's a lot simpler

difficult to debug? no, it's a lot easier

unnecessary joins? that's a myth

it's not the limited number of categories you need to worry about, but rather the number of rows in the ads table

but hey, don't take our word for it, that normalization crap is just for purists, and you should be just fine with the design that you have


... for a while ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[electricphp]

alright, I'm sure you know what you're talking about, so what do you suggest? Do I create a table for individual category entries, that holds the ad id and one category per line?

If that is the case how would my query change?

 

[r937]

here's how i would set it up --

CREATE TABLE ads
( id  INTEGER NOT NULL PRIMARY KEY
, othercolumns ...
);

CREATE TABLE categories
( cat_name VARCHAR(99) NOT NULL PRIMARY KEY
);

INSERT INTO categories VALUES
 ( 'Wines' )
,( 'Wines and Cheeses' )
,( 'Chocolate' )
, ... 

CREATE TABLE ad_categories
( ad_id INTEGER NOT NULL 
, FOREIGN KEY (ad_id) REFERENCES ads (id)
, cat_name VARCHAR(99) NOT NULL
, FOREIGN KEY (cat_name) REFERENCES categories (cat_name)
, PRIMARY KEY ( ad_id, cat_name )
);

INSERT INTO ad_categories VALUES
 ( 1, 'Wines' )
,( 1, 'Wines and Cheeses' )
,( 1, 'Chocolate' )
,( 2, 'Wines' )
, ...

the two foreign keys should be self-explanatory

the composite primary ensures that you don't add the same category to the same ad more than once

then to find ads that belong to the 'Wines and Cheeses' category, the query would look like this --

SELECT ads.*
  FROM ad_categories
INNER
  JOIN ads
    ON ads.id = ad_categories.ad_id
 WHERE ad_categories.cat_name = 'Wine and Cheeses'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[electricphp]

ok, thanks, i'll try to implement this later today. I'll update here after I test.

 

[electricphp]

what is the point of creating this table?

CREATE TABLE categories
( cat_name VARCHAR(99) NOT NULL PRIMARY KEY
);

if the category names are contained in the table ad_categories, which contains an id field that can be referenced.

 

[r937]

the point is to provide relational integrity

there are typically two ways to implement categories -- when you control the categories that may be applied to an object, and when you want to allow any category to be applied (see "folksonomy" for this type)

i assume you want to control which categories can be applied, and so the ads_categories table has a foreign key to the categories table, thus prohibiting the use of any category that isn't (pre)registered in the categories table

make sense?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon