How to get row count from MySQL with PHP/PDO

PDOStatement::rowCount() does not work well with MySQL, so here is the workaround code:

$pdo = new PDO('mysql:dbname=database;host=localhost;charset=UTF-8', 'username', 'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
...
$total = $pdo->query('SELECT COUNT(*) FROM database')->fetchColumn();

How to create a column in MySQL that is automatically set to row creation or update time

1. Column that is automatically set to current time when row is created:
   

   column TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   

2. Column that is is automatically set to current time when row is updated:
   

   column TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
   

3. Column that is automatically set to current time when row is created or updated:
   

   column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   

Complete example:

CREATE TABLE table (
id                INTEGER(10) UNSIGNED AUTO_INCREMENT,
last_updated      TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created           DATETIME DEFAULT NULL,
);

[Solution] PHP echo function (or print, fputs, etc) adds BOM to the beginning of binary output

I spent more than an hour trying to understand why JPEG image that I get from MySql database is not displayed correctly with PHP code.

Instead of my image, Firefox shows image with "The image ... cannot be displayed because it contains errors." text.

First, I found out that PHP is adding UTF-8 byte order mark (BOM) to the beginning of output.

If I write the same image to file, BOM is not added.

Then I checked my PHP source file, but it does not contain BOM.

And after about an hour I found that it is enough to clean the output buffer with ob_clean function call:

<?php

require_once './include/database.php';

$id = $_GET['id'];

$database = new Database();
$image = $database->getImage($id);

$data = $cover['data'];
$type = $cover['type'];

header("Content-type: $type");

ob_clean();

echo $data;
?>

Enable UTF-8 encoding in PHP5/PDO/MySQL

When opening connection, add SET NAMES command to PDO constructor:

$pdo = new PDO('mysql:host=hostname;dbname=database', 'username', 'password',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
    );

And related MySQL databse setup: For a new database:

CREATE DATABASE database_name
 CHARACTER SET utf8
 DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci
 DEFAULT COLLATE utf8_general_ci
 ;

For an already created database:

ALTER DATABASE database_name
 CHARACTER SET utf8
 DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci
 DEFAULT COLLATE utf8_general_ci
 ;

For a new table:

CREATE TABLE table_name(
 ...
 )
 DEFAULT CHARACTER SET utf8   
 COLLATE utf8_general_ci;

For an already created table:

ALTER TABLE table_name
 DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci
 ;