Search Script Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Search Script Problem

Post 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)) {
...
User avatar
harrisonad
Forum Contributor
Posts: 288
Joined: Fri Oct 15, 2004 4:58 am
Location: Philippines
Contact:

Post by harrisonad »

have you tried to run you query directly to mysql?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Any ideas?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Anyone? I would really appreciate some help.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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!!!!!!
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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'"));
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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
Post Reply