The Mysql UNION command.

Forum Moderators: open

Message Too Old, No Replies

The Mysql UNION command.

Should I use it for this, or am I being dumb?

jake66

8:23 am on Oct 2, 2007 (gmt 0)

Here is my present mysql query, which works fine.

$query = mysql_query("SELECT count(*) FROM blacklist where LOCATE(keystroke_entry, LOWER('$firstname')) > 0");
if(!$query) {
#handle error
}
$row = mysql_fetch_array($query);
if($row[0] > 0) { // $firstname contains a blacklisted entry
#redirect to error page
}

It compares the results of a form, to a list of random keystrokes to disable people from doing stupid things in the contact form.

For the life of me, I cannot figure out how to do this:

LOCATE(keystroke_entry, LOWER('$firstname' or '$lastname' or '$randombox')

I have tried the above query, replacing 'or' with ŚŚ and no luck.
I do not have much experience with locate() at all, but I've read up a bit about it and it appears as the bigger your table gets, the slower the results will be.
Is there a faster way to compare the form results against my database (using LIKE maybe? how could I do that?)

I am using php and mysql, not sure if it matters any.

phranque

11:54 am on Oct 2, 2007 (gmt 0)

you might try this:

$query_string = "SELECT count(*) FROM blacklist where LOCATE(keystroke_entry, LOWER('" . $firstname . "')) > 0 OR LOCATE(keystroke_entry, LOWER('" . $lastname . "')) > 0 OR LOCATE(keystroke_entry, LOWER('" . $randombox . "')) > 0";
$query = mysql_query($query_string);
...

this way you can also print the query string to make sure it is what you think it should be.
(i'm not a phper, but i think you need to do the string concatenation to get proper variable interpolation.)

jake66

1:32 am on Oct 3, 2007 (gmt 0)

Thank you phranque, that worked perfectly.

I really have no idea what you mean about:

i think you need to do the string concatenation to get proper variable interpolation.

Once my table starts getting larger, this is going to lessen in performance?
Is there a more efficient way to get results, or is this the only way to pull it as I need it?

Chiaroscuro

6:54 pm on Oct 5, 2007 (gmt 0)

Forgive my ignorance here, but in correcting his code by breaking out the LOCATE command into three OR statements why use the concatenation? You aren't connecting that string to anything as I read the code. I think it would work b/c there isn't anything attached (i.e. I think '$variable' and '.$variable.' without further modification should be equivalent).

phranque

10:40 pm on Oct 5, 2007 (gmt 0)

i wasn't correcting his php, but rather the sql.
forgiving my ignorance of php, i assumed an unquoted variable would interpolate more readily than a quoted one, so i took the safe route.
please feel free to offer a further correction of my suggestion - that's what we're here for...

Chiaroscuro

10:45 pm on Oct 5, 2007 (gmt 0)

I'm sure you know as much PHP as I do, I was just looking for insight into what you were doing it for (as I couldn't figure out from either MySQL or PHP sites). Jake66 - can you try it without the joining "." surrounding the variables and see if it works? Just curious as to the result.