Displaying a limited number of words from MySQL

Question

antwan1986 4 Junior Poster

15 Years Ago

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

php

4 Contributors
14 Replies
2K Views
3 Days Discussion Span
Latest Post 15 Years Ago Latest Post by nav33n

All 14 Replies

buddylee17 216 Practically a Master Poster

15 Years Ago

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

nav33n 472 Purple hazed!

15 Years Ago

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 ?

buddylee17 216 Practically a Master Poster

15 Years Ago

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

nav33n 472 Purple hazed!

15 Years Ago

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.

antwan1986 commented: Very useful! +1

Reply to this topic

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.

antwan1986 4 Junior Poster · Answer 1 · 2008-06-27T20:12:27+00:00

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?

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 2 · 2008-06-27T23:55:54+00:00

nav33n 472 Purple hazed!

15 Years Ago

Yep.. :)

DGStudios 0 Light Poster · Answer 3 · 2008-06-28T08:02:17+00:00

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

antwan1986 4 Junior Poster · Answer 4 · 2008-06-30T06:21:24+00:00

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.

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 5 · 2008-06-30T11:59:16+00:00

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.

antwan1986 4 Junior Poster · Answer 6 · 2008-06-30T17:26:08+00:00

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?

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 7 · 2008-06-30T17:31:20+00:00

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.

antwan1986 4 Junior Poster · Answer 8 · 2008-06-30T18:47:55+00:00

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

antwan1986 4 Junior Poster · Answer 9 · 2008-06-30T18:57:29+00:00

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

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 10 · 2008-06-30T19:01:56+00:00

nav33n 472 Purple hazed!

15 Years Ago

You are welcome :) !

Displaying a limited number of words from MySQL

Recommended Answers Collapse Answers

All 14 Replies

Recommended Answers