How to use a part of value from a variable to show queries in mysql using stmt prepared

Question

I tried for more then 2 days to find the answer of how to use variable which contains more words with special charactes in an only variable... to showing the queries which match with the one word from the variable.. So I have a column keywords which contain values with more words in the some record (more words for hashtags system) e.g. (#top-10, #travel, #italy) in the some variable called $keyword .

So the problem is when i have more hashtags words in the some record (column keywords) doesnt match with any word from the column(titlu) for the all the queries of the some table. Even if I made a function which remove the special characters called function clean which clean the special charaters from the variable (e.g. $keyword = "#top-10, #travel, #italy") into (top-10travelitaly) . But doesnot show me the queries which contain into column(titlu) for exemple word top or travel or italy even if I have a lot queries to match with that.... So What i missed, I tried also using SELECT id, titlu, link, poza, alt, keywords, linknews FROM stiinta WHERE approved='1' AND replace(replace(replace(keywords, ',', ''), '-', ''), ' ', '') LIKE ? ...

so my $keyword the variable what suppose to take the value from column keyword from the specifically record

$stmt = $con->prepare('SELECT keywords FROM stiinta WHERE link = ? LIMIT    1');
$stmt->bind_param('s', $pageid);
$stmt->execute();
$stmt->bind_result($keyword);//variabla pe care o vrei inlocuita prin bind_Result in loc de get_Result
while ($stmt->fetch())    {
   $keyword;    // faci acelasi lucru fara $row 
}
$stmt->close();

then the function clean and the select stmt to show all the queries which match with the hashtag words from the $keyword

function clean($string) {// pentru a scapa de characterele speciale nedorite
   $string = str_replace(' ', '', $string); // Replaces all spaces with hyphens.
   return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars. adica #

}
echo clean($keyword);
$stmt = $con->prepare(" SELECT titlu, link, poza, alt, keywords, linknews 
    FROM stiinta 
    WHERE   replace(titlu, '-', '') LIKE  concat('%', ? , '%')  
    LIMIT 15");  
$stmt->bind_param("s", $keyword);
$stmt->execute();
if(!$stmt->execute()){
    echo "a aparut o eroare";}
$stmt->store_result();
 /* Get the number of rows */
  $num_of_rows = $stmt->num_rows;
echo  $num_of_rows;
if ($stmt->num_rows == 0) {
    echo "You did not have any query yet.<br>";
}    
$stmt->bind_result($titluKEY, $linkKEY, $pozaKEY, $altKEY, $keywordKEY, $linknewsKEY);  
while ($stmt->fetch())    {
    echo '<div id="articol-content-more"><a href="/'.$linknewsKEY.'"><img src="/images/'.$pozaKEY.'.jpg"class="articol-content-more-image" alt="'.$altKEY.'"><p class="articol-content-more-title">'.$titluKEY.'</p></a><span><a class="articol-content-more-afla" href="/'.$linknewsKEY.'">Citește mai multe</a></span><span class="articol-content-more-fl"><div class="fb-share-button" data-layout="button_count" data-href="http://xxxx.com/'.$linknewsKEY.'"></div></span></div><br>';        
    }
   $stmt->free_result();
$stmt->close();

when i have the $keyword more words e.g. (#top, #travel, #italy) is giving me $num_of_rows is 0 so dont have any query... even if I have a all records which contain the words in their column titlu

Answer 1

You need a separate phrase of the WHERE clause for each keyword. Assuming you nave cleaned your keywords and they are in a single text variable, you could do something like:

$keys_array = $explode(' ', $keyword);

$sql = 'SELECT <your stuff> FROM <your table> WHERE ';
foreach($keys_array as $key) {
    $sql .= 'REPLACE(titlu, '-', '') LIKE ? OR ';
}

This will get you a $sql variable with a LIKE phrase for each key, something like SELECT ... WHERE REPLACE(titlu, '-', '') LIKE ? OR REPLACE(titlu, '-', '') LIKE ? OR '. Note there is an extra ' OR ' on the end; just slice if off:

$sql = subset($sql, -4);

Now you can prepare your statement and bind the actual values to the placeholders:

$stmt = $con->prepare($sql);
foreach($keys_array as $key) {
    $stmt->bind_param('s', '%'.$key.'%');
}

$stmt->execute();

Note that all those LIKE queries are gong to really slow you down a lot if your database gets big. If you can remove the LIKE so the keywords have to match the data in that column exactly, you will be much happier in the long run.