[SOLVED] MySQL TCL 'LIKE' Problem

[Yutani]

Hi here,

First of all I would like to say hi to the members of this forum. Been working with eggdrop for quite a while but I'm fairly new to working with Windrop in combination with MySQL.

Now on with my problem...

I'm trying to get a bot up and running on which you can search the database for information. To do so, I've created the following piece of code:

Code: Select all

set query [::mysql::query $database {SELECT * FROM `table` WHERE `field` LIKE '%$args%'}]

$args is coming straight from the users input.

But somehow this does not seem to work. When I replace $args with a string like so:

Code: Select all

set query [::mysql::query $database {SELECT * FROM `table` WHERE `field` LIKE '%test%'}]

It seems to work...

Perhaps its a small and minor fix but I can't get it to work. Is there anyone who can help me out in fixing this?

Thanks!

[Sir_Fz]

If "args" is a parameter in your procedure, it returns a list and not a string as you were expecting. Try changing the variable name to something else.

[Yutani]

Code: Select all

proc pub:search {nick host hand chan args} {

	global database prefix

	set search			[stripcodes abcgru $args]
	set search			[::mysql::escape $search]

	set query			[::mysql::query $database {SELECT * FROM `table` WHERE `field` LIKE '%$search%'}]
	set row				[::mysql::fetch $query]

...

This is the piece of code including the procedure. As you can see I've changed the vars but it's still not working. Somehow I can't figure out why... Maybe it's the way I get the results from the database? Although it does seem to work so I'm a bit confused.

[nml375]

The point is, you should not use "args" as an argument for your proc.

The issue at hand here, is that tcl treats "args" specially, in that it will take 0 or more arguments, and add them to a list (each argument as a separate list item). This list is then stored in args. Simply storing the value of args into a new variable will not automatically remove the list..

This is the reason why you see many "poor" scripters using code like this. Most of them using it because "it works", but they have no real clue as to why:

Code: Select all

proc myproc {nick host hand chan args} {
set args [lindex $args 0]
...
}

That is very bad practice, and you should instead use something like this:

Code: Select all

proc myproc {nick host hand chan text} {
...
}

[Yutani]

I did not know that. I do have to mention that this is my very first own creation so there could be more newb mistakes in the code. But since I'm trying to learn how to use it, your comment was very helpful!

I've done what you suggested but still there seems to be a problem.

Code: Select all

proc pub:search {nick host hand chan text} {

	global database prefix

	set text			[stripcodes abcgru $text]

	set query			[::mysql::query $database {SELECT * FROM table WHERE `field` LIKE '%$text%'}]
	set row				[::mysql::fetch $query]

	set return			[lindex $row 1]

	putquick "PRIVMSG $chan :$prefix Results: \002$return\002"

	# free the sql results
	::mysql::endquery $query
}

This is what I've got so far and still... when I change $text to a string, it works. And when I change the string back to $text, it doesn't work.

[nml375]

Now you do

Ahh, overlooked one thing..
When you enclose a string using {}, you prevent variable- and command-substitutions. So unfortunately, the query is actually looking for $text rather than the content of it.

Restore that ::mysql::escape you had in your previous post for security issues, and alter the ::mysql::query command to read as below:

Code: Select all

set query [::mysql::query $database "SELECT * FROM `table` WHERE `field` LIKE '%${search}%"]

The use of ${search} rather than $search is simply to make sure tcl does'nt try to add the last % to the variable name.

[Yutani]

Very nice! Thanks! I've managed to get it to work so I can continue programming the script.

nml375 and Sir_Fz, thanks for the effort!