Imploding an array into a MySQL query

Question

so I have a blog system, and i want to build a section for "related news", I am not making a 'Tags' system but just simply searching and storing the current title of the article (which is pulled from the database) in a string and exploding it to be able to later put all the words into a query, that later query will search all titles in the database to find any of those words, and if it does, it will return the title in a list. Here is the relevant code:

// note to stackoverflow peeps, $row_object_title is just the title that is pulled form the database
$row_object_title_lower = strtolower($row_object_title);
$keywords = explode(" ",$row_object_title_lower);

Code that is run later on the page:

$keywords_imploded = implode("','",$keywords);
$myquery = sql_query("SELECT object_title FROM table WHERE object_title IN ('$keywords_imploded')

Now i try to list the titles by printing the title out, but nothing is display.

I am sure there is matching titles in the database.

Thanks

Yeah, you're going to want to add some SQL escaping to that before you create a serious nightmare injection hole. — tadman
– tadman, Commented Apr 1, 2014 at 15:06
you need to echo out the query STRING, e.g. $sql = "SELECT ...", you're echoing out the query RESULT. — Marc B
– Marc B, Commented Apr 1, 2014 at 15:11
The IN clause will look for what is on the left in the list of values on the right. For example: WHERE id IN (2,3,5) - if id is in that list it will return true. In your case it is the opposite. It is likely you will have to build the query using several of these: object_title LIKE %keyword% — Paul Blundell
– Paul Blundell, Commented Apr 1, 2014 at 15:32

Kryten · Accepted Answer · 2014-04-01 15:12:20Z

9

Your array of keywords is generated with:

$keywords = explode(" ",$row_object_title_lower);

What if you have a title like "My Super Blog Post"? You're going to get:

$keywords = array( "My", "Super", "Blog", "Post" );

Later on, you query using those values imploded together:

$keywords_imploded = implode("','",$keywords);
$myquery = sql_query("SELECT object_title FROM table WHERE object_title IN ('$keywords_imploded')

The SELECT query is going to look like this:

SELECT object_title FROM table WHERE object_title IN ( 'My', 'Super', 'Blog', 'Post' );

I don't think that's going to find anything.

You need to re-evaluate how you're handling the list of titles (I think that's what you're going for, right?).

answered Apr 1, 2014 at 15:12

Kryten

15.8k7 gold badges50 silver badges70 bronze badges

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

Comments

Paul Blundell · Accepted Answer · 2014-04-01 15:45:59Z

4

It seems as though you have misunderstood how the IN clause works.

The IN clause will look for what is on the left in the list of values on the right. For example: WHERE id IN (2,3,5) - if id is in that list it will return true. In your case it is the opposite.

Something like this should work for what your after but there are likely to be better alternatives.

$sql = '';
foreach ($keywords AS $keyword)
{
    if ($sql != '')
        $sql .= ' OR ';

    $sql .= "object_title LIKE '%$keyword%'";
}

$query = 'SELECT object_title FROM table WHERE '.$sql;

% is a wildcard.

Just please remember to escape the values first.

answered Apr 1, 2014 at 15:45

Paul Blundell

1,8574 gold badges22 silver badges27 bronze badges

Comments

superphonic · Accepted Answer · 2014-04-01 15:05:54Z

-2

Try:

$keywords_imploded = join("','", $keywords);
$myquery = sql_query("SELECT object_title FROM table WHERE object_title IN ($keywords_imploded)

answered Apr 1, 2014 at 15:05

superphonic

8,0936 gold badges35 silver badges64 bronze badges

11 Comments

aaron-bond Over a year ago

IN('$keywords_imploded') -- if you used implode without the two inverted comma's like in OP this would break

user3332590 Over a year ago

I wish i could downvote you, that is for integers only, strings need the two quotes.

user3332590 Over a year ago

Correct me if im wrong, so all you did was replace implode with "join()" ??? you do realise join is just an alias for implode

superphonic Over a year ago

It was... I got a message in my inbox. Clicked it and saw it was marked as accepted. 5 seconds later it was gone...

aaron-bond Over a year ago

@superphonic lol thanks, at least I know I wasn't losing my mind there :p

|

Collectives™ on Stack Overflow

Imploding an array into a MySQL query

3 Answers 3

Comments

Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related