Limiting MySQL results
| Important: The following is a text only archive! For full features; Go to Limiting MySQL results |
posted by 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?
posted by 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.
posted by 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.
posted by 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.
posted by 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.