[Resolved] Need some help here..please

[7m25]

I have a table with the following fields:

projectName, status, date.

I have multiple projectName on my table but with different status. I wanted to get the projectName and status from the table with the latest date.

How will i do that? Thanks in advance.

[Pig]

You should always have an ID field as well.

That aside, if you want to get the project name and status for the record that has the lastest date, you could do:

SELECT projectName, status FROM table ORDER BY date limit 0,1

[7m25]

I was able to get the projectName with the latest Date but then, (sorry i did not state my question clearly on my first post) the real problem is:

 My table has mulitiple projectNames (some projectNames are 

the same they just differ in status). I wanted to get 'all' the projectNames, that are latest.

[Pig]

Do you mean, all project names and status for the project name that has the latest date?

If so, then you will need to get the project name with the greatest date, and then go back and get all the status's for that project name. You might be able to do it in one query , but this is more straight forward.

$result = mysql_query("SELECT project_name FROM table ORDER BY date LIMIT 0,1");
$row = mysql_fetch_row($result);
$result = mysql_query("SELECT status FROM table WHERE project_name='$row[0]'");
while( $row2 = mysql_fetch_array($result) )
{
echo( $row[0] . "-" . $row2[0] . "<br>");
}

[7m25]

i'm sorry man! i'm really having trouble explaining my problem

The contents of my table are

Project Names &nbsp; Status &nbsp; Date Changed

Project 1 &nbsp; Proposed &nbsp; 03-03-2003
Project 2 &nbsp; Proposed &nbsp; 03-03-2003
Project 3 &nbsp; Proposed &nbsp; 03-03-2003
Project 4 &nbsp; Proposed &nbsp; 03-03-2003
Project 1 &nbsp; Ongoing &nbsp; 03-18-2003
Project 2 &nbsp; Denied &nbsp; 03-18-2003
Project 4 &nbsp; Finished &nbsp; 03-18-2007

I wanted to get the latest projects and their status:

Expected Output:

Project Names &nbsp; Status &nbsp; Date Changed

Project 3 &nbsp; Proposed &nbsp; 03-03-2003
Project 1 &nbsp; Ongoing &nbsp; 03-18-2003
Project 2 &nbsp; Denied &nbsp; 03-18-2003
Project 4 &nbsp; Finished &nbsp; 03-18-2007

Thanks for bearing patiently with me. Thank you so much.

[mrhappiness]

SELECT
	p.project_name, p.status, p.changed
FROM
	project
	LEFT JOIN project dummy ON p.name = dummy.name AND p.changed < dummy.changed
WHERE dummy.changed IS NULL

try this

[7m25]

Is there any other way to do it?

[Pig]

You could probably find a way to do it in one query, but here is the idea if you don't mind 2 steps.

Query to get all the project_name's. Loop through the results, and run another query for each project name. In the second query, order by date, and limit the result to one.

$result = mysql_query("SELECT DISTINCT(project_name) FROM table");
while( $row = mysql_fetch_array($result) )
{
     $result2 = mysql_query("SELECT status, date FROM table WHERE project_name='$row[0]' ORDER BY date DESC LIMIT 0,1");
     while( $row2 = mysql_fetch_array($result2) )
     {
          echo($row[0] . ", " . $row2[status] . ", " . $row2[date] . "<br>");
     }
}

[7m25]

Thank you very much! It worked!

[mrhappiness]

Originally posted by 7m25
Is there any other way to do it?

did my statement not work?
what did it produce?

[7m25]

Originally posted by mrhappiness
did my statement not work?
what did it produce?

I have'nt tried your code yet. I am new to PHP/MySQL. I had a hard time understanding it. Can you please explain it further? Thanks

[mrhappiness]

it should provide you with the project's name its newest status and the corresponding date with only one select

just give it a try in phpmyadmin