Full Decent Full Decent - 4 months ago 16
MySQL Question

Merge multiple MySQL results from PHP, use same collation/sorting

I am using PHP to report on and merge information across three MySQL databases. The data from MySQL is basically:

SELECT email, money FROM accounts ORDER BY email


And in PHP I implemented a merge (pasting for fun):

$inputs = array(); // Elements of ['row'=>array, 'statement'=>PDOStatement]
while (count($inputs) > 0) {
$minimumInput = NULL;
$currentOutput = new OutputRow();
// Find minimum input
foreach ($inputs as $input) {
list($row, $statement) = $input;
if (is_null($minimumInput) || $row->email < $minimumInput->email) {
$minimumInput = $input['row'];
}
}
// Merge all matching inputs
$currentInput = 0;
do {
if ($inputs[$currentInput]['row']->email === $minimumInput->email) {
mergeDataToOutputRow($currentOutput, $inputs[$currentInput]['row']);
$fetch = $inputs[$currentInput]['statement']->fetch(PDO::FETCH_OBJ);
if ($fetch === FALSE) {
array_splice($inputs, $currentInput, 1); // that input is exhausted
} else {
$inputs[$currentInput]['row'] = $fetch;
}
} else {
$currentInput++;
}
} while ($currentInput < count($inputs));
// None of the inputs now match CURRENTOUTPUT
output($currentOutput);
}


This algorithm requires that the sorting in MySQL (
ORDER BY email
) is identical to the sorting in PHP (
$row->email < $minimumInput->email
). How can I confirm that this requirement is met for all possible (including non-latin) inputs?

Answer

Use ORDER BY email COLLATE utf8_bin in MySQL so that you get the same ordering as in PHP, thereby letting your 'merge' work. (At least, I think this is a solution.)

Comments