Please help with this code

[Gawn]

Hi I am from Thainald and new to PHP/MySQL. I am doing a news website and I can't do the related news, while I can for latest news. My code for latest news is
"SELECT * FROM news WHERE news_id!=$news_ id AND cat=$cat ORDER BY news.published DESC".
This work OK for latest news.

Code for related news is
"SELECT * FROM news WHERE news_id!=$news_ id AND cat=$cat AND kw LIKE '%$kw%' ORDER BY news.published DESC"
This also show news as latest news. What I want is when I put keywords in kw field, I want to find and show news where kw is related.

My data base structure is
"TABLE `news` (
`news_id` int(6) unsigned NOT NULL auto_increment,
`cat` int(6) unsigned NOT NULL default '0',
`kw` varchar(255) collate utf8_bin NOT NULL default '0',
`title` text collate utf8_bin NOT NULL,
`date` text collate utf8_bin NOT NULL,
`brief` text collate utf8_bin NOT NULL,
`story` text collate utf8_bin NOT NULL,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`modified` datetime default NULL,
`published` datetime default NULL,
PRIMARY KEY (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bi n AUTO_INCREMENT= 35 ;"

Please tell me how do I create keywords to show related news.

Please help. Thanks in advanced.
Gawn

[michaelb]

It's hard to say without knowing how your data is organazied
(what your kw field looks like?)
but assuming one common scenario you may need to surround your keyword with spaces. Try to replace
kw LIKE '%$kw%'
with
kw LIKE '% $kw %' -- note the spaces around $kw

This of course creates a subtle bug when the very first and the very last keyword won't be found, unless your kw field is padded with spaces before the first and after the last keyword.
There's a simple solution to it too, but let's first see if this change works for you at all.

michael.

[ronverdonk]

You could try to use a regular expression on this. That makes your WHERE clause like this[php]..... WHERE kw REGEXP '$kw' .....[/php]
Ronald :cool: