MySQL AND BETWEEN and LIKE query

Dev Shed Forums Sponsor:

December 19th, 2012, 05:57 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

MySQL AND BETWEEN and LIKE query

Hello there, Im fairly new to mysql and the query below is driving me nuts.

I have a database query form that has the following

Country - made up of tick boxes - it outputs to a string of country names depending how many you tick - $countries

Main Colour - same as above - $maincolours

Price range - $pricemin, $pricemax

Size - $minlength,maxlength

When I use

$strSQL = "SELECT * FROM rugs WHERE `price`BETWEEN $pricemin AND $pricemax AND `width` BETWEEN $widthmin AND $widthmax AND `length` BETWEEN $lengthmin AND $lengthmax"

It gives results as it should.

When I use

$strSQL = "SELECT * FROM rugs WHERE `price` BETWEEN $pricemin AND $pricemax AND `width` BETWEEN $widthmin AND $widthmax AND `length` BETWEEN $lengthmin AND $lengthmax AND `maincolour` LIKE '%$maincolours%'"

I want it to pick results based on exact matches from the PRICES, SIZES but narrow it by any colour that appears in the $maincolours string however as soon as I add that to the query I get no results.

Any advice would be greatly appreciated

December 19th, 2012, 06:01 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

Quote:

Originally Posted by mashamit

EDIT

The colour string is created by

$maincolours = "$black $brown $white $darkblue $denimblue $green $grey $oatmeal $paleblue $purple $red $rust $brickorange ";
$maincolours = substr($maincolours, 0, -1);

December 19th, 2012, 06:05 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 8 m 38 sec
Reputation Power: 1736

Do you get the correct result if you only use the LIKE condition:

Code:

SELECT * FROM rugs WHERE `maincolour` LIKE '%$maincolours%

Take notice that with so many conditions, it will be easier to read if you group each of them together using parentheses:

sql Code:

Original - sql Code
SELECT * FROM rugs WHERE (`price` BETWEEN $pricemin AND $pricemax) AND (`width` BETWEEN $widthmin AND $widthmax) AND (`length` BETWEEN $lengthmin AND $lengthmax) AND (`maincolour` LIKE '%$maincolours%')

 
SELECT * FROM rugs WHERE (`price` BETWEEN $pricemin AND $pricemax) AND (`width` BETWEEN $widthmin AND $widthmax) AND (`length` BETWEEN $lengthmin AND $lengthmax) AND (`maincolour` LIKE '%$maincolours%')

You have checked that there actually does exist entries with the search parameters you are using?

December 19th, 2012, 06:13 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

Quote:

Originally Posted by MrFujin

Do you get the correct result if you only use the LIKE condition:

Code:

SELECT * FROM rugs WHERE `maincolour` LIKE '%$maincolours%

Take notice that with so many conditions, it will be easier to read if you group each of them together using parentheses:

sql Code:

Original - sql Code
SELECT * FROM rugs WHERE (`price` BETWEEN $pricemin AND $pricemax) AND (`width` BETWEEN $widthmin AND $widthmax) AND (`length` BETWEEN $lengthmin AND $lengthmax) AND (`maincolour` LIKE '%$maincolours%')

 
SELECT * FROM rugs WHERE (`price` BETWEEN $pricemin AND $pricemax) AND (`width` BETWEEN $widthmin AND $widthmax) AND (`length` BETWEEN $lengthmin AND $lengthmax) AND (`maincolour` LIKE '%$maincolours%')

You have checked that there actually does exist entries with the search parameters you are using?

Thanks for the quick reply

No it is not showing when used on its own what should I check to see why that hasnt worked.

I have an entry with White as a colour, and Ive echo'd the string out to make sure it was passing White to the query

December 19th, 2012, 06:15 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 8 m 38 sec
Reputation Power: 1736

Quote:

Originally Posted by mashamit

I will be very surprised if you got that working 100%.

"Black White" is not the same as "White Black".
What if the object has the value "Black Brown White", then it will not find it if you only search for "Black White".

You will have to search for each colour separately in you query, e.g.

Code:

SELECT * FROM rugs WHERE `maincolour` LIKE '%$Black%' OR `maincolour` LIKE '%$White%'

Better yet would be to re-design the table, so the field only contains one colour.
Do you have the possibility to add the colours to a new separate table?

December 19th, 2012, 06:19 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

I have endless possibilities... just lacking the abilities

What is it your suggesting? Having a column for each colour with a yes/no 0/1 option to be searched?

Or is there a better way

December 19th, 2012, 06:42 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 8 m 38 sec
Reputation Power: 1736

No, you will have a table with all the colors:

Code:

table name: colors
id	colorname
0	red
1	blue
2	black
3	etc...

you have an table called rugs, assuming it has an id, it could looks like this:

Code:

table name: rugs
id	unkown_field	price	width	length
1	my rug		10	11	12	
2	second rug	20	21	22

Then a table which will "enable" which colours are available for each "rugs".

Code:

table name: rugs_colors
rug_id	color_id
1	0
1	2
2	0
2	1
2	3

Alternative, and maybe more simple, will be to have the colour added to the rugs_color table and omit the whole color table.
But it depend how strict you are about having duplicated data in same table.

for the select, you will have to look into JOIN syntax (official documentation)

December 20th, 2012, 05:15 AM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

Quote:

Originally Posted by MrFujin

No, you will have a table with all the colors:

Code:

table name: colors
id	colorname
0	red
1	blue
2	black
3	etc...

you have an table called rugs, assuming it has an id, it could looks like this:

Code:

table name: rugs
id	unkown_field	price	width	length
1	my rug		10	11	12	
2	second rug	20	21	22

Then a table which will "enable" which colours are available for each "rugs".

Code:

table name: rugs_colors
rug_id	color_id
1	0
1	2
2	0
2	1
2	3

Well as im learning, learning the right way seems the best option. Ill have a look through the documentation. Many thanks for the help so far.

December 20th, 2012, 07:12 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Code:

SELECT * FROM users;
+---------+---------+---------------------+
| user_id | name    | dtime               |
+---------+---------+---------------------+
|       1 | Adam    | 2011-05-11 10:11:13 |
|       2 | Ben     | 2011-05-11 10:11:13 |
|       3 | Charles | 2011-05-11 10:11:13 |
+---------+---------+---------------------+

SELECT * FROM users WHERE name IN('adam','ben');
+---------+------+---------------------+
| user_id | name | dtime               |
+---------+------+---------------------+
|       1 | Adam | 2011-05-11 10:11:13 |
|       2 | Ben  | 2011-05-11 10:11:13 |
+---------+------+---------------------+

SELECT * FROM users WHERE FIND_IN_SET(name,'adam,ben') > 0;
+---------+------+---------------------+
| user_id | name | dtime               |
+---------+------+---------------------+
|       1 | Adam | 2011-05-11 10:11:13 |
|       2 | Ben  | 2011-05-11 10:11:13 |
+---------+------+---------------------+

#10

December 20th, 2012, 08:02 AM

ManiacDan

Likely to be eaten by a grue.

Dev Shed God 10th Plane (9500 - 9999 posts)

Join Date: Oct 2006

Location: Pennsylvania, USA

Posts: 9,791

Folding Points: 127430 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 2 Months 3 Weeks 14 h 53 m 20 sec
Reputation Power: 6112

There are 2 correct ways to do this, neither of which I think have been presented in here.

Correct solution #1: Attribute-value table.

Make another table called rug_color and store rug_id and color. Then your query is:
SELECT rugs.* FROM rugs JOIN rug_color ON rugs.id = rug_color.rug_id WHERE rug_color.color = 'green' OR rug_color.color = 'black';

The most correct way to do this is the make TWO more tables:
RUG_ATTRIBUTE
attribute_id
attribute_name

RUG_ATTRIBUTE_VALUE
rug_id
attribute_id
attribute_name

That way, if you happen to add another attribute which can have multiple values per rug (like a misshapen rug, or a rug made of two kinds of material) you can add another attribute and your query will work just fine.

Correct solution #2: MATCH ... AGAINST

Given a text field properly indexed with a FULLTEXT index (like your COLOR column), you can use the MATCH ... AGAINST syntax to do a binary search with a syntax used by old-school search engines back in the day.

SELECT * FROM rugs WHERE MATCH (color) AGAINST ('black or green');

Comments on this post

mashamit agrees!

__________________
HEY! YOU! Read the New User Guide and Forum Rules

"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

"The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

#11

December 22nd, 2012, 05:49 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

Quote:

Originally Posted by ManiacDan

Im going for the first of your solutions and its working great with the Colour, Im not sure I understand your 3 table version, how do all three communicate together.

The Rug Attribute table - is this a table of all possible attributes I may wish to assign to a rug?

