0

I have to create a web service in my application to pull data from a MySQL database using PHP. I don't know if what I want to do will work or not as I am new to PHP. Here is the code:

$queryString = "SELECT uniqueID, address FROM main WHERE (state = ?) AND (propType IN (?)";
$stmt = mysqli_prepare($this->connection, $queryString);    
mysqli_stmt_bind_param($stmt, 'ss', $itemst, $itempt);
mysqli_stmt_execute($stmt);

My question has to do with the binding statement for the propType column of my database. In my application, there are like 8 different property types that a user can click a checkbox to search. What I would like to do is to stitch the strings together into one value represented above by $itempt, and just bind this single string to the SQL statement, but I don't know if I can do this???

If I can't, then I have to do something like the following:

$queryString = "SELECT uniqueID, address FROM main WHERE (state = ?) AND (propType IN (?,?,?,?,?,?,?,?)";

This could get complicated in the binding expression because I would have to test which property types were selected and come up with a variety of different queries/binding expressions, which would be laborious.

So my question is, with an "IN" clause, can I stitch all of the string values into a single comma-separated string, and then just bind this single value to my query, or do I have to pass a separate variable for each possible value and bind to them individually?

Thanks for any insight!

sax
  • 337
  • 1
  • 14

1 Answers1

0

I edited my answer since it did not work and rewrote the snippet in PDO style and tested it (just with a different table schema).

Here it is:

$servername = "localhost";
$username = "user";
$password = "pass";
$db = "stackoverflow";

try{
    $con = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // set this if you want to bind LIMIT values

}catch(PDOException $e){
    $error =  "Connection failed: " . $e->getMessage();
}

$itemst = ['active']; // cast search parameters as array so you can merge them
// watch the order in which you bind them! Else you will never find matching entries
$container = ['some title 1','some title 3','some title 5'];
$limit = [0,2];
$params = array_values(array_merge($itemst,$container,$limit));
$bindStr = implode(",", array_map(function($val) { return "?"; }, $container));
$queryString = "SELECT course_title FROM mysqli_quick WHERE state = ? AND course_title IN ({$bindStr}) LIMIT ?,?";

$stmt = $con->prepare($queryString);

$stmt->execute($params);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($result);

Prints

array (size=3)
  0 => 
    array (size=1)
      'course_title' => string 'some title 1' (length=12)
  1 => 
    array (size=1)
      'course_title' => string 'some title 3' (length=12)
Yolo
  • 1,569
  • 1
  • 11
  • 16
  • I am new to PHP, so had to do a little research on your response, but this solution looks brilliant to me. If I understand correctly, I have to pass an array of search parameters to PHP, these will get mapped to a string of question marks that matches the number of my search parameters, and then I loop through them and bind each value individually. Is this correct? Will this also work with an array of strings instead of integers? – sax Apr 02 '17 at 22:00
  • Yes absolutely. You can also use strings. – Yolo Apr 02 '17 at 22:46
  • Yolo, although your solution appears to be a valid solution, it does not look like mysqli will allow you to bind parameters over multiple statements the way that you suggest. I am getting the error "mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement". Are you positive that you can bind parameters across multiple statements the way that you suggest? – sax Apr 04 '17 at 04:09
  • I am pretty sure it is possible. You can even do this `call_user_func_array(array($stmt, 'bind_param'), $params);` The error usually indicates that your question marks count does not match the number of parameters you binded. Check if you really have a mismatched count of ? and params by printing the $queryString and counting the question marks and count how many $params your passing including $itemst and any others you might have. Probably you can also print $stmt after binding and before executing and see any mismatched counts. – Yolo Apr 04 '17 at 11:36
  • I may give this a try, but I don't know if the call_user_func_array will work because you are not passing in the string of variable types. ie 'sssid', so I don't know if the binding would work properly. Perhaps it would if I passed the variable type string in as the first parameter??? It has been suggested by others on the web that in this scenario, PDO is a better option to do what we are trying to do here, so I may consider that as well. – sax Apr 04 '17 at 19:44
  • Yes forget mysqli functions :) i rewrote the snippet in PDO style and its working just fine. Mind the two comments in grey. – Yolo Apr 04 '17 at 21:20
  • Yolo, you are exceptional at this! I can't tell you what a huge help you have been and I appreciate you taking the time. It is working like a charm. The only hickup that I have is that my query has a LIMIT ?,? clause at the end, and I am having trouble binding the LIMIT parameters, a common complaint with PDO. The solution I found was to add this attribute to the connection $con->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); I don't know if this is a good or bad idea, but once I added it, the binding was accepted. Let me know your thoughts. Thanks! – sax Apr 05 '17 at 21:46
  • Glad i could help :) Yes you are right. Setting PDO::ATTR_EMULATE_PREPARES to false will allow binding of limit values. I updated the answer to include that. If you want to know more about the implications of this setting (should they effect you), see this link: http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not – Yolo Apr 05 '17 at 22:41
  • Yolo, you are the best! Thanks again for all of the help and effort!! – sax Apr 05 '17 at 22:51