how to show limited numbers of text from a mysql column ?

[khalidbaloch]

is this possible to show limted no number of text from a mysql record .
for example i run this query
SELECT DISTINCT short_description FROM news_contents ORDER BY postdate DESC LIMIT 1

and if the record's length is greater than 50 i dont want to print any more text after 50 for example

12345678910111213141516171819202122232425262728293 03132333435363738394041424344454647484950......... ...........

thanx for any help in advance ...........

[miller]

Danger, Will Robinson!

First off, what you ultimately need is the SUBSTR function, which you can read about at: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

There is a flaw in your SQL statement though, and it revolves around the dual use of DISTINCT with an ORDER BY. Whenever the terms that you sort by are not included within the DISTINCT clause, the results cannot be predicted. Any values for "description" that are duplicated could come in the order specified by any of the records in that group. There is no predicting the results. You can read more about it at:
http://dev.mysql.com/doc/refman/5.1/en/distinct-optimization.html
http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-fields.html

I would therefore suggest that you simple remove the DISTINCT call altogether. This is especially true since you currently have a LIMIT of 1. Guess what, if you only pull 1 record, you can assume that it's "distinct". Amazing logic, yes?

I'll leave you with two SQL statements though. One of them will remove the distinct requirement and only pull 1 record. The second will pull 10 records and require that they be distinct. But once again, the second query will have unpredictable results for any "non-distinct" records.

SELECT SUBSTR(short_description, 0, 50) FROM news_contents ORDER BY postdate DESC LIMIT 1;

SELECT SUBSTR(short_description, 0, 50) FROM news_contents GROUP BY short_description ORDER BY postdate DESC LIMIT 10;

[khalidbaloch]

i read the manual that you refere and took some tutorials
when i ran the following query

<?php
include ("connection.ini.php");
$result = mysql_query("SELECT SUBSTR(short_description, 0, 50) FROM news_contents GROUP BY short_description ORDER BY postdate DESC LIMIT 10") 
or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
    echo $row['description'];
}
?>

i did not see any records on the page ,i also try by changing mysql_query
to

SELECT SUBSTR(short_description, 0, 50) FROM news_contents ORDER BY postdate DESC LIMIT 1;

and

SELECT SUBSTR(short_description,0,50) FROM news_contents LIMIT 1

but no record where printed
please make an example query with comlete code .. thanx

[khalidbaloch]

sorry i missed short_ in echo "$row['description'] it was actuly echo "$row['short_description']"; so that is not the cause of disappearance of records.

i read the manual that you refere and took some
tutorials
when i ran the following query

<?php
include ("connection.ini.php");
$result = mysql_query("SELECT SUBSTR(short_description, 0, 50) FROM news_contents GROUP BY short_description ORDER BY postdate DESC LIMIT 10") 
or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
    echo $row['_description'];
}
?>

i did not see any records on the page ,i also try by changing mysql_query
to

SELECT SUBSTR(short_description, 0, 50) FROM news_contents ORDER BY postdate DESC LIMIT 1;

and

SELECT SUBSTR(short_description,0,50) FROM news_contents LIMIT 1

but no record where printed
please make an examaple query with complete code .. thanx

[ronverdonk]

In SUBSTR the start position starts at 1 not 0, so your query should be e.g.

SELECT SUBSTR(short_description,1,50) FROM news_contents LIMIT 1
 

Ronald :cool:

[khalidbaloch]

problem is still there .i ran this query
SELECT SUBSTR(short_description,1,5) FROM news_contents LIMIT 1
but it retrive no result and afterword a ran the same query from phpmyadmin and i get this error
#1305 - FUNCTION mydbname.SUBSTR does not exist
and then i change the spelling of SUBSTR(short_description,1,5)
to SUBSTRING(short_description,1,5) ,and it worked in phpmyadmin
but i but want to run the same query in my php page for this purpose i have the following code .

<?php
include ("config.php");
$result = mysql_query("SELECT SUBSTRING(short_description,1,3) FROM news_contenys LIMIT 1") 
or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table 
    echo $row['short_description'];
}
?>

yes i agree that my thread is no more related to mysql forum , but i dont consider it good to post tha same thread in an other forum
.so when i put these codes is my php page no records retrived from the database please take a look at my php code and tell what i am missing or doing wrong in this code
i am already very thankfull to ronverdonk , he has soleved my first thread http://www.thescripts.com/forum/thread545705.html ,in php forum . and i am sure that ronverdonk will solve my this thread as well
Thanks ronverdonk please do me this fever.

[ronverdonk]

In your post you have your table name misspelled. Maybe that is the reason. Your statement says 'news_contenys' and it should be 'news_contents'.
But maybe it is just a typo. As far as I can see there is no other error in your code, unless the target rows disappeared mysteriously.

Ronald :cool:

[khalidbaloch]

yes this misspelled was made only in posting , but my problem has been solved thanks again for you personel attention, this is how the SUBSTRING function works on my local server please note the code ..

<?php
include ("config.php");
$result = mysql_query("SELECT SUBSTR(short_description,1,50) FROM news_contents") 
or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {
    echo $row['SUBSTR(short_description,1,50)'];
}
?>

the changes i made from previous code is only line 7 where i write "echo $row['SUBSTR(short_description,1,50)']; instead of "echo $row[short_description'];"

this idea came in to mind when i ran this
SELECT SUBSTR(short_description,1,50) FROM news_contents LIMIT 1
query from phpmyadmin and it worked ,

thanx again ronald for your sincer coopration ,atlast my this thread has been solved , and i am sure that i will learn more n more from this form ..

[ronverdonk]

You would make it a lot easier on yourself and the viewers of the resultset if myou use an alias for the MySQL substring result, such as 'description'. Your code would then be:[PHP]<?php
include ("config.php");
$result = mysql_query("SELECT SUBSTR(short_description,1,50)
AS description FROM news_contents")
or die(mysql_error());
while($row = mysql_fetch_array( $result )) {
echo $row['description'];
}
?>[/PHP]

Ronald :cool:

[khalidbaloch]

You would make it a lot easier on yourself and the viewers of the resultset if myou use an alias for the MySQL substring result, such as 'description'. Your code would then be:[PHP]<?php
include ("config.php");
$result = mysql_query("SELECT SUBSTR(short_description,1,50)
AS description FROM news_contents")
or die(mysql_error());
while($row = mysql_fetch_array( $result )) {
echo $row['description'];
}
?>[/PHP]


Ronald :cool:

this is a great idea ronverdonk thanxs again .........................