Re: My SQL Search

[Nathan Simms]

I am searching for a specific company name that gets passed to this page.  I 
tried adding the mysql statement:

SELECT product_id, company_name, product_name, url, description
FROM product, company
WHERE product.product_id = company.company_id AND
description LIKE '%FORM.query%' AND
company_name LIKE '%FORM.query%' AND
product_name LIKE '%FORM.query%';
It didn't work so I changed out the '%' to '#' and remove the ';' because 
this statement is going into a .cfm file so it now looks like:

SELECT product_id, company_name, product_name, url, description
FROM product, company
WHERE product.product_id = company.company_id AND
description LIKE '#FORM.query#' OR
company_name LIKE '#FORM.query#' OR
product_name LIKE '#FORM.query#'
Didn't work either,  I don't get an error just no result.
But... when I use the following statement it works:
SELECT product_id, company_name, product_name, url, description
        FROM product, company
WHERE product.product_id = company.company_id AND
        company_name regexp '#FORM.query#'
The problem is that I need my statement to search the product_name and 
description fields as well.  However when I add them to my statement, it 
doesn't work???
The statment below does not work:

SELECT product_id, company_name, product_name, url, description
                FROM product, company
WHERE product.product_id = company.company_id AND
        company_name regexp '#FORM.query#' AND
        product_name regexp '#FORM.query#' AND
        description regexp '#FORM.query#'
Also what about indexes?

Thanks in advance for your help.

Nathan



On Fri, 22 Aug 2003 23:28:18 +0000
"Nathan Simms" <[EMAIL PROTECTED]> wrote:
Hi, I'm currently using the following mysql statement to perform a search.
Right now it is only returning the results which are found in the
description field.  How do I modify this statement so that it is searching
the following fields:  company_name, product_name, description.
SELECT product_id, company_name, product_name, url, description
FROM product, company
WHERE product.product_id = company.company_id AND description regexp
'#FORM.query#'
try this one:

SELECT
product_id, company_name, product_name, url, description
FROM
product, company
WHERE
product.product_id = company.company_id AND
description LIKE '%FORM.query%' AND
company_name LIKE '%FORM.query%' AND
product_name LIKE '%FORM.query%';
of course, you should use LIKE only when you're seeking for partial match.
in other cases use "....company_name='Bla'....." and don't forget about 
indexes.

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]