The Rug Attribute Value - That connects the rugid to each attribute, in the same way you were adding multiple colours to a rugid? - What is the reason for having attribute name on the value table?

If I have colour and country would the query be

SELECT rugs.* FROM rugs JOIN rug_color ON rugs.id = rug_color.rug_id WHERE rug_color.color = 'green' OR rug_color.color = 'black' AND JOIN rug_country ON rugs.id = rug_country.rug_id WHERE rug_country.country = 'persia' OR rug_country.country = 'nepal' ;

will that return any rug that has any of those attributes so

persia or nepal or green or black

or will it be

persia and green or black
nepal and green or black
green and persia or nepal
black and nepal or persia

if that makes sense.

Also if I use $green string to get the information from the form, but it is empty, will that affect any results that are generated?

#12

December 22nd, 2012, 06:12 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 8 m 38 sec
Reputation Power: 1736

You can only have one WHERE in your statement.
General statement to JOIN two tables will be something like this:

Code:

SELECT *
FROM Table 1 AS t1
INNER JOIN table2 AS t2 ON t2.fieldname = t1.fieldname
INNER JOIN table3 as t3 ON t3.fieldname = t1.fieldname
WHERE <standard_condition>

INNER JOIN return values where there is match on the ON condition'
LEFT JOIN will get all values from table1 and those from table2 that match the ON condition (similar to a OR statement)

You will have to be more precise with what kind of result you want; examples will be great for this.

#13

December 22nd, 2012, 06:30 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

Quote:

Originally Posted by MrFujin

You can only have one WHERE in your statement.
General statement to JOIN two tables will be something like this:

Code:

SELECT *
FROM Table 1 AS t1
INNER JOIN table2 AS t2 ON t2.fieldname = t1.fieldname
INNER JOIN table3 as t3 ON t3.fieldname = t1.fieldname
WHERE <standard_condition>

rugs

------------------------------------
rugid price length width
------------------------------------
1 550 25 12
2 1000 20 13
3 800 15 14
4 400 18 11
------------------------------------

rug_colour

----------------
rugid colour
----------------
1 White
2 Blue
3 White
4 Brown
----------------

rug_country

--------------------
rugid country
--------------------
1 Persia
2 Persia
3 Nepal
4 Afghanistan
--------------------

Ok so from the above example data, Id want to get results based on any combination on colour and country or colour or country independently.

So

Persia & White would return - 1,2,3
Persia & Blue would return - 1,2
Brown & Blue would return - 2,4
white & Afghanistan would return - 1,3,4
White would return - 1,3

each item would be sent via string

$white, $persia etc etc
If the form does not send a value, the string wont exist - will this cause an issue?

#14

December 22nd, 2012, 07:05 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 22 h 8 m 38 sec
Reputation Power: 1736

Quote:

Originally Posted by mashamit

Persia & White would return - 1,2,3
Persia & Blue would return - 1,2
Brown & Blue would return - 2,4
white & Afghanistan would return - 1,3,4
White would return - 1,3

You have to be careful if you talk about AND or OR.
I first read "white & Afghanistan" as you want white rugs coming from Afghanistan. But what you want as result says it should be "white OR Afghanistan".
example with OR:

Code:

SELECT *
FROM rugs AS r
LEFT JOIN  rug_colour AS rcol ON rcol.rugid  = r.rugid 
LEFT JOIN  rug_country as rcoun ON rcoun.rugid  = r.rugid 
WHERE rcol.colour = "White" OR rcoun.country = "Afghanistan"

Work with the above statment and try to adjust it to fit it to the other result examples you posted.

Quote:

Originally Posted by mashamit

each item would be sent via string

$white, $persia etc etc
If the form does not send a value, the string wont exist - will this cause an issue?

Dynamic building of the string is not part of sql, that is part of the application/language you use to connect to the database.

When you have the database/tables in place and is starting to be confident on how it works, i suggest you create a new post with (the code of) what you have tried.
Remember to post in the forum relevant for the language you are using.

Comments on this post

mashamit agrees!

#15

December 23rd, 2012, 03:02 PM

mashamit

Registered User

Join Date: Dec 2012

Posts: 13

Time spent in forums: 3 h 20 m 42 sec
Reputation Power: 0

Thank you guys, particularly MrFujin and ManiacDan, repped you both up all the advice has been of great use and i shall go away and throw it all together and see what it spits out at the other end

MySQL AND BETWEEN and LIKE query

Developer Shed Advertisers and Affiliates