Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Oct 2010
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Get last ID's from Mysql table

    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 Code:
        <?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.

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Should just be able to modify your query to order by the news id in descending order and set limit to 5 records.

    PHP Code:
    $q mysql_query("SELECT news_title, news_content FROM news ORDER BY news_id DESC LIMIT 5"); 
    Last edited by Spookster; 11-11-2011 at 01:54 AM.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Something along the lines of, (untested):

    Code:
        <?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.';
        }
    ?>

  • #4
    New Coder
    Join Date
    Oct 2010
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #5
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Line 55 of which file?

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

    PHP Code:
    <?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.';
    }

    ?>
    Last edited by MattF; 11-11-2011 at 03:58 AM.

  • #6
    New Coder
    Join Date
    Oct 2010
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #7
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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?

  • #8
    New Coder
    Join Date
    Oct 2010
    Posts
    21
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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 Code:
        <?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 Code:
        <?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:
    PHP Code:
    <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 Code:
            <?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.

  • #9
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by kickthat View Post
    Unfortunately it's still not working. I'm not getting this error :

    Unknown column 'ID' in 'order clause'
    That ones fairly self-explanatory. Change:

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

    Code:
    '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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •