问在PHP中处理基于列条件的MySQL查询

EN

SELECT `to`, `senderid`, `message`, `unicode` 
FROM `sch_sms` 
WHERE `status`='0' 
AND `unicode` IN('1','2','3') 
LIMIT 1000

SELECT `to`, `senderid`, `message`, `unicode`
FROM `sch_sms` 
WHERE `status`='0' 
LIMIT 1000

while($row = <appropriate database function here>) {

    switch ($row['unicode']) {
    case 1:
        // code for this send type
    break;
    case 2:
        // code for this send type        
    break;
    case 3:
        // code for this send type        
    break;
}

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
    PDO::MYSQL_ATTR_DIRECT_QUERY => false,
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));
setup($pdo);

$currentUnicode = null;
foreach( $pdo->query('SELECT unicode, message FROM soFoo WHERE status=0 ORDER BY unicode', PDO::FETCH_ASSOC) as $row ) {
    // check if this message is the first of a new unicode-group
    if ( $row['unicode']!==$currentUnicode ) { 
        switchCode($row['unicode']);
        // it's the new "current" group
        $currentUnicode=$row['unicode'];
    }

    sendSMS($row);
}

function switchCode($newCode) {
    echo "--- switching to new Code: $newCode ---\r\n";
}

function sendSMS($data) {
    echo "sending msg: {$data['message']}\r\n";
}

// boilerplate, creates temp table and inserts sample data....
function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo (
            id int auto_increment,
            message varchar(32),
            unicode int,
            status int,
            primary key(id)
        )
    ');
    $stmt = $pdo->prepare('INSERT INTO soFoo (message,unicode, status) VALUES (?,?,0)');
    $stmt->bindParam(1, $message);
    $stmt->bindParam(2, $unicode);

    foreach( range('a','k') as $c ) {
        foreach(range(1,3) as $unicode) {
            $message = sprintf('msg%d%s', $unicode, $c);
            $stmt->execute();
        }
    }
}

--- switching to new Code: 1 ---
sending msg: msg1a
sending msg: msg1g
sending msg: msg1e
sending msg: msg1h
sending msg: msg1d
sending msg: msg1i
sending msg: msg1c
sending msg: msg1j
sending msg: msg1b
sending msg: msg1k
sending msg: msg1f
--- switching to new Code: 2 ---
sending msg: msg2i
sending msg: msg2j
sending msg: msg2h
sending msg: msg2g
sending msg: msg2k
sending msg: msg2f
sending msg: msg2c
sending msg: msg2a
sending msg: msg2e
sending msg: msg2d
sending msg: msg2b
--- switching to new Code: 3 ---
sending msg: msg3a
sending msg: msg3j
sending msg: msg3b
sending msg: msg3i
sending msg: msg3f
sending msg: msg3c
sending msg: msg3h
sending msg: msg3d
sending msg: msg3g
sending msg: msg3e
sending msg: msg3k

if ( changed(unicode) or changed(message) or toolong($to+row['to']) ) {
    send current sms request to gateway
    reset unicode, message, to
}
else {
    concat $to $row['to']
}

CREATE TABLE smsUsers (
    user_id int auto_increment,
    user_unicode int,
    user_phonenumber ...,
    ...
)

CREATE TABLE smsMessages (
    message_id int auto_increment,
    message varchar(32),
    ...
)

CREATE TEMPORARY TABLE smsMessageQueue (
    queue_id int auto_increment,
    message_id int,
    user_id int,
    queue_status int,
    ...
)