Home Page

Site BEST viewed with:
FireFox & JavaScript
Compatible with: IE6+
Further info here.

Warning: you do not have javascript enabled. This WILL cause layout glitches.

Welcome to my domain. This is an open space where I, Jeffrey Riaboy, am keeping a compendium of my projects, thoughts, news, and miscellaneous ideas. I am a self taught programmer/computer nerd that works in C++ by choice, and too many other languages due to the facts of life. I have been programming virtually my whole life and working with computers since before I can remember. They are my passion, hobby, job, first love, and have served me well, though, been a constant pain in the anatomy. I hope you can find something of interest or use here, as that is the reason I spend my time creating and organizing this content. Enjoy. ^_^
The original (well... last) intro page to my website before this became the home. It is a flash portal to my personal sites of the past.
[1999-2001?] My ancient NES emulator made in Visual Basic (which was made to prove the power and flexibility [not speed] of the language). But alas, one of my friends, David Finch beat me to it by optimizing BasicNes [Don Jarrett].
[2002] A chronicle of my experiences and tinkering from early ’02 to early ’04 on an addictive yet horribly crappy MMORPG. Site also has some nice “hacking”/reverse engineering tutorials.
Ragnarok Hacking
I’ve temporarily set this to link to the Projects section of this website until I’m ready to announce the new website this will link to.
Updates Archive
Section: Projects > DWCF
  • DSQL v2.0.2.2
    • Added ability via DSQL->$StrictMode variable to update MySQL strict mode
    • Added DSQL->RawQuery() function which takes just a query, and does no modification on it. I found this required due to extremely slow times on building very large queries.
    • Added DSQL->EscapeString() function which escapes a string for use in a mysql query. It has an optional parameter that adds single quotes around the result.
    • Added DSQL::PrepareInsertList() function which takes an array of names, and turns it into the following string: ['a', 'b', 'c'] => “(`a`, `b`, `c`) VALUES (?, ?, ?)”
  • DWCF v1.1.1
    • Added previous variable lookup to GetVars.VarArray.SQLLookup via %THEVAR-$VARNAME%
Section: Projects > DWCF
DWCF v1.1 updates
  • Added functionality:
    • GetVars.VarArray.SQLLookup:
      • Additional string parameters that have %THEVAR% are replaced with the value being checked
      • If the first item is NULL, it will be removed, and the return will include full rows ("*") of the query result set
  • Updated functionality:
    • json_encode() In RetMsg() and CallByAction() now use JSON_UNESCAPED_UNICODE
    • GetVars.VarArray.IsOptional now only triggers if it is (boolean)true
    • Added additional description specification to GetVars.VarArray.SQLLookup which says it uses the additional parameters as values to fill in the SQL Query
  • LICENSE: Now applies to 1.x instead of 1.0.x
Download Content
Section: Projects > DSQL
DSQL v2.0.2
  • Added static member $InitialPrintAndDieOnError which DSQL.PrintAndDieOnError inherits on creation
  • Bug Fixes:
    • mysqli_set_charset is set to utf-8
    • In FormatSQLError() the date() function used for “Start Time” now uses “24-hour format of an hour with leading zeros” [date(“H”)] instead of “12-hour format of an hour without leading zeros” [date(“g”)]
Download Content
Post Archive
RSS Feed
MySQL: Update multiple rows with different values
There are 3 different methods for updating multiple rows at once in MySQL with different values:
    			INSERT INTO FooBar (ID, foo)
    			VALUES (1, 5), (2, 8), (3, 2)
  2. TRANSACTION: Where you do an update for each record within a transaction (InnoDB or other DBs with transactions)
    			UPDATE FooBar SET foo=5 WHERE ID=1;
    			UPDATE FooBar SET foo=8 WHERE ID=2;
    			UPDATE FooBar SET foo=2 WHERE ID=3;
  3. CASE: In which you a case/when for each different record within an UPDATE
    			UPDATE FooBar SET foo=CASE ID
    				WHEN 1 THEN 5
    				WHEN 2 THEN 8
    				WHEN 3 THEN 2
    			WHERE ID IN (1,2,3);

I feel knowing the speeds of the 3 different methods is important.

All of the following numbers apply to InnoDB.

I just tested this, and the INSERT method was 6.7x faster for me than the TRANSACTION method. I tried on a set of both 3,000 and 30,000 rows and got the same results.

The TRANSACTION method still has to run each individually query, which takes time, though it batches the results in memory, or something, while executing. The TRANSACTION method is also pretty expensive in both replication and query logs.

