Jump to content
bbmak

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

Recommended Posts

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);
...

Share this post


Link to post
Share on other sites

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 by mac_gyver

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.