Duplicate entry for key 1 ??

[soadlink]

Hello,

I am working on a simple project where users can add strings of text to a mysql database that are converted to MD5, and search for MD5's that may already be in the database and reverse them to their string. So far, everything seems to work out ok, except when I add strings that are just spaces.

For example, I will use my add.php script to add the string "          " which is just 10 spaces. I get my confirmation I set up that says it was added ok. Then I go back and add the SAME string (10 spaces), and my script is SUPPOSED to say "That string is already in the database" like it does with normal strings, and not add the string... but instead a mysql error occurs. I modified my code to display the mysql error, and it is: [b]Duplicate entry '41b394758330c83757856aa482c79977' for key 1[/b]. This seems simple, it doesnt want a duplicate entry, except my code should already check for that!

Here is my code for adding strings:
[quote][code=php:0]<?php
//Get string to add, and convert it to md5
$premd5 = $_POST['premd5'];
$md5 = md5($premd5);

//database info
$location = "localhost";
$username = "username";
$password = "password";
$database = "database";

//connect to the database and display any connection errors
$conn = mysql_connect("$location","$username","$password");
if (!$conn) die ("Could not connect to database.");
mysql_select_db($database,$conn) or die ("Could not open database.");

//query the database to find any existing entries for that string
$query = "SELECT * FROM md5 WHERE orig='$premd5'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

//if the string is already there, tell the user, and do not add it again
if ($row[orig] == $premd5){
    echo 'The string <b>' . $premd5 . '</b> is already in the database:<br/><br/>';
    echo $row[md5];
} else {

//if the string is not there, attempt to add it with its md5 and display confirmation
$insert = "INSERT INTO md5 (orig,md5)
    VALUES ('$premd5','$md5')";
    mysql_query($insert) or die ('Error adding data: ' . mysql_error());

echo 'Added the string <b>' . $premd5 . '</b> with the hash <b>' . $md5 . '</b> to the database.';
}
?>[/code][/quote]

It appears that my php code doesn't find the existing string by itself... and attempts to add it to the database... but mysql stops it saying it's already there (which it is). On a side note, as far as I know every other string works fine and I am only having problems adding strings that are just spaces. So even the string "Hello        There" works fine with my script.

Some info about the table from phpmyadmin:
[img]http://img278.imageshack.us/img278/2444/tableyn8.jpg[/img]

I can provide any other info if needed.. and I know there are probably other things that could change to improve this project so off topic comments are fine too  8) Thanks!

[printf]

You can't fetch an array, if you don't have any result returned by the query. So if your just wanting to test if the result already exists, then use COUNT(*), so your sure to return a result! In other words you don't need to return a result other than the count, ([?] -> does it already exist), because all fields/columns can be expressed with the $_POST data, if it does exist!



[code]$query = "SELECT COUNT(*) AS total FROM md5 WHERE orig='" . $premd5 . "'";

$result = mysql_query ( $query );

$row = mysql_fetch_array ( $result );

//if the string is already there, tell the user, and do not add it again

if ($row['total'] == 1 )
{
echo 'The string <b>' . $premd5 . '</b> is already in the database:<br/><br/>';
}
else
{
$insert = "INSERT INTO md5 VALUES ( '" . $premd5 . "','" . $md5 . "' );";

mysql_query ( $insert ) or die ( 'Error adding data: ' . mysql_error () );

echo 'Added the string <b>' . $premd5 . '</b> with the hash <b>' . $md5 . '</b> to the database.';
}[/code]



p

[soadlink]

I got it to work just fine except I had to switch 1 thing in your code:
[code]$query = "SELECT COUNT(*) AS total FROM md5 WHERE md5='" . $md5 . "'";[/code]
I set it to search for the md5 of the string that is being added instead of the original string. If I set it to search for the original string (if it has all spaces), the php code still doesn't find it on its own and wants to add it, then mysql denies the request any gives that error... so I set it to search for the md5 of the string instead and now the php code catches the duplicate.

I guess the only problem that could occur with this is if for md5 collisions... where 2 different strings have the same md5. But I don't think the database will grow big enough for that. Thanks for the reply  8), any other ideas with this project?


edit: Added the pre and /pre tags to the strings that are echoed. That way when it displays a string like "Hello        There"  it shows up correctly on the page... and not "Hello There". :D