Even worse, the CASE method was 41.1x slower than the INSERT method w/ 30,000 records (6.1x slower than TRANSACTION). And 75x slower in MyISAM. INSERT and CASE methods broke even at ~1,000 records. Even at 100 records, the CASE method is BARELY faster.

So in general, I feel the INSERT method is both best and easiest to use. The queries are smaller and easier to read and only take up 1 query of action. This applies to both InnoDB and MyISAM.

Bonus stuff:

Using the INSERT method, there can be a problem in which NON-NULL fields with no default (in other words, required fields) are not being updated. You will get an error like “Field 'fieldname' doesn't have a default value”. The solution is to temporarily turn off STRICT_TRANS_TABLES and STRICT_ALL_TABLES in the SQL mode: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). Make sure to save the sql_mode first if you plan on reverting it.

As for other comments I’ve seen that say the auto_increment goes up using the INSERT method, I tested that too and it seems to not be the case.

Code to run the tests is as follows: (It also outputs .SQL files to remove PHP interpreter overhead)

//These 2 functions need to be filled in
function InitSQL()

function RunSQLQuery($Q)


//Run the 3 tests
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
    $DoQuery=function($Query) use (&$TheQueries)

    $DoQuery('DROP TABLE IF EXISTS '.$TableName);
    $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
    $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');

        $DoQuery('START TRANSACTION');
            $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);

            $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
        $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');

            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');

    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
Monitoring PHP calls

I recently had a Linux client that was, for whatever odd reason, making infinite recursive HTTP calls to a single script, which was making the server process count skyrocket. I decided to use the same module as I did in my Painless migration from PHP MySQL to MySQLi post, which is to say, overriding base functions for fun and profit using the PHP runkit extension. I did this so I could gather, for debugging, logs of when and where the calls that were causing this to occur.

The below code overrides all functions listed on the line that says “List of functions to intercept” [Line 9]. It works by first renaming these built in functions to “OVERRIDE_$FuncName[Line 12], and replacing them with a call to “GlobalRunFunc()” [Line 13], which receives the original function name and argument list. The GlobalRunFunc():

  1. Checks to see if it is interested in logging the call
    • In the case of this example, it will log the call if [Line 20]:
      • Line 21: curl_setopt is called with the CURLOPT_URL parameter (enum=10002)
      • Line 22: curl_init is called with a first parameter, which would be a URL
      • Line 23: file_get_contents or fopen is called and is not an absolute path
        (Wordpress calls everything absolutely. Normally I would have only checked for http[s] calls).
    • If it does want to log the call, it stores it in a global array (which holds all the calls we will want to log).
      The logged data includes [Line 25]:
      • The function name
      • The function parameters
      • 2 functions back of backtrace (which can often get quite large when stored in the log file)
  2. It then calls the original function, with parameters intact, and passes through the return [Line 27].

The “GlobalShutdown()” [Line 30] is then called when the script is closing [Line 38] and saves all the logs, if any exist, to “$GlobalLogDir/$DATETIME.srl”.

I have it using “serialize()” to encode the log data [Line 25], as opposed to “json_encode()” or “print_r()” calls, as the latter were getting too large for the logs. You may want to have it use one of these other encoding functions for easier log perusal, if running out of space is not a concern.

//The log data to save is stored here
global $GlobalLogArr, $GlobalLogDir;

//Override the functions here to instead have them call to GlobalRunFunc, which will in turn call the original functions
        'fopen', 'file_get_contents', 'curl_init', 'curl_setopt', //List of functions to intercept
) as $FuncName)
        runkit_function_rename($FuncName, "OVERRIDE_$FuncName");
        runkit_function_add($FuncName, '', "return GlobalRunFunc('$FuncName', func_get_args());");

//This optionally 
function GlobalRunFunc($FuncName, $Args)
        global $GlobalLogArr;
                ($FuncName=='curl_setopt' && $Args[1]==10002) || //CURLOPT enumeration can be found at https://curl.haxx.se/mail/archive-2004-07/0100.html
                ($FuncName=='curl_init' && isset($Args[0])) ||
                (($FuncName=='file_get_contents' || $FuncName=='fopen') && $Args[0][0]!='/')
                $GlobalLogArr[]=serialize(Array('FuncName'=>$FuncName, 'Args'=>$Args, 'Trace'=>array_slice(debug_backtrace(), 1, 2)));

        return call_user_func_array("OVERRIDE_$FuncName", $Args);

function GlobalShutdown()
        global $GlobalLogArr, $GlobalLogDir;
                file_put_contents($GlobalLogDir.date('Y-m-d_H:i:s.'.substr($Time-floor($Time), 2, 3), floor($Time)).'.srl', implode("\n", $GlobalLogArr));

