Need to trim text back to a space!

[sjones]

Hello I'm trying to query the DB and retrieve the results for the description text area. I would then like to trim it down for a small preview and then I will use a link to get to the full description. My ? is, I'm trying to use the substr() function and would like the last character to be a SPACE " " as not to end in the middle of a word. So I was thinking of moving backwards until the last character = " "

Does anyone know of how to achieve this? - Below is the code that will not produce any results. I can get results using numeric conditions in the

substr($record['description'],0,35); but I was trying to compair what the 35th character was and if it is not = " " then I wanted to subtract from there.

Any thoughts??

 

$sql = 'SELECT * FROM `events` WHERE `date_start` > \'2007-02-12\' ORDER BY `date_start` LIMIT 0, 30';

$result = mysql_query($sql);

while ($record = mysql_fetch_assoc($result)){

$last_char = 35;

if (substr($record['description'],0,$last_char) != " ") {

$last_char = ($last_char--);

}else{

$event_description = substr($record['description'],0,$last_char);

}

echo $event_description;

[alpine]

<?php

$text = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.";

echo join(' ', array_slice (explode(' ', $text), 0, 10)); // where 10 is words to print out before cutoff

?>

[effigy]

See SUBSTRING_INDEX.

[roopurt18]

Got one more for you:

 

Where:

  fld is the character field

  len is the length of the string you wish to return

 

SELECT

  TRIM(

    REVERSE(

      SUBSTRING(

        REVERSE(

          SUBSTRING(

            fld,

            1,

            len

          )

        ),

        LOCATE(

          ' ',

          REVERSE(

            SUBSTRING(

              fld,

              1,

              len

            )

          )

        )

      )

    )

  ) AS SmallField

FROM table WHERE 1 LIMIT 1

 

<?php

  // Sample use of the function below
  $sql = "SELECT id, name, "
       . makeShortField('description', 30, 'ShortDesc')
       . " FROM someTable WHERE 1 LIMIT 1";
  $q = mysql_query($sql);
  if($q){
    while($row = mysql_fetch_assoc($q)){
      echo '<pre style="text-align: left;">' . print_r($row, true) . '</pre>';
    }
  }

  // makeShortField
  // $field - the name of the db field
  // $len - the maximum number of characters from $field to display
  // $name - the name you'd like the field to be returned as in the query
  // Create the string of text that will extract a limited number of characters
  // from a DB field and cut off any impartial text
  // i.e: makeShortField('field1', 15, 'ShortDesc');
  //      On a DB field containing 'abcd efgh ijkl mnop qrst uvwx yz'
  //      Will make $row['ShortDesc'] contain: 'abcd efgh ijkl'
  // RETURN: the text string to insert into your DB query
  function makeShortField($field, $len, $name){
    $query = <<<QUERY
      SELECT
        TRIM(
          REVERSE(
            SUBSTRING(
              REVERSE(SUBSTRING($field,1,len)),
              LOCATE(' ',REVERSE(SUBSTRING($field,1,len)))
            )
          )
        ) AS $name
QUERY;
  }
?>

[roopurt18]

While I'm quite proud of my little concoction of SQL that I just came up with, after reading effigy's post I think something like

 

(edit - nm, I'll have to play with it more when I have free time.)

 

is a lot easier to read and use.

 

[sjones]

Hey everyone thanks for the help. I used Alpine's method. It seemed to be a global fix to the issue. Thanks again!

You guys are GREAT!

[sjones]

O.K. - I'm using this code.

 

$text = $record['description'];

echo "<p><span class='eventsLocation2'>" .$record['title']. "</span><br><br><div align='right'>"

. join(' ', array_slice (explode(' ', $text), 0, 12)) . " <a href='news.php'><span class='read_more'>....READ MORE</span></a></div></p><br>";

 

I'm running into a problem when the description that is typed in has any type of formatting such as <b></b>

if the closing tag is not before the cutoff then all of the other entries are bold. You can view this example @

http://www.cwptechnologies.com/2007newsite/index.php - if you look at the right side of the page you will see the truncated events section and you will see that once the bold tag was used it was never closed.

 

Any thoughts?