not listing users when using innerjoin with max in php mysql

i am new in php i have a problem in my php code.please help me.. i have two tables

seeker
seeker_nic-----username
111-------------ali
222-------------umer
333-------------raza

requestblood
id-------seeker_nic-----requireddate
1-------  111 ----------2012/9/9
2 ------- 222-----------2012/5/8
3 ------  111-----------2012/10/11
4 ------- 111-----------2012/11/12
5 ------- 222-----------2012/7/9
6 ------- 333 ----------2012/4/4

now i want to list users one time with maximum date like..

s.no---- username----- requireddate
 1------- ali---------- 2012/11/12
 2------- umer--------- 2012/7/9
 3------- raza--------- 2012/4/4

i am using this query

"SELECT  bloodrequest.requireddate, seeker.username
FROM 
bloodrequest
JOIN seeker ON bloodrequest.seeker_nic= seeker.seeker_nic
Join (SELECT max(requireddate)as maxdate FROM bloodrequest) maxresults on
bloodrequest.requireddate = maxresults.maxdate"

.. but it shows only 1 record not the list and if use this query (left join instead of join)

"SELECT bloodrequest.requireddate, seeker.username
FROM 
bloodrequest
left JOIN seeker ON bloodrequest.seeker_nic = seeker.seeker_nic
left join (SELECT max(requireddate)as maxdate FROM bloodrequest) maxresults
on bloodrequest.requireddate = maxresults.maxdate";

then it shows all records with all dates but not tha maximum..

id------seeker_nic -------requireddate<br>
1 ------ ali   ---------   2012/9/9<br>
2 ------ ali ----------    2012/10/11<br>
3 ------ ali ------------  2012/11/12<br>
4------  umer------------- 2012/5/8<br>
5------- umer -------------2012/7/9<br>
6 ------ raza--------------2012/4/4<br>

php
mysql

2012-04-04 18:41
by maham

You should be able to do this simpler:

select seeker.username, max(requestblood.requireddate)
from seeker
join requestblood on seeker.seeker_nic=requestblood.seeker_nic
group by seeker.username

Feel free to add any sorting order you need.

2012-04-04 18:45
by Aleks G

i tried this query but its not working.. :( i dun knw whts the proble - maham 2012-04-04 18:54

@maham What do you get when you run it - Aleks G 2012-04-04 18:55

just a blank page.. - maham 2012-04-04 18:56

@maham run the query from the command line in mysql (or whatever your database engine is) - not from php. Make sure you get the right data, only then put PHP code around it. Most likely your blank page results from an error in PHP somewhere - Aleks G 2012-04-04 18:57

i am sorry.. i made mistake here.. my second table is "bloodrequest" not "requestblood"... yeahh your query working in phpmyadmin.. it shows the results that i wanted but in php an error occured "undefined index "requireddate" ... // in this lin - maham 2012-04-04 19:07

/ - maham 2012-04-04 19:11

helppp plzzzzzzzzzzzzzzzzzzzzzzzzz : - maham 2012-04-04 19:39

@ aleks g ....i have solved it just by little change

select seeker.username, max(bloodrequest.requireddate) as requireddate
from seeker
join bloodrequest on seeker.seeker_nic=bloodrequest.seeker_nic
group by seeker.username

... thank u soo much : - maham 2012-04-04 20:08