How do you query a MySQL table columns highest entry and store it in a variable.

Question

How do you query a MySQL table columns highest entry and store it in a variable. Here is what I have but it is not working.

$query = "SELECT MAX(date) FROM Records WHERE ips='$currentip'";
$result = mysql_query($query) or die(mysql_error());
echo $result;

Update:

<?php
$db = mysql_connect("localhost", "123", "123");
mysql_select_db("123");
$currentip='123.456.789';
$query = "SELECT MAX(date) FROM Records WHERE ips='$currentip'";
$date = mysql_result($result, 0);
echo $date;
?>

Answer 1

use max() in your query

Answer 2

select max(column_name) from table_name

Answer 3

Max() will work with group by clause

"SELECT MAX(date) AS mdate FROM Records WHERE ips='$currentip' GROUP BY ips"; 

Answer 4

Just to point out, when you try to echo $result you are only echoing the resource since that is what mysql_query returns.

If you want to echo the column, try:

while($row = mysql_fetch_assoc($result)) {
   echo $row['date'];
}

Or you could use mysql_result which return the return value into a string.

$query = "SELECT MAX(date) FROM Records WHERE ips='$currentip'";
$date = mysql_result($result, 0);  
echo $date;

Either should work, it hasn't been tested or compiled.

In regards to your updated code:

<?php
$db = mysql_connect("localhost", "123", "123");
mysql_select_db("123");
$currentip='123.456.789';
$query = "SELECT MAX(date) FROM Records WHERE ips='$currentip'";
$result = mysql_query($query); // <--you forgot this line.
$date = mysql_result($result, 0); // <--now $result has a valid resource.
echo $date;
?>

I would also strongly suggest that since you are a new PHP programmer, get into the habit of incorporating some form of error-handling/checking, such as

$db = mysql_connect("localhost", "123", "123");
if (!$db) {
  die('Could not connect: ' . mysql_error());
}
...
$result = mysql_query($query);
if (!$result) {
  die('Could not perform query: ' . mysql_error());
}

Performing error-checks from the start of your programming experience is really good practice and will better suit you for the long-run.

Answer 5

 $myQuery = "SELECT MAX(date) FROM Records WHERE ips='$currentip'";

 $result = mysql_query($myQuery,$connection);

 $mydate= mysql_fetch_row($result);

 echo $mydate[0];

Answer 6

You absolutely need to read the manual chapter for the MySQL Functions. Composing a program by picking random functions from third-party code and trying to guess how they work can only lead to a nervous breakdown.

To sum up:

• mysql_query() executes a query and returns a resource ID
• You use such resource ID to fetch rows with any of the mysql_fetch_*() functions

I particularly like mysql_fetch_assoc():

<?php
# ...
if( $row = mysql_fetch_assoc($resource_id) ){
    echo $row['MAX(date)'];
}
?>

I'd also recommend some tutorial about basic SQL. For instance, you can define aliases to make results easier to handle:

SELECT MAX(date) AS max_date FROM ...
echo $row['max_date'];

Last but not least, if you put your site online before reading about SQL Injection please let us know so we can hack it ;-) (Hint: http://es.php.net/manual/en/function.mysql-real-escape-string.php)