Loes Visser Loes Visser - 6 months ago 20
SQL Question

SQL SELECT MAX(Id)

I try to make a graph of the 7 last entries of my SQL database.
So I tried to select the maximum (or last entered) id number, but it is not working.

My table:

Id | id_time | temp | RH
2700 | 2706 | 24.187 | 63.0999984741211
2701 | 2707 | 24.25 | 63
2702 | 2708 | 26.562 | 99.9000015258789
2703 | 2709 | 25 | 50.456
2704 | 2710 | 28.2 | 64.48
2705 | 2711 | 21.541 | 78.45876
2706 | 2712 | 18.567 | 55.787455465
2707 | 2713 | 23.25 | 58.54564
2708 | 2714 | 26.5 | 49.9000015258789
2709 | 2715 | 25 | 50.456
2710 | 2716 | 28.2 | 64.48
2711 | 2717 | 21.541 | 78.45876
2712 | 2718 | 18.567 | 55.787455465
2713 | 2719 | 23.25 | 58.54564
2714 | 2720 | 26.5 | 49.9000015258789


The python code that I used to make the tables:

with con:
cur = con.cursor()

cur.execute("DROP TABLE IF EXISTS tijd")
cur.execute("CREATE TABLE tijd(Id INT PRIMARY KEY AUTO_INCREMENT, \
jaar VARCHAR(25), \
maand VARCHAR(25), \
dag VARCHAR(25), \
uur VARCHAR(25), \
minuut VARCHAR(25))")
cur.execute("DROP TABLE IF EXISTS data")
cur.execute("CREATE TABLE data(Id INT PRIMARY KEY AUTO_INCREMENT, \
id_tijd VARCHAR(25), \
temp VARCHAR(25), \
RH VARCHAR(25))")


The php code that I use to select the data from the database:

<?php
$dbhost = 'localhost';
$dbuser = 'testuser1';
$dbpass = 'loes';

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn1 ) {
die('Could not connect: ' . mysql_error());
}


$sql1 ='SELECT MAX(Id) FROM data';
mysql_select_db('huygendb');
$retval1 = mysql_query( $sql1, $conn1 );

if(! $retval1 ) {
die('Could not get data: ' . mysql_error());
}

$max1 = mysql_query($sql1) or die ("no query");
echo "{$max1} <br>";
if ($max1-7 > 0){
$max = $max1 -7;}
else{
$max = 0;}
echo "{$max} <br>";

mysql_close($conn1);
?>


The result of the code is:

Resource id #3
0


There might be still somewhere a record with Id = 3, so I guessed my Id's are strings (because '3' is higher than '2714'), but I really thought I made them integers right?
How can I fix the php code to do what I want?

Answer

mysql_query(); returns a resource, not a result set. you need to fetch data from it:

   $res = mysql_query($sql1) or die ("no query");
   $max1 = mysql_fetch_array($res);
   var_dump($max1);

And then: don't use mysql_* functions. They are deprecated.