Shorten MySQL result | Site FUSE Forums

zoney 1 years, 6 months ago

Hi, I'm looking for a way to shorten my MySQL results, to a certain number of characters for each result taken from the database. ie: I need "I was raised by rabid wolves in an eskimo on the outskirts of france" shorted to: "I was raised by rabid wolves in an es..." Is there a quick way to do this kind of thing using MySQL and PHP?

Mr Nase 1 years, 6 months ago

Try something like this in your query

SELECT substring(FIELD, 1, LENGTH) FROM tableName

Where field is the field which stores the data you want to trim and length is the lenght you want returning.

OR

SELECT substring(FIELD, 1, round(char_length(FIELD) * 0.10) ) FROM tableName

This would return 10% of the data from the cell called FIELD.

Hope these help you.

zoney 1 years, 6 months ago

Thanks a heap for the code Mr Nase..cant say it helped that much. If you can see something wrong with this code...let me know:

$shortbio = mysql_query("SELECT substring($biography, 1, 100) FROM artist WHERE artist_id='$id'",$db);

The thing i'm trying to do is grab a band biography from the database, and shorten it down to save space. I use;

<?php printf("%s \n", mysql_result($shortbio,0,"biography")); ?>

to try and print the shortent bio to the page, but that doesn't seem to be working. I must be doing something not quite right...or completetly wrong.

Mr Nase 1 years, 6 months ago

Try this:

$sql = "SELECT substring(biography, 1, 100) FROM artist WHERE artist_id='$id'";
$res = mysql_query($sql, $db);

$bio = mysql_fetch_row($res); $bio = $bio['0'];

This code should work for you. $bio will store the short biography for you.

zoney 1 years, 6 months ago

Thanks again...everything works really well now. I've been going through various PHP3 and MySQL source code for about 5 hours now..

I think I need some more coffee.