Duplication checking not working

Question and code for php/sql problem

[Drunk N Japan]

I am working on duplication checking our data before it is written to the mysql data base but it is freezing on the num_rows area and I am loosing hair over not being able to figure it out.

$thirty_days_ago = time()-2592000; // Current unix timestamp minus thirty days

if ($prior_service == "Yes" && $badlead <> 1) {

dbx_connect();
$duplicate = "SELECT * FROM PriorNew WHERE First_Name = '$First_Name' AND Last_Name = '$Last_Name' AND Address = '$Address' AND City = '$City' AND State = '$State' AND Zip_Code = '$Zip_Code' AND Area_Code = '$Area_Code' AND Phone1 ='$Phone1' AND Phone2 = '$Phone2' AND Email = '$Email' AND Confirm_Email = '$Confirm_Email' AND Birth_Month = '$Birth_Month' AND Birth_Day = '$Birth_Day' AND Birth_Year = '$Birth_Year' AND Gender = '$Gender' ";
echo $thirty_days_ago;
$mysql_result=mysql_query($duplicate) or die(mysql_error());
$dupe_rows = mysql_num_rows($mysql_result) or die(mysql_error());
echo $mysql_result;
echo mysql_num_rows($mysql_result);
if ($dupe_rows >= 1)
{
}
else
{
dbx_connect();
$sqlPS = "INSERT INTO PriorNew VALUES ('$null', '$subject', '$First_Name', '$Middle_Name', '$Last_Name', '$Address', '$Address_2', '$City', '$State', '$Zip_Code', '$Zip_Code2', '$Area_Code', '$Phone1', '$Phone2', '$Email', '$Confirm_Email', '$Contact_By_Email', '$Birth_Month', '$Birth_Day', '$Birth_Year', '$Gender', '$Branch_of_Armed_Services', '$Army_MOS', '$Last_Rank', '$Ready_Reserve', '$years', '$obligation', '$Ethnic_BackgroundPrior', '$ETSPRIOR', '$PRIOR_WHY', '$validatekey', '1', '$signup_date', '$SSN')";
$resultPS = mysql_query($sqlPS);
}
}

I appreciate any help I can get.

[eelixduppy]

Hello,

Are you getting any errors from this? Also, it seems like some of this is unnecessary to do. Wouldn't you agree that you could determine a duplicate based on the email address alone, as they will always be unique? Just an idea to throw around. I don't immediately see any errors as long as you have register globals turned on. Although you haven't escaped the queries, we can sort that out when it works correctly :)

[Drunk N Japan]

Unfortunately I am not getting any errors. When I do have a duplicate it works and does not post the data but when I have a duplicate it freezes at the num_rows line. that is why the echo statements are there. I know some of the data seems redundant but I have to be sure of duplication.

[Drunk N Japan]

Sorry. When I don't have a duplicate it freezes. I am not sure what you mean about escaping the queries.
Thanx.

[justgowithit]

You've got confusion with your statements - You can't overlap conditionals.

The "if ($dupe_rows >= 1)" statement overlaps the "if ($prior_service..." statement.

[Drunk N Japan]

I can't have nested if statements?
Oh No!
Any way you can see pulling this off with both of the statements that are there? The first if statement selects the table the data goes in the second is just trying to determine if there were duplicates.
I can't say for sure but when I ran this it would only echo the timestamp but not the result and the num_rows. I am wearing concrete shoes and the tide is rising.

[Drunk N Japan]

I almost forgot that at the end of the select statement it check against the timestamp that is saved in signup_date.
after Gender AND signup_date > '$thirty_days_ago' ";

[eelixduppy]

justgowithit, nested statements are actually perfectly valid and are sometimes necessary. The following example produces the correct output, as intended:

$one = 1;
$two = 3;
if($one == 1) {
if($two == 2) {
echo 'It\'s two!';
} else {
echo 'It\'s not two!';
}
}

Control Structures [us3.php.net]

As for the error, what do you mean by "freezing"? Is your query timing out? How large is this table in terms of rows?

[Drunk N Japan]

By freezing I mean that it is not even timing out it is just giving me a white screen. (the submit screen for the site posts a thank you after the data is written to the database) The table has over 14000 rows. Thanks for the help.

[eelixduppy]

white screen = fatal error (most of the time)

Try turning up your error reporting [php.net] or checking your server's error logs for errors. In the mean time, I'll take one more quick look at your code ;)

[eelixduppy]

I've cleaned it up a little bit. Try something like this:

