Duplicate check seems overly complicated (MYSQL/PHP5)

Databases
1 

  Public Function NameIsOnFile($studentname) {
		$sql = "SELECT COUNT(*) FROM studentlist WHERE name = '{$studentname}'";
		$result = mysql_query($sql);
  	        $row = mysql_fetch_array($result, MYSQL_NUM);
		if($row[0] > 0) {			// student name is on file
			return true;
		}
		return false;
	} // end function

Is there an easier / less resource intensive way to accomplish this? (studentid is the key to this table, not name).

I’ve done a search in this forum, but must not be using the right keywords.

Regards,

grNadpa

2

depends on whether you want to ensure that only unique names are entered

if not, then it’s not clear what you’re asking

3

Slightly better:


Public Function NameIsOnFile($studentname) {
		$sql = "SELECT COUNT(*) FROM studentlist WHERE name = '{$studentname}'";
		$result = mysql_query($sql);
  	  
		if(mysql_num_rows($result) > 0) {			// student name is on file
			return true;
		}
		return false;
	} // end function

Ideally you should stop using the function mysql_query, mysql_num_rows etc as they are being phased out, check these instead http://php.net/manual/en/book.pdo.php

4

Yes, that’s why I am asking – I am checking to assure that the name is unique (as ‘name’ is NOT the key to this table).

5

That’s what I first tried. I must have mis-coded as I kept getting a positive number on mysql_num_rows when the name was unique.

I will take a look at the url you suggested. Thanks.

grNadpa

6

You will ger a positive value if the name does exist in the database.

7

Depending on what you want to do, you could also create a unique index on ‘name’, do an insert, and then handle the duplicate key error if the name already exists.

8

okay, to prevent duplicate names, declare a UNIQUE index on the name column

then, don’t bother with this function at all – just go ahead and do the INSERT without checking yourself

to handle what happens if a name already exists, you have several choices –

  1. you can use the INSERT IGNORE option, which lets the INSERT statement finish normally, but does not insert anything

  2. you can use the INSERT ON DUPLICATE KEY UPDATE option, to capture any additional data values from the insert and use them to update the existing row

  3. you can catch the error and report back a user-friendly message like “Name already exists”

9

Thanks guys. That sounds like the better approach. I have my validation packaged in a separate function within my class than the database maintenance (Insert/Update). As such, got into a bit of tunnel vision.

Will take your suggestions with gratitude.

Thanks

10

Not better at all considering mysql_num_rows() will always return a single row given the query. That function will never return false. Removing the aggregate function would fix it.

11