How to show results from SQL by letter counting. Ex( Showing the first 15 letters)...

Well, like the title says, i have a script that displays strings from SQL table. But, i need it to display only the fist 20 letters from about over 500 letters. So, how do i accomplish this using an SQL query.

 

select SUBSTRING(yourfield, 0, 20) from table where ...

 

Thanks but, it's not working or i am a bit lost.

Here is a copy of the code, concerning the query which i will have to modify..

 


$query="SELECT * FROM articles ORDER BY title DESC LIMIT 10";  
$rt=mysql_query($query);          
echo mysql_error();                   
	while($nt=mysql_fetch_array($rt)){
echo "
<br/>
 $nt[message] 

Code (markup):

Thanks for the Help.

 

He gave you the beginning part of the SQL statement... How can you get lost?

SELECT SUBSTRING(column_name, starting_position, length) FROM Table_Name ... etc;

Or in your example:

SELECT SUBSTRING(blah, 0, 20) FROM articles ORDER by title DESC LIMIT 10;

Assuming "blah" is a column in the table articles that you wish to limit the display of only 20 characters...

Personally...I wouldn't bother incorporating the SUBSTRING in to the SQL. I would perform that with PHP.

 

doing it in SQL might be slightly quicker as it would result in smaller data size (especially on huge table or if you do joins)

 

Thanks, but how can i display other colums as well. Because, in the table i have fields such as: Title, Body, Tags, Sources and users. Now, from all of this columns i only want to show the first 100 letters of the body, it's like a articles site, which recent articles are submitted by random users and in the index page, every article will be shown but with 100 letters first, then when the user click on the title it will take them to another page to display the full article. So, i need only the body to be displayed with 100 stings.

I replaced my query with this,

 [COLOR=#111111]SELECT SUBSTRING(body, 0, 20) [/COLOR][URL="http://ads.digitalpoint.com/go.php?k=FROM+articles"]FROM articles[/URL][COLOR=#111111] ORDER by title DESC LIMIT 10; [/COLOR]

Code (markup):

Out of this code

     <?php    include ('db-connect.php');
$query="SELECT SUBSTRING(body, 0,20) FROM articles ORDER BY title DESC LIMIT 10";  
$rt=mysql_query($query);          
echo mysql_error();                   
    while($nt=mysql_fetch_array($rt)){
echo "
<br/>
<ul id='article-fonts'> 
<li class='title'><h1>$nt[title]</h1></li>
<li class='body'>$nt[body]</li>
<li class='tags'> $nt[tags] | Reference Words $nt[reference]</li>


<li class='footnote'> Article by: <a href=''>$nt[user] </a> | Date: $nt[date] | Comments: 14  </li>
   
  
</ul>
" ; 
}?>  

Code (markup):

But, am getting this error

[TABLE="width: 1480"]
[TR]
[TD="class: content"]
[B]Notice[/B]: Undefined index: title in [B]C:\xampp\htdocs\DIRECTORY\Library\index.php[/B] on line [B]34[/B]

[B]Notice[/B]: Undefined index: body in [B]C:\xampp\htdocs\DIRECTORY\Library\index.php[/B] on line [B]35[/B]

[B]Notice[/B]: Undefined index: tags in [B]C:\xampp\htdocs\DIRECTORY\Library\index.php[/B] on line [B]36[/B]

[B]Notice[/B]: Undefined index: reference in [B]C:\xampp\htdocs\DIRECTORY\Library\index.php[/B] on line [B]36[/B]

[B]Notice[/B]: Undefined index: user in [B]C:\xampp\htdocs\DIRECTORY\Library\index.php[/B] on line [B]38[/B]

[B]Notice[/B]: Undefined index: date in [B]C:\xampp\htdocs\DIRECTORY\Library\index.php[/B] on line [B]38[/B][/TD]
[/TR]
[/TABLE]
 

Code (markup):

 

yes, your obviously need to mention the columns that you want to be returned with your query, such as

SELECT SUBSTRING(body, 0, 20) as bodyshort, title, tags, reference, user, date FROM articles ORDER by title DESC LIMIT 10;

and use $nt['bodyshort'] instead of $nt['body'] in your php