if ($prior_service == "Yes" && $badlead <> 1) {
#
dbx_connect(); 
$_POST = array_map('mysql_real_escape_string',$_POST);
$duplicate = "SELECT * FROM PriorNew WHERE Email = '".mysql_real_escape_string($_POST['Email'])."'";
$mysql_result = mysql_query($duplicate) or die(mysql_error());
$dupe_rows = mysql_num_rows($mysql_result);
echo 'Num of rows: '.$dupe_rows;
#
if(!$dupe_rows)
{
$sqlPS = "INSERT INTO PriorNew VALUES ('".$_POST['null']."', '".$_POST['subject']."', '".$_POST['First_Name']."', '".$_POST['Middle_Name']."', '".$_POST['Last_Name']."', '".$_POST['Address']."', '".$_POST['Address_2']."', '".$_POST['City']."', '".$_POST['State']."', '".$_POST['Zip_Code']."', '".$_POST['Zip_Code2']."', '".$_POST['Area_Code']."', '".$_POST['Phone1']."', '".$_POST['Phone2']."', '".$_POST['Email']."', '".$_POST['Confirm_Email']."', '".$_POST['Contact_By_Email']."', '".$_POST['Birth_Month']."', '".$_POST['Birth_Day']."', '".$_POST['Birth_Year']."', '".$_POST['Gender']."', '".$_POST['Branch_of_Armed_Services']."', '".$_POST['Army_MOS']."', '".$_POST['Last_Rank']."', '".$_POST['Ready_Reserve']."', '".$_POST['years']."', '".$_POST['obligation']."', '".$_POST['Ethnic_BackgroundPrior']."', '".$_POST['ETSPRIOR']."', '".$_POST['PRIOR_WHY']."', '".$_POST['validatekey']."', '1', '".$_POST['signup_date']."', '".$_POST['SSN']."')";
#
$resultPS = mysql_query($sqlPS) or die(mysql_error());
echo ($resultsPS)? 'Added Successfully!':'Failed!';
}
#
}

[justgowithit]

nested statements are actually perfectly valid

Thanks, but I never said they weren't. I said that:

You can't overlap conditionals

Drunk N Japan,
Your original code has the following (I cut some out for readability):

if ($prior_service == "Yes" && $badlead <> 1) {

//-> show info when true

///////////////////////
if ($dupe_rows >= 1){
} else {
//-> your code here
}
///////////////////////

} //-> shows white page when this statement is false

As I said earlier, you seem to have some confusion with your conditionals (which eelixduppy has fixed). The reason you were seeing the white screen is because there is no "else" clause to your original "$prior_service == "Yes"" statement because it looks like you mistakenly overlapped (NOT nested) the "$dupe_rows >= 1" conditional. There's nothing wrong with your code, it just has nothing to do if your original conditional is false - hence, blank page. You can see for yourself by adding the else conditional to the end of your original code like:

if ($prior_service == "Yes" && $badlead <> 1) {

//-> show info when true

///////////////////////
if ($dupe_rows >= 1){
} else {
//-> your code here
}
///////////////////////

} else {
echo "This is a white page";
}

It's nice of eelixduppy to fix your code, but it's better to understand why you are not achieving the desired results yourself.

[mcibor]

The problem was somewhere else

$dupe_rows = mysql_num_rows($mysql_result) or die(mysql_error());
if mysql_num_rows is 0, then you die, but as there was no mysql error you die with a blank page.

Correct it to
$dupe_rows = mysql_num_rows($mysql_result)
and it should work.

Regards
Michal

[justgowithit]

Ahhh... Good catch Michal - thank you.

[eelixduppy]

Correct it to
$dupe_rows = mysql_num_rows($mysql_result)

Don't forget the semicolon at the end ;)

$dupe_rows = mysql_num_rows($mysql_result);

but it's better to understand why you are not achieving the desired results

Most defnitely, I agree! It was my understanding that there was a fatal error in the code which was leading to the blank page, not a logical error. If there were an error, it would probably be from something I fixed in my post, so I was trying to show by example.

I'm glad Michal has well-trained eyes as I definitely didn't catch that! :)

[mcibor]

My only mistake nowadays is lack of ; or sth similar... :)

As you could well see...

Only if I give up I rewrite whole script ;)

Regards
Michal

[eelixduppy]

Drunk N Japan, one last thing that wasn't mentioned that I think is worth your time. You do not seem to have escaped any of your query variables. This can prove to be dangerous. I would recommend using mysql_real_escape_string [php.net] to escape each of those variables. I have shown a shortcut to doing each in my post above (with the code) using array_map [php.net] which is a nice work-around for the $_POST array. Anyway, just wanted to give you a heads up so you can prevent from SQL Injection :)

Good luck!

[Drunk N Japan]

Thanks to all of you for your help and I see now what was causing the problem. Hopefully one day I will be able to post some advice for someone that needs help. In the mean time gotta get this working and find a ZIP code verification database for free.

[Drunk N Japan]

