Solved

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
3
627 Views
Last Modified: 2013-12-13
PHP MYSQL

Hello,

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?


Thanks.
0
Comment
Question by:gingera
  • 2
3 Comments
 
LVL 9

Expert Comment

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

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

3:
  SELECT MAX(`Value`) FROM table

4:
  SELECT MIN(`Value`) FROM table
0
 
LVL 9

Accepted Solution

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

0
 

Author Closing Comment

by:gingera
ID: 31466195
THANKS
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

808 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