How do I display only the last row in a SQL db result?

Codes here !

Moderators: macek, egami, gesf

alexjhobbs
New php-forum User
New php-forum User
Posts: 4
Joined: Fri Apr 04, 2003 12:04 am
Location: Noosa, Queensland, AUS
Contact:

How do I display only the last row in a SQL db result?

Postby alexjhobbs » Fri Apr 04, 2003 12:09 am

Hi, I'm trying to get a page to display the latest newsletter in a database. The newsletter is generated by the client.

The client has the ability to delete newsletters so incremental ID settings in SQL go out the window.

Is there a way I can use the mysql_num_rows command to isolate the last row in a DB result to display the most recent newsletter (ie the most recent entry in the table).

any other suggestions are welcome I've tried everything. 8O

Thanks, Alex.

User avatar
mammal
New php-forum User
New php-forum User
Posts: 37
Joined: Wed Apr 02, 2003 1:31 am
Location: Hythe, UK

Postby mammal » Fri Apr 04, 2003 12:35 am

This might be a complicated way of doing this but you could do two SQL queries. One to find the last unique id and then the next to search for the rest of the details under that ID number:

<?

// Replace the word "unique_id" with whatever your id label is for that column.
$query = "SELECT MAX(unique_id) FROM newsletters LIMIT 1";
$result = mysql_query ($query);
$row = mysql_fetch_array ($result);

$query2 = "SELECT * FROM newsletters WHERE unique_id = '$row[unique_id]'";
$result2 = mysql_query ($query2);

?>

There may be a way of doing it using only one query but hey, this will work.

alexjhobbs
New php-forum User
New php-forum User
Posts: 4
Joined: Fri Apr 04, 2003 12:04 am
Location: Noosa, Queensland, AUS
Contact:

Postby alexjhobbs » Fri Apr 04, 2003 1:15 am

Thanks dude, doesn't work though - getting this problem:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource


Here's the code :

<?php
$query = "SELECT MAX(unique_id) FROM $table2 LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

$query2 = "SELECT * FROM $table2 WHERE unique_id = '$row[unique_id]'";
$result2 = mysql_query ($query2);
$body = stripslashes($result2->content);
print "<link rel=stylesheet href=$url/style.css type=text/css>$body<br><br><div align=center><a href=$url2>Home</a> | <a href=$url/newsletter/archive.php>Newsletter Archive</a> </div>";
mysql_close($link);
?>

Is it something I'm doing?

alexjhobbs
New php-forum User
New php-forum User
Posts: 4
Joined: Fri Apr 04, 2003 12:04 am
Location: Noosa, Queensland, AUS
Contact:

Whoops, sorry missing the unique_id bit

Postby alexjhobbs » Fri Apr 04, 2003 1:16 am

It's been a long day - missed the bit about replacing unique_id, I'l try it again.

Alex.

alexjhobbs
New php-forum User
New php-forum User
Posts: 4
Joined: Fri Apr 04, 2003 12:04 am
Location: Noosa, Queensland, AUS
Contact:

Top stuff, that works fine

Postby alexjhobbs » Fri Apr 04, 2003 1:46 am

Thanks very much!

User avatar
mammal
New php-forum User
New php-forum User
Posts: 37
Joined: Wed Apr 02, 2003 1:31 am
Location: Hythe, UK

Postby mammal » Fri Apr 04, 2003 1:47 am

No worries...

User avatar
Redcircle
Moderator
Moderator
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA
Contact:

Postby Redcircle » Fri Apr 04, 2003 6:28 am

if you are using auto_increment right after an insert you can get the last inserted id by using

$id = mysql_insert_id();

then you will need to make a new query and use $id


Return to “mySQL & php coding”

Who is online

Users browsing this forum: Bing [Bot] and 17 guests