on duplicate update help!

[Foster]

hi, i have some code that i need to update existing columns in a table, but i just cant seem to get it to work if anyone caan please help it would be appreciated.

 

// select the database

mysql_select_db($database)

or die ("Could not select database because ".mysql_error());

 

$insert = mysql_query("insert into $table values ('NULL', '".$_POST['email']."', '".$_POST['password']."', '".$_POST['name']."', '".$_POST['Gender']."', '".$_POST['Age']."', '".$_POST['Race']."', '".$_POST['SpecialtyOne']."', '".$_POST['Feet']."', '".$_POST['Weight']."', '".$_POST['Eyes']."', '".$_POST['Hair']."', '".$_POST['Strength']."', '".$_POST['Stamina']."', '".$_POST['Perception']."', '".$_POST['Agility']."', '".$_POST['Intelligence']."', '".$_POST['Tech']."', '".$_POST['Luck']."', '".$_POST['Appearance']."', '".$_POST['Background']."', '".$_POST['Weapons']."', '".$_POST['Armour']."', '".$_POST['Items']."', '".$_POST['Enhancements']."', '".$_POST['Skills']."')")

or die("Could not insert data because ".mysql_error());

 

?>

 

<?php

[xyph]

How do you know it isn't working?

[Foster]

i have tested it, people originally register using a registration form. this is from a 'shop' page where they buy items, they need to type their username and click the item they want to buy, it should then be updated but it says they're duplicates and cannot add to table

[xyph]

It may have to do with your table structure. Are there any columns marked as UNIQUE?

 

Giving us more accurate error messages will help.

[Foster]

the error message is "Could not insert data because Duplicate entry 'Foster' for key 'name'"

 

Yes 'name' is unique

[haku]

Your database table must have 'name' set as either a unique field, or a the primary key. You already have a value of 'Foster' in the 'name' column, and are trying to enter another row with the value of 'Foster' in the name column. This is what is giving you the error.

[Foster]

name is a unique field, i want to update the row when a unique field is found

[haku]

Then you'll first need to do a query to see if the value already exists in the unique field. If it does, do an update, if it doesn't, then do an insert.

 

That said, you may be setting yourself up for some problems with this. Names generally aren't unique - I've known at least three people over the years named Mike Clark for example. If two of those signed up it would cause problems. Particularly if name is only a last name - there are many, many Smiths out there for example. So unless you know only a specific set of users will be using your application, and they definitely won't have duplicate names, you are probably better off using a different field as a unique field.

 

Of course, if 'name' refers to a username, and you don't want more than one user to be able to use a username, then you could leave name as unique. But you will run into problems if you aren't using a unique user ID and anyone ever wants to change their username.

[Foster]

i figured i would have to do a query, and was hoping someone here could help. the name has to be unique as it is for a game and only 1 person will have that name

[haku]

$user_exists = mysql_result(mysql_query("SELECT 1 FROM $table WHERE name = '$name'"), 0, 0);
if($user_exists)
{
  //do update
}
else
{
  //do insert
}

One thing to recognize is that the above query is very dangerous, as it's dropping values directly into the query. Values should always be sanitized before database queries. But for the sake of brevity, I used the above.

[Foster]

where in my above code would i put that?

[haku]

In the spot where it happens.

 

You're welcome by the way, seeing as you were so generous with saying thank you. Oh wait...

[xyph]

Haku, a better way to do this would be to use ON DUPLICATE KEY UPDATE

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 

If you don't understand the manual, let us know and we'll provide a small example.

[haku]

Interesting, I like that.

[xyph]

The thing I dislike about 'checking' if a field is unique by using a select query and then after, an insert, is race conditions

 

I'd rather just insert and check for the duplicate entry error number, or use INSERT...ON DUPLICATE KEY UPDATE

[Foster]

i'd like an example please, been looking at insert on duplicate and couldnt work it out.

 

thanks for your help

[haku]

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

That's from the manual. It's fairly straightforward - it's a normal insert query, followed by ON DUPLICATE KEY UPDATE then the same as an update query would be - something="something", something_else = "something_else.

[xyph]

This article does a decent job of explaining

http://www.digimantra.com/tutorials/insert-update-single-mysql-query-duplicate-key/

[Foster]

tried a few things from the link and other suggestion but it still isnt working how it should. would a bit of background info on what it's for help and maybe a link to the site i need it to work from?

[xyph]

Perhaps some code. We understand what you're trying to do, but we don't want to do it for you.

[haku]

How about some code? And you may want to try the word 'thanks', seeing as many members here have put a fair bit of time into helping you out.

[Foster]

$query = "INSERT INTO $table(email,password,name,Gender,Age,Race,SpecialtyOne,Feet,Weight,Eyes,Hair,Strength,Stamina,Perception,Agility,Intelligence,Tech,Luck,Appearance,Background,Weapons,Armour,Items,Enhancements,Skills,Vehicles)  VALUES('$email','$password','$name','$Gender','$Age','$Race','$SpecialtyOne','$Feet','$Weight','$Eyes','$Hair','$Strength','$Stamina','$Perception','$Agility','$Intelligence','$Tech','$Luck','$Appearance','$Background','$Weapons','$Armour','$Items','$Enhancements','$Skills','$Vehicles')

