On 15 Jan 2004 at 12:33, Matthew Stuart wrote:
> 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
>
Hi Mat,
You might want to try:
SELECT *
FROM tbl_allarticles
WHERE
(
fld_headline LIKE '%userinput%' OR
fld_summary LIKE '%userinput%' OR
fld_body LIKE '%userinput%'
) AND fld_show = 1
Also, are you validating the user input so you don't get any SQL injection ?
I created this ASP function to make sure data is quoted properly
userinput = MySQLescape( Request("qstextfield") )
function MySQLescape (strData)
MySQLescape = Replace( strData, "'", "\'")
MySQLescape = Replace( MySQLescape """", "\""")
MySQLescape = Replace( MySQLescape Chr(0) , "\0")
end function
You should really check the type of data as well, to make sure its a string.
If you are going to have a lot of data to search you may want to try using a
FULLTEXT index on the searchable data:
<http://www.mysql.com/doc/en/Fulltext_Search.html>
Regards
Ian
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
