Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Limit Characters on a PHP MySQL query

Posted on 2011-02-22
Medium Priority
Last Modified: 2012-05-11
I need to limit the amount of characters show on a query. The amount of characters I need to show is 100 from the 'copy' column

Here's my query code:

mysql_select_db($database_Hollybush, $Hollybush);
$query_news = "SELECT id, `date`, title, copy, FROM news ORDER BY id DESC";
$news = mysql_query($query_news, $Hollybush) or die(mysql_error());
$row_news = mysql_fetch_assoc($news);
$totalRows_news = mysql_num_rows($news);

I have tried this but nothing shows?

mysql_select_db($database_Hollybush, $Hollybush);
$query_news = "SELECT id, `date`, title, SUBSTR(copy,50,50) FROM news ORDER BY id DESC";
$news = mysql_query($query_news, $Hollybush) or die(mysql_error());
$row_news = mysql_fetch_assoc($news);
$totalRows_news = mysql_num_rows($news);

Anyone know how to do this?
Question by:BrighteyesDesign
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3

Expert Comment

ID: 34954583
Try this:

"SELECT id, `date`, title, LEFT(copy,100) FROM news ORDER BY id DESC";

Author Comment

ID: 34954644
Thanks for that.

It doesn't show any result i'm afraid.

The text shows with the first of my two codes above by the way so everything else is ok.

Expert Comment

ID: 34954680
That is strange since the first statement contains a syntax error (improper comma between "copy" and "from):

   "SELECT id, `date`, title, copy, FROM news ORDER BY id DESC";

What is the type of that copy column, ie. how did you create it?
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 34954725

Yes, you're right, I actually deleted that comma after posting.

I created it in MySQL as text (see screenshots) MySQLScreen-shot-2011-02-22-at-19.14..png

Expert Comment

ID: 34954785
I tried to simulate it on my MySQL instance:

   drop table test;
   create table test (a int, copy text);
   insert into test values (1, 'abcde fghij klmno');
   select a,copy from test;
   select a, left(copy,5) from test;

The first select returns:

  1, abcde fghij klmno

and the second:

  1, abcde

just like expected.

By "no results" do you mean the column selected as left(copy,100) is empty but other colums show right or that there are no rows returned at all?


Accepted Solution

AriMc earned 2000 total points
ID: 34954800
You could also try:

"SELECT id, `date`, title, LEFT(copy,100) as copy FROM news ORDER BY id DESC";

Author Closing Comment

ID: 34955951
Perfect, thanks a lot for that!

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question