query with columns like array

Question

after trying any hours to add fulltext indexes into my table I found that Like %searchword% works too like that. I get from a first query the array $keywordse and after that I create with $keywordsonetoeight = implode(', ', $keywordse[0]); a comma seperated list with this output: string(25) "firstword, secondword, , , , , ,". I use mysql and the table runs with MyIsam.

$keywordse looks like this:

    array(1) { [0]=> array(8) { ["keyword1"]=> string(0) "" ["keyword2"]=> 
string(5) "ballo" ["keyword3"]=> string(5) "ballo" ["keyword4"]=> 
string(0) "" ["keyword5"]=> string(0) "" ["keyword6"]=> 
string(0) "" ["keyword7"]=> string(0) "" ["keyword8"]=> string(0) "" } }

my query:

"SELECT *
FROM posts 
WHERE title, text, area, contact LIKE %'$keywordsonetoeight'% AND (autorid != $userid)
ORDER BY id DESC";

The output is NULL

This works:

 "SELECT *
FROM posts 
WHERE title LIKE '%firstword%' AND (autorid != $userid)
ORDER BY id DESC";

Rajdeep Paul · Accepted Answer · 2016-01-10 10:35:54Z

5

Don't use implode() function directly on your array because several empty elements do exist in the array. First unset the empty array elements from your $keywordse[0] array, and then use REGEXP for your search.

Your code should be like this:

foreach($keywordse[0] as $key => $value){
    if(empty(trim($value))){
        unset($keywordse[0][$key]);
    }
}

$conditions = implode("|", $keywordse[0]);

$query = "SELECT *
FROM posts 
WHERE (title REGEXP '{$conditions}' 
OR text REGEXP '{$conditions}' 
OR contact REGEXP '{$conditions}') 
AND autorid <> {$userid} 
ORDER BY id DESC";

// Now execute this $query

Here's the reference:

REGEXP

edited Jan 10, 2016 at 10:35

answered Jan 10, 2016 at 9:42

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Bodoppels Over a year ago

Here I get ´array(0) { }´ too. But Ive checked that the array values are the same how in an column

Rajdeep Paul Over a year ago

@Bodoppels Are you still getting NULL? If yes then please update your question with the contents of $keywordse array.

Bodoppels Over a year ago

No I get array(0) { }. If I echo $conditions I get string(17) "|ballo|ballo|||||". The columns have an index and are fulltext is there a problem maybe?

Rajdeep Paul Over a year ago

@Bodoppels Found the bug. $conditions = implode("|", $keywordse); should be $conditions = implode("|", $keywordse[0]);. I've updated my answer.

Rajdeep Paul Over a year ago

@Bodoppels Glad I could help. Cheers! :)

|

Gouda Elalfy · Accepted Answer · 2016-01-10 09:39:34Z

1

if you want compare all values in your array you can user REGEXP:

"SELECT *
FROM posts 
WHERE title REGEXP '$first_word|$second_word|$third_word' 
OR text REGEXP '$first_word|$second_word|$third_word' )  AND (autorid != $userid)
ORDER BY id DESC";

edited Jan 10, 2016 at 9:39

answered Jan 10, 2016 at 9:12

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

3 Comments

Bodoppels Over a year ago

That doesnt change something, I get ´NULL´.

Bodoppels Over a year ago

Thank you, I need the second query but I get NULL, if I change one ´'%$keywordsonetoeight%'´ with a word that in an column in posts exists it works otherwise with your code dont. Anything must be wrong with the syntax in the query at the array

Gouda Elalfy Over a year ago

try the query after updating

Collectives™ on Stack Overflow

query with columns like array

2 Answers 2

10 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related