Comparing two records does not work.

[jake66]

Code:

$blacklist_keystroke_query = tep_db_query("SELECT * FROM " . TABLE_BLACKLIST_KEYSTROKE . " where keystroke_entry LIKE '%$firstname%'");
while($blacklisted_keystroke = tep_db_fetch_array($blacklist_keystroke_query))
{

if (strpos($blacklisted_keystroke['keystroke_entry'], "$firstname"))
{
tep_redirect('banned.html');
break;
}
}

What I am trying to achieve:
I have a database full of common random keystrokes, like "sdf" and such.

I am trying to select the database of common keystrokes and compare them to the person's first name they entered on the form.

If they are guilty of using a common keystroke term, redirect them to the banned page.

Can anyone spot what I'm missing here? I've been working on this for hours and cannot figure out what's wrong.

[borntobeweb]

Well, your if statement won't work properly if $firstname is at the beginning of the blacklisted keystroke. strpos() will return 0 and your if statement will fail, as per the manual [php.net], use:

if(strpos($blacklisted_keystroke['keystroke_entry'], "$firstname")!== false)

Also the logic doesn't look quite right to me, you're only fetching keystrokes that include $firstname, then testing again to see if $firstname appears in the keystroke, maybe i'm missing something... And if the entries in your table are all in lower case, make sure you convert $firstname to lower case in your where clause.

[jake66]

I suppose testing your if statement won't work just yet, until I get the sql logic down? :)

you're only fetching keystrokes that include $firstname, then testing again to see if $firstname appears in the keystroke, maybe i'm missing something

I'm not that good at php, just a beginner. Here's what I am trying to do:
Keystroke: sdf
$firstname iiudhgsdfogjfdoig

Any suggestions what I can do to get the sql query correct?

[Habtom]

SELECT * FROM TABLE WHERE firstName LIKE '%sdf%'
Should return you all first names with sdf in them.

Similarly:

Ending with "sdf"
SELECT * FROM TABLE WHERE firstName LIKE '%sdf'

Begining with "sdf"
SELECT * FROM TABLE WHERE firstName LIKE 'sdf%'

Habtom

[jake66]

I have to select three different times if I want to scan for sdf being before, after or hidden in the first name? Is there anyway it can look in: BEFORE, AFTER and mixed/hidden in the name all at once, with one query?

You said:
SELECT * FROM TABLE WHERE firstName LIKE '%sdf%'

Where I bolded the statement, is being passed from a form. What is the proper way to use this:
'.$firstname.'
$firstname
..?

[borntobeweb]

I believe this should work:

SELECT * FROM TABLE_BLACKLIST_KEYSTROKE where LOCATE(keystroke_entry, LOWER('$firstname')) > 0;

If the number of rows returned is non-zero, then there's a match. Here's the reference manual for locate() [dev.mysql.com] and other string functions. As usual, when there's a function call involved in your WHERE clause, you end up doing a full table scan which is ok if your table isn't big (like only 100's of records), but may slow things down if bigger. Oh and make sure all values in keystroke_entry are in lowercase.

As far as constructing the '$firstname' expression, make sure you either have PHP magic_quotes on (better to have it off) or you escape single-quotes in the input value by calling mysql_real_escape_string(), otherwise your program breaks if someone types in a firstname with a ' in it.

Hope this helps.

[jake66]

I'm about to update my query, but I suspect this will no longer work:

if (strpos($blacklisted_keystroke['keystroke_entry'], "$firstname"))
{
tep_redirect('banned.html');
break;
}

How could I make use of this with your updated query?

[borntobeweb]

Since the query only returns rows if $firstname contains one of your keystroke_entries, you don't need the if statement at all. Do this:

$query = mysql_query("SELECT count(*) FROM ".TABLE_BLACKLIST_KEYSTROKE." 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
tep_redirect('banned.html');
}

[jake66]

Fantastic. Thanks so much for your help! It works perfectly.

On a similar note, say I want to check for duplicates like:
cc

..that don't exist in any name combo I've ever heard (though I am familiar with Aaron)

Would it be easier employing further scanning techniques, or to simply add the variations into the database?