Page 1 of 1
Search Script Problem
Posted: Thu Jul 21, 2005 12:11 am
by Mr Tech
In have this code to search my database... It aint returning any results even when it should... any ideas? It was working yesterday. I may have changed soemthing...
Code: Select all
$showlinksresult = mysql_query("select *, MATCH (title,description,article) AGAINST ('$query') AS score from ".$tbl_name."_articles WHERE MATCH (title,description,article) AGAINST('$query') and pending!='y' order by $sort $order limit $vstart,$Spagecut") or die("<b>MySQL Error:</b> " . mysql_error());
$numrows = mysql_num_rows($showlinksresult);
if ($numrows==0) $searchresults .= "<tr><td><br>No Results...</td></tr>";
while ($row = mysql_fetch_array($showlinksresult)) {
...
Posted: Thu Jul 21, 2005 12:29 am
by harrisonad
have you tried to run you query directly to mysql?
Posted: Thu Jul 21, 2005 12:57 am
by Mr Tech
Yes. It just returned
SQL-query :
select *, MATCH (title,description,article) AGAINST ('html and table') AS score from article_articles WHERE MATCH (title,description,article) AGAINST('html and table') and pending!='y' LIMIT 0, 30
No results, no errors... Is there antyhing wrong with my MySQL that maybe stuffing it up?
I have set those three tables as fullindex:
Code: Select all
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 2 Drop Edit id
fulltext FULLTEXT None Drop Edit title
description 1
article 1
Posted: Sun Jul 24, 2005 9:40 pm
by Mr Tech
Any ideas?
Posted: Sun Jul 24, 2005 10:08 pm
by josh
Are all the fields you are trying to search FULLTEXT? Leave out your != 'y' where clause and see if that's whats messing it up.
Try matching against just one column, where results returned?
Also check if your search term is being marked as a stop word
If your search term is present in more then 50% of rows it is effectively a stop word to mysql
Sorry I don't have a solution, but I just felt I should just suggest this stuff incase you overlooked something.
Also read these restrictions if you haven't yet(I'm sure you already have)
http://dev.mysql.com/doc/mysql/en/fullt ... tions.html
and of course the manual page
http://dev.mysql.com/doc/mysql/en/fulltext-search.html
Posted: Sun Jul 24, 2005 11:19 pm
by Mr Tech
Thanks jshpro2...
Tried everything you said and I've had no luck. I'm not even using common words in my searches...
Since I can't get this to work, is there another method of searching that will work well until I sort this out?
Cheers
Ben
Posted: Mon Jul 25, 2005 6:47 pm
by Mr Tech
Anyone? I would really appreciate some help.
Posted: Mon Jul 25, 2005 6:57 pm
by josh
Theres always LIKE.
Try coding an example, in a new script construct a simple query, and search through an example table. Does that work? That would isolate the problem as to wether or not it's your code. If the test works ok do this:
Slowly add 1 thing at a time untill you basically have the query you posted above, if you can get an example of the query above working fine, just scrap the old code. If not this should at least help you isolate the problem.
You said it IS a myIsam right?
Posted: Tue Jul 26, 2005 4:03 am
by Mr Tech
Huh? I created a new table and created this code:
Code: Select all
$gettotall = mysql_fetch_array(mysql_query("select title from test where MATCH ( title ) AGAINST ( 'noob' )"));
echo $gettotall[title];
And it returns the one and only row in the table which does not have the word noob in it... I then added a new row and again it returned nothing...
ARGH!!!!!!
Posted: Tue Jul 26, 2005 4:14 am
by Mr Tech
The rlike worked... But if you write more then two words it doesn't work...
$gettotall = mysql_fetch_array(mysql_query("select count(*) as links from ".$tbl_name."_articles where article rlike '$_POST[tehquery]' and pending!='y'"));
Posted: Tue Jul 26, 2005 4:49 am
by Mr Tech
OK, I did a backup of the structure of the database:
Code: Select all
#
# Table structure for table `article_articles`
#
CREATE TABLE article_articles (
id int(11) NOT NULL auto_increment,
picdir int(11) NOT NULL default '0',
name text NOT NULL,
title varchar(255) NOT NULL default '',
cat int(11) NOT NULL default '0',
description text,
author text NOT NULL,
website text NOT NULL,
websitetitle text NOT NULL,
type varchar(255) NOT NULL default '',
featured varchar(255) NOT NULL default '1',
article text,
info text NOT NULL,
uptime varchar(255) NOT NULL default '',
parent int(11) NOT NULL default '0',
pending varchar(255) NOT NULL default 'y',
email text NOT NULL,
views int(11) NOT NULL default '0',
PRIMARY KEY (id),
FULLTEXT KEY title (title,article)
) TYPE=MyISAM;
That one doesn't work...
The I found this script
http://www.onlamp.com/pub/a/onlamp/2003 ... ltext.html... here is the table...
Code: Select all
CREATE TABLE blog_entries
(
entryID INT(9) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
posted INT(11) UNSIGNED NOT NULL DEFAULT '0',
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
title CHAR(255) NOT NULL DEFAULT '',
entry TEXT NOT NULL DEFAULT '',
PRIMARY KEY (entryID),
KEY (posted),
KEY (categoryID),
FULLTEXT(title,entry)
);
That one works... Why does that one work and mone doesn't?
Ben