Member Avatar for antwan1986

Hi everyone and thanks for reading. I've been making a news section for a client's site and now I'm trying to make a little box for the front page to show the five latest stories. Everything is fine in that aspect; I know about using the LIMIT keyword in the SQL statement to only bring back a few stories, but what I want to do is limit the amount of words brought back; a teaser so to speak.

How would I go about limiting the amount of words in the 'description' field (as an example) to 20 or so? I'd then want to append a "..." onto the end and then have a link which says Read More.

Thanks in advance,


Anthony

  • 4 Contributors
  • 14 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by nav33n
Member Avatar for buddylee17

Use substr. I hate to repost this so here's the link. Good luck

Member Avatar for antwan1986

Use substr. I hate to repost this so here's the link. Good luck

Thanks very much, this seems to do characters which is fine but I'd have for a word to cut off, is there any way the function can be adapted to pick up words rather than characters?

Member Avatar for nav33n 
select SUBSTRING_INDEX(description," ",3) from table [where condition];

description = column name
" " = delimiter
3 = count
For example, if we apply the above query to the following text,

This is an example of substring_index.

it returns,

This is an

Umm.. Clear enough ?

Member Avatar for buddylee17

So it's counting the space in between the words each time? Never thought of doing it this way. Great approach!

Member Avatar for nav33n

Yep.. :)

Member Avatar for DGStudios

Another option is to just get the information either the substr version from SQL ore the full length. and simply use the wordwrap function built into php that will break only at a space.

http://www.php.net/manual/en/function.wordwrap.php

You can find the documentation for that function at that link on php.net.

If you're not worried about trailing characters then substr is fine as it is a character based function it counts out that many characters and cuts off. However, wordwrap intelligently finding a space within your length and returns that string from the function call, this is a much better function to use when your looking for asthetics within the page as you don't have the problem with characters being there that aren't whole words.

DGStudios

Member Avatar for antwan1986 
select SUBSTRING_INDEX(description," ",3) from table [where condition];

description = column name
" " = delimiter
3 = count
For example, if we apply the above query to the following text,

it returns,


Umm.. Clear enough ?

I have tried your code but I'm not able to get it working. 'tips' is the name of my database and 'tip' is the name of the field in the database that I want to select only ten words from. Here is my attempt:

<?php
$connection = mysql_connect("***", "***", "***");
if (!$connection) { die("The Database connection failed. " . "<br />" . mysql_error()); }
		
$database_select = mysql_select_db("***",$connection);
if (!$database_select) { die("The Database selection failed. " . "<br />" . mysql_error());	}

$result = mysql_query("SELECT SUBSTRING_INDEX(tip,' ',8) FROM tips", $connection);
if (!$result) { die("The Database query failed. " . "<br />" . mysql_error()); }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP: Limiting Number Of Words From MySQL</title>
</head>

<body>
<?php
  while ($row = mysql_fetch_array($result)) {
    echo $row["tip"];
  }
?>
</body>
</html>

If I change the SQL statement to:

$result = mysql_query("SELECT * FROM tips WHERE tip_id = 1", $connection);

then it works fine, so I know the problem is narrowed down specifically to the SQL function.

Member Avatar for nav33n

Hmm.. Execute the query

SELECT SUBSTRING_INDEX(tip,' ',8) FROM tips;

in phpmyadmin/mysql console and see if you get any output ! Maybe there are no records in the table with more than 7 spaces.

Member Avatar for antwan1986

Ok here is the latest. The query runs or else it would have died. I added some BR tags into the loop, and when I run the following query:

$result = mysql_query("SELECT SUBSTRING_INDEX(tip,' ',8) FROM tips;", $connection);

This is the output I get:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PHP: Limiting Number Of Words From MySQL</title>
</head>

<body>
**A TIP SHOULD BE HERE**
<br />
<strong></strong>

**A TIP SHOULD BE HERE**
<br />
<strong></strong>

**A TIP SHOULD BE HERE**
<br />
<strong></strong>

**A TIP SHOULD BE HERE**
<br />
<strong></strong>

**A TIP SHOULD BE HERE**
<br />
<strong></strong>

**A TIP SHOULD BE HERE**
<br />
<strong></strong>

**A TIP SHOULD BE HERE**
<br />
<strong></strong>
</body>

So I count seven in there, and I went to phpmyadmin and ran that query. It returns the results fine, it returns all seven including the data, so I'm totally confused why PHP isn't echoing out the row correctly; do I not reference it as $row?

Member Avatar for nav33n

Ah!!! You can do it this way.

$query = "SELECT SUBSTRING_INDEX(tip,' ',8) as tip  FROM tips";

This will solve your problem, I am sure.

Member Avatar for antwan1986

Ah!!! You can do it this way.

$query = "SELECT SUBSTRING_INDEX(tip,' ',8) as tip  FROM tips";

This will solve your problem, I am sure.

Thank you very much, it seems to work now! So the problem was that it was not assigning the SUBSTRING_INDEX result to a variable?

Anthony

Member Avatar for nav33n

No.. You are creating an alias for SUBSTRING_INDEX(tip, ' ',8) . Php parser will look for a column name and since you aren't specifying any, it will be empty.

SUBSTRING_INDEX(tip, ' ',8) as tip

This will return the result under "user defined" column name tip, so that php parser can access it.

commented: Very useful! +1
Member Avatar for antwan1986

Thanks again for explaining that; you have (as always) been a great help. Much appreciated friend!

Member Avatar for nav33n

You are welcome :) !

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.