MySQL's LEFT() Function

Album Cover: First Impressions of Earth

"Don't be a coconut. God is trying to talk to you."
The Strokes / Ask Me Anything

Posted on December 27, 2004 1:18 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

While evaluating an unfortunate, massive comment-spamming that has taken place on my Coldplay site, I managed to learn something cool. In MySQL you can use the LEFT() string function to select a set number of characters from the left of a particular data column. This allows me to take a look at the first few words in a comment to evaluate whether it is spam or not. In cases where the commenter's name doesn't quite give it away, this is very useful.

So for example, the following query gives the displayed results:

SELECT id, name, LEFT(comments, 25) FROM comments ORDER BY cdate DESC LIMIT 5;

+------+--------------+---------------------------+
| id   | name         | LEFT(comments, 25)        |
+------+--------------+---------------------------+
| 1003 | phentermine  | <h1>In your free time, ch |
| 1002 | online poker | <h1>Please check out the  |
| 1001 | phentermine  | <h1>You can also visit so |
| 1000 | online poker | <h1>You may find it inter |
|  999 | mortgages    | <h1>In your free time, ch |
+------+--------------+---------------------------+

For more examples of MySQL's cool string manipulation functions, be sure to check out the MySQL Manual.

Comments

Arcanius on December 27, 2004 at 9:29 PM:

Bernie, I'm sorry to hear that the comment spammers have gotten to you. Aside from the fact that I think MySQL is kinda lame, when compared to solutions like PHP 5's built-in SQLite (which I have never used, so maybe I'm just blowing smoke), this LEFT() function sounds like a pretty useful feature. In case I haven't mentioned this enough when talking about why I think the SQLite solution for databases is so much better is because it makes the site much more portable. The database is just another file on the web server; no additional program is needed. If you actually need the ful scope of a database application, MySQL is a poor choice compared to, for example, PostgreSQL (once againm, I haven't used PostgreSQL myself, maybe I'm wrong). This evalution is based not on experience, but on the shortcomings of MySQL is places such as subselects, and for a lightweight solution, it is not nearly as lightweight as SQLite. But back to my main point, comment spamming. I have been quite successfully thwarting comment spammers by adding a "I Am Human" checkbox next to the commenter's name. The setting is remembered in a cookie as well, so it is checked the next time a real commenter comes back. This makes it very easy for my real commenters, and has blocked all attempts at comment spam so far. I encourage my friends to try similar solutions (it is a drop-in two-file replacement for Wordpress). Of course, this solution relies on the fact that I am one of only a handful implementing it, so don't let it get too far out of the bag :-).

Permalink

Bernie Zimmermann on December 28, 2004 at 12:48 AM:

Arcanius, it's funny that you brought all of this up because I was planning on posting (and probably still will) about some of the shortcomings of MySQL and how they apply to the issues I've been facing with comment-spam.

When it comes down to it, though, I wouldn't trust a database system like SQLite to run my blog and especially not a more popular site like my Coldplay site. For one, SQLite integration with PHP is a new thing, and that brings its own set of caveats. Secondly, a lot of the things the SQLite folks sacrificed in order to live up to the "lite" monniker are things that I look for in a good database system.

If MySQL wasn't an option, I would most certainly use PostgreSQL as the backend to my blog, my Coldplay site, and any other sites that required a database backend. I've used Postgres before and am very pleased with the feature set offered. The only things I'm underwhelmed by are the lack of a good, consistent source of documentation (they've actually come a long way since I last used their database system) and ease-of-use from the command line. Sure, you can get used to anything...look at Vi, for instance...but I've found that after you've stepped away for a while, remembering something like "show tables" (MySQL) is a lot easier than remembering dp (Postgres). All-in-all, I hate to say anything bad about Postgres because I've considered using it even with MySQL as an option, but when it boils down to it I continue to pick the latter every time.

MySQL has a command-line interface that is easy to remember. Important maintenance tasks like database dumps and backups are a snap. 99% of the time when something goes wrong with a MySQL table (and it does happen), a simple "CHECK TABLE" will identify the problem, and a "REPAIR TABLE" will fix it. When you're running a big site that carries a lot of useful data and sometimes requires interesting schema changes that don't affect integrity, MySQL has proven (for me at least) to be the best option.

This could get me into a bit of trouble, since comparing MySQL to Postgres is a bit like comparing Firefox and Opera (both very good browsers), but a little controversy is good for you every now and then. I think.

Permalink

MB on November 27, 2007 at 11:39 PM:

But isn't there one important difference as well: PostgreSQL costs, while MySQL comes free... Surely an important issue if you're a hobbyist!

Permalink

Sam Hammouri on November 28, 2008 at 12:30 AM:

I know my comment here is a bit belated, but I just wanted to say that I've been using MySQL for 5 years, and featurewise It hasn't let me down once. I certainly am not a guru, I don't know all the functions and commands of MySQL, but on each and every occasion that I've tried to get something done, I could always depend on MySQL to have some command that does the job nicely and efficiently. And I've created some rather complex SQL queries.

Permalink

Is Mundu on September 15, 2009 at 9:20 AM:

#MB: PostgreSQL costs nothing. In fact, it has a more open license than MySQL.

Permalink

percocet information on October 25, 2011 at 9:04 PM:

It is a very good thing, http://www.squidoo.com/lensmasters/percocets All about percocet, rckq,

Permalink

buy xanax online on October 27, 2011 at 1:09 PM:

Best, http://jiss.eventbrite.com/ buy xanax, :)),

Permalink

All about percocet on October 30, 2011 at 3:20 AM:
buy adipex on October 30, 2011 at 12:57 PM:
replica rolex watches online on October 30, 2011 at 3:02 PM:

Nice, http://rrw.eventbrite.com/ replica rolex watches online, >:[[,

Permalink

buy cialis for you on October 30, 2011 at 5:11 PM:

It is a very good thing, http://www.squidoo.com/lensmasters/buycialises buy cialis, =)),

Permalink

buy xanax on November 16, 2011 at 5:51 AM:

Give somebody the to a site about the, http://jiss.eventbrite.com/ buy xanax here, =-DDD,

Permalink

Buy xanax online overnight shipping on November 16, 2011 at 9:52 AM:

Best Wishes, http://xoos.eventbrite.com/ xanax online overnight shipping, 029,

Permalink

Only buy tramadol on November 16, 2011 at 11:18 PM:
Best replica rolex watches on November 17, 2011 at 5:01 AM:

Hi, http://rrw.eventbrite.com/ replica rolex watches, iewj,

Permalink

buy cialis free on November 17, 2011 at 6:58 AM:

I want to say thanks!, http://www.squidoo.com/lensmasters/buycialises buy cialis, %OOO,

Permalink

fake coach purses on November 17, 2011 at 2:00 PM:

I like your work!, http://www.fakecoachpursee.iforums.us fake coach purses, bvc,

Permalink

replica rolex watches on November 17, 2011 at 3:09 PM:

Very interesting sites., http://www.replicarolexwai.iforums.us Best replica rolex watches, ufgav,

Permalink

Best free musical ecards on November 17, 2011 at 5:03 PM:

can you do thi for me, http://www.freemusicalecaa.iforums.us Best free musical ecards, =-),

Permalink

hair transplant on November 17, 2011 at 6:22 PM:

Only, http://www.twitter.com/hairtransplano hair transplant, bwpj,

Permalink

arthritis treatments free on November 17, 2011 at 7:20 PM:

So where it to find, http://www.arthritistreate.iforums.us arthritis treatments free, >:-)),

Permalink

power wheelchair on November 17, 2011 at 9:52 PM:

What is it, http://www.twitter.com/powerwheelchaa power wheelchair, =[[,

Permalink

panty pics now on November 18, 2011 at 2:30 AM:

Your Site Is Great, http://www.twitter.com/pantypicss panty pics now, fnxoo,

Permalink

diovan free on November 20, 2011 at 6:55 PM:

I like it so much, http://www.diovan.iforums.us diovan, >:-),

Permalink

buy xanax online on November 20, 2011 at 7:06 PM:

Is it so important?, http://www.buyxanax.iforums.us buy xanax, 1954,

Permalink

Golf Club Bags on June 03, 2012 at 9:43 PM:

Simple and very nice script ! Thanks !

Permalink

forex broker reviews on June 04, 2012 at 10:28 PM:

forex broker reviews can give potential currency market traders and investors,more of human insight into the different brokers available.

Permalink

Dustin James on June 11, 2012 at 6:48 AM:

I have read your post here and i enjoy a lot and learned a lot of things about your blogs! Great post!

http://www.worldstars.se/
"> företag

Permalink

top forex brokers on June 12, 2012 at 8:12 PM:

This article is about the top forex brokers in the market.

Permalink

David on June 14, 2012 at 5:06 AM:

Simple but very nice Script! Thanks!

http://www.moviestars.se/
"> moviestar

Permalink

top forex brokers for USA on June 17, 2012 at 9:49 PM:

Thank you for taking the time to publish this information! I’m still waiting for some interesting thoughts from your side in your next post thanks visit this site also top forex brokers for USA

Permalink

JessicaBuzz on September 13, 2012 at 1:01 AM:

Brushing up on the latest gossip with celebrity news websites and blogs is a great way to pass the time. You might even find some great people to talk to or find a new hobby by adding your own content to the sites.

Permalink

Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.