You are viewing our Forum Archives. To view or take place in current topics click here.
[PHP] Best way to do an SQL statement based on parameters
Posted:

[PHP] Best way to do an SQL statement based on parametersPosted:

-Deano
  • Rated Awesome
Status: Offline
Joined: Aug 19, 201014Year Member
Posts: 5,243
Reputation Power: 532
Status: Offline
Joined: Aug 19, 201014Year Member
Posts: 5,243
Reputation Power: 532
I need to do an SQL statement depending on what parameters were sent to the php script.

For example:



if ( isset(param1) && isset(param2) && isset(param3) ) {
  SQLStatement1;
} else if ( isset(param1) && isset(param2) && !isset(param3) {
  SQLStatement2;
}



I would need to do a different statement for each case where the parameters are changed.

Is there a simple way to go about this? My current implementation is just a bunch of if statements checking if the variable is set.


<?php

  $1 = $_GET["1"];
  $2 = $_GET["2"];
  $3 = $_GET["3"];
 
  $conn = connect();
  $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
 
  $stmt = "";
  $results = "";
 
  if ( isset($1) && isset($2) && isset($3) ) {
    // 1, 2, 3
    $sql = "SELECT columns
            FROM table
            WHERE table.column1 = ?
            AND table.column2 = ?
            AND table.column3 LIKE ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, $1);
    $stmt->bindParam(2, $2);
    $stmt->bindParam(3, '%$3%');
  } else if ( isset($1) && isset($2) && !isset($3)) {
    // 1, 2
    $sql = "SELECT columns
            FROM table
            WHERE table.column1 = ?
            AND table.column2 = ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, $1);
    $stmt->bindParam(2, $2);
  } else if ( isset($1) && !isset($2) && isset($3)) {
    // 1, 3
    $sql = "SELECT columns
            FROM table
            WHERE table.column1 = ?
            AND table.column2 = ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, $1);
    $stmt->bindParam(2, '%$3%');
  } else if ( !isset($1) && isset($2) && isset($3)) {
    // 2, 3
    $sql = "SELECT columns
            FROM table
            WHERE table.column2 = ?
            AND table.column3 LIKE ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, $2);
    $stmt->bindParam(2, '%$3%');
  } else if ( isset($1) && !isset($2) && !isset($3)) {
    // 1
    $sql = "SELECT columns
            FROM table
            WHERE table.column1 = ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, $1);
  } else if ( !isset($1) && isset($2) && !isset($3)) {
    // 2
    $sql = "SELECT columns
            FROM table
            WHERE table.column2 = ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, $2);
  } else if ( !isset($1) && !isset($2) && isset($3)) {
    // 3
    $sql = "SELECT columns
            FROM table
            WHERE table.column3 LIKE ?";
    $stmt = $conn->prepare( $sql );
    $stmt->bindParam(1, '%$3%');
  }

  $stmt->execute();
  $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

#2. Posted:
Ryzen
  • Resident Elite
Status: Offline
Joined: May 16, 20177Year Member
Posts: 232
Reputation Power: 72
Status: Offline
Joined: May 16, 20177Year Member
Posts: 232
Reputation Power: 72
What exactly are you trying to accomplish with this code?
#3. Posted:
tortuga
  • Fairy Master
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
Status: Offline
Joined: Dec 25, 200914Year Member
Posts: 2,314
Reputation Power: 1686
I've never worked with PHP, but the idea is to isolate the conditional logic and use named parameters to our advantage.

$a = $_GET["a"];
$b = $_GET["b"];
$c = $_GET["c"];

$conditions = array();

if ( isset($a) ) {
    array_push( $conditions, "table.column1 = :a" );
}
if ( isset($b) ) {
    array_push( $conditions, "table.column2 = :b" );
}
if ( isset($c) ) {
    array_push( $conditions, "table.column3 LIKE :c" );
}

$condition = implode( " AND ", $conditions );

$sql = "SELECT columns FROM table WHERE {$condition}";
$stmt = $conn->prepare( $sql );
$stmt->execute([':a' => $a, ':b' => $b, ':c' => "%{$c}%"]);
#4. Posted:
lamronsavage
  • New Member
Status: Offline
Joined: Feb 25, 20168Year Member
Posts: 19
Reputation Power: 13
Status: Offline
Joined: Feb 25, 20168Year Member
Posts: 19
Reputation Power: 13
if u trynna check all 3 u can legit just do
isset(1,2,3)
#5. Posted:
-Deano
  • Rated Awesome
Status: Offline
Joined: Aug 19, 201014Year Member
Posts: 5,243
Reputation Power: 532
Status: Offline
Joined: Aug 19, 201014Year Member
Posts: 5,243
Reputation Power: 532
lamronsavage wrote if u trynna check all 3 u can legit just do
isset(1,2,3)


I need to check all combinations of all 3 being set.
(If the user does not enter parameter 3 but does enter parameter 1 and 2, the SQL statement would be different to one where they enter parameter 1 and 3.
#6. Posted:
Gavin-
  • Winter 2020
Status: Offline
Joined: Nov 02, 201311Year Member
Posts: 4,340
Reputation Power: 1865
Status: Offline
Joined: Nov 02, 201311Year Member
Posts: 4,340
Reputation Power: 1865
To make it shorter couldnt you not just do on the first if condition check if all of them are not set else do your logic ?

Sorry if i dont understand the question.
#7. Posted:
-Deano
  • PC Master Race
Status: Offline
Joined: Aug 19, 201014Year Member
Posts: 5,243
Reputation Power: 532
Status: Offline
Joined: Aug 19, 201014Year Member
Posts: 5,243
Reputation Power: 532
Gavin- wrote To make it shorter couldnt you not just do on the first if condition check if all of them are not set else do your logic ?

Sorry if i dont understand the question.


I do that already.

Basically the conditions are the following:

- 1 + 2 + 3
- 1 + 2
- 1 + 3
- 2 + 3
- 1
- 2
- 3
Jump to:
You are viewing our Forum Archives. To view or take place in current topics click here.