How to split a search strings and put it into a prepare statement?

[bbmak]

Hi,

How do you split a search string by space and put it into a prepare query? 

$strings = $_POST['strings'];

$string_array = explode(" ", $strings);

$search = $mysqli->prepare("
SELECT * FROM core_table
where item_name in (
?
)
");

$search->bind_param("s", $string_array);
...

[mac_gyver]

to do this, you will need to dynamically build the sql query statement, with a place-holder for each value (you can use a few array function calls to do this), then you will need to dynamically bind all the parameters.

 

unfortunately, the php mysqli extension was designed poorly concerning prepared queries and dynamically calling the bind_param() statement requires a lot of extra, slow, messy code.

 

if you can, switch to use the php PDO extension. it is much easier, straightforward, and consistent to use, over the php mysqli extension.

 

to do this using the php PDO extension, after you build the sql query with the correct place-holders in it, you don't need to call any bind statements, and can just supply the exploded array as the input parameter to the execute() call.

Edited March 24, 2017 by mac_gyver

[bbmak]

Can you show an example of dynamic binding in PDO? I planned to move PDO in future, but I stayed with mysqli for now. I have to rewrite my functions and classes in pdo.

I may switch to hybrid mode though.

[Jacques1]

There is nothing to be shown. You create as many parameters as you need, you pass the array to execute(), and that's it.

$searchTerms = explode(' ', $_POST['strings']);

$searchStmt = $databaseConnection->prepare('
    SELECT
        * -- list the specific columns!
    FROM
        core_table
    WHERE
        item_name IN (NULL '.str_repeat(', ?', count($searchTerms)).')
');
$searchStmt->execute($searchTerms);

[Psycho]

If you want to do this with mysqli, there is a comment in the documentation with an example class: http://www.php.net/manual/en/mysqli-stmt.bind-param.php#109256

 

 

But, PDO is the way to go.

Edited March 24, 2017 by Psycho

[Jacques1]

The comment is outdated, and it's not true that mysqli requires messy code for a dynamic number of parameters. PHP 5.6 introduced the splat operator which makes this trivial to implement:

$searchTerms = explode(' ', $_POST['strings']);

$searchStmt = $databaseConnection->prepare('
    SELECT
        * -- list the specific columns!
    FROM
        core_table
    WHERE
        item_name IN (NULL '.str_repeat(', ?', count($searchTerms)).')
');
$searchStmt->bind_param(str_repeat('s', count($searchTerms)), ...$searchTerms);
$searchStmt->execute();

Nonetheless, PDO is the better extension.

[bbmak]

Awesome, thx u guys.