Posted on

Submitting forms using iFrames on iOS devices

Silent but deadly

Working on AtYourService.com.cy I have to submit a lot of forms using iframes since almost all forms have file attachments and a lot of feedback from our application before completing the process. A weird bug was eating my brain when all forms that returned html messages in the response object suddenly stopped working on iOS devices. I used a jQuery extension which submitted the form in an iframe and I returned json encoded data from the server. The script simply wrapped the response in a <pre> tag and fetched the contents to decode and pass them back on the client side handler. All works fine unless you have some numbers in an html json property and mr Apple decides to convert them to anchor links with a tel: prefix in the href to call phones directly. A handy little feature when it works correctly. But since the double slashes are not escaped you are left with an invalid json syntax. The quick solution is to just return the data and construct the html with javascript. it is indeed more efficient but when you are developing an mvp or just testing an experiment you simply dont’t have or want to waste time on making two templates.

Posted on

FIND_IN_SET. A handy little MySQL function

MariaDB MySQL

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

Posted on

Convert all tables and fields in an sql database to utf8

database encoding scheme
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)!!