0

I'm trying to setup a query that will return all rows in the database with the same subject.

Let's say our subject is: "We love beer." I want to return every row that contains the word "beer" So it will show titles like "Beer is cool" or "Beer is very nice" I want to return every row that contains the word beer. But the problem is, the subject is not always the same..

So I tried this in MySQLi:

    $query = "SELECT * FROM cms_news WHERE subject LIKE '%".$subject."%'";
    $result = mysqli_query($dbc, $query) or die('Whoops-> Query failed!');

But it only returns itself, the row with the exact same title. Now I read something about the SQL CONTAIN function, but I can't quite understand how it works.

Kara
  • 6,115
  • 16
  • 50
  • 57
The301
  • 111
  • 1
  • 1
  • 5
  • You need to use `LIKE '%beer%'` not `LIKE '%We love beer%'` – Barmar May 11 '17 at 20:36
  • Or you should implement full-text indexing. – Barmar May 11 '17 at 20:36
  • I don't think there's a `CONTAIN` function in MySQL. – Barmar May 11 '17 at 20:37
  • How do you know that "beer" is the focal point of the query? Why not "love"? – MonkeyZeus May 11 '17 at 20:40
  • The subject is a string from the database. That's the problem.. – The301 May 11 '17 at 20:41
  • That doesn't answer my question. – MonkeyZeus May 11 '17 at 20:41
  • If you echo the $subject and then die() immediately before the you build the $query, what does it say? – pendo May 11 '17 at 20:42
  • @pendo It says "We love beer.", per the second sentence of the question. – MonkeyZeus May 11 '17 at 20:42
  • 1
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard May 11 '17 at 20:42
  • I want it to return subjects of the same kind, so if my subject is "Cool kids" and there is more articles with let's say the subject "Cool dogs" i want it to return every row with the word "Cool" in it. – The301 May 11 '17 at 20:45
  • How do you know that "kids" would be the irrelevant word in that context? Maybe you should return "Bad kids" instead of "Cool dogs". I am not trying to harass you, I am trying to figure out your logic. – MonkeyZeus May 11 '17 at 20:46
  • Is there any other metadata (like a category or a tag) that could have the word beer in it? Because, I'm assuming you want to use this for other subjects than just beer. A hack way around it would be to explode() and then always choose the last word....this would require you stick to a naming convention for subjects. And yes per @JayBlanchard, please protect your query from SQL injection. – pendo May 11 '17 at 20:47
  • I will protect my query, thanks for the advice. I'll try to explode() – The301 May 11 '17 at 20:48
  • Thank you so much @pendo i exploded the string and took the first word, and it works! Thank you! – The301 May 11 '17 at 20:52
  • Wild card the `subject` and use it against `$subject`. – chris85 May 11 '17 at 21:06

1 Answers1

-1

Well, I would suggest you using PDO

$var1="some value here"
$query = "SELECT * FROM cms_news  WHERE subject LIKE ? ";
$params = array("%$var1%");
$stmt = $handle->prepare($query);
$stmt->execute($params);
Aki003
  • 422
  • 3
  • 13