Preventing multiple database entries.

Should be fairly simple.

[dkin]

I am trying to make my form processor detect if an item name submitted to the database already exists I would like to display a message saying the item is already in the db.

I tried something like this

if ($Item_Name == "$row["Item_Name"]") {

print "That item is already in the database";

}

Now I know this is wrong but if someone could point me in the right direction it would help very much.

[quozt]

if u do that it will still add to the database you need to do

if ($foo = $row['foo'])

{
echo "Already Exsists"
}
else
{
// add to database
}

[ukgimp]

$sql = mysql_query("SELECT * FROM users WHERE username = '$username'");

//Check if username/email already in use
if (mysql_num_rows($sql) > 0){
print "That's already in use you muppet"'

}
else
{
print "well done you are not a muppet, we are now going to insert your data";

//do sql that inserts the data

}

Hope that gives you an idea or two

[timster]

If you define the column to be unique in data, you can let SQL check your work, like so:

$sql = "insert into table (colname) VALUES ('value')";

if ( mysql_query($sql) ) {
echo "Insert succeedinated.";
} elseif ( preg_match('/Duplicate entry/', mysql_error()) ) {
echo "Duplicate data, dude. Didn't do it.";
} else {
echo "Query failed.";
}

[dkin]

but I only want to make sure that the item name does not duplicate. It is in an "insert" with many other values.

[dkin]

I have tried all of these solutions but can not get any of them to work. This is my code for the last one.

$link = mysql_connect("localhost","", "") or die ("couldnt connect: " . mysql_error());
mysql_select_db("",$link) or die ("couldnt select db: " . mysql_error());

$sql = "insert into nuke_item_database (Item_Name) VALUES ('$ItemName')";

if ( mysql_query($sql) ) {
echo "Insert succeedinated.";
} elseif ( preg_match('/Duplicate entry/', mysql_error()) ) {
echo "Duplicate data, dude. Didn't do it.";
} else {
$query="insert into nuke_item_database (Item_Name,Item_Slot,Item_Level,No_Trade,Lore,Hp,Dur,Weapon_Type, Damage,HpMax,Rang,Str,Sta,Agi,Dex,Wis,Intel,Cha,AC,Power,HoT,PoT, PR,DR,FR,CR,LR,AR,Classes,Races,Item_Description,Item_Effects, Acquired,Mob_or_NPC_Name,Zone) values ('".$ItemName."','".$ItemSlot."','".$ItemLevel."','".$NoTrade."', '".$Lore."','".$Hp."','".$Dur."','".$WeaponType."','".$Damage."','". $HpMax."','".$Rang."','".$Str."','".$Sta."','".$Agi."','".$Dex."','". $Wis."','".$Intel."','".$Cha."','".$AC."','".$Power."','".$HoT."','". $PoT."','".$PR."','".$DR."','".$FR."','".$CR."','".$LR."','".$AR."','". $classes."','".$races."','".$ItemDescription."','".$ItemEffects."','". $Acquired."','".$MoborNPCName."','".$Zone."')";
mysql_query($query) or die ("query died: " . mysql_error());
}

[edited by: jatar_k at 7:01 pm (utc) on June 30, 2004]
[edit reason] fixed sidescroll [/edit]

[coopster]

What is being shown here are two alternatives, dkin. quozt showed you a piece of logic, but didn't mention that you would run a query to SELECT the row first, then check it against your data to see if it is a duplicate, which is what ukgimp added for you.

Then timster added that if you ALTER the table so that your Item column has the UNIQUE keyword assigned to it, you wouldn't have to run a SELECT first, simply attempt an INSERT and monitor for error messages.

It doesn't matter if you are trying to INSERT many column names at once or only the unique column by itself (you can do both), as long as the UNIQUE column is in part of your option list and has a unique value, the INSERT will be successful. If a duplicate (NON-UNIQUE) value is attempted, you'll get an error.

[dkin]

ok I like the uniquue Idea. But when I do that I get this.

query died: Duplicate entry 'Item Name' for key 3

i would like a custom error saying. Sorry but $Item_Name already exists in our db.

[Birdman]

Probably just need to suppress the error, like so:

if ( @mysql_query($sql) )

[dkin]

I do not understand what that would do.

[Birdman]

PHP Error Control Operators [php.net]

[dkin]

For some reasonnone ofthis is working for me.Is there an easier way that will only check the Item Name.If it is already in the db return a custom error message, if it is notin the db insert all values.

[coopster]

Yes, msg#3 describes how to do so.

[dkin]

When I do what is in message 3 I get this
Parse error: parse error in /home/eqoagui/public_html/modules/Item_Database/insert.php on line 41.

This is line 41

$query="insert into nuke_item_database (Item_Name,Item_Slot,Item_Level,No_Trade,Lore,Hp,Dur,Weapon_Type, Damage,HpMax,Rang,Str,Sta,Agi,Dex,Wis,Intel,Cha,AC,Power,HoT,PoT, PR,DR,FR,CR,LR,AR,Classes,Races,Item_Description,Item_Effects, Acquired,Mob_or_NPC_Name,Zone) values ('".$ItemName."','".$ItemSlot."','".$ItemLevel."','".$NoTrade."','". $Lore."','".$Hp."','".$Dur."','".$WeaponType."','".$Damage."','". $HpMax."','".$Rang."','".$Str."','".$Sta."','".$Agi."','".$Dex."','". $Wis."','".$Intel."','".$Cha."','".$AC."','".$Power."','".$HoT."','". $PoT."','".$PR."','".$DR."','".$FR."','".$CR."','".$LR."','".$AR."','". $classes."','".$races."','".$ItemDescription."','".$ItemEffects."','". $Acquired."','".$MoborNPCName."','".$Zone."')";

[edited by: jatar_k at 7:02 pm (utc) on June 30, 2004]
[edit reason] fixed sidescroll [/edit]

[coopster]

You'll need to have a look at that query first using the troubleshooting tactics offered by jatar_k previously [webmasterworld.com] to determine why you are getting a parse error.

[timster]

The line of PHP you provided parses fine on my machine.

Don't know whether this is happening to you, but be wary of copying from Web pages into your text editor. Sometimes white space isn't "made of" normal spaces and tabs, and my cause syntax errors.