On duplicate?

[Streakerstyle]

Hello,

 

I'm quite new to mysql and putting things in the database through php,

but i've managed to get quite far with my script.

It can connect with the database,

get to the right table and write the given username in it and set the "votes" to 1.

 

The only problem i encountered is:

$sql="INSERT INTO `players` (`name`, `votes`) VALUES ('".$voter."', '1')
ON DUPLICATE KEY UPDATE `votes`=votes+1";

 

The ON DUPLICATE part of the code isn't working.

And i can't figure out why.

So if someone here can help me, that would be fantastic!

 

What i want to do is:

if given username doesn't exist in the database put him in with "votes" set to 1

if username already exists in the database add +1 to "votes"

 

The full code:

<?
$voter = $_GET['username'];
$conn = new MySQLi("localhost", "username", "password", "votetest");
if (mysqli_connect_errno()) {
exit('connect failed: '. mysqli_connect_error());
}
"INSERT INTO `players` (`name`, `votes`) VALUES ('".$voter."', '1')
ON DUPLICATE `name` UPDATE `votes`=votes+1";
echo "added";
?>

 

NOTE:

don't mind my grammar, i'm dutch.

Edited January 4, 2013 by Streakerstyle

[Barand]

You would need a UNIQUE key defined on the name column

 

Syntax is

INSERT INTO `players` (`name`, `votes`) VALUES ('".$voter."', '1')
ON DUPLICATE KEY UPDATE `votes`=votes+1"

 

And you need to execute the query with mysqli_query

[Streakerstyle]

I've set the mysqli_query() to execute the query,

and changed the syntax from name to KEY again.

 

mysqli_query("INSERT INTO `players` (`name`, `votes`) VALUES ('".$voter."', '1') 
 ON DUPLICATE KEY UPDATE `votes`=votes+1");

 

But now it is giving me the warning:

Warning: mysqli_query() expects at least 2 parameters, 1 given in ... on line 16

 

line 16 is empty, and under the newly set on duplicate code.

So what parameter am i missing in this case?

 

and a quick question about that on duplicate,

will it add the +1 to all the votes or just to the vote for the given username?

[Barand]

check the link I gave you to the manual for mysqli_query

 

edit - updates just the record for the given username

Edited January 4, 2013 by Barand

[Streakerstyle]

i was reading it and spotted the error.

had to put the $conn from the first line before the input.

 

But still it isn't working how it should,

it will now create a new line even if name exists.

And won't add 1 to the "votes" column if the name already exists.

 

mysqli_query($conn, "INSERT INTO `players` (`name`, `votes`) VALUES ('".$voter."', '1')
ON DUPLICATE KEY UPDATE `votes`=votes+1");

 

so there will probably be still a mistake in the ON DUPLICATE line of the code.

can it be: `votes`=votes+1? because in the database there won't be "votes" but 1 in this case.

 

---

The database after executing the edited php file:

Edited January 4, 2013 by Streakerstyle

[Barand]

You would need a UNIQUE key defined on the name column

[Streakerstyle]

Oh oversaw that,

added a UNIQUE key in name column and it works.

 

Thanks!