SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Oct 2005
    Posts
    288
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Duplicate check seems overly complicated (MYSQL/PHP5)

    Code:
      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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    depends on whether you want to ensure that only unique names are entered

    if not, then it's not clear what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Slightly better:

    Code:
    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. #4
    SitePoint Addict
    Join Date
    Oct 2005
    Posts
    288
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    depends on whether you want to ensure that only unique names are entered

    if not, then it's not clear what you're asking
    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. #5
    SitePoint Addict
    Join Date
    Oct 2005
    Posts
    288
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by RichardAskew View Post
    Slightly better:

    Code:
      	  
    		if(mysql_num_rows($result) > 0) {			// student name is on file

    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
    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. #6
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You will ger a positive value if the name does exist in the database.

    Quote Originally Posted by Grnadpa View Post
    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

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Grnadpa View Post
    (as 'name' is NOT the key to this table).
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Oct 2005
    Posts
    288
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.
    Quote Originally Posted by r937 View Post
    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

    ...

    3. you can catch the error and report back a user-friendly message like "Name already exists"
    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. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RichardAskew View Post
    Slightly better:

    Code:
    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
    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.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •