PHP running MySQL query

Question

So let me give you some information, i have a blog system which is backed to a database, the database holds the title of the articles. Now i have tried to create a "related news" feature, and it is rather basic, it just takes the title of the existing page and splits it up like so (Note that $row_object_title is just the title pulled from the database):

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

I then run it through my function:

exclude_conjuctions($keywords);

code for that function(looks for certain words and removes it from the array:

function exclude_conjuctions($array){
    global $keywords_new;

    $keywords_new = $array;
    $conjuctions = array("here","to","and","but","or","nor","for");

    $counter = count($keywords_new);
    foreach($conjuctions as $conjuction){
        for($i=0;$i <= $counter;$i++){
            if ($keywords_new[$i] == $conjuction){
                unset($keywords_new[$i]);
            }
        }
    }

    return $keywords_new;
}

So now i will build my query to retreive all articles that have the keywords in the title:

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

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

$squery = 'SELECT object_title FROM example_table WHERE '.$sql;

NOW. It seems to be working okay, but there are times when it returns a title which does not have the same words as the current article, so i investigated it and it seems it picks up parts of the word and returns it, which is of course not what we want, if you are confused take a look at this image:

http://puu.sh/7UhhW.jpg

Note how i search for "dow" and those letters are found in both the current title and the retrieved titles. Of course i need it to only return related articles that have the full words in the title, not part of the words. What am i doing wrong guys? maybe my MySQL query needs to be changed? maybe there is a better solution? would love some help.

This is a problem as you can imagine.

Thanks for the help in advance.

could you put echo $sql; before $squery(stuff) and show full query ? — przeqpiciel, Apr 03 '14 at 10:09
@przeqpiciel Sure: SELECT object_title FROM webmanage_objects WHERE object_title LIKE '%messaging%' OR object_title LIKE '%service%' OR object_title LIKE '%whatsapp%' OR object_title LIKE '%goes%' OR object_title LIKE '%down%' OR object_title LIKE '%same%' OR object_title LIKE '%day%' OR object_title LIKE '%it%' OR object_title LIKE '%boasts%' OR object_title LIKE '%of%' OR object_title LIKE '%record%' OR object_title LIKE '%usage%' AND parentid=564 AND is_visible=1 AND archived=0 ORDER BY published_date DESC LIMIT 0,10 — user3378366, Apr 03 '14 at 10:12
what if you this query put into phpmyadmin ? It is retur nwhat you want ? In my opinion you should put parenthesis somethink like this select fields from table ( you builded OR's query ) AND (parentid=564 AND is_visible=1 AND archived=0) ORDER BY ... other stuff — przeqpiciel, Apr 03 '14 at 10:15

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

Try doing LIKE '% {$keyword} %'

Also your query is vulnerable for SQL Injections.

How can I prevent SQL injection in PHP?

EDIT : A better way to do this would be using a Regular Expression:

REGEXP '[[:<:]]{$keyword}[[:>:]]'

Instead of LIKE...

edited May 23 '17 at 11:49

Community

1
1

answered Apr 03 '14 at 10:11

Brovoker

896
5
16

I already am aware of this,consultation on security is needed here. I will try your suggestion now. – user3378366 Apr 03 '14 at 10:13
This seems to have done the job, can you explain how this has fixed it please. – user3378366 Apr 03 '14 at 10:26
he put spaces before and after looking word it like search for word starting with this keyword or endind on this word - IMO :) – przeqpiciel Apr 03 '14 at 10:28
Your wild-cards and word are now separated with a space. This probably won't work with first & last words though. – Brovoker Apr 03 '14 at 10:29
maybe you should use regexp instead like statement ? – przeqpiciel Apr 03 '14 at 10:30
@przeqpiciel Edited with your advice. – Brovoker Apr 03 '14 at 10:34
can you please explain the difference, sorry im just trying to learn – user3378366 Apr 03 '14 at 10:49
The second one will also detect the first and last words of the title. Because the first doesn't start with a space in front of it and the last one doesn't end with a space. – Brovoker Apr 03 '14 at 11:33

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

Try using the === operator instead of the == operator to compare strings. A good reason why can be found here

Also, you are wrapping your query with % on each side. That says to return all matches that CONTAIN those strings. Thus 'Dow' is contained in 'Down' and would be returned. You probably want to add a space around the %'s to only get matches that equal your keywords.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 03 '14 at 10:09

WildBill

9,143
15
63
87

This does not answer the question – Brovoker Apr 03 '14 at 10:09
Yes, This would be more suited to be a comment, however i did make the change and it made no difference. – user3378366 Apr 03 '14 at 10:11

score 0 · Answer 3 · answered Apr 03 '14 at 10:16

0

Could you implement the "LIKE" search to include a preceding and succeeding space? You would possibly need to have three conditions though to cater for words at the start and end of sentence:

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

answered Apr 03 '14 at 10:16

user3493215

1

Please explain what difference this would make. – user3378366 Apr 03 '14 at 10:24
The three conditions allow for $keyword to appear at the start, end or midway through a sentence. So as per your example where: $keyword = "dow" '% $keyword %' matches 'sometext dow some more text', '$keyword %' matches 'dow sometext' and '% $keyword' matches 'some text dow' – user3493215 Apr 03 '14 at 10:50

PHP running MySQL query

3 Answers3