SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Still consider even if?!?!

    I'm trying to make a searchscript where I can search for a record depending on country and state.

    I have 3 records in my table like this:
    id title con sta
    1 test1 213
    2 test2 213 455
    3 test3 213 389

    Now the issue is that when I only use the country parameter in the search it shows all records in result as it should, but when I then take the state parameter, lets say 455, it only shows the record with the sta 455. It should also show the record without the sta.

    Hope this makes some sense?!?

    Here is how I do now:
    PHP Code:
    if(!$state){
        
    $data="SELECT * FROM ".$prefix."_roundrequest WHERE con=$country";
    } else {
        
    $data="SELECT * FROM ".$prefix."_roundrequest WHERE con=$country AND sta=$state";

    I have an idea that I have to change the "AND" in the second query but dont know to what???

  2. #2
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,888
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    Try this:
    PHP Code:

    $data
    =
    "
       SELECT * 
       FROM  
            "
    .$prefix."_roundrequest 
       WHERE 
           con=
    $country 
       OR WHERE 
           (con=
    $country AND sta=$state); 

    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    Try this:
    PHP Code:

    $data
    =
    "
       SELECT * 
       FROM  
            "
    .$prefix."_roundrequest 
       WHERE 
           con=
    $country 
       OR WHERE 
           (con=
    $country AND sta=$state); 

    When trying to do so I get this error:
    Supplied argument is not a valid MySQL result resource

    Any other ideas?

  4. #4
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,888
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    Try this:
    PHP Code:

     $data
    ="SELECT * FROM "  .$prefix ."_roundrequest WHERE con=$country 
              OR WHERE   con=
    $country AND sta=$state "
    >>> It should also show the record without the sta.
    I am confused. Why bother searching for the "sta" if you only want the all the results from the country?
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by John_Betong View Post
    >>> It should also show the record without the sta.
    I am confused. Why bother searching for the "sta" if you only want the all the results from the country?
    What I am trying to acomplish is that user has made an request to the board in which he is able to target into a certain direction. Either to a country or to a certain state in a country.

    Now, when another user is searching for a request he can use 2 parameters. Country and state.

    Now. in the first record the user has only targeting the country, but that has not excluded the record to be showned if the searching user also uses the state parameter in his search. So no matter what state his search, the records with only the correct country and no state should also be showned...

    Hope this makes some sense :-|

    By the way. The above code didnt work :-|

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT * 
    FROM ".$prefix."_roundrequest 
    WHERE con=$country 
    AND sta IN ('', $state)

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code MySQL:
    SELECT * 
    FROM ".$prefix."_roundrequest 
    WHERE con=$country 
    AND sta IN ('', $state)
    V.Nice.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code MySQL:
    SELECT * 
    FROM ".$prefix."_roundrequest 
    WHERE con=$country 
    AND sta IN ('', $state)
    But one problem though... If the search for state doesnt have any record it shows no records at all... It should still show the country record!

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    It does. That is, if the value of the state column in that case is ''. If it's NULL, try
    Code MySQL:
    SELECT *
    FROM ".$prefix."_roundrequest
    WHERE con=$country
    AND sta IN (NULL, $state)
    If it's zero, try
    Code MySQL:
    SELECT *
    FROM ".$prefix."_roundrequest
    WHERE con=$country
    AND sta IN (0, $state)
    The point is, the IN statement selects all rows that have the values listed in the sta column. So putting the user inputted value, AND the value the column has in the 'country' row, it will always return the 'country' row.

  10. #10
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    It does. That is, if the value of the state column in that case is ''. If it's NULL, try
    If it's zero, try
    Code MySQL:
    SELECT *
    FROM ".$prefix."_roundrequest
    WHERE con=$country
    AND sta IN (0, $state)
    The record is 0 but still it shows no records?

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Do an echo of $data and see what the actual query looks like

  12. #12
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Do an echo of $data and see what the actual query looks like
    Sorry, my bad... Found the error... Works like a charm. Thanks alot Guido :-)


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
  •