[SOLVED] Limit text output

[Daveyboy]

How do you limit text output to say the first 200 charachters in a column? I tried this, but wrong

[code]$sql_events = mysql_query("SELECT 'id', 'name', 'version', 'dblookupid', SUBSTRING('comments' 0, 100), 'date' FROM bugslist ORDER BY 'id'  ASC") or die (mysql_error());[/code]

[shoz]

Remove the single quotes from around the column names. If you get an error in syntax starting at "version" then you may need to surround that name with backticks (` - commonly the key before the 1 on the keyboard).

Even if you don't get an error, know that you should try to choose column and table names that are unlikely to be [url=http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html]reserved keywords[/url]

[fenway]

LEFT() also works.

[Daveyboy]

bump

[shoz]

Why are you bumping the topic. You've been given solutions to your problem. Have you tried the suggestions and if so what was the result?

[Daveyboy]

i tried this;

$sql_events = mysql_query("SELECT `id`, `name`, `version`, `dblookupid`, `date`, SUBSTRING(`comments` 0,30) FROM bugslist  ORDER BY `id` ASC") or die (mysql_error());

getting this;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0,30) FROM bugslist ORDER BY `id` ASC' at line 1

[shoz]

A comma is missing after `comments`
[code]
SUBSTRING(`comments`, 0,30)
[/code]

[Daveyboy]

ok i used LEFT(`comments`, 200) that outputs what I want, however, does this mean my column name is now called LEFT(`comments`, 200) as showing in the MySql console? So know I am going to have to change my column name in php to LEFT(`comments`,200) so it registers the $comments variable in the script below properly?

[code]$sql_events = mysql_query("SELECT `id`, `name`, `version`, `dblookupid`, `date`, LEFT(`comments`,200)  FROM bugslist  ORDER BY `id` ASC") or die (mysql_error());

while ($row = mysql_fetch_array($sql_events)) {
   
    $view= $row["id"];
    $edit= $row["id"];
    $delete= $row["id"];
    $id = $row["id"];
    $name = $row["name"];
    $version = $row["version"];
    $database= $row["dblookupid"];
    $comments= $row["comments"];
    $date_submitted= $row["date"];[/code]

[shoz]

You can create an alias for the column
[code]
SELECT LEFT(comments, ....) AS comments
[/code]

Btw, I missed this but the other error with the query was that the position starts at 1 not 0 for SUBSTRING(). ie

[code]
SUBSTRING(comments, 1, 30)
[/code]

would have been the correct usage.

[Daveyboy]

ok thanks dood, works, appreciate your help.