Limiting MySQL results

zoney

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

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

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

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

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.