I'm not sure if anyone is still monitoring this thread, but I have had a few problems arise from the duplication checks that I am doing. I have instigated a duplication check per the discussion above, but now I am getting multiple additions to the data base. Here is an abbreviation of the code I am using.

if((all the variables pass verification)){
dbx_connect();//connect to database function
$duplicate="SELECT * From `table` Where var1 = 'var1eqv' AND ...
$dupe_result=mysql_query($duplicate) or die(mysql_error());
$dupe_rows = mysql_num_rows($dupe_result);
if (!$dupe_rows)
{
//insert non duplicate with status as 3
dbx_connect();
$sqlAR = "INSERT INTO table VALUES ('$null','etc',...)";
$resultAR = mysql_query($sqlAR);
}
else
//if duplicate put 99 as status
{
dbx_connect();
$sqlAR = "INSERT INTO table VALUES ('$null','etc',...)";
$resultAR = mysql_query($sqlAR);
}

For some reason unknown to me I am now getting submissions to the database that are up to 6 in a row, with a difference in time of about 6 seconds. While these extra submissions are being caught as duplicates(all except 2) they are slowly but surely eating up memory in my database. Could it be that because I have the dbx_connect in there for each read and write phase individually that it is causing this many entries? Also could it be that I am using the same variables in the two different INSERT statements that this is happening? I appreciate any advice that you can give me.

[mcibor]

I just did (monitor :) )
You don't have any kind of loop, at least I don't see it. Therefore I think it's something else that does the multiple submissions.
Did you restrict the page from double posting?
Moreover if you don't mysql_close, then you don't need to dbx_connect(); twice.
As for the dbx_connect(); to create multiple entries I don't know, cause it's only your function. You would have to check it if it doesn't perform any additional queries. But still get rid of the latter two:

if((all the variables pass verification)){
dbx_connect();//connect to database function
$duplicate="SELECT * From `table` Where var1 = 'var1eqv' AND ...
$dupe_result=mysql_query($duplicate) or die(mysql_error());
$dupe_rows = mysql_num_rows($dupe_result);
if (!$dupe_rows)
{
//insert non duplicate with status as 3
$sqlAR = "INSERT INTO table VALUES ('$null','etc',...)";
$resultAR = mysql_query($sqlAR);
}//end if non duplicate
else
//if duplicate put 99 as status
{
$sqlAR = "INSERT INTO table VALUES ('$null','etc',...)";
$resultAR = mysql_query($sqlAR);
}//end else if non duplicate
}//end if all vars ok

if now it's only 3 entries, then you've got yourself an answer
Michal

[Drunk N Japan]

Thanks for the reply. I have redone the whole checking scenario. I have created an include(location,location,location)
from there I created an entirely separate php file for duplication checking. I do a series of if else if statements to determine the table that I should be checking. The I do a single select statement to check for a duplicate. I then establish the status of the entry and finished.
it looks something like...

<?
// var1
if ($var1 == "Yes" && $badlead <> 1)
{$dbtable = "table1";}

// var2
else if ($var2 == "Yes" && $badlead <> 1)
{$dbtable = "table2";}

// var3
else if ($var3 == "Yes" && $badlead <> 1)
{$dbtable = "table3";}

//calculate 30 day interval
$thirty_days_ago = time()-2592000; // Current unix timestamp minus thirty days

//connect to database
dbx_connect();

if ($dbtable == "table3")
{
$duplicate = "SELECT * FROM `$dbtable` WHERE E_MAIL = '$Email' AND entry_date > '$thirty_days_ago' AND (status = '4' OR status = '3')";
$dupe_result=mysql_query($duplicate) or die(mysql_error());
}
else if ($dbtable=="table2")
{
$duplicate = "SELECT * FROM `$dbtable` WHERE Email = '$Email' AND entry_date > '$thirty_days_ago' AND (status = '1' OR status = '2')";
$dupe_result=mysql_query($duplicate) or die(mysql_error());
}
else
{
$duplicate = "SELECT * FROM `$dbtable` WHERE Email = '$Email' AND entry_date > '$thirty_days_ago' AND (status = '4' OR status = '3') ";
$dupe_result=mysql_query($duplicate) or die(mysql_error());
}
$dupe_rows = mysql_num_rows($dupe_result);
if (!$dupe_rows)
{
//initial entry status value
if ($dbtable == "table2")
{$status="1";}
else
{$status = "3";}
}
else
//duplicate status (99)
{$status = "99";}

?>

As I said earlier I tried removing all of the dbx_connects as they were redundant. I have also added to each of the submit buttons on the various forms we have a single click function. To my dismay I am still receiving duplicates. So I reinstated the old form with out the duplication checking. Surprise, Surprise, now I only have deliberate duplication from someone reposting with different data. Could the select statement before the insert statement be causing the database to be confused? Should I use a different command other than select for checking the duplicates? Have I confused everyone yet?