ON DUPLICATE KEY UPDATE Weapons = '$Weapons', name = '$name'";

$go = mysql_query($query)

or die("Could not insert data because ".mysql_error());

 

this is where i am with the code at the min, it will populate the table but not with the information from the website.

 

<form action="Unarmed.php" method="post">

 

<input type="hidden" name="_submit_check" value="1"/> <center>

<table width="98%" border="0" cellpadding="3" cellspacing="1" bgcolor="">

<tr>

<td bgcolor=""><font> </td>

<td bgcolor=""><font><nobr>Weapon Name</nobr></font></td>

<td bgcolor=""><font>Description</font></td>

<td bgcolor=""><font>Price</font></td>

<tr><td bgcolor="" valign="top"><font size="1" face="Verdana"><input type="radio" name="Weapons" value="Brass Knuckles"></td><td bgcolor="" valign="top"><font size="1" face="Verdana">Brass Knuckles</td><td bgcolor="" width="90%"><font size="1" face="Verdana"><strong>DMG</strong>: +5<BR><strong>Special</strong>: None<BR><strong>Restrictions:</strong>: None<BR>Want to pack a bit more in your punch? This finely crafted brass piece fits over the knuckles with a brace fitting into your palm. Perfect for gaining the upper hand in a fist fight.</td><td bgcolor="" valign="top"><font size="1" face="Verdana">$125</font></td></tr><tr><td bgcolor="" valign="top"><font size="1" face="Verdana"><input type="radio" name="Weapons" value="Spurs"></td><td bgcolor="" valign="top"><font size="1" face="Verdana">Spurs</td><td bgcolor="" width="90%"><font size="1" face="Verdana"><strong>DMG</strong>: +5<BR><strong>Special</strong>: Kicks only.<BR><strong>Restrictions:</strong>: None<BR>A pair of spurs to put on the heels of boots. Most of the time they're sharpened to add in a cut to the initial 'heel to the face' manuever. Just dont try riding a horse with a pair of the sharpened ones.</td><td bgcolor="" valign="top"><font size="1" face="Verdana">$125</font></td></tr><tr><td bgcolor="" valign="top"><font size="1" face="Verdana"><input type="radio" name="Weapons" value="Knife Boots"></td><td bgcolor="" valign="top"><font size="1" face="Verdana">Knife Boots</td><td bgcolor="" width="90%"><font size="1" face="Verdana"><strong>DMG</strong>: +15<BR><strong>Special</strong>: Kicks only.<BR><strong>Restrictions:</strong>: None<BR>A pair of boots with retractable knives in them. Made for those who prefer to put Boot-to-Ass instead of Fist-to-Face.</td><td bgcolor="" valign="top"><font size="1" face="Verdana">$250</font></td></tr><tr><td bgcolor="" valign="top"><font size="1" face="Verdana"><input type="radio" name="Weapons" value="Spiked Gloves"></td><td bgcolor="" valign="top"><font size="1" face="Verdana">Spiked Gloves</td><td bgcolor="" width="90%"><font size="1" face="Verdana"><strong>DMG</strong>: +15<BR><strong>Special</strong>: Can use gloves to scale walls, rocks.<BR><strong>Restrictions</strong>: 25 Strength to climb walls<BR>Not just badass biker gear, the gauntlet-like metal plates sewn into the upper fingers and knuckles of these gloves act as a brace for the half-inch spikes protruding from the leather.</td><td bgcolor="" valign="top"><font size="1" face="Verdana">$350</font></td></tr><tr><td bgcolor="" valign="top"><font size="1" face="Verdana"><input type="radio" name="Weapons" value="Power Fist"></td><td bgcolor="" valign="top"><font size="1" face="Verdana">Power Fist</td><td bgcolor="" width="90%"><font size="1" face="Verdana"><strong>DMG</strong>: +25<BR><strong>Special</strong>: None<BR><strong>Restrictions:</strong>: Requires small energy cells.<BR>A mechanical glove that fits over both hands, the Power Fist is a mighty weapon for those who rely on their fists. Power by energy cells, the Power Fist makes a small explosion every punch by firing an energy cell out. </td><td bgcolor="" valign="top"><font size="1" face="Verdana">$600</font></td></tr><tr><tr><td></td><td width="20%"><font> Name:   </td>

    <td width="80%"><INPUT TYPE="text" NAME="name" size="25%" value=""></td><td bgcolor="" colspan="5" align="right"><input type="submit" name="update" value="BUY" /></tr></table><font size="1" face="Verdana"></form><BR><BR></td></tr></table>

 

this is the code for the website that should be used to update the table.

 

i appreciate all your help but saying thank you in every post iss a bit redundant.

[xyph]

Echo $query. Post the output here.

[Foster]

sorry i'm really new to php and mysql, where in the code would i put that?