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_descripti on 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

123456789101112 131415161718192 021222324252627 282930313233343 536373839404142 434445464748495 0.............. ......

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 "descriptio n" 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.ht ml
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_de scription, 0, 50) FROM news_contents ORDER BY postdate DESC LIMIT 1;

SELECT SUBSTR(short_de scription, 0, 50) FROM news_contents GROUP BY short_descripti on 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_de scription, 0, 50) FROM news_contents ORDER BY postdate DESC LIMIT 1;

and

SELECT SUBSTR(short_de scription,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_descript ion']"; 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_de scription, 0, 50) FROM news_contents ORDER BY postdate DESC LIMIT 1;

and

SELECT SUBSTR(short_de scription,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_de scription,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_de scription,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_d escription,1,50 )']; instead of "echo $row[short_descripti on'];"

this idea came in to mind when i ran this
SELECT SUBSTR(short_de scription,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("SE LECT SUBSTR(short_de scription,1,50)
AS description FROM news_contents")
or die(mysql_error ());
while($row = mysql_fetch_arr ay( $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("SE LECT SUBSTR(short_de scription,1,50)
AS description FROM news_contents")
or die(mysql_error ());
while($row = mysql_fetch_arr ay( $result )) {
echo $row['description'];
}
?>[/PHP]


Ronald :cool:

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