-2

I want to pull a list of data from a table based on the requests by a user.

1. $query = "SELECT * FROM users LIMIT 10";
2. $query = "SELECT * FROM users WHERE fname = ? LIMIT 10";
3. $query = "SELECT * FROM users WHERE fname = ? AND mname = ? LIMIT 10";
4. $query = "SELECT * FROM users WHERE fname = ? AND mname = ? AND lname = ? LIMIT 10";

If no parameter is provided, query (1)
If first name provided, query (2)
If first and middle name provided, query (3)
If all are provided, query (4)

It's hard for me to know which one the user will request.
How do I prepare, bind, execute, and fetch data of the chosen one from above?

UPDATE: more details.

<?php

    $db = new mysqli("It's all OK");
    $query = "SELECT * FROM users LIMIT 10";
    $fname = (isset($_POST['fname']) AND !empty($_POST['fname'])) ? trim($_POST['fname']) : "";
    $mname = (isset($_POST['mname']) AND !empty($_POST['mname'])) ? trim($_POST['mname']) : "";
    $lname = (isset($_POST['lname']) AND !empty($_POST['lname'])) ? trim($_POST['lname']) : "";

    if(!empty($fname) AND empty($mname) AND empty($lname)){
      $query .= " WHERE fname = ? LIMIT 10";
     }elseif(!empty($fname) AND !empty($mname) AND empty($lname)){
      $query .= " WHERE fname = ? AND mname = ? LIMIT 10";
     }elseif(!empty($fname) AND !empty($mname) AND !empty($lname)){
      $query .= " WHERE fname = ? AND mname = ? AND lname = ? LIMIT 10";
     }

?>

Given all the details, query is built but it's hard to predict what the user will request.

I have done:

$stmt = $db->prepare($query);

Now I have a problem binding the unpredictable parameters.
Please help.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Lian
  • 113
  • 8
  • conditional statements, `OR` operator etc. lot of ways to go about this. – Funk Forty Niner Aug 28 '15 at 17:51
  • A working example or resource link will be appreciated. By the way, it has to be a prepared statement, not procedural one. – Lian Aug 28 '15 at 17:53
  • Very high level: just build the WHERE string with it initialized to "" and when your receive the input from the user, you just start concatenating the values. `if fname != null then whereStr += fname.toString()` and so on for each possible field. Add more logic to determine if you need to concatenate the "AND"s in there based on a field already having a value. At the end, concat the whereStr to the base SQL str and add the " WHERE " if whereStr is != "". So baseSQL + whereStr != "" ? " WHERE " : "" + whereStr - more to it than that but should get you started. – Craig Conover Aug 28 '15 at 17:57

3 Answers3

1

I guess you have variables somewhere like fname, mname, and lname. Put them in an array like

$options = ['fname' => $fname, 'mname' => $mname, 'lname'=> $lname];
$defaults = ['fname' => '', 'mname' => '', 'lname'=> ''];

$options = array_merge($defaults, $options);

$options = array_diff($options, []);

$query = "SELECT * FROM users";
foreach ($options as $key => $value){
     $query .= " AND $key = ?"
}
$query .= ' LIMIT 10';
gmponos
  • 2,157
  • 4
  • 22
  • 33
0

There is a brilliant solution

$fname = (!empty($_POST['fname'])) ? trim($_POST['fname']) : NULL;
$mname = (!empty($_POST['mname'])) ? trim($_POST['mname']) : NULL;
$query = "SELECT * FROM users WHERE 
        (? is null OR fname = ?) 
    AND (? is null OR mname = ?) 
    AND so on
LIMIT 10";

A variable will be used in the query only if its value is not null. this way you will need only one query and one set of parameters for any number of parameter combinations.

$stmt = $db->prepare($query);
$stmt->bind_param('ss', $fname, $fname, $mname, $mname);
$stmt->execute();

(You need to bind every variable twice though).
But this approach will leave you with only one query and with straight call to bind_param() with constant number of variables.

But if you still want to bind unknown number of variables, here is the solution.

Your own solution won't work if only lname provided or fname and lname but no mname.
Sometimes I think that sharing knowledge on SO is a biggest waste of time.

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Thanks for your reply. It'll work if there are only two parameters. But my question is what if there are three parameters, or only one parameter? – Lian Aug 28 '15 at 18:45
-1

Thanks all for your effort to answer. I really appreciated it.

After several attempts, I manage it to work.

    $stmt->prepare($query);
    if(!empty($fname) AND empty($mname) AND empty($lname)){
          $stmt->bind_param("s", $fname);
    }elseif(!empty($fname) AND !empty($mname) AND empty($lname)){
          $stmt->bind_param("ss", $fname, $mname);
    }elseif(!empty($fname) AND !empty($mname) AND !empty($lname)){
          $stmt->bind_param("sss",$fname,$mname,$lname);
    }
   $stmt->execute()

and bind result, fetch it that's all. It might not be the best answer, but it does work.

Again, thanks all.

Lian
  • 113
  • 8