work – Page 2 – George Hadjivarnava

At AtYourService.com.cy we send a lot of emails. Especially when you compare it to the Cyprus market we send even more. Advocates of the Lean method we measure and report EVERYTHING. We even monitor some data real time. Reporting of a live table is a no go when you are running a real time application. So a lot of reports contain a lot of GROUP_CONCAT fields. When it comes to combinig these reprots with a semi hot table its kind of a mess to join over a field containing comma separated keys from another table.

SELECT * FROM 
table1,report_table2 
WHERE 
date BETWEEN 2015-01-01 AND NOW() 
AND table1.id in (x1,x2,x3)

But x1,x2,x3 is actually a text field in report_table2.

WHERE id in (table.field)

throws an error (well maybe not if the values are integers and MySql CASTs them to the first scalar value and if you are lucky the expression will evaluate to true if you hit the first one)

The solution?

SELECT * FROM 
table1,report_table2 
WHERE 
date BETWEEN 2015-01-01 AND NOW() 
AND FIND_IN_SET(table1.id,report_table2.field)

Maybe I should have known this but I didnt. It blew my mind and I thought I had to share.

Share your favourite MySQL function in the comments below

I cant count the times i had to debug a script for errors and found myself spending hours on it just to find out that it was a database collation / encoding issue. Living in a greek speaking country I find my self in such a situation quite frequently…
Here is a php script to convert all your database fields and tables to utf8 or any other encoding you may wish!

database encoding scheme — Database utf8 scheme

set_time_limit(900); /* or whatever your server can handle:)*/

function mysqlError()
{
    if (mysql_errno()) {
        echo "Mysql Error: " . mysql_error() . "\n";
    }
}

$username = "db_username";
$password = "sb_password";
$db = "databasename";
$host = "localhost";

$target_charset = "utf8";
$target_collate = "utf8_general_ci";

echo "Starting
";
$conn = mysql_connect($host, $username, $password);
mysql_select_db($db, $conn);
/*Select all the tables*/
$tables = array();
$r = mysql_query("SHOW TABLES");
mysqlError();
while (($row = mysql_fetch_row($r)) != null) {
    $tables[] = $row[0];
}

/* Begin processing */
foreach ($tables as $table) {
    $r = mysql_query("show index from {$table}");
    mysqlError();
    $indices = array();

    while (($row = mysql_fetch_array($r)) != null) {
        if ($row[2] != "PRIMARY") {
            $indices[] = array("name" =& gt; $row[2], "unique" =& gt; !($row[1] == "1"), "col" =& gt; $row[4]);
mysql_query("ALTER TABLE {$table} DROP INDEX {$row[2]}");
mysqlError();
echo "Dropped index {$row[2]}. Unique: {$row[1]}\n";
}
    }

    $res = mysql_query("DESCRIBE {$table}");
    mysqlError();
    while (($row = mysql_fetch_array($r)) != null) {
        $name = $row[0];
        $type = $row[1];
        $set = false;
        if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat)) {
            $size = $mat[1];
            mysql_query("ALTER TABLE {$table} MODIFY {$name} VARBINARY({$size})");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "CHAR")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} BINARY(1)");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "TINYTEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} TINYBLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "MEDIUMTEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} MEDIUMBLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "LONGTEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} LONGBLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "TEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} BLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} TEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        }

        if ($set)
            mysql_query("ALTER TABLE {$table} MODIFY {$name} COLLATE {$target_collate}");
    }

    /* re-build indices as dropped in previous step*/
    foreach ($indices as $index) {
        if ($index["unique"]) {
            mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$table} ({$index["col"]})");
            mysqlError();
        } else {
            mysql_query("CREATE INDEX {$index["name"]} ON {$table} ({$index["col"]})");
            mysqlError();
        }

        echo "Created index {$index["name"]} on {$table}. Unique: {$index["unique"]}\n";
    }

    /* set default collate on each table*/
    mysql_query("ALTER TABLE {$table}  DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
}
/* change database charset*/
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
mysql_close($conn);

And thats it… Make sure you change the database variables (Username password dbname)!!

Category: work

FIND_IN_SET. A handy little MySQL function

Convert all tables and fields in an sql database to utf8

Προεδρικές Εκλογές Κυπριακής Δημοκρατίας 2013 – Cyprus presidential elections 2013