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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Age Searching and zip code search

    Hey all, I'm making a new free dating website. Now, I'm working on the age calculation and the following code works. . .


    Code:
    $search =mysql_query("select *  from users left join info using(id_user) left join aboutme using(id_user) left join 
    
    active using(id_user) where users.login='$user' or active.new='n' AND active.active='y' AND info.gender='$sex' AND 
    
    info.bodystyle LIKE '$bodystyle' AND info.eyecolor LIKE '$eyecolor' AND info.haircolor LIKE '$haircolor' AND 
    
    info.ethnicity LIKE '$ethnicity' AND (floor(info.rating/info.ratingnumbervotes)) BETWEEN '$rating' and '10' ORDER BY info.lastlogindate")or 
    
    die(mysql_error());







    But the following does not. Do you see whats wrong?

    Code:
    $search =mysql_query("select *  from users left join info using(id_user) left join aboutme using(id_user) left join 
    
    active using(id_user) where users.login='$user' or active.new='n' AND active.active='y' AND info.gender='$sex' AND 
    
    info.bodystyle LIKE '$bodystyle' AND info.eyecolor LIKE '$eyecolor' AND info.haircolor LIKE '$haircolor' AND 
    
    info.ethnicity LIKE '$ethnicity' AND (floor(info.rating/info.ratingnumbervotes)) BETWEEN '$rating' and '10' AND 
    
    (floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh' ORDER BY info.lastlogindate")or 
    
    die(mysql_error());



    Also, I want to add in zip code distance search, know of any useful links to get me started?

  • #2
    Regular Coder
    Join Date
    Mar 2009
    Location
    United Kingdom
    Posts
    161
    Thanks
    6
    Thanked 28 Times in 28 Posts
    Can we see the mysql error report please?

  • #3
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Killermud View Post
    Can we see the mysql error report please?
    Sorry, how do I get that? There is no error that comes up. I do an if else statement if there are results display them else "sorry no matches" and when I try to calculate age I get the sorry no matches error. I'm wondering if my formula is wrong somewhere in the search.

    (floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh'

    Wondering if something wrong there.

  • #4
    Regular Coder
    Join Date
    Mar 2009
    Location
    United Kingdom
    Posts
    161
    Thanks
    6
    Thanked 28 Times in 28 Posts
    Just try editting it to this and tell me what shows up :
    PHP Code:
    if(mysql_query("select *  from users left join info using(id_user) left join aboutme using(id_user) left join
    active using(id_user) where users.login='$user' or active.new='n' AND active.active='y' AND info.gender='$sex' AND
    info.bodystyle LIKE '$bodystyle' AND info.eyecolor LIKE '$eyecolor' AND info.haircolor LIKE '$haircolor' AND
    info.ethnicity LIKE '$ethnicity' AND (floor(info.rating/info.ratingnumbervotes)) BETWEEN '$rating' and '10' AND
    (floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh' ORDER BY info.lastlogindate"
    )){
        echo 
    "worked";
    }else{
    echo 
    mysql_error();    


  • #5
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    ok it says worked. Nothing more. The exact error is in the code


    Code:
    AND
    (floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh'

    If I take that out, it works fine. I echo agelow and get 18 age high get 50, yet when I display results I get NOTHING. If I remove that line I get all results of all ages.

    Today is in this format:
    Code:
    $today=date('Y-m-d');
    If I use phpmyadmin birthdate is displayed as: YYYY-mm-dd IE: 1981-05-11

    so to my understanding it *SHOULD* work

  • #6
    Regular Coder
    Join Date
    Mar 2009
    Location
    United Kingdom
    Posts
    161
    Thanks
    6
    Thanked 28 Times in 28 Posts
    Hmmm well the the script you have given has :

    Code:
    (floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh' ORDER BY info.lastlogindate
    In which the begining bracket does not close so id say change it to :
    Code:
    floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh' ORDER BY info.lastlogindate

  • #7
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Same thing, I got response worked, but no matches found. FYI: This is how my search code looks on a broader scale:




    Code:
    <?
    include("db.inc.php");
    $user=$_POST['user'];
    $today=date('Y-m-d');
    
    Echo "TODAY: " . $today . "AGE HIGH: " . $agehigh . "<BR>AGE LOW: " . $agelow;
    
    
    $search =mysql_query("select *  from users left join info using(id_user) left join aboutme using(id_user) left join 
    
    active using(id_user) where users.login='$user' or active.new='n' AND active.active='y' AND info.gender='$sex' AND 
    
    info.bodystyle LIKE '$bodystyle' AND info.eyecolor LIKE '$eyecolor' AND info.haircolor LIKE '$haircolor' AND 
    
    info.ethnicity LIKE '$ethnicity' AND (floor(info.rating/info.ratingnumbervotes)) BETWEEN '$rating' and '10'  AND
    floor(($today-info.birthdate)-1) BETWEEN '$agelow' and '$agehigh' ORDER BY info.lastlogindate")or 
    
    die(mysql_error());
    
    
    
    if(mysql_query("select *  from users left join info using(id_user) left join aboutme using(id_user) left join 
    
    active using(id_user) where users.login='$user' or active.new='n' AND active.active='y' AND info.gender='$sex' AND 
    
    info.bodystyle LIKE '$bodystyle' AND info.eyecolor LIKE '$eyecolor' AND info.haircolor LIKE '$haircolor' AND 
    
    info.ethnicity LIKE '$ethnicity' AND (floor(info.rating/info.ratingnumbervotes)) BETWEEN '$rating' and '10'  AND
    (floor(($today-info.birthdate)-1)) BETWEEN '$agelow' and '$agehigh' ORDER BY info.lastlogindate")){
        echo "worked";
    }else{
    echo mysql_error();    
    }?> 
    
    
    
    <table border=0 cellspacing=0 cellpadding=0 width=100%>
     <TR>
    	
    	<TD bgcolor="#E6F3FF" id="navigation" align=middle valign=top width=25%>
    	<?php
    	// This is the logged in links page
    	readfile("/hsphere/local/home/sparvin/realsimpledating.com/loggedinlinks.html");?>
    	</TD>
    
    <?
    $searchresults=mysql_num_rows($search);
    if ($searchresults=='0')
    	{
    	Echo "<TD colspan=5 class=bodytext align=middle valign=top><CENTER><font size=+1>No Matching searches found 
    
    <BR>";	
    	}
    else
    { 
    Echo "<TD colspan=5 class=bodytext align=middle valign=top><CENTER><font size=+1>See your perfect matches listed 
    
    below.  Click on a users picture to see their profile. <BR>";
    	}
    
     
    
    
    
    	while($myrow=mysql_fetch_array($search))
    
    	{ 
    
    	//pull out birthdate info and calculate age
    	$birthday=$myrow['birthdate'];
    
    	$today = date('Y-m-d');
    	$age=floor(($today-$birthday)-1);
    
    
    	$user=$myrow[id_user];
    	echo "
    	
    		<TABLE width=100% height=100 border=5>
    		<TR>";
    
    		//pull main picture to display
    		$picinfo=mysql_query("select * from pictures  where id_user ='$user' and totalnumber='1'");
    		$picresult =mysql_fetch_array($picinfo);
    
    		$pic1path=$picresult['picturename'];
    
    		if (!$picresult)
    		{
    		Echo "<TD height=200 width=200 rowspan=4><A href=/viewprofile.php?user=$user>User has not uploaded a 
    
    photo yet</a></TD> ";
    		}
    	else	{
    		Echo "<TD height=150 width=150 rowspan=4 bgcolor=black><A href=/viewprofile.php?user=$user><img 
    
    height=150 width=150 border=0 src=/" . $pic1path . "></a></TD> ";
    		}
    
    		Echo "
    		<TD align=left with=50%><B>$myrow[login]</B> $age Year old $myrow[gender]</TD> 
    		<TD align=left width=50%>Last login: $myrow[lastlogindate]</TD>
    		</TR>
    		<TR>
    		<TD height=100 bgcolor=white colspan=2>$myrow[aboutme]</TD>
    		</TR>
    		</TABLE>";
    
    		}
    
    ?>
    
    
    	</TD>
    
    
    	</TR>
    </TABLE>
    
    
    
    
    
    </body>
    </html>

  • #8
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Bump any ideas plssss?
    Last edited by doodguy; 05-05-2009 at 11:54 PM.

  • #9
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    daily bump I'm stuck Plssss help

  • #10
    GŁtkodierer
    Join Date
    Apr 2009
    Posts
    2,127
    Thanks
    1
    Thanked 426 Times in 424 Posts
    Substracting two strings and hoping the result will be a number of years is probably a mistake.

    Read up on the MySQL date functions, e.g. DATEDIFF.

  • #11
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by venegal View Post
    Substracting two strings and hoping the result will be a number of years is probably a mistake.

    Read up on the MySQL date functions, e.g. DATEDIFF.
    What I don't understand is this code works fine below after the search results are found:

    Code:
    //pull out birthdate info and calculate age
    	$birthday=$myrow['birthdate'];
    
    	$today = date('Y-m-d');
    	$age=floor(($today-$birthday)-1);
    It gets that info in a while loop from the search results returned. So why can't I plugin the age formula?

  • #12
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    after furthere research seems I have to use the datetime class but I have NO CLUE how to do this during a sql query. can someone pls point me in right direction?

  • #13
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You need to convert the birthdate column in your table to a date datatype. If you have a lot of data already in the table then this may be a multi-step process where you add a new column of data type "date", then run a script that takes the value from the old column, converts it to a proper date format, and updates the new column with that value. You can then remove the old column.

    You may be able to get by not going through this process; it just depends on how the date is stored. Whatever you do, work on a test table in a test environment until you get the results you want!

  • #14
    New Coder
    Join Date
    Apr 2009
    Posts
    59
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    You need to convert the birthdate column in your table to a date datatype. If you have a lot of data already in the table then this may be a multi-step process where you add a new column of data type "date", then run a script that takes the value from the old column, converts it to a proper date format, and updates the new column with that value. You can then remove the old column.

    You may be able to get by not going through this process; it just depends on how the date is stored. Whatever you do, work on a test table in a test environment until you get the results you want!
    birthdate is currently stored as a date datatype in format 0000-00-00 Any ideas on how I should write this code in the sql query?

  • #15
    GŁtkodierer
    Join Date
    Apr 2009
    Posts
    2,127
    Thanks
    1
    Thanked 426 Times in 424 Posts
    Quote Originally Posted by doodguy View Post
    What I don't understand is this code works fine below after the search results are found:

    Code:
    //pull out birthdate info and calculate age
    	$birthday=$myrow['birthdate'];
    
    	$today = date('Y-m-d');
    	$age=floor(($today-$birthday)-1);
    What you have to understand is that this snippet is miles away from working fine. I will tell you what's happening here. You have two strings of the form yyyy-mm-dd, and try to subtract them from each other. Because there is no such thing as subtracting strings, PHP converts them to integers. How does it do that? It disregards everything after the first non-numeric character. So, your strings have become integers of the format yyyy. The "age" you get is actually the difference between the birthyear and the current year, completely disregarding the actual birthday. It seems like you tried to fix it with that "-1", which doesn't do anything except pushing the problem away to the other half of the year.

    Now, you were talking about using a datetime class. That won't do you any good, since the date calculations are performed by MySQL, not by PHP.

    MySQL apparently handles string to int conversion differently from PHP, which is why the code that didn't work very well in the first place, doesn't work there at all.

    I told you to read up on MySQL's date functions. DATEDIFF can take two strings of the exact same format you got there and return the difference in days. I bet you are able to convert that to an age.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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