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 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts

    comparing dates.

    I am doing a Select and my date comparison is not working.

    I am comparing the date as such
    Code:
    user.exp_date >= curdate()
    This is my query,
    PHP Code:
    $where "WHERE zip IN ("$zips_in_range .") AND user.bd_year <= $year1 AND user.bd_year >= $year2 AND genderPref = $genderPref 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
                           $where"
    )or die('query 2 failed'); 

    Works fine when I dumb it down

    PHP Code:
    $result mysql_query("SELECT * FROM user WHERE gender = 2 AND genderPref = 1 AND exp_date >= curdate()")or die(mysql_error());

    while (
    $r=mysql_fetch_array($result)){
            
    $userID=$r["userID"];
            
    $exp_date=$r["exp_date"];

            echo 
    "$userID, $exp_date <br>";

    Can someone see what I am doing wrong? I am not getting an error it is just not eliminating those with the exp_date
    Last edited by harkly; 11-08-2010 at 08:56 PM.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    You'll get more help by asking this moved to the mySQL forum, as its clearly a mySQL issue. I'll take a look and play around to see if I can find the issue in the meantime. If you echo out your final query, what does it look like?

  • #3
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    try to wrap all fields' values with single quotes, ex:
    PHP Code:
    " AND appearance.height >= '$heightMin' " 
    and
    PHP Code:
    " LEFT JOIN photos ON user.userID = 'photos.userID' " 
    Last edited by poyzn; 11-08-2010 at 07:18 PM.

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Actually, its bad form, and can even be problematic, to quote numbers. If its a number, quote it, if its not, don't.

  • #5
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Here is my query

    SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1, CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)= 1911 AND user.gender = 1 AND user.genderPref = 2 AND user.exp_date >= curdate()

  • #6
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    Quote Originally Posted by Keleth View Post
    Actually, its bad form, and can even be problematic, to quote numbers. If its a number, quote it, if its not, don't.
    Agree with you, but I don't know the structure of his tables and ID's can contain letters. So try to see if it works or the problem is not a quotes

  • #7
    Regular Coder poyzn's Avatar
    Join Date
    Nov 2010
    Posts
    266
    Thanks
    2
    Thanked 61 Times in 61 Posts
    Quote Originally Posted by harkly View Post
    Here is my query

    SELECT user.userID, user.gender, user.genderPref, user.city, user.state, photos.photo_1, CURDATE(), (YEAR(CURDATE())-YEAR(birth_date)) - (RIGHT(CURDATE(),5)= 1911 AND user.gender = 1 AND user.genderPref = 2 AND user.exp_date >= curdate()
    Where is the WHERE clause?

  • #8
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by poyzn View Post
    Where is the WHERE clause?
    That's all that shows when I use the following

    PHP Code:
    echo "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
                                             $where"

    and also when I paste the $where into it

    PHP Code:
    WHERE user.bd_year <= $year1 AND user.bd_year >= $year2 AND user.gender $genderPref AND user.genderPref $gender AND user.exp_date >= curdate() 

  • #9
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by poyzn View Post
    Agree with you, but I don't know the structure of his tables and ID's can contain letters. So try to see if it works or the problem is not a quotes
    The IDs can contain letters, but I have the search working. I just added the date requirement and that is the only part that is not working.

    It still pulls up data without regard to the exp_date.

  • #10
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Got it to work!


  •  

    Posting Permissions

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