PHP/MySQL Limit The Length of the Result

**sb44940** · Feb 2, 2009, 08:00

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

**SoulScratch** · Feb 2, 2009, 08:02

http://www.php.net/substr

**Latox** · Feb 2, 2009, 08:02

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

**Latox** · Feb 2, 2009, 08:04

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.

**kuzmanin** · Feb 2, 2009, 08:07

LEFT mysql function

**r937** · Feb 2, 2009, 08:08

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) ...

**guido2004** · Feb 2, 2009, 08:08

If you don't want to cut words in half, take a look at wordwrap.

**r937** · Feb 2, 2009, 08:10

Originally Posted by dyfuCa

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.

**Latox** · Feb 2, 2009, 08:12

Originally Posted by r937

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

**SoulScratch** · Feb 2, 2009, 08:13

Originally Posted by r937

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?

**Latox** · Feb 2, 2009, 08:14

Originally Posted by SoulScratch

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?

**r937** · Feb 2, 2009, 08:18

Originally Posted by SoulScratch

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

**r937** · Feb 2, 2009, 08:19

Originally Posted by dyfuCa

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

**Latox** · Feb 2, 2009, 08:23

Originally Posted by r937

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.

**SoulScratch** · Feb 2, 2009, 08:26

Originally Posted by dyfuCa

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.

**sb44940** · Feb 2, 2009, 08:27

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

**r937** · Feb 2, 2009, 08:29

Originally Posted by dyfuCa

I'm just saying

i know, and i was asking for an example page

**guido2004** · Feb 2, 2009, 08:33

Originally Posted by sb44940

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.

**Latox** · Feb 2, 2009, 08:36

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.

The SitePoint Forums have moved.

User Tag List

Thread: PHP/MySQL Limit The Length of the Result

Thread Tools

Display

PHP/MySQL Limit The Length of the Result

Bookmarks

Bookmarks

Posting Permissions