syntax for selecting first 100 characters from a mysql field

[eltreno]

The heading pretty much says it

I'm not even sure if it's possible but can you get the first 100 or whatever you specify from a certain field

ie something like

select id,name,partofstring(0,100,description) from tablename;

meaning I only want the first 100 chars from the description field but all the 'id' and all from 'name'

Can't find anything on mysql.com for this

Thanks
Trent

[jatar_k]

search this page for SUBSTRING [dev.mysql.com]

[WebsiteMgrs]

Check out the substr function and either declare $description before the SQL statement, or separate it inline like below.

$descript = substr("description",0,100);
select id,name,$descript from tablename;
OR
select id,name,".substr("description",0,100)." from tablename;

Jim

[eltreno]

I can't get either to work

this is my code

$sql2 = "select Cid, Pid, Sid, link_text, SUBSTRING('chapter_content',0,20) from chapters where chapter_content like '%" . $_GET["query"] . "%'";

and I'm doing standard while and printing

while ($myrow2 = mysql_fetch_array($result2)){
$search_content .= '<p><a href="' . rootdir('chapters.php?pid=' . $myrow2['Pid'] . '&amp;sid=' . $myrow2['Sid'] . '&amp;cid=' . $myrow2['Cid']) . '">' . $myrow2['link_text'] . '</a> - Chapter Page<br/>' . str_replace('¦¦¦¦', ' ', $myrow2['chapter_content']) . '</p>';
}

But now no chapter_content shows... also not getting any errors

Trent

[jatar_k]

I just ran it this way and it worked

SUBSTRING(chapter_content,1,20)

offset of the first char seems to be 1 and I didn't use quotes, it uses the literal string with the quotes, not the column

[jollymcfats]

You could also use LEFT() to get the same result.

LEFT(chapter_content, 100)

[eltreno]

Well I tried all these options (every combination possible) and still for the life of me can't get a return from content_chapter.

It's weird not to even get an error on any of the ways I try the page still loads and displys the link_text etc ok ....

The content_chapter field is of type 'TEXT' if that makes any difference, other then that I'm stummped.

Also is the way i'm trying to get the return ie $myrow2['chapter_content'], (Just like normal), is that still right

Cheers
Trent

[coopster]

Have you ever executed the query?

$sql2 = "select Cid, Pid, Sid, link_text, SUBSTRING(chapter_content,0,100) from chapters where chapter_content like '%" . $_GET["query"] . "%'"; 
$result2 = mysql_query [php.net]($sql2); 
while ($myrow2 = mysql_fetch_array($result2)) { 
$search_content .= '<p><a href="' . rootdir('chapters.php?pid=' . $myrow2['Pid'] . '&amp;sid=' . $myrow2['Sid'] . '&amp;cid=' . $myrow2['Cid']) . '">' . $myrow2['link_text'] . '</a> - Chapter Page<br/>' . str_replace('¦¦¦¦', ' ', $myrow2['chapter_content']) . '</p>';

[eltreno]

Yer I have the query works fine like that bit nothing comes from $myrow2['chapter_content'] it's like it is empty, but works fine if just calling chapter_content in query as normal.

I can't imageing what's wrong because the code does what it should ie make a link etc but just won't print anything from $myrow2['chapter_content'].

I may have to let this one go as an unsolved mystery :)

[brotherhood of LAN]

$sql2 = "select Cid, Pid, Sid, link_text, SUBSTRING('chapter_content',0,20) from chapters where chapter_content like '%" . $_GET["query"] . "%'";

try

$sql2 = "select Cid, Pid, Sid, link_text, SUBSTRING('chapter_content',0,20) AS chapter_content from chapters where chapter_content like '%" . $_GET["query"] . "%'";

[eltreno]

Yes I have tried that and just did again, but still gives nothing.

I try to echo $myrow2['chapter_content'] in the while statement without the rest and still nothing yet I can echo $myrow2['link_text'] fine.

I also just tried
$sql2 = "select Cid, Pid, Sid, SUBSTRING(link_text,0,5) AS link_text, chapter_content from chapters where chapter_content like '%" . $_GET["query"] . "%'";

and
$sql2 = "select Cid, Pid, Sid, SUBSTRING(link_text,0,5), chapter_content from chapters where chapter_content like '%" . $_GET["query"] . "%'";
(same but without the 'AS')

and it will now show chapter_content but alas now no link_text

maybe a mysql setting problem or version or somthing..

[coopster]

Have you tried running the query from a command line? I'll often do that to troubleshoot a query statement. You can dump the $sql statement in your browser, then copy and paste to a MySQL command line and see what you get.

$sql2 = "select Cid, Pid, Sid, SUBSTRING(link_text,0,5) AS link_text, chapter_content from chapters where chapter_content like '%" . $_GET["query"] . "%'"; 
exit($sql2); // insert this line to stop processing and see statement 
$result2 = mysql_query($sql2); 
while (...) {

Nice catch on the ALIAS there, BOL

[johnt]

Try selecting

$myRow['SUBSTRING(\'chapter_content\',0,20)']

in the while loop.
Failing that, use print_r($myRow) to see what the array contains after you've done the fetch.
I usually do
echo "<pre>\n";
print_r($myRow);
echo "</pre>\n";

so that I can see what's going on.

Hope this helps

John

[coopster]

Welcome to WebmasterWorld, johnt.

Try selecting

$myRow['SUBSTRING(\'chapter_content\',0,20)']

That's why BOL recommended using an ALIAS. MySQL will return the column name in the result set as whatever the ALIAS is named.

[eltreno]

Hi All

Thansk for your replays

I just got back from holidays and we'll the break did me good I worked it out

it was a combination of using SUBSTRING(chapter_content,0,100) instead of SUBSTRING(chapter_content,1,100) I did try this as suggested by jatar_k but did not combine it with AS chapter_content on the same attempt :(, with I debuged running the query on the command line..

final working code..

$sql2 = "select Cid, Pid, Sid, link_text, SUBSTRING(chapter_content,1,100) AS chapter_content from chapters where chapter_content like '%" . $_GET["query"] . "%'";

I'm using this metod apposed to the substring($myrow['chapter_content'],0,100]) just because I think is quicker code letting mysql do the work and giving php less data to need to work with.

Cheers
Trent