Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep 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
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by mashamit
    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

    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);
  4. #3
  5. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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:
     
    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?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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:
     
    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
  8. #5
  9. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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);
    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?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep 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
  12. #7
  13. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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)
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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
    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)
    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.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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 |
    +---------+------+---------------------+
  18. #10
  19. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    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.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by ManiacDan
    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');
    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?
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    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>
    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.
    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?
  26. #14
  27. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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.

    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
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep 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
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo