Checking duplicate records

alexcg

Trying to write some code to check if an event already exists in our calendar:

foreach($all_events as $value) {
if(mysql_num_rows(mysql_query("SELECT starttime FROM ".$db_table." WHERE starttime = '".$value["starttime"]."'"))){
if(mysql_num_rows(mysql_query("SELECT title FROM ".$db_table." WHERE title = '".$value["title"]."'"))){
echo "there's something at this time already!<p>";
}
}
}

Testing it out now, but it's posting duplicate events and not echoing anything at all

rsmith

Looks like you forgot to specify the threshold to compare the mysql_num_rows() portion of your statement to. That or your structure is all wrong. I can't double check this right now but give it a shot.

$startTime = mysql_query("SELECT starttime FROM ".$db_table." WHERE starttime = '".$value['starttime']."'");

$title = mysql_query("SELECT title FROM ".$db_table." WHERE title = '".$value['title']."'");

$rows1 = mysql_num_rows($startTime);
$rows2 = mysql_num_rows($title);

if($rows1 >= 0 || $rows2 >= 0 ){
     echo "Something goes here.";
}

alexcg

Thanks for that. Does this code cover events with the same start time AND title, or same start time OR title? Since I'm dealing with recurring events, AND is what I really need.

Cheers

rsmith

What I posted was just to get you in the right direction. It only does OR. What you'd need to do is select the start time and the title from the db at the same time and compare them to the values submitted.

if($value['starttime'] == $startTime && $value['title'] == $title){
    echo "Something.";
}

alexcg

Thanks. Think I've sorted that out, but keeps returning a result of "0", even if records exist with that titie and start time

foreach($all_events as $value) {
$title_and_start = mysql_query("SELECT title FROM ".$db_table." WHERE title = '".$value['title']."' AND starttime = '".$value['starttime']."'");

$rows2 = mysql_num_rows($title_and_start);

if($rows2 == 0 ){Insert into database}
else{echo $value["title"]." already exists.<p>";}
}

Again, thanks for all the help so far -- much closer now than I was

laserlight

I am wary of queries done within a loop.

Firstly, it looks like you identify an event based on its title and starttime. Therefore, make (title, starttime) the primary key of the events table.

Then, all you need to do is to attempt an insert, then check if it succeeds. If it does not, you can check the error code and then inform the user that he/she attempted to set a duplicate event.

Piranha

I kind of agree with laserlight, but not 100%. I don't like natural primary keys since they may get changed. For instance, the starttime may change 30 minutes, and it will mess your tables up. Also I don't like primary keys containing more than one column, it gets to messy when you use them as foreign keys.

What I do agree on is that it should not be possible to insert duplicates. Use "unique (title, starttime)" to make it impossible. The result will be the same when you try to insert a duplicate, you will get an error message.

To be able to clean up the current database before you make the columns unique you should use something like the followin:

select whatever from table group by title, starttime having count(*) > 1

alexcg

Finally got it working by:

$value["uid"] = md5($value["title"].$value["date"];
foreach($all_events as $value) {

$uid = mysql_query("SELECT uid FROM ".$db_table." WHERE uid = '".$value['uid']."'");
$rows = mysql_num_rows($uid);


if($rows == 0 ){
echo "<li><strong>".$value["title"]."</strong> added</li>";
mysql_query("INSERT INTO $db_table 
(uid, title, starttime) VALUES
('" . $value["uid"] . "', '" . $value["title"] . "', '" . $value["starttime"] . "')")
or die(mysql_error());

 }
else{
    echo "<li>".$value["title"]." already exists";
}
}