trying to avoid duplicates

[essexboyracer]

Hi

I am trying to run a select statement before an IF to avoid inserting duplicate records, as follows

$surname = $item[2];
$phone = $item[4];

$result1=mysql_query("SELECT * FROM proposer WHERE proposer3 LIKE '$surname' AND proposer5 LIKE '$phone'");

if ($result1) {
   die('Duplicate entry found, stopping:' . mysql_error());

} else {


$query = "INSERT INTO proposer SET ID = '', date = '$today', certNo = 'TEST', proposer1 = '$item[0]', proposer2 = '$item[1]', proposer3 = '$item[2]', proposer4 = '$item[3]', proposer5 = '$item[4]', proposer6 = '$item[5]', proposer7 = '$item[6]', proposer8 = '$item[7]', proposer9 = '$item[8]', proposer10 = '$item[9]', proposer11 = '$item[10]', proposer12 = '$item[11]', proposer13 = '$item[12]', proposer14 = '$item[13]'";

mysql_query($query);
$last_insert_id = mysql_insert_id();
mysql_close();


echo $last_insert_id; echo $item[0]; echo $item[1]; echo $item[2]; echo $item[4]; echo $item[8];

}

While testing I have $phone and $surname as values that I know are in the database, but it still executes the INSERT query, and deosnt stop with the die(). In addition mysql_error() doesnt give me anything.

[laserlight]

Just because the query was successful does not mean that there were any rows retrieved. You want to count the number of rows retrieved.

$result1 = mysql_query("SELECT COUNT(*) FROM proposer WHERE proposer3='$surname' AND proposer5='$phone'");

if (mysql_result($result1, 0) > 0) {
	die('Duplicate entry found, stopping:' . mysql_error());
} else {
	// ...
}

[edwardp]

I m getting a similar problem.

im doing this:

$query = "SELECT a.PART_ID, b.DESCRIPTION, count(a.PART_ID) as number FROM CUST_ORDER_LINE as a, PART as b WHERE a.PART_ID = b.ID AND a.PART_ID NOT LIKE '%FIN%' AND a.PART_ID NOT LIKE 'CI%' AND a.PART_ID NOT LIKE '%D&M%' AND a.PART_ID NOT LIKE 'PRECHA%' AND a.PART_ID NOT LIKE 'works van' AND a.PART_ID NOT Like '[0-9][0-9]SHT[0-9][0-9][0-9][0-9]' AND a.PART_ID NOT Like '[0-9][0-9]TIM[0-9][0-9][0-9][0-9]' GROUP BY a.PART_ID, b.DESCRIPTION ORDER BY number desc";

$dbaseConnection = connectToMSSQL();
$result = mssql_query($query,$dbaseConnection);

for ($i = 0; $i < mssql_num_rows( $result ); $i++){
	$line = mssql_fetch_array($result);
        print_r($line);
        echo "<br>";
}

and a few selections of results gives

Array ( [0] => 97PLU6017 [PART_ID] => 97PLU6017 [1] => Shower Tray 900 X 720 White [DESCRIPTION] => Shower Tray 900 X 720 White [2] => 351 [number] => 351 ) 
Array ( [0] => 99PLU0005 [PART_ID] => 99PLU0005 [1] => Water Filler And Locking Cap Complete [DESCRIPTION] => Water Filler And Locking Cap Complete [2] => 351 [number] => 351 ) 
Array ( [0] => 00IRN0010 [PART_ID] => 00IRN0010 [1] => Swivel Table Leg - Brown [DESCRIPTION] => Swivel Table Leg - Brown [2] => 312 [number] => 312 ) 
Array ( [0] => 99ELE2200 [PART_ID] => 99ELE2200 [1] => 13w Batten Uplighter - Ds415 [DESCRIPTION] => 13w Batten Uplighter - Ds415 [2] => 237 [number] => 237 ) 
Array ( [0] => 00MAS1011 [PART_ID] => 00MAS1011 [1] => 2001/2 Frt/Chy SideTape20/45/45/20mm Pe [DESCRIPTION] => 2001/2 Frt/Chy SideTape20/45/45/20mm Pe [2] => 177 [number] => 177 ) 
Array ( [0] => AFT03WIN0002 [PART_ID] => AFT03WIN0002 [1] => Inner Lock Assy, [DESCRIPTION] => Inner Lock Assy, [2] => 162 [number] => 162 )

As you may see, in each array (line) i am getting a double results...one with integer indexes and another with assoc indexes. I would like only assoc. Is this usual or am missing soething in my code??

regards

[edwardp]

Resolved

This i changed the line with $line = mssql_fetch_array($result) to $line= mssql_fetch_array($result,MSSQL_ASSOC) na it worked beautifully

[essexboyracer]

Thank sLaserlight, I dont fully understand the difference between you explaination and !$return

[Sxooter]

WHOA! Hold on! None of these ways are how you avoid duplicates, because they're all vulnerable to race conditions. I.e. someone can insert the same thing between when you select for dupes and insert.

You avoid duplicates with unique indexes. Since a unique index on a large text field is large and unwieldy, it's an easy cheat to do something like create a separate field, and put and md5 of the text field in there, and make the unique index on that field.

create table posts (id int autoincrement, textarea text, checksum text);
$text = "this is a test message";
insert into posts (textarea, checksum) values ('$text',md5sum($text));

Get the idea? If they try to insert the same thing twice, the unique index on the md5checksum should catch it every time, no race condition.

[essexboyracer]

eh? my eye is startin to twitch and I am murmering with dribble down the side of my mouth

whats race got to do with it...

[Sxooter]

Race conditions have nothing to do with the color of your skin...

T1 us transaction 1, T2 is transaction 2.

T1: select count() from db where message = 'abc'; <-- abc not in database, count==0
T2: select count() from db where message = 'abc'; <-- abc not in database, count==0

milliseconds pass...

T1: insert into db values ('abc');
T2: insert into db values ('abc');