MySQL Entry Problem

[DavidPr]

I'm having a problem putting entries into a table.

I have a job board and I want to prevent certain companies from being able to register - again. I don't allow certain type ads, so when they register the first time and I delete them they just re-register and this process goes on and on and on.

I put a piece of code into the registration form that checks to see if if this company is in a list and if so a message is returned saying "registrati on denied". Only problem with this is that I have to open the page and hard code the company name into the script.

What these people will do is to change the name slightly and they're allowed to re-register, for instance: Company name: Redstar, Company name: RedStar, Company name: Red-star, Company name: Red-Star, and on and on and on.

Here's what I have right now:

$cn = $company_name;

if ($cn == 'REDSTAR' OR $cn == 'Redstar' OR $cn == 'RedStar' OR $cn == 'Red-star' OR $cn == 'Red-Star' OR $cn == 'Spectrum' OR $cn == 'Pre Paid Legal Services Inc' OR $cn == 'Typeinternational' OR $cn == 'Ozwebresources' OR $cn == 'HOME WORK' OR $cn == 'Advantage Marketing' OR $cn == 'Moms Work Smarter' OR $cn == 'Satisfaction Services' OR $cn == 'Ameriplan' OR $cn == 'ozwebresources' OR $cn == 'OzWebresources' OR $cn == 'OzWebResources' OR $cn == 'ozwebResources' OR $cn == 'Grindhouse Associate' OR $cn == 'John H Maxson')
{
echo "Registration Denied";
footer();
exit();
}
else
{ // continue with registration

So what I'm trying to do now is to build a "banned names" database that when the registration page is submitted the first this it does it check to see if the $company_name matches a name on file in the "banned names" table. If so the registration stops and a registration denied message is delivered.

The problem is that MySQL isn't allowing different versions of the same name to be entered. See the different variations of the same name above where a different letter in the company name will be capitalized? When I try to enter in a different variation of the same name I get this message:

The banned name could not be updated due to a system error.

Duplicate entry '' for key 2

Query: INSERT INTO banned VALUES ('', 'Redstar', '')

Here's my add banned name script:

if (isset($_POST['submitted']))
{ // Handle the form.

$b_name = escape_data($_POST['b_name']);
$b_email = escape_data($_POST['b_email']);

$query = "INSERT INTO banned VALUES ('', '$b_name', '$b_email')";
$result = mysql_query($query);
if (mysql_affected_rows() == 1) { // If it ran OK.

// Print a message.
echo "<p>The banned name has been entered.</p>";

} else { // If it did not run OK.
echo "<h2>System Error</h2>
<p>The banned name could not be added due to a system error.</p>"; // Public message.
echo "<p>" . mysql_error() . "<br><br>
<strong>Query:</strong> " . $query . "</p>"; // Debugging message.

footer(); // Include the HTML footer.
exit();
mysql_close();
}

I have no unique keys set for the banned names table.

Thanks

[Markus]

What's the table structure? I've never had this error before.

[DavidPr]

MySQL version: 4.1.20



Field Type Null Extra

id int(11) No auto_increment
b_name varchar(60) No
b_email varchar(55) No


Keyname Type Cardinality Field

PRIMARY PRIMARY 2 id

[Markus]

MySQL version: 4.1.20



Field Type Null Extra

id int(11) No auto_increment
b_name varchar(60) No
b_email varchar(55) No


Keyname Type Cardinality Field

PRIMARY PRIMARY 2 id

I did some research and it looks like the value for your A.I field is too large for it's data type. Try changing it to BIGINT.

[dlite922]

The easiest way is to create a function that will stabilize the name (ie take all non-alphanumeric characters out, including spaces?) , convert to lowercase, THEN put this in your MySQL database. So that after plugging in all those "Red-Star" variations in, the function always outputs "redstar", and this will have only one record in the table instead of 10.

I'm curious,

can't the company make up just ANY name, instead of redStar can't they call it "LASkskf93skdfh " ??

Cheers,


Dan

[Markus]

I'm curious,

can't the company make up just ANY name, instead of redStar can't they call it "LASkskf93skdfh " ??

Cheers,


Dan

Good point. You could track IP addresses, also, but they, too, are not reliable.

I imagine, for there to be rhyme & reason to them signing up so incessantly, you must provide something that benefits them, e.g. a 'website' link-back. If so, maybe you can track what value a new signee (not a word, I know) gives.

[dlite922]

Good point. You could track IP addresses, also, but they, too, are not reliable.

I imagine, for there to be rhyme & reason to them signing up so incessantly, you must provide something that benefits them, e.g. a 'website' link-back. If so, maybe you can track what value a new signee (not a word, I know) gives.

exactly find what is unique about each user and how verifiable is that piece of info.

[Atli]

Hi.

You shouldn't provide a value for a auto_increment field.
It's best to just leave them out of the query.

If you absolutely must include it, at least use NULL instead of an empty string.
[code=mysql]/* Instead of: */
INSERT INTO banned VALUES ('', 'Redstar', '')

/* Do: */
INSERT INTO banned(b_name, b_email) VALUES ('Redstar', '')

/* This might even work to (can't test it on MySQL 4 atm) */
INSERT INTO banned VALUES (NULL, 'Redstar', '')[/code]


As for the case-sensitive search... you will have to specify a collation, either when you create the table, or in your SELECT query.

Like:
[code=mysql]mysql> CREATE TABLE test_cs(
-> value varchar(50)
-> CHARACTER SET latin1
-> COLLATE latin1_general_ cs
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_cs(value)
-> VALUES ('John'), ('john'), ('JOHN');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test_cs WHERE value = 'john';
+-------+
| value |
+-------+
| john |
+-------+
1 row in set (0.00 sec)[/code]

Or, if you already have a table that does not specify the collation:
[code=mysql]mysql> SELECT * FROM test_ci WHERE value = 'John';
+-------+
| value |
+-------+
| John |
| john |
| JOHN |
+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test_ci
-> WHERE value = 'John' COLLATE latin1_general_ cs;
+-------+
| value |
+-------+
| John |
+-------+
1 row in set (0.00 sec)[/code]
Check out 9.1. Character Set Support in the manual for more info on that.

P.S.
I don't have MySQL 4 to test this on, but this does work on MySQL 5.

P.P.S.
You really really should upgrade to MySQL 5 ;)

[DavidPr]

The table must automatically create or assign a Collation because I never do. It's currently set to latin1_swedish_ ci

The only advantage to my job board is that it's free to use. I use htmlspecialchar s, so a link in the ad will not be a link. But they don't know that.

The Company Name will show up in their ad if they don't choose to display "National or Local Company". But no, there's nothing stopping them from using a jumbled up name. I'd delete it and their ad for doing so, but that would be after the fact.

I think what it is that makes these people so persistent is anger. Even though I tell them two, three times before they can place an ad that I do not allow certain type of job ads and if they post one I'm going to remove it. Some try it and I remove it and that's that. But some people just can't let go, they keep posting out of spite to try and annoy me I guess.

I probably would charge a little something to place an ad, but that's over my head.



I didn't think about it, but I don't really need an "id" field. I didn't think about removing all spaces and convert the name to all lower case letters. I know how to change to lower case, but I'll have to read up on how to eliminate spaces and hash marks, etc. Good suggestions. Thanks!

EDIT--

I was just thinking about removing all spaces, hash marks, etc. and changing the company name to all lower case letters to check against banned words. Since they may want to include their company name in their ad, and because I like to have it for my files, I really couldn't modify the company name. Not unless you could do this for checking purposes and if it passes put the company name back to the way it was originally.

[Markus]

I was just thinking about removing all spaces, hash marks, etc. and changing the company name to all lower case letters to check against banned words. Since they may want to include their company name in their ad, and because I like to have it for my files, I really couldn't modify the company name. Not unless you could do this for checking purposes and if it passes put the company name back to the way it was originally.

Store an edited version of the company name along with it's original.

You can remove everything but alpha-numeric via a preg_replace.

$string = "A string #*& with _=+ different characters.";
$string = preg_replace('/[^a-zA-Z0-9]/', '', $string);
echo $string;

[DavidPr]

How would you store a modified version of company_name and the actual company_name?