List:General Discussion« Previous MessageNext Message »
From:Matthew Stuart Date:January 15 2004 12:33pm
Subject:Problem searching table
View as plain text  
ASP and VB

I have created a search web page searching a MySQL table with the  
following code:

CREATE TABLE tbl_allarticles (
fld_ID mediumint(8) unsigned NOT NULL auto_increment,
fld_category tinyint(2) unsigned NOT NULL default '0',
fld_updateddate timestamp(8) NOT NULL,
fld_createddate timestamp(8) NOT NULL,
fld_mainstory char(1) NOT NULL default '',
fld_reldate date NOT NULL default '0000-00-00',
fld_headline varchar(255) default NULL,
fld_summary text,
fld_body mediumtext,
fld_displayname tinyint(1) unsigned default NULL,
fld_show tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY  (fld_ID),
KEY fld_category  
(fld_category,fld_updateddate,fld_createddate,fld_mainstory,fld_reldate, 
fld_headline,fld_displayname,fld_show),
FULLTEXT KEY fld_body (fld_body),
FULLTEXT KEY fld_summary (fld_summary)
) TYPE=MyISAM;

SELECT *
FROM tbl_allarticles
WHERE fld_headline OR fld_summary OR fld_body LIKE '%userinput%' AND  
fld_show = 1

with the variable of:

userinput    '    Request("qstextfield")

'qstextfield' is the name of the text area in which the user inputs  
their search criteria. It is working fine as a search but it seems only  
to be looking in one field, that being fld_body. I have done exact  
searches to match content in fld_headline and I am getting zero  
results. I have also tried AND instead of OR in the sql, but that  
returned no results on any search...

I suspect it is something to do with KEY, I created the table with  
MySQL-Front and expected it to create INDEX's, but it has created KEY,  
something I have never come across before.

Any help in how I might make this work will be appreciated, thanks.

Mat

Thread
Problem searching tableMatthew Stuart15 Jan
  • Re: Problem searching tableRoger Baklund15 Jan
  • Re: Problem searching tableIan Gibbons15 Jan