Sort powers in MySQL

We are using the ORDER BY CAST (field AS SIGNED) clause to sort the results by ‘power’ field. But it’s wrong!!

The way for sort ‘power’ it’s: ORDER BY `Power`+0

Strange… but works fine!!!

MySQL Count Distinct

I'm always (stupidly) trying to do something like "select count(distinct(country))..." when I want so see some results like...
Spain 50
France 20
Germany 10
The correct way to do it is "select country, count(*) as mycount from xxx group by country order by mycount desc"
Hopefully, now that I have this, I'll never do a search on google looking for something so obvious ;->

JShop MySQL Query Performance

NOTE: This quick and dirty hack (part of the insert function in /routines/dbAccess_mysql.php) kills the last ID - it would need to be made nice to work 100%.

$theQuery = str_replace('\"', '\\\"', str_replace("'", "''", $sql_query));
if ($result == FALSE) {
$this->lastError = mysql_errno().": ".mysql_error();
mysql_query("insert into amex_dbPerf (descript, err, time) values ('$theQuery', $this->lastError, $totalTime)");
return FALSE;
} else {
mysql_query("insert into amex_dbPerf (descript, err, time) values ('$theQuery', '', $totalTime)");
return $result;

CREATE TABLE `amex_dbperf` (
`actionID` int(11) NOT NULL auto_increment,
`descript` text NOT NULL,
`err` varchar(128) NOT NULL default '',
`time` decimal(12,11) NOT NULL default '0.00000000000',
PRIMARY KEY (`actionID`)
)

PHP Timing

list($secs, $micros) = split(" ", microtime());
$startTime = $secs + $micros;
//Do Something
list($secs, $micros) = split(" ", microtime());
$totalTime = ($secs + $micros) - $startTime;
totalTime is a decimal with seconds

Visual Studio Regular Expressions Example

I had a number of lines of code in the form...
custNode["address1"].InnerText
...where custNode and address1 were variable, and I wanted to replace them with the more safe...
SafeInnerText (custNode, "address1")
To do this, I made one of my occasional ventures into regular expressions, and thought I would document it for future reference.
Find expression was this...

{<:i*>}{\[\"}{:i*}{\"\].InnerText}

The find is broken up into 4 "tags" (terminology from Visual Studio). I broke it up into multiple tags since I only need 2 parts of the found expression, not all of them.

Here is a rundown of the parts...

{<:i*>} (first tagged expression)
Finds custNode["address1"].InnerText

{} to indicate it's an expression
< indicates the beginning of a word
:i* to find the "custNode" part (Matches the expression ([a-zA-Z_$][a-zA-Z0-9_$]*))
> the end of the word

{\[\"} (second tagged expression
Finds custNode["address1"].InnerText

{} to indicate it's an expression
\[ (escaping special chars) to find the left bracket
\"(escaping special chars) to find the double quote

{:i*} (third tagged expression)
Finds custNode["address1"].InnerText

{} to indicate it's an expression
:i* to find the "address1" part (Matches the expression ([a-zA-Z_$][a-zA-Z0-9_$]*))

{\"\].InnerText} (fourth tagged expression)
Finds custNode["address1"].InnerText

{} to indicate it's an expression
\"(escaping special chars) to find the double quote
\[ (escaping special chars) to find the left bracket
.InnerText - literal

Now the replace...
SafeInnerText (\1, "\3")

\1 and \3 are replaced by the first and third tagged expressions.

NOTE: \0 gives you ALL the tagged expressions - in this case, it would be custNode["address1"].InnerText

Zend (javaw.exe) using 100% CPU

Many thanks to Alberto Miranda who gave me the solution to a grave problem - Zend Studio 5.2.0 suddenly (without me changing anything that I'm aware of) started using close to 100% of CPU time (actually, the Zend process no - it was the javaw.exe process - but it was spawned by Zend). I used Alberto's solution (unchecking the "Use OS Look and Feel" option in the Zend preferences) and, after rebooting the computer and rebooting Zend (which was DEAD slow until it was fully launched) fixed the problem. Alberto, m'man, I owe you unas copas!

Generating Euro and Dollar Prices from Pounds

JSHOP: I wrote a file called amex_setPrices.php for the Lensbase site (in the admin folder) which sets price according to the following formula:
//Start: Price with VAT (e.g. EasySEPT, £18.00)
//Remove tax (VAT): £15.31
//Multiply by tax (VAT) rate: 15.31 * 119% = 18.22
//Multiply by exchange rate: 18.22 * 1.55 EUR = 28.24
//Round TO NEAREST DOLLAR (e.g. 28.2 = 28, 28.5 = 29): €28
I needed to apply this to both the $tableProducts, and a variation for $tableAdvancedPricing.
Checked into the LB source code.

myunhtmlentities

I got a reorder reminder cron job error that said:
ERROR: Failed to send reminder 61871 sent for order 315956, customer 2326, email bob&helen@talktalk.net

Looking at the reorder reminder table showed me that the email address is retreived from the customer table. There, it is "correctly" stored as bob&helen@talktalk.net. I say "correctly", since JShop generally does store string HTML escaped, which to me is weird - but it's also a fact.

Solution: whenever getting things from tables in JShop, remember that the data is probably HTML escaped. When you need unescaped strings, you can use the myunhtmlentities (from visiondirect/admin/resources/includeBase.php. If you need this outside the admin area, move it to someplace accessible by the non-admin code.

Simple Control.Invoke

Just read a great post by John Wood on dotnetjunkies.com called "SafeInvoke: Making GUI Thread Programming Easier in C#". It takes a lot of the drudgery out of the way by avoiding the direct typing of delegates. The code is here, and I include it here in case the link disappears.

using System;
using System.Collections;
using System.Reflection;
using System.Reflection.Emit;
...

public class SafeInvokeHelper
{
static readonly ModuleBuilder builder;
static readonly AssemblyBuilder myAsmBuilder;
static readonly Hashtable methodLookup;

static SafeInvokeHelper()
{
AssemblyName name = new AssemblyName();
name.Name = "temp";
myAsmBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(name, AssemblyBuilderAccess.Run);
builder = myAsmBuilder.DefineDynamicModule("TempModule");
methodLookup = new Hashtable();
}

public static object Invoke(System.Windows.Forms.Control obj, string methodName, params object[] paramValues)
{
Delegate del = null;
string key = obj.GetType().Name + "." + methodName;
Type tp;
if (methodLookup.Contains(key))
tp = (Type)methodLookup[key];
else
{
Type[] paramList = new Type[obj.GetType().GetMethod(methodName).GetParameters().Length];
int n = 0;
foreach (ParameterInfo pi in obj.GetType().GetMethod(methodName).GetParameters()) paramList[n++] = pi.ParameterType;
TypeBuilder typeB = builder.DefineType("Del_" + obj.GetType().Name + "_" + methodName, TypeAttributes.Class | TypeAttributes.AutoLayout | TypeAttributes.Public | TypeAttributes.Sealed, typeof(MulticastDelegate), PackingSize.Unspecified);
ConstructorBuilder conB = typeB.DefineConstructor(MethodAttributes.HideBySig | MethodAttributes.SpecialName | MethodAttributes.RTSpecialName, CallingConventions.Standard, new Type[] { typeof(object), typeof(IntPtr) });
conB.SetImplementationFlags(MethodImplAttributes.Runtime);
MethodBuilder mb = typeB.DefineMethod( "Invoke", MethodAttributes.Public | MethodAttributes.Virtual | MethodAttributes.HideBySig, obj.GetType().GetMethod(methodName).ReturnType, paramList );
mb.SetImplementationFlags( MethodImplAttributes.Runtime );
tp = typeB.CreateType();
methodLookup.Add(key, tp);
}

del = MulticastDelegate.CreateDelegate(tp, obj, methodName);
return obj.Invoke(del, paramValues);
}
}

FM Inventory Quantities

- "in box" is the number of physical boxes which actually exists
- "on hand" is "in box" minus any order lenses which have not yet been shipped. AKA would be "available for ordering".
- Newly added QtyCorrected is corrective qty used to adjust stock levels after checking inventory.



QUANTITY IN BOX: conversiontolenses + Total Bought - TOTAL QUANTITY SOLD TRANSFERED + QtyCorrected
Conversion to lenses: number
Total Bought: Initial Inventory Level + IQuantity Bought
Initial Inventory Level: number
IQuantity Bought: number
TOTAL QUANTITY SOLD TRANSFERED: QUANTITY TRABNSFERED SOLD + QUANTITY WHOLESALE TRABNSFERED SOLD
QUANTITY TRABNSFERED SOLD: number
QUANTITY WHOLESALE TRABNSFERED SOLD : number

Regediting ActiveSync Folder

In order to allow multiple pocket PCs to syncronize to the same folder, open RegEdit and go to HKEY_CURRENT_USER\Software\Microsoft\Windows CE Services\Partners\. There, each partnership defines it's shared folder in HKEY_CURRENT_USER\Software\Microsoft\Windows CE Services\Partners\[somenumber]\Services\Synchronization\Briefcase Path. Make sure all the Briefcase paths are set to the same folder. EXACT NAME IS IMPORTANT, so copy and paste the value from the partnership that you are interested in.

GMail PHPed

When FMImporter is done importing the CSV files into FileMaker, it sends an email to a gmail account with the mail title the name of the CSV file as the title and the contents as the body. Today, it happened that I needed to reprocess 115 of these files, and wanted an easy way to turn the emails into files.

Easy just didn't happen. I tried the FireFox GSpace Add On, and the shell extension GMail Drive, but they ONLY treat their own files (although both are cool tools to use a gmail account as a file store). They do this by using a special email title, and by storing the files as attachments.

Also tried PhpGmailDrive 0.3.2, but this only displays files which are attachments.

Finally, I hacked PhpGmailDrive's usage of libgmailer (after upgrading libgmailer to version 0.9 Beta 2), which is a PHP wrapper for the gmail API. The documentation is not the best, and I was never able to get more than the first 50 messages, but by applying a "label" to the gmail messages of interest, 50 emails at a time, I was able to get the following hacked code to download the emails as files...

if ($gm[$a]->connect()) { //
$gm[$a]->fetchBox(GM_LABEL, "fix", 0); // name of constants can be found in libgmailer.php
$snapshot = $gm[$a]->getSnapshot(GM_LABEL);
$lastidx = count($snapshot->box)-1; //

//$gf->TreeData .= "// ".count($snapshot->box).".\n";
//$gf->TreeData .= "// ".$snapshot->box_total.".\n";

//for ($c=0;$c<=$snapshot->box_total;$c+1)
//{
//$gm[$a]->fetchBox(GM_STANDARD, "inbox", $c); // name of constants can be found in libgmailer.php
//$snapshot = $gm[$a]->getSnapshot(GM_STANDARD);
//$lastidx = count($snapshot->box)-1;
foreach ((array)$snapshot->box as $item) { //
$gm[$a]->fetchBox(GM_CONVERSATION, $item["id"], 0); // name of constants can be found in libgmailer.php
$snapshot1 = $gm[$a]->getSnapshot(GM_CONVERSATION);
foreach ((array)$snapshot1->conv as $item1) {
$fh = fopen("C:\\down\\".$item1["subj"], "w");
$body = $item1["body"];
$body = str_replace(""", "\"", $body);
$body = str_replace("

", "", $body);
$body = str_replace("
", "\r\n", $body);
$body = str_replace("

", "", $body);
fwrite($fh, $body);
fclose($fh);
continue;

Selenium IDE

When tracking down my "system" php call bug, I needed to register a user again and again and again. After the first dozen times or so, I looked for macros for FireFox, and found Selenium IDE, which did the trick for me. The only "trick" you need to know is that the record button is the little red one to the RIGHT of the toolbar. Otherwise, it's all pretty intuitive.

php system call problems

Using something like...
system ("C:\\cool.exe C:\\coolfile.cfl", $returnVal);
simply does not work, and simply gives a returnVal of 1. Nothing in the php error log even with all errors turned on, nothing in the system log, and a "file not found" error returned by using some suggestions from the system documnentation page (something like echo system("(".$SysCmd." > /dev/null) 3>&1 1>&2 2>&3", $Ret);). I tried a million combinations, and they worked on the cmd line, but failed with system.

Solution?
system ("C:/cool.exe C:/coolfile.cfl", $returnVal);

My new rule is to always use forward slashes with system.

WebGains

For WebGains, be sure to keep a min. of £100 balance in the account. This is required by their contract, which states "5.4 The Merchant shall ensure that there are always sufficient cleared funds in the Merchant Account to meet all of the liabilities of the Merchant. In the event that the funds in the Merchant Account fall below £100, WBG reserves the right to suspend the Services unless and until sufficient funds are provided.". Got this from Derek Grant [derek.grant@webgains.com] on Mon 16/01/2006 16:47 as enclosure.

You can print the invoices from their web site. Log in, and look for the "account statement" link on the lower left.

PHP - auto-create variables from query

I'm often doing something really dumb like..

select someField, someOtherField from somewhere
while ($rec = fetch) {
  $someField = $rec["someField"];
  $someOtherField = $rec["someOtherField"];
  do something...
}

To save the steps of pulling the variables out into variables, I use the following...

$rociResult = $dbA->query ("select someField from $table where xxx");
if (TRUE == $rociResult) {
  while ($rociRec = $dbA->fetch ($rociResult)) {
    extract($rociRec, EXTR_PREFIX_ALL, "v");
    echo $v_someField;
  }
}

EasyPHP for Macintosh

If you want to set up a xAMP enviornment quickly, you use EasyPHP or one of the similar packages for Windows. But how about for Macintosh? Even though there are already parts of an xAMP setup there, it's MUCH quicker and easier to just set up MAMP, which will not interfer with any other xAMP components that you already have installed on your machine.

Win2K DVD Decoder

A bit off topic - to get free DVD playback on Win2K, try DScaler 5 - check the "DScaler 5 - Alpha MPEG Filters" link.