SQL kan hvad SQL kan. Jeg har outlined nogle forskellieg løsninger.
Her er demo:
<?php
function get_connection() {
$con = new PDO('mysql:host=localhost;dbname=Test', 'root', '');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
return $con;
}
function two() {
$con = get_connection();
$stmt = $con->prepare('SELECT SUM(f1) AS sum FROM T1 WHERE f2 >= :f2');
$stmt->execute(array(':f2' => 'C'));
$row = $stmt->fetch();
$sum = $row['sum'];
echo "sum = $sum\r\n";
echo "f2 =";
$stmt = $con->prepare('SELECT f2 FROM T1 WHERE f2 >= :f2');
$stmt->execute(array(':f2' => 'C'));
while($row = $stmt->fetch()) {
$f2 = $row['f2'];
echo " $f2";
}
echo "\r\n";
}
function multi() {
$con = get_connection();
$stmt = $con->prepare('SELECT SUM(f1) AS sum FROM T1 WHERE f2 >= :f2; SELECT f2 FROM T1 WHERE f2 >= :f2');
$stmt->execute(array(':f2' => 'C'));
$row = $stmt->fetch();
$sum = $row['sum'];
echo "sum = $sum\r\n";
echo "f2 =";
$stmt->nextRowSet();
while($row = $stmt->fetch()) {
$f2 = $row['f2'];
echo " $f2";
}
echo "\r\n";
}
/*
DELIMITER //
CREATE PROCEDURE sp_two(_f2 varchar(50))
BEGIN
SELECT SUM(f1) AS sum FROM T1 WHERE f2 >= _f2;
SELECT f2 FROM T1 WHERE f2 >= _f2;
END//
DELIMITER ;
*/
function sp() {
$con = get_connection();
$stmt = $con->prepare('CALL sp_two(?)');
$arg = 'C';
$stmt->bindParam(1, $arg, PDO::PARAM_STR);
$stmt->execute();
$row = $stmt->fetch();
$sum = $row['sum'];
echo "sum = $sum\r\n";
echo "f2 =";
$stmt->nextRowSet();
while($row = $stmt->fetch()) {
$f2 = $row['f2'];
echo " $f2";
}
echo "\r\n";
}
function union() {
$con = get_connection();
$stmt = $con->prepare('(SELECT CAST(SUM(f1) AS CHAR) AS sumorf2 FROM T1 WHERE f2 >= :f2) UNION (SELECT f2 AS sumorf2 FROM T1 WHERE f2 >= :f2)');
$stmt->execute(array(':f2' => 'C'));
$first = true;
while($row = $stmt->fetch()) {
if($first) {
$sum = $row['sumorf2'];
echo "sum = $sum\r\n";
$first = false;
echo "f2 =";
} else {
$f2 = $row['sumorf2'];
echo " $f2";
}
}
echo "\r\n";
}
two();
multi();
sp();
union();
?>
2 kald er den portable løsning. 1 kald med 2 result set og SP er ikke portable. Union hacket er bare så grimt.