Page 1 of 1

Selecting most recent entry from db

Posted: Sat Dec 03, 2005 2:46 pm
by dru_nasty
I'm trying to learn the building blocks of php w/mysql.

I have a table called 'testTable' with two columns, 'id' and 'testField'.
id is auto_incremented . I've got 4 rows basically like this:

id testField
1 some text
2 more text
3 some more text
4 and more text

I would just like to get the latest entry from the db which would be "and more text".

so here's my code so far:

Code: Select all

<?php
$conn = mysql_connect("localhost", "myusername","mypassword");
mysql_select_db("mydatabase_name", $conn);

$sql = "SELECT testField FROM testTable";
$result = mysql_query($sql, $conn) or die(mysql_error());
echo "$result";
?>
And the result i get from that query is this "Resource id #3".

How would i get only the latest entry from the db to display?

Re: Selecting most recent entry from db

Posted: Sat Dec 03, 2005 3:02 pm
by foobar
Try this:

Code: Select all

<?php
$conn = mysql_connect("localhost", "myusername","mypassword");
mysql_select_db("mydatabase_name", $conn);

/* Order by (id) descending and subsequently retrieve only the first element */
$sql = "SELECT testField FROM testTable ORDER BY id DESC LIMIT 1";
$result = mysql_query($sql, $conn) or die(mysql_error());

/* Fetch data from result set into an associative array */
$row = mysql_fetch_assoc($result);

/* Access any SELECT'ed fields via $row['field_name'] */
echo $row['testField'];
?>
dru_nasty wrote: And the result i get from that query is this "Resource id #3".
That's because you're trying to output something that you can't output...
A PHP Resource is a special type of its own.

Posted: Sat Dec 03, 2005 3:03 pm
by djot
what about

Code: Select all

WHERE id=max(id)
??

Posted: Sat Dec 03, 2005 3:04 pm
by foobar
djot wrote:what about

Code: Select all

WHERE id=max(id)
??
You could do that to, I suppose. :)

Posted: Sat Dec 03, 2005 3:23 pm
by dru_nasty
I'm getting this error now "Invalid use of group function"

Here's the sql statement:

Code: Select all

$sql = "SELECT testField FROM testTable WHERE id=max(id)";

Posted: Sat Dec 03, 2005 3:29 pm
by foobar
Use my code then.

Posted: Sat Dec 03, 2005 3:35 pm
by dru_nasty
That did the trick, thanks so much. :D

Posted: Sat Dec 03, 2005 4:41 pm
by mickd
shouldnt it be something like

Code: Select all

$sql = "SELECT max(id) as id_max FROM testTable";
?

Posted: Sat Dec 03, 2005 7:24 pm
by John Cartwright
mickd wrote:shouldnt it be something like

Code: Select all

$sql = "SELECT max(id) as id_max FROM testTable";
?
yup.