PHP&MySQL - Dont insert the title two times ?

Discussion in 'PHP' started by thesurface, Apr 29, 2012.

0
  1. #1
    How to NOT allow mysql to insert the same name if its already exists? I have the code but requires a mysql process on 110% o.o I have about 800,000 records is there some other way?

     $md5     = md5($title);
    $result = mysql_query('SELECT * FROM `test` WHERE `hash` LIKE \'%' . $md5 . '%\'') or die(mysql_error());
    $row = mysql_fetch_array($result);
    if ($row["hash"] != $md5) {
echo "not exists";
}
else
{
echo "exists"";
}
    PHP:
     
    thesurface, Apr 29, 2012 IP
  2. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    737
    Likes Received:
    52
    Best Answers:
    33
    Trophy Points:
    150
    #2
    Update your structure to:

    
ALTER TABLE `test` CHANGE `hash` `hash` VARCHAR( 32 ) NOT NULL ;
ALTER TABLE `test` ADD UNIQUE (`hash`);
    Code (markup):
    In your PHP you do:

    
$md5     = md5($title);
$query   = "REPLACE INTO `test` SET `hash`='$md5', `fieldname` = '$filename1'";
mysql_query($query) or die(mysql_error());
    PHP:
    This will insure that only 1 record exists. If you actually need to know if the row exists in your controller, you do:

    
$md5     = md5($title);
$result = mysql_query("SELECT * FROM `test` WHERE `hash` = '$md5'") or die(mysql_error());
$row = mysql_num_rows($result);
if ($row <= 0) {
    echo "not exists";
} else {
    echo "exists"";
}
    PHP:
    The above SQL optimization are also needed on either case. Since MD5 is pretty much unique (for most cases) no need to do a LIKE comparison, unless the field stores more than 1 hash value.
     
    ThePHPMaster, Apr 29, 2012 IP