问题:如何准备一个包含大量if语句的预处理语句?

我的 sql 查询有大约 25 个可选字段。我正在做的是,使用 ajax(用于分页)将数据发布到另一个 php 文件以准备和执行查询。

示例(index.php)

// Get optional value
if (isset($_POST['il']) && !empty($_POST['il'])) $il = $_POST['il'];
// JS
change_page('0');
});

function change_page(page_id) {
 var dataString = page_id;
// Define optional value
 <?php if (isset($_POST['il'])&& !empty($_POST['il'])):?>
 var il = '<?php echo $il;?>';
 <?php endif; ?>

 // Send optional value
 $.ajax({
    type: "POST",
    url: "includes/post.php",
    data: {
    <?php if (isset($_POST['il'])&& !empty($_POST['il'])):?>'il': il,<?php endif; ?>  },
...

Post.php(处理操作的地方)

...
// Main Query (default)
$bas_query = "SELECT id, a, b, c, d, e FROM test.db WHERE a=$1 AND b=$2";

// If user has an input for 'il', add it to query.
    if (isset($_POST['il']) && !empty($_POST['il'])) {
        $il = $_POST['il'];
        $bas_query. = ' AND city='.$il;
}

// How it's executed after statements (without ifs included)
$exec = pg_prepare($postgre,'sql2', 'SELECT id, a, b, c, d, e FROM test.db WHERE a=$1 AND b=$2 OFFSET $3 LIMIT $4');
$exec = pg_execute($postgre,'sql2', array($1,$2,$3,$4));

我的问题是,我如何准备一个包含大量 if 语句的准备好的语句?我可以连接并准备pg_prepare的查询,但是在以正确的顺序执行查询之前如何分配值?

提前感谢您的帮助。

解答

试试这个方法

// Change your query to below
$bas_query = "SELECT id, a, b, c, d, e FROM test.db WHERE";

// Initiate your array and counter for prepared statement
$data = array();
$counter = 1;

// I assume you assigned a and b value same as below
...
// Add data to the array for each case
if (isset($_POST['il']) && !empty($_POST['il']))
    {
    $il = $_POST['il'];
    $bas_query.= ' AND city=$' . $counter;
    $counter++;
    $data[] = $il;
    } 
... /* other if statements */
// To prepare and execute the statement; (after all the if statements)
/* A) concatenate OFFSET and LIMIT end of your final query
   B) insert the OFFSET and LIMIT value to the array       */
$bas_query.= ' OFFSET $' . $counter . ' LIMIT $' . ($counter + 1);
$data[] = $your_offset_val;
$data[] = $your_limit_val;

$exec = pg_prepare($postgre, 'sql2', $bas_query);
$exec = pg_execute($postgre, 'sql2', $data);

您将有以下输出

SELECT id, a, b, c, d, e FROM test.db WHERE a=$1 AND b=$2 AND city=$3 OFFSET $4 LIMIT $5

Array ( [0] => a_value [1] => b_value [2] => city_value [3] => offset_value [4] => limit_value )