Limiting characters from database

howster

Hi,
I would like to limit the number of characters to e.g 100 that are retrieved from the database in short_description. There can be an indefinite number of characters held in the database for short_description but for display purposes I only want to display the first 100 characters or first 8 words.
Amy ideas please. Totally new to this. The code is below. THANKS!!!

$sql = " SELECT item_id, item_title, friendly_url, date_start, short_description ";
$sql .= " FROM " . $table_prefix . "ads_items ";
$sql .= " WHERE item_id IN (" . $db->tosql($items_ids, INTEGERS_LIST) . ") ";
$sql .= " ORDER BY date_start DESC, date_added ";

$db->query($sql);
if($db->next_record())
{
	$latest_columns = get_setting_value($page_settings, "ads_latest_cols", 1);
	$t->set_var("latest_column", (100 / $latest_columns) . "%");
	$latest_number = 0;
	do
	{
		$latest_number++;
		$item_id = $db->f("item_id");
		$item_title = get_translation($db->f("item_title"));
		$friendly_url = $db->f("friendly_url");
		$[COLOR="red"]short_description [/COLOR]= get_translation($db->f("short_description"));

		$t->set_var("item_id", $item_id);
		$t->set_var("latest_item_title", $item_title);
		$t->set_var("[COLOR="red"]short_description[/COLOR]", nl2br($[COLOR="red"]short_description[/COLOR]));

howster

resolved

$latest_number++;
$item_id = $db->f("item_id");
$item_title = get_translation($db->f("item_title"));
$friendly_url = $db->f("friendly_url");
$short_description = get_translation($db->f("short_description"));
$short_description = substr($short_description, 0, 50);

Bjom

you actually retrieve the complete description and then truncate it - this is different from your question. You can limit what you get with the SUBSTR() function in SQL like below, dont forget to read the manual.

$sql = " SELECT item_id, item_title, friendly_url, date_start, SUBSTR(short_description,1,100) AS short_description ";
$sql .= " FROM " . $table_prefix . "ads_items ";
$sql .= " WHERE item_id IN (" . $db->tosql($items_ids, INTEGERS_LIST) . ") ";
$sql .= " ORDER BY date_start DESC, date_added ";

If that thing can be any length...I wonder what a long description would look like? A limited description_short and an unlimited description_long might be worth considering.

Please use [php ] [/php ] tags and mark resolved threads as resolved

howster

Thanks Bjom,
Ya ur right I should limit the characters for short and long description too. The CMS Im using doesnt have a limit on it.

howster

Just wondering is there a way to extract say just the first 5 words as opposed to the first 100 characters?
Thx

Bjom

Have a look at the manual page that I linke for you.... Yes there is that possibility look for spaces and get everything up to the 5th space....

howster

Thanks,
Does this look right please?
$sql = " SELECT item_id, item_title, friendly_url, date_start, SPACE(short_description,5) AS short_description ";

Bjom

right yes, but this simply produces a string containing 5 spaces - not what you want

howster

Another forum rcommends:
SELECT SUBSTRING_INDEX(str,' ',10) FROM table_name

but how would I apply it to:
$sql = " SELECT item_id, item_title, friendly_url, date_start, SUBSTR(short_description,1,100) AS short_description ";

Any ideas please guys? Completely new to this.
Thanks

howster

$sql = " SELECT item_id, item_title, friendly_url, date_start,SUBSTR(short_description,' ',10) AS short_description "; Sound right?

Bjom

you would have to find the 5th occurence of space using instr() - that is inefficient. I recommend you use the SUBSTR(.....,1,100) in the SQL to get a portion of your description.

Then in the PHP code use explode on this part of your description (see php manual for explode) then discard everything but the first 5 elements of the array and use implode on it again to make it a neat string again. A'ight?