How could I retrieve rows specifying a keyword on PHP

Question

This is really a dumb question from a beginner, I have a table called "stations" inside my database and on it I have a column called "keywords". I need to retrieve all those rows that contain one specific keyword (let's say "pizza"). I tried with this but it didn't work:

foreach ($stations->result() as $row) 
{       
    foreach ($row->keywords as 'pizza') 
    {  

        <--my code-->
    }
}

How could I retrieve all rows that contain "pizza" inside a specific column using PHP?

You use backtick ` to escape keywords in MySQL, or you can use double quote if ANSI mode is enabled. — Eric J., Feb 12 '15 at 17:39
possible duplicate of [How do I escape reserved words used as column names? MySQL/Create Table](http://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table) — Eric J., Feb 12 '15 at 17:40
Where does your PHP code get $stations->result() from? That query needs to be changed to what I sent you. — Matt Runion, Feb 12 '15 at 17:45
`"I'm not asking to retrieve data from mysql"` Excuse me but I beg to differ since you put MySQL in the title, and also tagged it. — developerwjk, Feb 12 '15 at 17:49
codeigniter doesn't allow where clauses in SQL? Well if that's the case it would prevent adoption, so I doubt that. — developerwjk, Feb 12 '15 at 17:52
@developerwjk please take a cup of tea and relax, I never say nothing like that...I wrote also that I'm a beginner — NineCattoRules, Feb 12 '15 at 17:54
I put it the wrong way, but basically what I meant was: using a where clause is fast. Doing this in PHP like you intend to do is ridiculously slow. And I mean in processing speed not the time it takes to code it. — developerwjk, Feb 12 '15 at 17:56
@developerwjk Thank you very much for the info, I need to learn lot more, however I don't understand why my code has a syntax error — NineCattoRules, Feb 12 '15 at 18:01

score 2 · Answer 1 · answered Feb 12 '15 at 17:43

2

Instead of parsing it in PHP, you can select only the relevant rows using find_in_set:

SELECT *
FROM   stations
WHERE  FIND_IN_SET ('pizza', keywords) > 0

answered Feb 12 '15 at 17:43

Mureinik

297,002
52
306
350

score 2 · Accepted Answer · answered Feb 12 '15 at 17:58

2

You can accomplish this using explode() to turn the list into an array. Then you can use in_array():

foreach ($stations->result() as $row) {               
   if ( in_array('pizza', explode(",", $row->keywords) ) ) {
       <--my code-->
   }
}

explode(",", $row->keywords) turns your comma-separated list into an array.

in_array('pizza', $array ) returns TRUE if pizza is found, otherwise it returns FALSE.

answered Feb 12 '15 at 17:58

Digital Chris

6,177
1
20
29

after a BIG headache finally you saved me! Thanks alot – NineCattoRules Feb 12 '15 at 18:07

How could I retrieve rows specifying a keyword on PHP

2 Answers2