Insert part of an SQL value

[PhilipK]

I am trying to build a news feed which shows the first 100 characters of news copy. I have it working to paste in the full text but am not sure how to insert only the first 100 characters.

                       
<?php
	while($news_row=mysql_fetch_array($news)) {
	echo "
                <div class=\"news_box\">
                <h3><a href=\"html/news.html\" rel=\"shadowbox; width=800; height=400;\">".$news_row['news_title']."</a> </h3>
                 ".$news_row['news_copy']."
                 </div>";
}
?>

 

[Pikachu2000]

You can do that in the query string.

SELECT CONCAT( SUBSTR(news_copy, 100), ' . . . ' ) as news_copy FROM `table` (etc.)

 

If you'd rather return a certain number of full words or full sentences, look at MySQL's SUBSTRING_INDEX() function.

[PhilipK]

Seems to be what I need but I can't get it to work.

 

Here is my SQL query

 

<?php 
$news = mysql_query("SELECT news_title, CONCAT( SUBSTR(news_copy, 100), ' . . . ' ) FROM tbl_news ORDER BY news_id DESC LIMIT 0, 3");
?>

 

news_title shows up but no news_copy

 

[Pikachu2000]

You removed the field's alias from the query string, so the array index ['news_copy'] is no longer present in the result array.

 

SELECT CONCAT( SUBSTR(news_copy, 100), ' . . . ' ) AS news_copy

[PhilipK]

Thanks for the quick reply. I can't seem to get it right.

 

<?php
$news = mysql_query("SELECT news_title, CONCAT( SUBSTR(news_copy, 100), ' . . . ' ) as news_copy FROM tbl_news ORDER BY news_id DESC LIMIT 0, 3");
?>

 

Now I get news_copy concatenated with ... but the text isn't cut down to 100 characters.

 

[cunoodle2]

Please show your code where you are echoing and or working with your mysql query results.

[cyberRobot]

For some reason, SUBSTR() or SUBSTRING() don't work for me either. But LEFT() does:

 

<?php
$news = mysql_query("SELECT news_title, CONCAT( LEFT(news_copy, 100), ' . . . ' ) as news_copy FROM tbl_news ORDER BY news_id DESC LIMIT 0, 3");
?>

 

 

Note:

The second argument for SUBSTR() is the start position; the third arguement is the string length. So to get the first 100 characters, you'll need to change it to SUBSTR(news_copy, 0, 100)

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

 

But as mentioned earlier, I wasn't able to get SUBSTR() to work. Note that SUBSTR(news_copy, 100) does work for me, but it cuts off the first 100 characters and leaves the rest.

[cyberRobot]

Ok, I was able to get SUBSTR() and SUBSTRING() to work.

 

<?php
$news = mysql_query("SELECT news_title, CONCAT( SUBSTR(news_copy, 1, 100), ' . . . ' ) as news_copy FROM tbl_news ORDER BY news_id DESC LIMIT 0, 3");
?>

 

 

The problem was that the functions consider 1 to be the first character and not 0.