SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    (****** or Deleted)
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem in SELECT statement...

    OK, I know the problem, but just not how to fix it LOL

    Here is my statement:

    PHP Code:
    SELECT maps.idmaps.map_namemaps.member_idmaps.type_idmaps.race_idmaps.descriptionmaps.votesmaps.vote_countmaps.downloadsmaps.map_pathmaps.minimap_pathmaps.timestampmember.usernamemember.emailtype.typerace.race FROM maps LEFT JOIN member ON maps.member_id=member.member_id LEFT JOIN type ON maps.type_id=type.type_id LEFT JOIN race ON maps.race_id=race.race_id WHERE maps.map_name LIKE '%$searchstring%' AND maps.type_id $searchtype
     ORDER BY $searchsort $searchorder 
    Now, the problem is the WHERE statement, and here is why:

    $searchstring will only ever contain the reults of a input text box, so will always return a string...

    $searchtype can be either "0", "1" or "0 OR 1"

    The "OR" is causing the problem as it the WHERE staement is picking it up as a logical operator, instead of being part of the results...

    I need to say:

    If $searchtype = 0 get all rows with a 0
    If $searchtype = 1 get all rows with a 1
    If $searchtype = 0 OR 1 get all rows with a 0 or a 1, is there some way I can wrap this in parenthesis or quotation marks or something, so that SQL sees the OR as part of the variable, or is their some other way to write the script?

    If this is cnofusing, just let me know LOL

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try modifing $searchtype to contain "0", "1", or "0, 1" and then modify your query to do "AND maps.type_id IN ($searchtype)"

  3. #3
    (****** or Deleted)
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, that worked perfectly

    Would you mind explaining to me the difference between maps.type_id = $searchtype and maps.type_id IN ($searchtype)?

    What is the IN function used for, and why did I then put the variable in parenthisis?

    Thanks for the help

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by Wizardx8
    Thank you, that worked perfectly

    Would you mind explaining to me the difference between maps.type_id = $searchtype and maps.type_id IN ($searchtype)?

    What is the IN function used for, and why did I then put the variable in parenthisis?

    Thanks for the help
    The IN statement replaces this kind of statement:

    Code:
    WHERE maps.type_id = "0" or maps.type_id = "1" or maps.type_id = "2"
    It's also real helpful when you can search for a dynamic number of items (ie 0 OR 1 OR 0,1). It works just as well with maps.type_id in (0) as maps.type_id in (0,1)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


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
  •