Tag-Archive for ◊ mysql ◊

Simple mysql toggle (tinyint value)
Saturday, October 16th, 2010 | Author:

A very simple way to toggle a tinyint field in your mysql table.

The mysql table example:

CREATE TABLE `products` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
$sql = mysql_query(" UPDATE products SET visible = visible <> 1 WHERE id = '$id' ");

If you want to toggle the visible field of a specific product, just execute this query. If the field visible has value 0, it will be updated to 1. If it is already 1, it will be updated to 0.

Extra tip: I put the $id variable inside the ‘…’ quotes to avoid broken query, if the $id is empty or not an integer.

Category: Web development  | Tags:  | One Comment
Storing images from internet to mysql
Sunday, September 05th, 2010 | Author:

There is a lot of articles about storing images to mysql database, but they are mostly related to uploaded images. What about images that are available on the internet? I must say: very simple.

First you have to get the contents of image file:

$url = 'http://www.editor.si/template/images/logo.gif';
 
$image = file_get_contents($url);

That’s it. To store the $image to mysql database you should also put the addslashes() around it to avoid broken query.

You may also need to save the image’s size (length) and type to successfully retrieve it and show it from your database later.

Category: Web development  | Tags: ,  | One Comment
Login and remember me on many computers
Sunday, May 30th, 2010 | Author:

Everything has already been said and written about login and remember me. I’ve tried some different techniques and the best one seems to be “the clientside cookie”. Of course you cannot reveal the users password, but you have to store something else into the cookie.

So far I used a special cookie field for each user, which is a random md5 hash. If your visitor owns the ‘remember’ cookie, you just have to check: SELECT * FROM users WHERE cookie = ‘$_COOKIE[remember]‘.

When some users logs in with the ‘remember me’ checkbox checked, you have to write some random md5 hash into the cookie field and set a cookie with that value to the client’s browser.

What happens if some user wants to be remembered on more computers at the same time?

  1. You can ignore it. Just write a new md5 hash every time and users won’t be remembered anywhere else.
  2. Check if the cookie field exists and set it’s value to the cookie. If the field is empty you fill it first. You can fill it with some random hash also at the very creation of a new user…
  3. You don’t need the cookie field in the database, just create a random value from the other data, for example md5($id . $username . $email . $datefield) and put it into the cookie! The database query should be slightly changed to: SELECT * FROM users WHERE MD5(CONCAT(id, username, email, datefield)) = ‘$_COOKIE[remember]‘.

Be sure to check and escape the $_COOKIE variable before inserting into the query to avoid the SQL injection attack! I wrote it into the query just to simplify this post.

Category: Web development  | Tags: , ,  | 3 Comments
My approach to detecting and trapping bots
Saturday, February 27th, 2010 | Author:

Hi, there is another post from me. You should know something about PHP, MySQL and HTTP protocol to understand it well. It’s not my intention to describe how to manage statistics on your website, I am just illustrating it in order to explain how do I detect and trap bots.

I have a website with Mod Rewrite on, because I redirect everything to index.php document no matter what ever you type into URL line. To be more specific I redirect everything to index.php?q=*, so I can use the $_GET['q'] variable to manage different URLs.

The next step is logging every single visit into MySQL database. In order to do that I have a table of visits which looks something like this:

CREATE TABLE `visits` (
    `id` int(20) NOT NULL AUTO_INCREMENT,
    `datefield` datetime NOT NULL,
    `ip` varchar(100) NOT NULL,
    `useragent` varchar(255) NOT NULL,
    `uri` varchar(255) NOT NULL,
    `referer` varchar(255) NOT NULL,
    `session` varchar(32) NOT NULL,
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

When somebody visits my website I can insert some data into that DB table:

$ip = $_SERVER['REMOTE_ADDR'];
$agent = $_SERVER['HTTP_USER_AGENT'];
$uri = $_SERVER['REQUEST_URI'];
$referer = $_SERVER['HTTP_REFERER'];
$session = session_id();
mysql_query ("
    INSERT INTO visits(datefield, ip, useragent, uri, referer, session)
    VALUES (NOW(), '$ip', '$agent', '$uri', '$referer', '$session')
");

OK, so that’s really easy. The table visits represents raw data about every single visit and this is only the beginning. If you want to get some real benefit out of your statistics you should create some statistics summary and collect data into some useful information: daily hits, unique hits, referrers, bots visits, users browsers, users operation systems and so on.

There are many solutions to get it done right but as I said before it was not my intention to talk about that. Let’s just concentrate on bots visits. As you know there are many robots crawling through the web and collecting data from websites. It’s allways good to know who they are and what are they doing on your website. It’s also useful to trap and redirect bad robots away.

Look again at the table visits and check column useragent. It holds data about users browsers and it looks like Mozilla/5.0 (Windows; U; Windows NT 6.1; sl,en:us; rv:1.9.2) Gecko/20100115 Firefox/3.6 (.NET CLR 3.5.30729) when the user is human and something like Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) when the user is a robot. I could look for a word ‘bot’ in my useragent column and I should find most of them really easy.

But I found even easier way to do that. It’s true, robots are not very smart. They can’t resist trying to open the document robots.txt. When a robot comes around there is a huge possibility that it would search for robots.txt file. Of course, I don’t have one. If there would be such a file, a robot would open it and so it would slip around my statistics collector. But in my case I just see in my $GET['q'] variable that he wanted a robots.txt file (but my .htaccess file redirects him to index.php script).

That’s first step how can I detect bots because humans don’t search for robots.txt file very often. In addition with ‘bot’ word in useragent column I can be pretty sure if you are a human or you are just another bot. Of course I don’t like bots to go to index.php when they are requesting robots.txt file. So right after when I insert it’s visit into my database I create a fake robots.txt file with PHP code:

if($_GET['q']=='robots.txt'){
    $text = "User-agent: *\r\nDisallow: /email-list/";
    header("Content-Type: text/plain");
    echo $text;
    exit();
}

There is a slight trap for bad robots included. As you can see the robot requests robots.txt file and gets:

User-agent: *
Disallow: /email-list/

Good robots obey and don’t try to access the email-list folder. But bad robots do just that! They immediately try to get into my email-list folder… which doesn’t exist, of course! It’s a simple trap which helps me to separate good robots from bad ones. I have a separate table in my database just for robots where I specify if a robot is good or bad.

So, that’s it. It is up to your imagination what to do with bad robots. You can simply write them some die(‘spammer’); command, you can trap them into some PHP script and have fun with them or you can immediately redirect them to www.google.com! You can do whatever you want and live happily ever after!

Category: Web development  | Tags: , , , ,  | 2 Comments