#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2005
    Posts
    62
    Rep Power
    9

    dinamic Mysql LIKE query problem


    Got this code:
    Code:
    ...
    	elif accion1 == "1":
    		nombre = raw_input("The contact to search is: ")
    		cursor.execute("SELECT * FROM agenda WHERE nombre LIKE '%s' " % nombre)
    		result = cursor.fetchall( )
    		hay = cursor.rowcount
    		if hay == 0:
    			print "No results"
    		else:	
    			print "The results are::"
    			print
    			for record in result:
    				print "ID:",record[0],"==>",record[1]
    				print "Telephone:",record[2],
    				print "Email:",record[3]
    				print
    		raw_input("Enter to continue")	
    		clear()	
    ...
    The problem is in here:
    Code:
    ...
    nombre = raw_input("The contact to search is: ")
    ...
    cursor.execute("SELECT * FROM agenda WHERE nombre LIKE '%s' " % nombre)
    ...
    Suppose I got a contact who's name is Daniel Godspeed, then when I search for him by Daniel I get no results. This problem is resolved if I search for Daniel Godspeed.
    What I want is to search, for example, for "D", "Dan", or "Daniel" and get the result for Daniel Godspeed. Any clues for doing that?
    Thanks in advance.
    Daniel (but not Godspeed)
  2. #2
  3. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    You're not using a wildcard. So when you search for 'Dan', MySQL will only return fields that match 'Dan' only.

    The MySQL wildcard is %, so:
    "SELECT * FROM agenda WHERE nombre LIKE 'Dan%' ";
    - will match 'Dan', 'Daniel', 'Danaslkfpqcjecpcascjpscjspcajcksa'
    "SELECT * FROM agenda WHERE nombre LIKE '%Dan%' ";
    - will match 'Dan', 'Daniel', 'Danaslkfpqcjecpcascjpscjspcajcksa', 'aslkjnhaskcsanDan', and 'KdvsdvDanSasocasc' etc.

    --Simon
  4. #3
  5. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Oh - and if you want to match the word 'Dan' only and not 'aaDanaa' etc, then you should use this:

    SELECT * FROM agenda WHERE nombre regexp '[[:<:]]Dan[[:>:]]';

    --Simon
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2005
    Posts
    62
    Rep Power
    9
    Ok, but how can i make it if I'm using the %s thing??
    "SELECT * FROM agenda WHERE nombre LIKE '%s%' ";
    Or
    "SELECT * FROM agenda WHERE nombre LIKE '%s'% ";
    Or
    "SELECT * FROM agenda WHERE nombre LIKE %%s'% ";
    and etc...
    Does not work...
  8. #5
  9. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Double them up ( i.e. escape the literal % chars with another %:
    Code:
    query = "SELECT * FROM agenda WHERE nombre LIKE '%%%s%%' " % ( 'Dan')
    --Simon
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2005
    Posts
    62
    Rep Power
    9
    Thank u!! It worked.

IMN logo majestic logo threadwatch logo seochat tools logo