...

View Full Version : Get last ID's from Mysql table



kickthat
11-11-2011, 01:38 AM
Hi,

This may be a little basic.
I am very new to php, but am in the process of editing a very simple CMS template.

I have set up a News page linked to a Mysql database. Using the backend in the CMS I can add new content to the database.

However, I want to set up a News Feed and News page.

I need the feed to display the newsTitle value of the last 5 published news items.

I need the News page to show the newsTitle and the newsContent of the last 5 published items.

This is what existed in the template to call data for the pages created and place it in the front end:


<?php
//if no page clicked on load home page default to it of 1
if(!isset($_GET['p'])){
$q = mysql_query("SELECT * FROM news WHERE newsID='1'");
} else { //load requested page based on the id
$id = $_GET['p']; //get the requested id
$id = mysql_real_escape_string($id); //make it safe for database use
$q = mysql_query("SELECT * FROM pages WHERE newsID='$id'");
}

//get page data from database and create an object
$r = mysql_fetch_object($q);

//print the pages content
echo "<h1>$r->newsTitle</h2>";
echo $r->newsCont;
?>


I have been trying to edit that, but I do not know how to make it call the last 5 ID's without manually entering them, which defeats the point of creating a CMS for ease.

Can this code be edited for this purpose, or do I need to try a different method?

Any help would be massively appreciated.

Spookster
11-11-2011, 01:49 AM
Should just be able to modify your query to order by the news id in descending order and set limit to 5 records.



$q = mysql_query("SELECT news_title, news_content FROM news ORDER BY news_id DESC LIMIT 5");

MattF
11-11-2011, 01:49 AM
Something along the lines of, (untested):



<?php
//if no page clicked on load home page default to it of 1
if(!isset($_GET['p'])){
$q = mysql_query("SELECT * FROM news ORDER BY id DESC LIMIT 0,5");
} else { //load requested page based on the id
$id = $_GET['p']; //get the requested id
$id = mysql_real_escape_string($id); //make it safe for database use
$q = mysql_query("SELECT * FROM pages WHERE newsID='$id'");
}

if (mysql_num_rows($q))
{
while($r = mysql_fetch_assoc($q))
{
//print the pages content
echo "<h2>$r['newsTitle']</h2>";
echo $r['newsCont'];
}
}
else
{
//print the pages content
echo "<h2>No news</h2>";
echo 'No news items available.';
}
?>

kickthat
11-11-2011, 02:04 AM
Thank you both for the responses.

Spookster, what you have suggested has done the trick in terms of pulling the last ID. However, it is ONLY pulling the last one.

MattF, what you suggested is returning the following error.

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /domain on line 55

MattF
11-11-2011, 02:20 AM
Line 55 of which file?

Can't see any syntax error in that code, but try this:



<?php

if (!isset($_GET['p']))
{
$q = mysql_query('SELECT * FROM news ORDER BY id DESC LIMIT 0,5') or exit(mysql_error());
}
else
{ //load requested page based on the id
$id = $_GET['p']; //get the requested id
$id = mysql_real_escape_string($id); //make it safe for database use

$q = mysql_query("SELECT * FROM pages WHERE newsID='$id'") or exit(mysql_error());
}

if (mysql_num_rows($q))
{
while ($r = mysql_fetch_object($q))
{
//print the pages content
echo '<h2>'.$r->newsTitle.'</h2>';
echo $r->newsCont;
}
}
else
{
//print no content
echo '<h2>No news</h2>';
echo 'No news items available.';
}

?>

kickthat
11-11-2011, 02:42 AM
That returns and error with this line

if (mysql_num_rows($q))

The error being

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /news.php on line 54

MattF
11-11-2011, 03:59 AM
Try the code above again. I've made a slight alteration. Btw, is that file included in another script, or are you only posting partial code?

kickthat
11-11-2011, 02:12 PM
Unfortunately it's still not working. I'm not getting this error :

Unknown column 'ID' in 'order clause'

This section is within a page (.php).

It was originally calling the page title and page content. I created duplicates of all pages and another database table which basically substituted page for news.

I've taken a slightly different approach. The CMS automatically sets created pages as links in the navigation. Instead of using the code that the pages use to call the content, I have used the code to create these links.

I have adapted this:


<?php
//get the rest of the pages
$sql = mysql_query("SELECT * FROM pages WHERE isRoot='1' ORDER BY pageID");
while ($row = mysql_fetch_object($sql))
{
echo "<li><a href=\"".DIR."?p=$row->pageID\">$row->pageTitle</a></li>";
}
?>


To this:


<?php
//get the rest of the pages
$sql = mysql_query("SELECT * FROM news WHERE isRoot='1' ORDER BY newsID DESC LIMIT 5");
while ($row = mysql_fetch_object($sql))
{
echo "<li><a href=\"".DIR."?p=$row->newsID\">$row->newsTitle</a><br>$row->newsCont</li>";
}
?>


This displays both the title of the news item and its content in a list and restricts it to the last 5 entries.

Removing the section:

<br>$row->newsCont

Will allow me to show just the feed of news titles.

I have then used the following code to show the news items on their own page when clicked:


<?php
//if no page clicked on load home page default to it of 1
if(!isset($_GET['p'])){
$q = mysql_query("SELECT * FROM news WHERE newsID='1'");
} else { //load requested page based on the id
$id = $_GET['p']; //get the requested id
$id = mysql_real_escape_string($id); //make it safe for database use
$q = mysql_query("SELECT * FROM news WHERE newsID='$id'");
}

//get page data from database and create an object
$r = mysql_fetch_object($q);

//print the pages content
echo "<h1>$r->newsTitle</h2>";
echo $r->newsCont;
?>


I think that should have done it. Massive thanks for the help. Any thoughts on this? Like I say I'm really not all that sure with php so it's likely this is a faily bodged job.

MattF
11-11-2011, 07:36 PM
Unfortunately it's still not working. I'm not getting this error :

Unknown column 'ID' in 'order clause'

That ones fairly self-explanatory. Change:



'SELECT * FROM news ORDER BY id DESC LIMIT 0,5'


to:



'SELECT * FROM news ORDER BY newsID DESC LIMIT 0,5'


I must have changed that bit for some unknown, (even to me), reason. Do start double checking code before blindly using it, btw. Example code is exactly what the term implies.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum