Query for a variable from query?

mysql, query

[goosebriar]

I am fairly new to mysql and I am trying to build a query as follows:

$current = SELECT payendDate FROM lab_payroll ORDER BY payendDate DESC, LIMIT 1

This gives me one record with the most recent date in the database.

Now I want to find all the records in the database whose payendDate equals that date. How do I do that?

I've tried

$results=SELECT * FROM lab_payroll WHERE payendDate=$current

That seems logical, but it doesn't work. Help!

[txbakers]

Your logic is correct, but the syntax is wrong. I'm not a PHPer, so I"m going to bump this to the top.

Anyone know the correct syntax for him?

[coopster]

Welcome to WebmasterWorld, goosebriar.

If you are running MySQL >= 4.1 you can use a subquery:

SELECT * FROM lab_payroll WHERE payendDate = (SELECT MAX(payendDate) FROM lab_payroll)

If not, then you could fetch the maximum date value from your result set in the first query and use it as a variable in the next query.

$query = 'SELECT MAX(payendDate) AS currentPayendDate FROM lab_payroll'; 
$rows = mysql_query [php.net]($query); 
if ($row = mysql_fetch_assoc [php.net]($rows)) { 
 $current = $row['currentPayendDate']; 
 $query = "SELECT * FROM lab_payroll WHERE payendDate = $current"; 
 $rows = mysql_query($query); 
 while ($row = mysql_fetch_assoc($rows)) { 
  // process the result set 
 } 
}

Either way, you cannot just use a value directly from a query. You need to execute the query and get a result set returned from which you then fetch rows. See the corresponding manual links for more information.

[goosebriar]

Thank you! I will give it a try.

[goosebriar]

It worked! Wahoo! (that is the first subquery worked)

Thanks again! Wahoo!