How to Create Blog Excerpts with PHP
videos

How to Create Blog Excerpts with PHP

on Jul 22nd 2010 with 50 comments
Tutorial Details
  • Topics Discussed: MySQL, PHP
  • Difficulty: Intermediate
  • Tut Format: 15 Minute Video
Download Source Files

Ever visited a blog, and noticed that each posting’s overview only contained a hundred characters or so, which is then followed by “…”? Ever wondered how to do that yourself? It’s not quite as easy as you might think. What if character number 100 occurs in the middle of a word? Do you want to display half of a word on the page? Of course not. In this video tutorial, I’ll show you how to compensate for this, as well as a host of other neat little things, like working with MySQL’s LEFT() function, using PHP’s strrpos, and substr, creating the beginning’s of an actual blog, and more.

What You’ll Learn

  • Use MySQL’s LEFT() function to create temporary columns
  • Use PHP prepared statements to query the database
  • How to create an “excerpts” section for your blog
  • PHP’s strrpos and substr functions
  • The process of creating the beginnings of a blog

Premium Members: Download this Video ( Must be logged in)

 
Enjoyed this Post?

Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!

By

Rollover to read this author's bio or click through to see a full list of posts by this author.

I'm the editor of Nettuts+, and love making the process of building for the web as easy as possible. Also, I'm finishing up my latest book on Laravel Testing! If you want be friends, say hi on Twitter.  

Plus Premium
 
 
Note: Want to add some source code? Type <pre><code> before it and </code></pre> after it. Find out more
  • http://www.ecustom.ca/ Damon Bridges

    This is so cool! Thanks Jeffrey, bookmarked for later.

  • Joris

    Heey great tutorial!
    Could you also go a little deeper into the subject and add things such as pagination and post editing?

    • http://wurtzy.com Chandler

      That sounds like a premium tutorial.

      • luke

        Lies..

        Epic…

        Lies…

        It SMELLS like a premium tutorial.

        Hearing is so last century.

        Noses are all the rage.

  • http://wurtzy.com Matt Chandler

    Is there a performance benefit for using SQL’s LEFT like this? Couldn’t you have substr the body into a new excerpt variable? Or, would it be faster to just store the excerpt in the database so you’re not running all these string functions every run?

    • http://www.arvag.net GaVrA

      I would think that its faster to run strpos(or what was the function) on string that has 100 chars, then the one which have 1000.

  • eM.

    Or using MySQL substring function?

  • Brad

    Excellent tut Jeffery. This is scary easy to do in codeigniter but I never knew how to do it in straight PHP. Nor had I known of the left usage in mysql.

    Thanks!

  • http://www.monitojoomla.com/ jmarreros

    What about using wordwrap() PHP function?

    • David

      I use wordwrap often. Since I never use monospace fonts, words are all different lengths, so counting letters never gets you equal-length excerpts anyway.

  • KanZonk

    Very simple & usefull tutorial….

    It’s basic, but very important for everyone to know about technique programming using PHP. Yeah, nice job jeff.

  • http://www.facebook.com/pages/Pinoy-How-To/88906659020?v=app_7146470109&ref=ts xbiboy

    nice! simple and very informative more.. more ..

  • http://www.art-coder.com Eugene

    I just wonder will the LEFT function prevent caching of mysql query. If yes, then wouldn’t it be better to parse the result with PHP?

  • w1sh

    Love it JW. Thanks for another excellent screencast.

  • http://prop-14.com Randy

    Great tut Jeff. Hoping this isn’t a silly question[and slightly off this tut's scope] but, after following the OOP tuts, I am wondering how you are accessing methods from the class mysqli with $mysql -> prepare? (no “i” in mysql).

    • Robert

      It’s because he has assigned an instance of the mysqli class (which he created with the line “$mysql = new mysqli) to the variable $mysql. This variable is then able to access the methods (functions), and properties (variables) of the mysqli class.

      • http://prop-14.com Randy

        Agh! I forgot that you assign a new instance to its own variable! Thanks! I’ll get this OOP stuff sooner or later!

    • Rodolfo Palma

      That’s because the instance of the mysqli class is actually stored in the variable called $mysql :).

      • James

        In fact, if done properly, you are not binding an instance to a variable, instead, you create a reference point to that instance in the variable.

        So the variable itself is in fact empty. It only contains the reference to the instance of an object..

        And, while we`re at it, the first query to run on index.php is bad. Well, it`s not bad, its valid SQL, however the use of it is bad practice.

        Why bother to select the body if you are only going to use the id, title and excerpt? Why would you fetch the body as well?

        And in the case of caching, no, any query which creates fields inside a query ( using LEFT, RIGHT, AS in your initial request ) will not be cached.

        So, if you are going to use the excerpt, dumpt he body. It contains over 1000 characters of unused data, so no help there, it will not be cached ( so it has to execute the query every time ) etc.

        If you leave the LEFT function out, and decide to stick with the body, you can simply use the same functions.

        function present_excerpt($initial_count)
        {
        // initial count is a variable integer, so you can specify the max characters you want your initial
        // to present

        $excerpt = substr($body, 0, $initial_count);
        $lastIndex = strtrpos($excerpt, ‘ ‘);

        return substr($excerpt, 0, $lastIndex);
        }

  • http://www.vaxis.net Vasili

    Nice tutorial, LEFT in db queries is new for me, thanks.
    Hey Jeff, can you tell me what font are you using in netbeans? It’s seems like Cuourier New, but the default one on mac is unreadable.
    Thanks for asnwer.

  • http://juliazjournal.com Julia

    Are you psychic? Whenever I find myself struggling with something, you post a tutorial on it!

  • Chris

    Nice, this technique will be useful I’m sure.

  • Scott

    Would it be faster in your tutorials to use instead of for those single variable print outs?

    Good beginning tutorial for blog though. I didn’t know about the LEFT(). That will be useful.

  • http://twitter.com/erikneves Erik Neves

    SELECT SUBSTRING_INDEX(comment_col, ‘ ‘, 30) AS excerpt FROM comments_table

    This will select the first 30 words of a comment from the comments_table. Word is defined as something delimited by a space (‘ ‘). You can change 30 to something else that suits your needs.

  • http://www.ecustom.ca/ Damon Bridges

    Jeffrey, video can’t go full screen here, so, um, it’s hard to read.
    Can you fix, at all?

    • http://www.ecustom.ca/ Damon Bridges

      I decided to open up BOXEE, and I found that it’s full screen there, just the little full screen thing for whatever video hosting service this video uses wouldn’t work for me. :(

      But anyways, thanks for the great tut, it’s really informative. Also, just a question, is there any specific reason why you used Netbeans in this tut?

      • http://net.tutsplus.com Jeffrey Way

        Hey Damon – Why can’t you click on the full screen icon at the lower right portion of the video?

      • http://www.ecustom.ca/ Damon Bridges

        Jeffrey, it’s not that I can’t click it, it’s just that when I click it, the screen goes full, but the video still displays at the size it shows up as on the post. Hope that helps :).

  • http://99points.info Zeeshan Rasool

    awesome tutorial, thanks but just make video in full size ;)

  • http://bimtee.com Dwayne Clarke

    ammmm….inefficient!

    • Luis Milanese

      You can always make your tutorial and prove the rest of the world you really know how to program. :)

    • Ian

      Dwayne, your website’s blog is hideous and generic. If you’re incapable of even styling a website I certainly don’t think you’re in a position to be bad-mouthing Jeffrey’s code.

  • http://www.chrome-clan.de Saddy

    Hey Guys,

    Sorry for my bad English. I come from Germany.

    I have a problem:

    I view the Tutorial Video und make it so as in the Video looks. And it is good, but if you have a text that is not so long like 95 abc… the txt dont show. I write a little bit for this, but it dont work so right. Can you look it?

    Source Code:

    News

    prepare(“SELECT id, title, LEFT(body, 95) AS excerpt, body from news”) or die(‘There was a problem connection to the table’);
    $stmt->execute();
    $stmt->bind_result($id, $title, $excerpt, $body);

    while ($row =$stmt->fetch()) :
    $lastSpaceIndex = strrpos($excerpt, ‘ ‘);
    ?>

    <?php if($lastSpaceIndex

    <a href=
    "single.php?id=”>

    if echo $body show, themlast word of body dont show at the post in the browser.

  • Zaro

    thanks Jeffery !
    that was great tuto ;)

  • Jaspreet Singh

    Awesome.. Tut.. !!
    I am 3 years php exp.. developer.. but never used LEFT … so that was really new thing to learn!

  • http://www.liverunning.dk Jan

    Great tutorial, Jeffery!

    Thank you!

  • http://www.baidu.com baidu
  • http://www.circuitbomb.com Dustin

    This is a great tutorial, and funny because I did something rather similar for my blog re-dev, heres the code. Using this I do set a limit to the characters, but usually my excerpts will always be more than the limit, so I use a breakpoint similar to wordpress’ “more”. (oh yea im using it in codeigniter)

    function mod_result($string, $url) {

    $limit = 50;
    $break = ‘*/More/*’;
    $pad = ‘(Continue Reading)‘;

    if(strlen($string) <= $limit) {
    return $string;
    } elseif(false !== ($breakpoint = strpos($string, $break, $limit))) {
    if ($breakpoint < strlen($string) – 1) {
    $string = substr($string, 0, $breakpoint) . $pad;
    }
    }

    return $string;
    }

  • http://tejasluvs.tumblr.com/ Tejas Sali

    The tutorial is very helpful & used it in following way. Thanks!

    Original code can be found here: http://www.php.net/manual/en/function.strlen.php#49714

    function nicetrim ($string) {
    $MAX_LENGTH = 50;
    $str_to_count = html_entity_decode($string);
    if (strlen($str_to_count) <= $MAX_LENGTH) {
    return $string;
    }
    $s2 = substr($str_to_count, 0, $MAX_LENGTH);
    $spaceIndex=strrpos($s2, ‘ ‘);
    return htmlentities(substr($s2,0, $spaceIndex ).’…’);
    }

  • http://dagrevis.lv/ daGrevis

    Nice video. Simple, but useful… =)

    P.S. You forgot to close the paragraph tag! =D

  • Bobby

    I am pretty new with netbeans… can anyone help me on this video on 2:22, the author typed “prepared” then the php script pops out…. how can I implement so it works like in the video?

  • http://www.madphp.org/ Chris

    I realise this video is a few months old already, but here’s a quick way of doing an excerpt through SQL;

    SET @length=95;

    SELECT CONCAT( SUBSTRING( LEFT( `body`, @length ), 1, ( LENGTH( LEFT( `body`, @length ) ) – ( LOCATE( ‘ ‘, REVERSE( LEFT( `body`, @length ) ) ) ) ) ), ‘…’ ) AS `excerpt` FROM `posts` WHERE `id` = 1;

  • http://www.luneya.com Leonie

    Thank you for the great tut!
    I have been searching for a way to create excerpts and generated pages for the full post like this with news posts for a long time.

    Really appreciate the time you put into this! Been a bigggg help :D

    Thanks again!

  • Jeffrey

    He guys,

    i try it but the LEFT() function is not coming in PHPMyAdmin is set the columns id, title and body, and then change the SQL into: SELECT id, title, LEFT(body, 100) AS excerpt, body FROM posts; but nothings shows up only the 3 columns id, title and body can someone help me ?

  • http://dsullweb.com derek

    I tried this, and it didn’t work… here is a snippet of my code… I don’t get any errors, it just doesn’t show anything:

    $query = mysql_query(“SELECT id,cid,title,author,LEFT(body, 100),date FROM submissions ORDER BY id DESC”) or die (mysql_error());
    while ($fetch = mysql_fetch_array($query)) {
    extract($fetch);
    $end = strpos($body, ‘ ‘);
    $body = substr($body, 0, $end);
    echo ”.$title.’

    ‘.$body.’

    Posted by ‘.$author.’ On ‘.$date.’
    ‘.”\n”;
    }

  • http://www.level2d.com Philip Brown

    What we are trying to accomplish is to create an excerpt function to allow for tags within the excerpt. As with the tutorial, you must first remove all tags before creating the excerpt or you will be left with tags that are not closed or half a tag declaration. I’m planning on trying to tackle this either with PHP or javascript. Does anyone already have a solution? I’ve done some planning and seems to be quite difficult.

  • http://adizo.in Sachin

    Wow. It’s realy good.

  • http://www.youtube.com/watch?v=YD90AvZlGrs&feature=relmfu test

    Very intresting and powerfull material for web developers like me

  • http://www.eworldsms.net Aamir

    It’s a really very informative information. It’s very useful and knowledgeable for me. I will bookmark this page and come soon to see the updates.

  • watchthesimpsonsonline

    Perfect