[SOLVED] Returning results using keywords search

[ryanwood4]

I'm using this basic code to display a list of recent articles, taken from a database. However, I'd like to change it, so it only displays links to articles with certain keywords in the title/body of the article.

 

Is this possible.

 

<?php
$con = mysql_connect("xxxx","xxxx","xxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("f1times_articles", $con);

$result = mysql_query("SELECT * FROM n2s_article ORDER BY article_id DESC LIMIT 0,10");

echo "<table class='links' border='0'>
<tr>
<th></th>
</tr>";

while($row = mysql_fetch_array($result))
  {
    $url = $row['article_url'] . "-" . $row['article_id'] . ".html";
    echo "<tr>";
    echo "<td class='link'><font face='verdana' size='1pt'>» <a href='$url'>".stripslashes($row['article_title'])."</a></td>";
    
  }

mysql_close($con);
?>

 

Example: Currently it displays the latest 10 articles. I would like it, so it displays the latest 10 articles, but only ones which contain the words: Silver, Arrow, Mercedes.

 

Any help is appreciated, as my knowledge of PHP is minimal. Thanks.

[Bricktop]

Hi ryanwood4,

 

Would you like these keywords to be dynamic, i.e. dependant upon what a user enters or clicks on?

 

Or are these keywords static? i.e. you enter them and they remain set.

 

Thanks

[ryanwood4]

Hi,

 

Static keywords.

 

Thanks.

[Bricktop]

Hi ryanwood4,

 

Change your MySQL query to read:

 

result = mysql_query("SELECT * FROM n2s_article WHERE match(articletitle,articlebody) against ('Silver', 'Arrow', 'Mercedes') ORDER BY article_id DESC LIMIT 0,10");

 

Change the 'articletitle' and 'articlebody' to match the actual row names in your table.

 

Hope this helps.

[ryanwood4]

I'm getting this error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/f1times/public_html/mclaren_news.php on line 24

[Bricktop]

Sorry, ryanwood4, I missed the $ from the beginning of the query, change it to:

 

$result = mysql_query("SELECT * FROM n2s_article WHERE match(articletitle,articlebody) against ('Silver', 'Arrow', 'Mercedes') ORDER BY article_id DESC LIMIT 0,10");

[ryanwood4]

I added that in beforehand, and got the same error.

[Bricktop]

Oh, sorry, you need to add a FULLTEXT index to the required fields before the query will work with the MATCH and AGAINST operators.

 

i.e.

 

ALTER TABLE n2s_article ADD FULLTEXT(articletitle,articlebody);

 

You could always try the simple search case of:

 

SELECT * FROM n2s_article WHERE articletitle,articlebody LIKE '%Silver%' or '%Arrow%' or '%Mercedes%' ORDER BY article_id DESC LIMIT 0,10");  

[ryanwood4]

Do I need to add a FULLTEXT query to the simpler version you suggested?

 

If not, I tried that and still get the same error.

[Bricktop]

No, fulltext is not required for the simpler version, try:

 

SELECT * FROM n2s_article WHERE 'articletitle,articlebody' LIKE '%Silver%' or  WHERE 'articletitle,articlebody' LIKE '%Arrow%' or WHERE 'articletitle,articlebody' LIKE '%Mercedes%' ORDER BY article_id DESC LIMIT 0,10");  

[ryanwood4]

Still getting this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/f1times/public_html/mclaren_news.php on line 24

 

 

<?php
$con = mysql_connect("xxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("f1times_articles", $con);

$result = mysql_query("SELECT * FROM n2s_article WHERE 'article_title,article_content' LIKE '%Silver%' or  WHERE 'article_title,article_content' LIKE '%Arrow%' or WHERE 'article_title,article_content' LIKE '%Mercedes%' ORDER BY article_id DESC LIMIT 0,10");  

echo "<table class='links' border='0'>
<tr>
<th></th>
</tr>";

while($row = mysql_fetch_array($result))
  {
    $url = $row['article_url'] . "-" . $row['article_id'] . ".html";
    echo "<tr>";
    echo "<td class='link'><font face='verdana' size='1pt'>» <a href='$url'>".stripslashes($row['article_title'])."</a></td>";
    
  }

mysql_close($con);
?>

 

I can't think of a reason as to why it won't work though. Thanks

[Bricktop]

OK, how about:

 

SELECT * FROM n2s_article WHERE 'article_title' LIKE '%Silver, Arrow, Mercedes%' AND 'article_body' LIKE '%Silver, Arrow, Mercedes%' ORDER BY article_id DESC LIMIT 0,10");  

 

Sorry, instead of going from memory I should have just run the query myself - would have saved you a lot of time!

[ryanwood4]

Thanks, that has solved the error, but the page isn't showing any results, it's just blank.

[Bricktop]

OK, it's probably because of the AND statement limiting the results, you can change this to OR i.e.:

 

SELECT * FROM n2s_article WHERE 'article_title' LIKE '%Silver, Arrow, Mercedes%' OR 'article_body' LIKE '%Silver, Arrow, Mercedes%' ORDER BY article_id DESC LIMIT 0,10");  

 

Or, you may only with to search the article title (or body) in which case;

 

SELECT * FROM n2s_article WHERE 'article_title' LIKE '%Silver, Arrow, Mercedes%' ORDER BY article_id DESC LIMIT 0,10");  

 

Obviously, change 'article_title' to 'article_body' if it's the artcile body only you wish to search.

 

Hope this helps.

 

 

[gevans]

The field needs to be unwrapped or wrapped with ticks not single suotes;

 

SELECT * FROM n2s_article WHERE 'article_title' LIKE '%Silver, Arrow, Mercedes%' ORDER BY article_id DESC LIMIT 0,10"); 

 

should be

 

SELECT * FROM `n2s_article` WHERE `article_title` LIKE '%Silver, Arrow, Mercedes%' ORDER BY `article_id` DESC LIMIT 0,10"); 

[ryanwood4]

Great Success! 

 

Thanks, Bricktop and gevans.

 

The problem was articles' weren't matching all the keywords.

 

How do I make it so it's Silver or Mercedes or Arrow, rather than Silver, Mercedes and Arrow?

[Bricktop]

Hi ryanwood4,

 

Change your query to:

 

SELECT * FROM `n2s_article` WHERE `article_title` LIKE '%Silver%' OR '%Arrow%' OR '%Mercedes%' ORDER BY `article_id` DESC LIMIT 0,10"); 

 

Hope this helps.

[ryanwood4]

Adding OR '%Arrows%' doesn't work, it only shows results containing the first keyword 'Silver'

[Bricktop]

SELECT * FROM `n2s_article` WHERE `article_title` LIKE '%Silver%' OR `article_title` LIKE '%Arrow%' OR `article_title` LIKE '%Mercedes%' ORDER BY `article_id` DESC LIMIT 0,10"); 

[ryanwood4]

Genius.

 

Thank you very, very, very much for all your help.

 

Ryan.