...

View Full Version : Age Searching and zip code search



doodguy
05-05-2009, 05:20 PM
Hey all, I'm making a new free dating website. Now, I'm working on the age calculation and the following code works. . .



$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?


$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?

Killermud
05-05-2009, 05:30 PM
Can we see the mysql error report please?

doodguy
05-05-2009, 05:40 PM
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.

Killermud
05-05-2009, 05:51 PM
Just try editting it to this and tell me what shows up :


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();
}

doodguy
05-05-2009, 06:29 PM
ok it says worked. Nothing more. The exact error is in the 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:
$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

Killermud
05-05-2009, 06:56 PM
Hmmm well the the script you have given has :


(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 :


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

doodguy
05-05-2009, 07:13 PM
Same thing, I got response worked, but no matches found. FYI: This is how my search code looks on a broader scale:





<?
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>

doodguy
05-05-2009, 11:42 PM
Bump any ideas plssss?

doodguy
05-06-2009, 03:52 PM
daily bump I'm stuck :( Plssss help

venegal
05-06-2009, 04:33 PM
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.

doodguy
05-06-2009, 06:27 PM
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:


//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?

doodguy
05-06-2009, 06:33 PM
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?

Fumigator
05-06-2009, 07:29 PM
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!

doodguy
05-06-2009, 07:56 PM
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?

venegal
05-06-2009, 08:09 PM
What I don't understand is this code works fine below after the search results are found:


//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.

Fumigator
05-06-2009, 08:27 PM
Doodguy my apologies, I jumped to the conclusion your date was not stored as a date datatype. Since your table is in order, you can use this tricky little query to get someone's age:



SELECT YEAR(CURRENT_DATE()) - YEAR(birthdate) - (RIGHT(CURRENT_DATE(),5) < RIGHT(birthdate,5)) AS age
FROM table1


The thing to consider is you can't do a simple "current year minus birth year" because if the birthday hasn't happened in the current year yet, then the age will be one year less than if the birthday has already happened in the current year (what a mouthful).

To remedy this, we compare the month/day of today against the month/day of the birthday. The "tricky" bit comes in by understanding that MySQL returns a 0 for false and 1 for true, so if we subtract the results of the comparison (either 0 or 1) from the simple "current year minus birth year", we come up with the correct age.

doodguy
05-06-2009, 09:20 PM
Doodguy my apologies, I jumped to the conclusion your date was not stored as a date datatype. Since your table is in order, you can use this tricky little query to get someone's age:



SELECT YEAR(CURRENT_DATE()) - YEAR(birthdate) - (RIGHT(CURRENT_DATE(),5) < RIGHT(birthdate,5)) AS age
FROM table1


The thing to consider is you can't do a simple "current year minus birth year" because if the birthday hasn't happened in the current year yet, then the age will be one year less than if the birthday has already happened in the current year (what a mouthful).

To remedy this, we compare the month/day of today against the month/day of the birthday. The "tricky" bit comes in by understanding that MySQL returns a 0 for false and 1 for true, so if we subtract the results of the comparison (either 0 or 1) from the simple "current year minus birth year", we come up with the correct age.

WOOOOOOOOOHOOOOOOOOO!!!!!!!!! THIS WORKS!!!!!!!!! NOW I get to deal with zip codes =/

Here is code that WORKS:


$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

YEAR(CURRENT_DATE()) - YEAR(info.birthdate) - (RIGHT(CURRENT_DATE(),5) < RIGHT(info.birthdate,5)) BETWEEN '$agelow'

and '$agehigh' ORDER BY info.lastlogindate DESC")or
die(mysql_error());



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum