SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP/MySQL Limit The Length of the Result

    Hi Guys,

    Im pretty new to using MySQL within PHP and have come across a problem. I wondered if it is possible to limit the number of characters or words displayed from a result.

    What I am trying to do is have a description which only displays the first say 50 words, the user can then click onto the item to view the full description!

    I am just not sure of what SQL code I need to use!

    I have tried searching the net, but the only results I get are to do with limiting the number of results returned, not limiting the length of the results themselves.

    Any help would be greatly appreciated!

    Regards

    Shaun

  2. #2
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cross browser css bugs

    Dan Schulz you will be missed

  3. #3
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have exactly what you want, view an example on my site:
    http://www.30.com.au/portfolio (like this page?)

    Let me grab my code for you, two secs
    :-)

  4. #4
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heres my code:

    PHP Code:
    <?echo substr($pf['description'],0,50);?>....(<a href="/portfolio/<?echo$pf['id'];?>/<?echo$pf['thumb'];?>" title="<?echo$pf['name'];?>" class="navy">more</a>)
    Basically, you grab your description field from your table, it starts at character 0 and ends at character 50, you can then code a page without the limit to display the whole description!

    This is a PHP function, not mySQL - for future reference, most things you do on your website that involve your database can probably be done with PHP functions.
    :-)

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2008
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LEFT mysql function

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i prefer to use SUBSTRING_INDEX to pull, say, the first 20 full words (so as not to break off in mid-word)

    SELECT SUBSTRING_INDEX(somecolumn, ' ', 20) ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you don't want to cut words in half, take a look at wordwrap.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dyfuCa View Post
    most things you do on your website that involve your database can mainly be done with PHP functions.
    except for those things which require sending a lot of data to the php client and then using only some of it

    like returning only the latest entry in a blog (instead of all entries), computing the number of entries in each category (instead of counting them with SQL), sending an entire TEXT column value when you only want the first 20 words, etc.

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    except for those things which require sending a lot of data to the php client and then using only some of it

    like returning only the latest entry in a blog (instead of all entries), computing the number of entries in each category (instead of counting them with SQL), sending an entire TEXT column value when you only want the first 20 words, etc.

    Of course, but he was asking for an SQL command to do things that PHP can already do, that way if he uses substr, if he wants to do it on the same query more than once, he doesn't have to use multiple queries
    :-)

  10. #10
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i prefer to use SUBSTRING_INDEX to pull, say, the first 20 full words (so as not to break off in mid-word)

    SELECT SUBSTRING_INDEX(somecolumn, ' ', 20) ...
    Hm, sounds like a better solution than substr. Happen to know if this is just specific to MySQL or do other DBMS's have a similar implementation?
    Cross browser css bugs

    Dan Schulz you will be missed

  11. #11
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SoulScratch View Post
    Hm, sounds like a better solution than substr. Happen to know if this is just specific to MySQL or do other DBMS's have a similar implementation?
    Its a good solution, but what if you wanted to show the description field three times, with a different amount of text each time? Wouldn't it be easier to use substr on each result?
    :-)

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SoulScratch View Post
    Happen to know if this is just specific to MySQL or do other DBMS's have a similar implementation?
    yes, just mysql

    in other databases, i would use SUBSTRING (which is standard SQL) to grab 113 characters and then trim from the right until i hit a space


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dyfuCa View Post
    what if you wanted to show the description field three times, with a different amount of text each time?
    if you can find an example page on teh interwebs which does this, then sure

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you can find an example page on teh interwebs which does this, then sure

    I'm just saying, say someone wanted:

    Description Part 1
    Show first 50 of description


    Description Part 2
    Show second 50 of description


    Description Part 3
    Show third 50 of description

    Say they wanted to split text up into 3 tables, instead of using 3 queries, they could use 3 functions, eliminated lines of code and query time.
    :-)

  15. #15
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dyfuCa View Post
    I'm just saying, say someone wanted:

    Description Part 1
    Show first 50 of description


    Description Part 2
    Show second 50 of description


    Description Part 3
    Show third 50 of description

    Say they wanted to split text up into 3 tables, instead of using 3 queries, they could use 3 functions, eliminated lines of code and query time.
    Well - I don't think it's that common and surely inconsistent compared to grabbing words from the start.
    Cross browser css bugs

    Dan Schulz you will be missed

  16. #16
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your fast responses!

    I have implemented the 'substr' option and it works great!

    I knew it would be something rather simple!

    You can see it in action here:

    http://www.glenlucecaravansales.co.uk/dev

    (Excuse the styling on the pop-up windows, I ain't got that far yet)

    Again, Thank you very much!

    Shaun

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,267
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dyfuCa View Post
    I'm just saying
    i know, and i was asking for an example page
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by sb44940 View Post
    Thank you for your fast responses!

    I have implemented the 'substr' option and it works great!

    I knew it would be something rather simple!

    You can see it in action here:

    http://www.glenlucecaravansales.co.uk/dev

    (Excuse the styling on the pop-up windows, I ain't got that far yet)

    Again, Thank you very much!

    Shaun
    You really should try r937's solution. Right now you're truncating in the middle of a word, and that's ugly.

  19. #19
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A lot of people that would read the description would get to the end and click more anyway, never mind if the last word has completed or not.
    :-)


Bookmarks

Posting Permissions

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