SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

    Code:
    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

  2. #2
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the subquery will cause you grief in any version of mysql prior to 4.1

    you'll need to generate a join for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks rudy

    will work on that.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's exactly what I was doing. Hey, my improvements im sql are terribly slow, but i remember all your good advice rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •