Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

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

Codes here !

Moderators: macek, egami, gesf

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.
alexjhobbs
New php-forum User
New php-forum User
 
Posts: 4
Joined: Fri Apr 04, 2003 12:04 am
Location: Noosa, Queensland, AUS

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.
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 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

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

Top stuff, that works fine

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

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

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

No worries...
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 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
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA


Return to mySQL & php coding

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.