PHP MYSQL: How to retreive (1) latest date, (2) value associated with latest date, (3) largest value (4) lowest value?

Posted on 2008-06-11
Last Modified: 2013-12-13


I have a MySQL database table I want to retrieve data from.

The format is
[ Date ] [ Value ]

I want to retrieve 4 things from the table:
1. The latest date (call it $latest_date)
2. The value associated with the latest date (call it $latest_value)
3. The highest value in the column Value (call it $max_value)
4. The lowest value in the column Value (call it $min_value)

How do I write in my PHP script a MySQL query to retrieve those 4 things?

Question by:gingera
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

by:Rob Siklos
ID: 21761180
Here's the SQL:

1 and 2:
  SELECT `Value`, `Date`
  FROM table
  HAVING `Date` = MAX(`Date`)

  SELECT MAX(`Value`) FROM table

  SELECT MIN(`Value`) FROM table

Accepted Solution

Rob Siklos earned 500 total points
ID: 21761234
so in PHP, you can do it like this:

// 1 and 2
$sql = "SELECT `Value`, `Date`
  FROM table
  HAVING `Date` = MAX(`Date`)";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
$latest_date = $row['Date'];
$latest_value = $row['Value'];
// 3 and 4
$sql = "SELECT MAX(Value) AS maxval, MIN(Value) AS minval FROM table";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
$max_date = $row['maxval'];
$min_value = $row['minval'];

Open in new window


Author Closing Comment

ID: 31466195

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question