0

I'm trying to create a feature where users can chose which columns in the database they would like to filter on. Filter options are from_date, to_date, reference, offset, status and customer_id which are passed in the URL like so: example.com/file?from_date=2022-10-22&to_date=2022-10-29&reference=ref&offset=25&status=Picked&customer_id=0.

Now, I'm using prepared statements to safely fetch the requested data from the database. Because I don't know which $_GET parameter will be used, I cannot find a way to make the SQL query correct. Below you will find a piece of code that I have until now.

$where = "";
$bind = "";
$bind_count = "";
if(isset($_GET['from_date'])){
    $where .= " AND `created_on`>?";
    $bind .= ", " . $_GET['from_date'];
    $bind_count .= "s";
}
if(isset($_GET['to_date'])){
    $where .= " AND `created_on`<=?";
    $bind .= ", " . $_GET['to_date'];
    $bind_count .= "s";
}
if(isset($_GET['customer_id'])){
    $where .= " AND `customer_id`=?";
    $bind .= ", " . $_GET['customer_id'];
    $bind_count .= "s";
}
if(isset($_GET['status'])){
    $where .= " AND `status`=?";
    $bind .= ", " . $_GET['status'];
    $bind_count .= "s";
}
if(isset($_GET['reference'])){
    $where .= " AND `customer_reference`=?";
    $bind .= ", " . $_GET['reference'];
    $bind_count .= "s";
}
if(isset($_GET['offset'])){
    $where .= " OFFSET ?";
    $bind .= ", " . $_GET['offset'];
    $bind_count .= "s";
}
$i = 0;
$io = 0;
$data = array();
if($bind_count == ""){
    $stmt = $conn->prepare("SELECT * FROM `orders` LIMIT 25");
}else{
    $stmt = $conn->prepare("SELECT * FROM `orders` WHERE 1=1 $where LIMIT 25");
    $stmt->bind_param("$bind_count" . $bind);
}
$stmt->execute();
$oresult = $stmt->get_result();
if($oresult->num_rows > 0) {
    while($orow = $oresult->fetch_assoc()){
        // Continue rest of the code
    }
}

While running this code, I get the following errors:

Wrong parameter count for mysqli_stmt: :bind_param() Trying to get property 'num_rows' of non-object

I have tried using arrays to work with the variables and also tried to check which variable is being used and processing that into the query. Unfortunately, none of that worked.

user3783243
  • 5,368
  • 5
  • 22
  • 41
Jasper
  • 1
  • 1
    Your `$bind` is flawed. You are building that as if the PHP would parse the string as executable code. PHP doesn't execute strings. All `$bind`s should change to `$bind[] = ` then use the splat operator on the `bind_param`. e.g. `$bind[] = $_GET['from_date'];`... then `$stmt->bind_param($bind_count, ...$bind);` – user3783243 Oct 29 '22 at 20:04
  • your paramter "binding" can be an array by named placeholder to make life easier, see the second example https://www.php.net/manual/en/pdostatement.execute.php – Scuzzy Oct 29 '22 at 20:05
  • 2
    @Scuzzy This is mysqli, named can't be used. – user3783243 Oct 29 '22 at 20:06
  • The `offset` value also should be an integer, not a string. – user3783243 Oct 29 '22 at 20:06
  • It's worth noting that with PDO this is *extremely* simple, while `mysqli` makes it obnoxiously hard. – tadman Oct 29 '22 at 20:40

0 Answers0