SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query exact results and the wildcard

    I need to run a query that pulls 100% accurate data. For this I need to use the "equal" rather than "LIKE". To throw a monkey wrench in the equation, not all fields are mandatory. When the user does not choose to search by a field, I simply want the query to treat it as ALL.

    Normally I would use the wildcard for this, but the wildcard only works on LIKE. If I use EQUAL, MySQL wants to match the wildcard which of course returns no records.

    EXAMPLE DETAILS: Variables are retrieved and sanitized from POST. For the sake of this example, I will keep it simple. Each variable collects a the POST if it exists, and if it does not exist it will set the default to "%" (the mySql wildcard).

    The variables are as follows:
    $city, $state, $gender

    The Query would be:
    SELECT * FROM users WHERE city = '$city' AND state = '$state' AND gender = '$gender'

    THE PROBLEM:
    If the user does not submit a city, the $city becomes a wildcard. That will return 0 results. If I use LIKE, and the user enters "Richmond" I will get every city that has Richmond in the name (Richmond Hills, Richmond Valley, Richmond Lake, etc....)

    The above is just to give an idea, it really is not the query involved. I just want to know if there is a way to use EQUAL, yet still retain wildcard usability when a value is not given.

    Hope that makes sense, and thanks in advance for your time.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    assuming you're using PHP:
    PHP Code:
    $query 'select * from users';
    $where = array()
    if (isset(
    $city)) {$where[] = "city = '$city'";}
    if (isset(
    $state)) {$where[] = "state = '$state'";}
    if (isset(
    $gender)) {$where[] = "gender = '$gender'";}
    if (
    count($where) > 0) {$query ' where ' implode(' AND '$where);} 
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it appears that you are using php

    therefore, i suggest that you use it

    when the user does not enter a value for city, then you should exclude that criterion from the WHERE clause --

    SELECT * FROM users WHERE state = '$state' AND gender = '$gender'

    see? all cities
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Winnipeg Canada
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to both of you for your suggestions. Makes perfect sense to me, and it's something I simply never thought of trying. I was merely attempting to do it all with SQL.

    Thanks again for pointing me in the right direction.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •