building a dynamic request based on user input for a search engine. I am lost.

[duuudie]

Hi

I am trying to create a sql statement based on user input for a little search engine.

I must confess that I am totally lost.

Here is the most complex request that a user could generate.

Can anyone spot my mistakes?

SELECT userstexts.textID 
, userstexts.textTitle 
, userstexts.textText 
, userstexts.category 
, userstexts.postedDate 
, userstexts.userID
, categories.catID 
, categories.catPosition 
, categories.catName 
FROM userstexts
, categories 
WHERE 1 = 1 
AND 
	( userstexts.textTitle LIKE '%blah%' 
	OR userstexts.textText LIKE '%blah%' ) 
AND userstexts.userID IN 
	(SELECT users.userID 
	FROM users 
	WHERE users.username 
	LIKE 'alex') 
AND userstexts.category = categories.catID 
ORDER BY categories.catPosition ASC

thanks in advance for your time and energy

[IlyaDevyatovsky]

I'd change

WHERE 1 = 1

to

WHERE (1 = 1)

and check for the error you get trying to run the request (with a MySQL database you may want to use phpMyAdmin to find out the problem).

[r937]

the subquery will cause you grief in any version of mysql prior to 4.1

you'll need to generate a join for that

[duuudie]

ok thanks rudy

will work on that.

[r937]

ok, good luck

i think i know what you're doing

you need to script the joining or not joining to the users table based on whether they enter a user name into a field on the search form

it will be easier to script if you use INNER JOIN syntax instead of adding another table name to the FROM list and another condition in the WHERE clause

holler if you don't know what that means

[duuudie]

That's exactly what I was doing. Hey, my improvements im sql are terribly slow, but i remember all your good advice rudy