...

View Full Version : Displaying Last 5 Records



ttttt
11-29-2006, 06:56 PM
Hi,
I have a database which contains the articles on the front page in it. Well, to save clogging up the front page, I would like to display only the latest 5 articles in the table. The articles have an id field which could be ordered descending, but from then on, I have no idea what to do.
I would appreciate any help,
Thanks,

Linark
11-29-2006, 07:19 PM
SELECT * FROM <%YOUR_TABLE%> LIMIT 5 ORDER BY desc

Should do the trick

Tyree
11-29-2006, 07:22 PM
Oh well, beat me to it. :thumbsup:

ttttt
11-29-2006, 07:23 PM
Wow! Thanks, could you also tell me how I list the items using this query?
Thanks again,

Tyree
11-29-2006, 07:31 PM
$query = "SELECT * FROM <%YOUR_TABLE%> LIMIT 5 ORDER BY desc";

while ($row = mysql_fetch_assoc($query))
{
$lastFiveRecords[] = $row;
}




foreach($lastFiveRecords AS $record) {
print $record['data'];
}


'data' being the name of a column in your db table

ttttt
11-29-2006, 08:32 PM
That doesn't work for me. Can you help? (I get this error:mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource )
Thanks,

Tyree
11-29-2006, 08:42 PM
Sorry, I use a function to perform my queries. I forgot a line...


$query = "SELECT * FROM <%YOUR_TABLE%> LIMIT 5 ORDER BY desc";

$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result))
{
$lastFiveRecords[] = $row;
}

ttttt
11-29-2006, 08:48 PM
I now get the same error and :
Invalid argument supplied for foreach()
My full code, if it helps is:


<?php
//compression

//include settings
include('settings.php');
//set variables
$id = $_REQUEST['id'];
$action = $_REQUEST['action'];
//db connect
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
//db queries
//id query
//check if id is numeric
if(isset($id))
{
if(is_numeric($id))
{
$select_id = mysql_query("SELECT * FROM front_page WHERE id=$id ORDER BY id");
$echo_id = mysql_fetch_array($select_id);
}
//is not numeric, do NOTHING
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="eng-GB">
<head>
<!-- site title and page name -->
<title>Versa-Soft - Home Page</title>
<!-- meta tags -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<meta name="keywords" content="software,website,versatile,open-source,free,forums,forum,blog,site,versa-soft,download"/>
<meta name="description" content="Free/Open source software, designed to make the web a cleaner and more versatile place."/>
<meta name="ROBOTS" content="ALL"/>
<!-- include styles and favicon -->
<link href="style.css" rel="stylesheet" type="text/css"/>
<link rel="shortcut icon" href="images/favicon.ico"/>
</head>
<body>
<div class="margin">
<div class="container">
<div class="header">
<div class="headerimg"> <img class="header" src="images/header.jpg" alt="Versa-Soft.com" height="150px" width="100%"/> </div>
<div class="top_menu">
<!-- Top menu could go here -->
</div>
</div>
<div class="left_column">
<div class="main_menu">
<h4 class="navigation">Navigation</h4>
<ul class="main_menu">
<li class="main_menu"><a href="/index.php">Home</a></li>
<li class="main_menu"><a href="/forum">Forum</a></li>
<li class="main_menu"><a href="/news">News</a></li>
<li class="main_menu"><a href="/software">Software Home</a></li>
<li class="main_menu"><a href="/downloads.php">Downloads</a></li>
<li class="main_menu"><a href="/docs.php">Documentation</a></li>
<li class="main_menu"><a href="/support.php">Support</a></li>
<li class="main_menu"><a href="/rss.php">RSS</a></li>
<li class="main_menu"><a href="/info.php">Information</a></li>
<li class="main_menu"><a href="/contact.php">Contact Us</a></li>
<li class="main_menu"><a href="/about.php">About</a></li>
</ul>
</div>
<div class="login">
<h4 class="login">Login</h4>
<p>Coming Soon...</p>
</div>
<div class="syndication">
<h4 class="syndication">Syndication</h4>
<ul class="syndication">
<li class="syndication"><a href="/xml/rss.xml">RSS</a></li>
<li class="syndication"><a href="/xml/rss.xml">XML</a></li>
</ul>
</div>
<div class="newsbox">
<h4 class="newsbox">Newsbox</h4>
<ol class="newsitems">
<li class="newsitems"></li>
<li class="newsitems"></li>
<li class="newsitems"></li>
<li class="newsitems"></li>
<li class="newsitems"></li>
</ol>
</div>
</div>
<div class="right_column">
<div class="title">
<h1 class="title">
<?php
//if site is under construction, display notice
if($under_construction == '1')
{
echo $construction_title;
}
//check if id is set
else if(isset($id))
{
//check if id is numeric
if(is_numeric($id))
{
echo $echo_id['title'];
}
//is not numeric, display not found
else
{
echo('Record Not Found');
}
}
//otherwise, display the front page
?>
</h1>
</div>
<div class="content">
<p class="content">
<?php
//if site is under construction, display notice
if($under_construction == '1')
{
echo $construction_notice;
}
//check if id is set
else if(isset($id))
{
//check if id is numeric
if(is_numeric($id))
{
echo $echo_id['body'];
}
//is not numeric, display not found
else
{
echo('The id you have entered (');echo $id; echo(') could not be found. This could be because it is not numeric, or because of a dodgy link, or the record just not existing.');
}
}
//check if action is set
else if(isset($action))
{
//discover what kind of action
echo('action');
}
//otherwise, display the front page
else
{
$query = "SELECT * FROM front_page LIMIT 5 ORDER BY id desc";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$lastFiveRecords[] = $row;
}
foreach($lastFiveRecords AS $record) {
echo $record['title'];
}
}
?></p>
<p class="notices"></p>
</div>
</div>
<div class="clear"></div>
<div class="footer">
<div class="copyright">
<p class="copyright"><a href="copyright.php"><?php echo $copyright_notice; ?></a></p>
</div>
<div class="badges"> </div>
</div>
</div>
</div>
</body>
</html>

Thanks,

Tyree
11-29-2006, 09:11 PM
I see you must have your database login into in that settings file, are you successfully logging in to your server?

No queries are going to work if you're not connected to your database to begin with.

Secondly, the foreach() I gave you was just simply to show how you could get the info out of the array. You'll probably want to do some formatting of the data you spew out with the foreach(). Right now, all it's going to do is list the data out across the page. I'm assuming you want to put it in a table or something.

Here's what I did and it's working on mine with my database:


$query = "SELECT * FROM News ORDER BY ID DESC LIMIT 5";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$lastFiveRecords[] = $row;
}
foreach($lastFiveRecords AS $record) {
print $record['ID']."<br />";
}

Lee Stevens
11-30-2006, 02:09 AM
Quick hint dont have to double echo ;) only silly but hey:


echo('The id you have entered ('.$id.') could not be found. This could be because it is not numeric, or because of a dodgy link, or the record just not existing.');

ttttt
11-30-2006, 05:00 PM
Well, I now get a really strange error, displayed here:
http://versa-soft.com/
Thanks,

Tyree
11-30-2006, 05:22 PM
I don't see an error. Are you talking about the "12UT A" going down the page? Is that where your "last 5" should be showing?

Post your code again so I can see what you changed.

ttttt
11-30-2006, 08:17 PM
Are you talking about the "12UT A" going down the page?
Yes, exactly.


<?php
//compression

//include settings
include('settings.php');
//set variables
$id = $_REQUEST['id'];
$action = $_REQUEST['action'];
//db connect
mysql_connect($db_host, $db_user, $db_pass);
mysql_select_db($db_name);
//db queries
//id query
//check if id is numeric
if(isset($id))
{
if(is_numeric($id))
{
//$select_id = mysql_query("SELECT * FROM front_page WHERE id=$id ORDER BY id");
//$echo_id = mysql_fetch_array($select_id);
}
//is not numeric, do NOTHING
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="eng-GB">
<head>
<!-- site title and page name -->
<title>Versa-Soft - Home Page</title>
<!-- meta tags -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<meta name="keywords" content="software,website,versatile,open-source,free,forums,forum,blog,site,versa-soft,download"/>
<meta name="description" content="Free/Open source software, designed to make the web a cleaner and more versatile place."/>
<meta name="ROBOTS" content="ALL"/>
<!-- include styles and favicon -->
<link href="style.css" rel="stylesheet" type="text/css"/>
<link rel="shortcut icon" href="images/favicon.ico"/>
</head>
<body>
<div class="margin">
<div class="container">
<div class="header">
<div class="headerimg"> <img class="header" src="images/header.jpg" alt="Versa-Soft.com" height="150px" width="100%"/> </div>
<div class="top_menu">
<!-- Top menu could go here -->
</div>
</div>
<div class="left_column">
<div class="main_menu">
<h4 class="navigation">Navigation</h4>
<ul class="main_menu">
<li class="main_menu"><a href="/index.php">Home</a></li>
<li class="main_menu"><a href="/forum">Forum</a></li>
<li class="main_menu"><a href="/news">News</a></li>
<li class="main_menu"><a href="/software">Software Home</a></li>
<li class="main_menu"><a href="/downloads.php">Downloads</a></li>
<li class="main_menu"><a href="/docs.php">Documentation</a></li>
<li class="main_menu"><a href="/support.php">Support</a></li>
<li class="main_menu"><a href="/rss.php">RSS</a></li>
<li class="main_menu"><a href="/info.php">Information</a></li>
<li class="main_menu"><a href="/contact.php">Contact Us</a></li>
<li class="main_menu"><a href="/about.php">About</a></li>
</ul>
</div>
<div class="login">
<h4 class="login">Login</h4>
<p>Coming Soon...</p>
</div>
<div class="syndication">
<h4 class="syndication">Syndication</h4>
<ul class="syndication">
<li class="syndication"><a href="/xml/rss.xml">RSS</a></li>
<li class="syndication"><a href="/xml/rss.xml">XML</a></li>
</ul>
</div>
<div class="newsbox">
<h4 class="newsbox">Newsbox</h4>
<ol class="newsitems">
<li class="newsitems"></li>
<li class="newsitems"></li>
<li class="newsitems"></li>
<li class="newsitems"></li>
<li class="newsitems"></li>
</ol>
</div>
</div>
<div class="right_column">
<div class="title">
<h1 class="title">
<?php
//if site is under construction, display notice
if($under_construction == '1')
{
echo $construction_title;
}
//check if id is set
else if(isset($id))
{
//check if id is numeric
if(is_numeric($id))
{
echo $echo_id['title'];
}
//is not numeric, display not found
else
{
echo('Record Not Found');
}
}
//otherwise, display the front page
?>
</h1>
</div>
<div class="content">
<p class="content">
<?php
//if site is under construction, display notice
if($under_construction == '1')
{
echo $construction_notice;
}
//check if id is set
else if(isset($id))
{
//check if id is numeric
if(is_numeric($id))
{
echo $echo_id['body'];
}
//is not numeric, display not found
else
{
echo('The id you have entered (');echo $id; echo(') could not be found. This could be because it is not numeric, or because of a dodgy link, or the record just not existing.');
}
}
//check if action is set
else if(isset($action))
{
//discover what kind of action
echo('action');
}
//otherwise, display the front page


// problems here !!!!!!!



else
{
$query = "SELECT * FROM front_page ORDER BY ID DESC LIMIT 5";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result))
{
$records = $row;
}
foreach ($records AS $record) {
echo $record[title]."<br />";
}
}
?></p>
<p class="notices"></p>
</div>
</div>
<div class="clear"></div>
<div class="footer">
<div class="copyright">
<p class="copyright"><a href="copyright.php"><?php echo $copyright_notice; ?></a></p>
</div>
<div class="badges"> </div>
</div>
</div>
</div>
</body>
</html>

Linark
11-30-2006, 08:48 PM
Is that the first letter(s) to your database row title(s)?

Tyree
11-30-2006, 09:07 PM
First thing I see is this:


$records = $row;

Should be...


$records[] = $row;

That makes $records an array.

Still, you're not getting an error. You're just not getting what you expected. You're VERY close! :)

Tyree
12-01-2006, 12:01 AM
Okay, I took your entire page and put it on my server, switched out my db login and my table name, fixed the while statement and voila:
http://tyreeonline.com/tests/ttttt/

Those are indeed the last 5 titles to my news posts.

So, you should be able to do this:


$query = "SELECT * FROM front_page ORDER BY ID DESC LIMIT 5";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$lastFiveRecords[] = $row;
}
foreach($lastFiveRecords AS $record) {
print $record['title']."<br />";
}


Also, when you specify "$record['title']"..."title" has to be in the quotes. You were missing that.

The code above should work prefectly for you provided your table references are correct...i.e. your table name is "front_page" and the column you're calling is "title". Case sensitive!

Good luck!

ttttt
12-02-2006, 10:34 AM
Well, that now works, but the id (?id=72) selecting (earlier in te script) now doesn't.
Thanks so far!

Tyree
12-02-2006, 12:00 PM
Okay, but that's a separate issue, right? We've got the "last 5" going. So, one thing down.

Are you speaking of this line in your code?


if(isset($id))
{
if(is_numeric($id))
{
$select_id = mysql_query("SELECT * FROM front_page WHERE id=$id ORDER BY id");
$echo_id = mysql_fetch_array($select_id);
}
//is not numeric, do NOTHING
}


So, you're passing the $id var from a form on a previous page?

At first glance I don't see what's wrong. I'll be gone most of the day today, but, if you can wait, or if someone else doesn't answer in the meantime, I'll work on it as soon as I get back tonight.

Later!

ttttt
12-02-2006, 03:08 PM
Yes that's the line. It did work before, but not now.
I can post my code again if you need me to.
Thanks a lot, I really appreciate it,

ttttt
12-02-2006, 03:21 PM
Don't worry. I've got it working, turns out I commented out the query :o .
Thanks for all of your help.

Tyree
12-04-2006, 01:02 PM
Ah sweet! I'm glad it was something that simple. My next request was for you to repost your code. I couldn't see anything in your original code post that would screw it up!!! :D

Later!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum