Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: searching

  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts

    searching

    I have this search and it is working, but now I need to add one more thing to it and I just can't get it to work.

    I need to add code to prevent a user showing if they are in the 'blockUser' table

    I thought I could add that to the bottom of the $wheres but it doesn't work. Tried some different things as well.

    this is the code I was trying to work with::
    PHP Code:
    if (!isset($blockID)) {
      
    $where .= " AND blockUser.blockID = user.userID";} 

    my search code::
    PHP Code:
        $z = new zipcode_class;
        
    $zips $z->get_zips_in_range($zip_code$miles_ZIPS_SORT_BY_DISTANCE_ASCtrue);
        
    $zips_in_range implode(','array_keys($zips) );
        
        
    $where "WHERE zip IN ("$zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= CURDATE() ";
        if (!empty(
    $smoke)) {
            
    $where .= " AND about_me.smoking = $smoke"; }
        if (!empty(
    $religion))    {
            
    $where .= " AND bkgd.relg = $religion"; }
        if (!empty(
    $heightMin)) {
            
    $where .= " AND appearance.height >= $heightMin"; }
        if (!empty(
    $heightMax)) {
            
    $where .= " AND appearance.height <= $heightMax"; }
        if (!empty(
    $hairColor)) {
            
    $where .= " AND appearance.hair_color = $hairColor";    }
        if (!empty(
    $eyeColor))    {
            
    $where .= " AND appearance.eye_color = $eyeColor";    }

        
    $result mysql_query("SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1,
                 CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5)) AS age
                 FROM user
                 LEFT JOIN photos
                 ON user.userID = photos.userID
                 LEFT JOIN about_me
                 ON user.userID = about_me.userID
                 LEFT JOIN bkgd
                 ON user.userID = bkgd.userID
                 LEFT JOIN appearance
                 ON user.userID = appearance.userID
                 LEFT JOIN blockUser
                 ON user.userID = blockUser.blockID
                 $where
                 LIMIT $offset, $rowsperpage"
    )or    die(mysql_error()); 
    I am not sure if it is a simple issue or if I am way off, could use some help
    Last edited by harkly; 11-22-2011 at 09:28 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,031
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    When you use a WHERE clause that is conditional upon a *dependent* table (e.g, the right side table in a LEFT JOIN), you convert that LEFT JOIN to an INNER JOIN, thus effectively destroying all your join logic.

    Here's an old post of mine that explains why:
    http://www.codingforums.com/showthre...192#post818192

    And not to ask a dumb question, but why do you need that part of the WHERE, in any case???

    It's identical to the ON condition:
    Code:
    if (!isset($blockID)) {
      $where .= " AND blockUser.blockID = user.userID";} 
    
    versus
    
                 LEFT JOIN blockUser
                 ON user.userID = blockUser.blockID
    Anyway, if you really intended those LEFT JOINs to *BE* left joins, then you need to completely reorganize that query.

    Instead of having
    Code:
        ...
                 LEFT JOIN appearance
                 ON user.userID = appearance.userID 
        ...
                 WHERE ...
                 AND appearance.height >= $heightMin
        ...
    (just to pick *one* example), you need to have
    Code:
        ...
                 LEFT JOIN appearance
                 ON user.userID = appearance.userID 
                 AND appearance.height >= $heightMin
        ...
    so that the condition becomes part of the ON.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    I see what your saying and that works for everything but my blockUser table.

    I modified my code and am trying to use NOT IN. I can get it to work in a simple query but not my larger one, can anyone help me out with that??

    Works fine here
    PHP Code:
    $result mysql_query("
        SELECT user.userID
        FROM user 
        WHERE userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID')"


    But not here

    PHP Code:
    $result mysql_query("SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1,
      CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)<RIGHT(birth_date,5)) AS age
      FROM user
      
      LEFT JOIN photos
      ON user.userID = photos.userID
      
      LEFT JOIN about_me
      ON user.userID = about_me.userID
      AND about_me.smoking = $smoke 
      
      LEFT JOIN bkgd
      ON user.userID = bkgd.userID
      AND bkgd.relg = $religion
      
      LEFT JOIN appearance
      ON user.userID = appearance.userID
      AND appearance.height >= $heightMin
      AND appearance.height <= $heightMax
      AND appearance.eye_color = $eyeColor
      
      WHERE zip IN ("
    $zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender = $genderPref AND user.genderPref = $gender AND user.exp_date >= CURDATE() AND user.userID NOT IN (SELECT blockUser.blockID FROM blockUser WHERE blockUser.blockUserID = '$clientID')
      
      LIMIT $offset, $rowsperpage"
    )or    die(mysql_error()); 
    Last edited by harkly; 11-23-2011 at 08:10 PM.


  •  

    Posting Permissions

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