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, 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.
But isn't there one important difference as well: PostgreSQL costs, while MySQL comes free... Surely an important issue if you're a hobbyist!
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.
#MB: PostgreSQL costs nothing. In fact, it has a more open license than MySQL.
It is a very good thing, http://www.squidoo.com/lensmasters/percocets All about percocet, rckq,
Best, http://jiss.eventbrite.com/ buy xanax, :)),
What?, http://www.squidoo.com/lensmasters/percocets All about percocet, 8-],
Only, http://www.squidoo.com/lensmasters/buyadipex buy adipex online, xse,
Nice, http://rrw.eventbrite.com/ replica rolex watches online, >:[[,
It is a very good thing, http://www.squidoo.com/lensmasters/buycialises buy cialis, =)),
Give somebody the to a site about the, http://jiss.eventbrite.com/ buy xanax here, =-DDD,
Best Wishes, http://xoos.eventbrite.com/ xanax online overnight shipping, 029,
Great, http://www.squidoo.com/lensmasters/buytramadoles Only buy tramadol, sjwbvj,
Hi, http://rrw.eventbrite.com/ replica rolex watches, iewj,
I want to say thanks!, http://www.squidoo.com/lensmasters/buycialises buy cialis, %OOO,
I like your work!, http://www.fakecoachpursee.iforums.us fake coach purses, bvc,
Very interesting sites., http://www.replicarolexwai.iforums.us Best replica rolex watches, ufgav,
can you do thi for me, http://www.freemusicalecaa.iforums.us Best free musical ecards, =-),
Only, http://www.twitter.com/hairtransplano hair transplant, bwpj,
So where it to find, http://www.arthritistreate.iforums.us arthritis treatments free, >:-)),
What is it, http://www.twitter.com/powerwheelchaa power wheelchair, =[[,
Your Site Is Great, http://www.twitter.com/pantypicss panty pics now, fnxoo,
I like it so much, http://www.diovan.iforums.us diovan, >:-),
Is it so important?, http://www.buyxanax.iforums.us buy xanax, 1954,
Simple and very nice script ! Thanks !
forex broker reviews can give potential currency market traders and investors,more of human insight into the different brokers available.
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
This article is about the top forex brokers in the market.
Simple but very nice Script! Thanks!
http://www.moviestars.se/
"> moviestar
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
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.
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.
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