我有以下SQL查询：-
try {
$query = "SELECT VehicleSystemId, Fare FROM tblfixedfares
WHERE ShortPostCodeA IN ('$post_code_a','$post_code_a_two','$post_code_a_three','$post_code_a_four','$post_code_a_five')
AND ShortPostCodeB IN ('$post_code_b', '$post_code_b_two', '$post_code_b_three','$post_code_b_four','$post_code_b_five')
AND DayHalf = :day_half
AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':day_half', self::$day_half, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);
//$result = $stmt->fetch(PDO::FETCH_COLUMN);
$car = $result['Car'][0]['Fare'];
$est = $result['Est'][0]['Fare'];
$exec = $result['Exec'][0]['Fare'];
$exec_est = $result['ExecEst'][0]['Fare'];
$six_seater = $result['6B'][0]['Fare'];
$seven_seater = $result['7B'][0]['Fare'];
$eight_seater = $result['8B'][0]['Fare'];
$bus = $result['Bus'][0]['Fare'];
$wheelchair = $result['7W'][0]['Fare'];
$stmt->closeCursor();
$dbh = null;
// Set fare to specific vehicle
if ($_REQUEST['v_sys'] == NULL || $_REQUEST['v_sys'] == 'NULL' || $_REQUEST['v_sys'] == ''){
$result = $car;
return $result;
}
if ($_REQUEST['v_sys'] == 'Car') {
$result = $car;
return $result;
}
if ($_REQUEST['v_sys'] == 'Est') {
$result = $est;
return $result;
}
if ($_REQUEST['v_sys'] == 'Exec') {
$result = $exec;
return $result;
}
if ($_REQUEST['v_sys'] == 'ExecEst') {
$result = $exec_est;
return $result;
}
if ($_REQUEST['v_sys'] == '6B') {
$result = $six_seater;
return $result;
}
if ($_REQUEST['v_sys'] == '7B') {
$result = $seven_seater;
return $result;
}
if ($_REQUEST['v_sys'] == '8B') {
$result = $eight_seater;
return $result;
}
if ($_REQUEST['v_sys'] == 'Bus') {
$result = $bus;
return $result;
}
if ($_REQUEST['v_sys'] == '7W') {
$result = $wheelchair;
return $result;
}
}
基本上我需要做的是将其拆分为 5 个不同的查询，因此首先它将搜索：
query = "SELECT VehicleSystemId, Fare FROM tblfixedfares
WHERE ShortPostCodeA = '$post_code_a_five
AND ShortPostCodeB = '$post_code_b_five
AND DayHalf = :day_half
AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";
如果找到匹配项，它将返回结果，否则它将尝试下一个查询：
query = "SELECT VehicleSystemId, Fare FROM tblfixedfares
WHERE ShortPostCodeA = '$post_code_a_four
AND ShortPostCodeB = '$post_code_b_four
AND DayHalf = :day_half
AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";
同样，如果未找到结果，它将尝试下一个查询：-
query = "SELECT VehicleSystemId, Fare FROM tblfixedfares
WHERE ShortPostCodeA = '$post_code_a_three
AND ShortPostCodeB = '$post_code_b_three
AND DayHalf = :day_half
AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W')";
等等...
我只是不确定如何做到这一点，所以任何帮助将不胜感激。
您可以准备一次语句，然后用不同的值重用它（这是预准备语句背后的一个主要概念）：
$query = <<<EOSQL
SELECT VehicleSystemId, Fare FROM tblfixedfares
WHERE ShortPostCodeA = :post_code_a
AND ShortPostCodeB = :post_code_b
AND DayHalf = :day_half
AND VehicleSystemId IN ('Car', 'Est', 'Exec', 'ExecEst', '6B', '7B', '8B', 'Bus', '7W');
EOSQL;
$stmt = $dbh->prepare( $query );
foreach( $postCodes as $postCode )
{
$stmt->bindValue( ':post_code_a', $postCode['A'] );
$stmt->bindValue( ':post_code_b', $postCode['B'] );
$stmt->bindValue( ':day_half', $day_half );
if( $stmt->execute() === true && $stmt->rowCount() > 0 )
{
/* fetch data here */
/* then leave the loop */
break;
}
else
{
continue;
}
}