Hello,
We want to Find Related Posts from text field of table
we have posts table contain :
id,title,description,keywords,username,hits[/URL],comments

we want create sql command to get Related posts by keywords
but keywords field type is text " more than 1 keywords "

how we can do that ???


we tried to use this code

PHP Code:

function more_items_keywords($what,$keywords,$html) {
global $s,$m;
if ($html) $function_name = 'A_parse_part'; else $function_name = 'parse_part';
$where = get_where_fixed_part('',0,0,$s[cas]);$s[more_items_list]=10;
$table = $s[item_types_tables][$what];
$q = dq("select * from $table where keywords = '$keywords' order by hits limit 10",1);
while ($x = mysql_fetch_assoc($q)) $items[] = '<a href="'.get_detail_page_url($what,$x[n],$x[rewrite_url],'',5).'">'.$x[title].'</a>';
if ($items[0])
{ //$a[items] = implode('<br />',$items);
  //$a[title] = $m['more_'.$what];
  return implode('<tr><td class="posts" align="right">',$items);
}
} 


it's extract related posts if keywords field have 1 keyword only !!!
so that how we can Select only first four or six lines, from Sql text field ?

can you help

select * from $table where keywords LIKE '%$keywords%'

You should make that column a FULLTEXT index

we tried
$q = dq("select * from $table where keywords LIKE '%$keywords%' order by rand() limit 3",1);

but its give me same post !!!

can anyone help ?

If the related posts are by keywords then you would do something like: select keywords from table where keywords like %THEKEYWORDS% limit 4;

PHP Code:

$kw = explode(' ', $userKw); $sqlWhere = ' WHERE ';
if(count($kw)==0) $sqlWhere =''; else
foreach($kw as $w) {
    $sqlWhere .= 'keywords LIKE "%'.$w.'%" AND'
} $sqlWhere = substr($sqlWhere, 0, strlen($sqlWhere)-3);
// and use in SELECT ... FROM `table` $sqlWhere 


Originally Posted by vectorialpx

PHP Code:

$kw = explode(' ', $userKw); $sqlWhere = ' WHERE ';
if(count($kw)==0) $sqlWhere =''; else
foreach($kw as $w) {
    $sqlWhere .= 'keywords LIKE "%'.$w.'%" AND'
} $sqlWhere = substr($sqlWhere, 0, strlen($sqlWhere)-3);
// and use in SELECT ... FROM `table` $sqlWhere 


Will work if all keywords are matched but not if say, two out of three are.I'm not a MySql expert but I can't think of any way of doing it in one clever/fast query (I'd be delighted to learn if someone finds a way). You could build up a load of boolean clauses based on keyword combinations but I'd do a query for each keyword, storing primary key and hit count in arrays, then do a bit of array matching and shuffling in code to get the desired result.