Split string by " " and use it in a query - What is wrong?

Question

I am new to php and coding in general.

Until now, my users had to match a perfect formatting when doing a search because the whole string had to be exactly included in the mysql table.

I am trying to split the search string by spaces into an array and then match every array with the search column "itemname". Using my code, I can only search for a single word. The $split[0]; is being searched, if another word is added, no matches will be found.

// Search from MySQL database table
$search=$_POST['search'];
$stat=$_POST['stat'];

$split = explode(" ", $search);

$query = $pdo->prepare("SELECT itemname, itemprice, itemupdate, itemstat, itemmarket, itemdiff FROM premium WHERE itemname LIKE ? AND itemname LIKE ? AND itemname LIKE ? AND itemname LIKE ? AND itemname LIKE ? AND itemname LIKE ? AND itemstat=? ORDER BY itemprice DESC LIMIT 15");
$query->bindValue(1, "%$split[0]%", PDO::PARAM_STR);
$query->bindValue(2, "%$split[1]", PDO::PARAM_STR);
$query->bindValue(3, "%$split[2]", PDO::PARAM_STR);
$query->bindValue(4, "%$split[3]", PDO::PARAM_STR);
$query->bindValue(5, "%$split[4]", PDO::PARAM_STR);
$query->bindValue(6, "%$split[5]", PDO::PARAM_STR);
$query->bindValue(7, $stat);
$query->execute();

A friend of mine changed my code a few days ago to protect me from sql-injections. He added "?" instead of a variable name into the query and changed something about the bindvalue things below the query.

I tried to make the delimiting work by myself, but I think I don't understand my own code good enough. What will I have to change in the query and the lines below and why? I am trying to understand.

And will i have to add each array number by itself? What if a user is searching with a 10-word-long string, but there are only 5 arrays in the query?

---

Short - What i would like to achieve:

SELECT * FROM premium WHERE itemname LIKE $split[0] and itemname LIKE $split[1] and itemname Like $split[2] ... and so on ...

---

EDIT:

Here is my code, before I tried modified it:

$search=$_POST['search'];
$stat=$_POST['stat'];
$query = $pdo->prepare("SELECT itemname, itemprice, itemupdate, itemstat FROM pricedata WHERE itemname Like ? AND itemstat=? ORDER BY itemprice DESC LIMIT 15");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->bindValue(2, $stat);
$query->execute();

I actually want to achieve some kind of "AND" search.

How it is: User wants to find "This is an apple tree" - He has to type "is an apple" or "This is an" because his string need to match the result as a whole string.

How it should be: Users should find the same sentence when typing "tree apple" or "this is tree". I'd like to achieve that by splitting up the search string and then check for every word if it matches inside the result. Only results should be found where EVERY word in the search string is present.

---

EDIT2

Code right now:

// Search from MySQL database table
$search=$_POST['search'];
$stat=$_POST['stat'];
$split = explode(" ", $search);
$query = $pdo->prepare("SELECT itemname, itemprice, itemupdate, itemstat, itemmarket, itemdiff FROM premium WHERE MATCH (itemname) AGAINST (?) ORDER BY itemprice DESC LIMIT 15");
$query->execute(array(implode(' ', $split)));


$sql = "UPDATE `cars` SET `model` = :model WHERE id = :id";
$statement = $pdo->prepare($sql);

Answer 1

If you want to use the full text search capabilities of mySQL, you could do this:

...
$query = $pdo->prepare("
    SELECT itemname, itemprice, itemupdate, itemstat, itemmarket, itemdiff
    FROM premium
    WHERE MATCH (itemname) AGAINST(?)
    ORDER BY itemprice DESC
    LIMIT 15
");
$query->execute([implode(' ', $split)]);

OR you can do a boolean search with + (must-haves) or - (mustn't-haves) and * (partial word matches). See example for partial must-haves:

$query = $pdo->prepare("
    SELECT itemname, itemprice, itemupdate, itemstat, itemmarket, itemdiff
    FROM premium
    WHERE MATCH (itemname) AGAINST(? IN BOOLEAN MODE)
    ORDER BY itemprice DESC
    LIMIT 15
");
$query->execute(['+'.implode('* +', $split).'*']);

For boolean mode you would have to clean up the just like a, the, etc to actually make it work best. I recommend to read some tutorials about the full-text search and you can implement better sorting and other cool things too.

Quick tip: you will need to full-index the itemname field in your db table.