PHP String Concatenation - Stringbuilder results

I wrote the code at the end of this post to test the different forms of string concatenation and they really are all almost exactly equal in both memory and time footprints.

The two primary methods I used are concatenating strings onto each other, and filling an array with strings and then imploding them. I did 500 string additions with a 1MB string in PHP 5.6 (so the result is a 500MB string). At every iteration of the test, all memory and time footprints were very very close (at ~$IterationNumber*1MB). The runtime of both tests was 50.398 seconds and 50.843 seconds consecutively which are most likely within acceptable margins of error.

Garbage collection of strings that are no longer referenced seems to be pretty immediate, even without ever leaving the scope. Since the strings are mutable, no extra memory is really required after the fact.

HOWEVER, The following tests showed that there is a different in peak memory usage WHILE the strings are being concatenated.

$OneMB=str_repeat('x', 1024*1024);
print memory_get_peak_usage();
Result=10,806,800 bytes (~10MB w/o the initial PHP memory footprint)

$OneMB=str_repeat('x', 1024*1024);
$Final=implode('', Array($OneMB, $OneMB, $OneMB, $OneMB, $OneMB));
print memory_get_peak_usage();
Result=6,613,320 bytes (~6MB w/o the initial PHP memory footprint)

So there is in fact a difference that could be significant in very very large string concatenations memory-wise (I have run into such examples when creating very large data sets or SQL queries).

But even this fact is disputable depending upon the data. For example, concatenating 1 character onto a string to get 50 million bytes (so 50 million iterations) took a maximum amount of 50,322,512 bytes (~48MB) in 5.97 seconds. While doing the array method ended up using 7,337,107,176 bytes (~6.8GB) to create the array in 12.1 seconds, and then took an extra 4.32 seconds to combine the strings from the array.

Anywho... the below is the benchmark code I mentioned at the beginning which shows the methods are pretty much equal. It outputs a pretty HTML table.

//Please note, for the recursion test to go beyond 256, xdebug.max_nesting_level needs to be raised.
//You also may need to update your memory_limit depending on the number of iterations

//Output the start memory
print 'Start: '.memory_get_usage()."B<br><br>Below test results are in MB<br>";

//Our 1MB string
global $OneMB, $NumIterations;
$OneMB=str_repeat('x', 1024*1024);

//Run the tests

//Output the results in a table
  Array('ConcatTest', 'ImplodeTest', 'RecurseTest'),
  Array($ConcatTest, $ImplodeTest, $RecurseTest)

//Start a test run by initializing the array that will hold the results and manipulating those results after the test is complete
function RunTest($TestName)
  RunTestReal($TestName, $CurrentTestNums, $StrLen);

  //Subtract $TestStartMem from all other numbers
  foreach($CurrentTestNums as &$Num)


  return $CurrentTestNums;

//Initialize the test and store the memory allocated at the end of the test, with the result
function RunTestReal($TestName, &$CurrentTestNums, &$StrLen)

//Concatenate 1MB string over and over onto a single string
function ConcatTest(&$CurrentTestNums)
  global $OneMB, $NumIterations;
  return $Result;

//Create an array of 1MB strings and then join w/ an implode
function ImplodeTest(&$CurrentTestNums)
  global $OneMB, $NumIterations;
  return implode('', $Result);

//Recursively add strings onto each other
function RecurseTest(&$CurrentTestNums, $TestNum=0)
  Global $OneMB, $NumIterations;
    return '';

  $NewStr=RecurseTest($CurrentTestNums, $TestNum+1).$OneMB;
  return $NewStr;

//Output the results in a table
function OutputResults($TestNames, $TestResults)
  global $NumIterations;
  print '<table border=1 cellspacing=0 cellpadding=2><tr><th>Test Name</th><th>'.implode('</th><th>', $TestNames).'</th></tr>';
  $FinalNames=Array('Final Result', 'Clean');
    $TestName=($i<$NumIterations ? $i : $FinalNames[$i-$NumIterations]);
    print "<tr><th>$TestName</th>";
    foreach($TestResults as $TR)
      printf('<td>%07.4f</td>', $TR[$i]/1024/1024);
    print '</tr>';

  //Other result numbers
  print '<tr><th>Final String Size</th>';
  foreach($TestResults as $TR)
    printf('<td>%d</td>', $TR[$NumIterations+2]);
  print '</tr><tr><th>Runtime</th>';
    foreach($TestResults as $TR)
      printf('<td>%s</td>', $TR[$NumIterations+3]);
  print '</tr></table>';