[RESOLVED] Basic Script - Insert name = finds name and adds point or adds name and adds point

Clarkie

Hey there,

I am trying to write a basic script but I am new to PHP. I was wondering whether this should go in Newbie section or coding but I decided on posting here. I apologize for not spotting similar threads but there are so many its hard to tell what it the right stuff.

I am attempting to write a script. This is basically what it needs to do.

User goes to page
Enter name in form and submit
Script adds name to database (column=username) and also adds 1 point to the point columns value (column=points)
If name is already inserted it simply adds a point to the point column rather than making a new column
User then visits output page

So far the script adds a name to the database in the correct column. If you enter it twice it makes another field that is identical.

Here is the code I am using:

Form:

<html>
<body bgcolor="#CCCCCC">
<center>
<form action="insert.php" method="post">
Username: <input type="text" name="username" />
<input type="submit" />
</form>
<a href="ranks.php">Click here to see the rank point table!</a>
</center>

</body>
</html>

Insert.php page

<?php
$con = mysql_connect("[COLOR="Red"]DB CONNECT[/COLOR]","[COLOR="Red"]USERNAME[/COLOR]","[COLOR="Red"]PASS[/COLOR]");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("[COLOR="Red"]DB NAME[/COLOR]", $con);

$sql="INSERT INTO [COLOR="Red"]TABLE NAME[/COLOR] (Username)
VALUES
('$_POST[username]')";


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 Rank Point Added!";

mysql_close($con)
?>

Output Page

<?php
$con = mysql_connect("[COLOR="Red"]DB CONNECT[/COLOR]","[COLOR="Red"]USERNAME[/COLOR]","[COLOR="Red"]PASS[/COLOR]");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("[COLOR="Red"]DB NAME[/COLOR]", $con);

$result = mysql_query("SELECT * FROM [COLOR="Red"]DB TABLE[/COLOR]");

echo "<table border='1' align='center' width='80%'>
<tr>
<th>Username</th>
<th>Rank Points</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['username'] . "</td>";
  echo "<td>" . $row['points'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

If you need to know anything else just ask and I'll do my best to communicate with you however I have never done any PHP scripting before now so it may be hard.

Thanks very much,
Clarkie

dagon

before the first insert, do a select to see if the name exists. if it does do an update of points else do the insert

Clarkie

Thanks for the reply. I'll look into your suggestion and hopefully it should work 🙂

bradgrafelman

Rather than doing a separate SELECT statement, add a UNIQUE index (or PRIMARY if you don't have one already) on the username field. Then, use MySQL's "INSERT... ON DUPLICATE KEY" syntax to update the points if the row already exists.

The result is less and more efficient code.

EDIT: For guidance, here is the relevant page in the MySQL manual. Basically, all the syntax you'll need can be gleamed from the first query example on that page:

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

Clarkie

Well you can tell I'm a total noob from this but could you give me the exact code to paste in as all these guides seem really complex and I have no idea what is what. Sorry 🙁

Clarkie

This is what I entered:

$sql="INSERT INTO Clan (Username)
VALUES
('$_POST[username]') ON DUPLICATE KEY UPDATE points=points+1";

and it still adds duplicates and the points remain untouched.

Clarkie

OMG! Can't edit messages! I changed it to:

$sql="INSERT INTO Clan (Username)
VALUES
('$_POST[username]')" ON DUPLICATE KEY UPDATE points=points+1;

and when you submit the page goes blank white and nothing at all is submitted/changed in the db.

bradgrafelman

Clarkie;10947309 wrote:

the page goes blank white and nothing at all is submitted/changed in the db.[/QUOTE]

That's because you a) have a parse error (missing double quote to close the string), and b) don't have display_errors set to On (and/or don't utilize the log_errors directive either).

Other than that, the SQL query syntax looks fine... as long as you've added either a PRIMARY or UNIQUE index on the Username column.

Clarkie

Well I'm assuming I only have to fix the parse error.

$sql="INSERT INTO Clan (Username)
VALUES
('$_POST[username]') ON DUPLICATE KEY UPDATE points=points+1";

That's what I understood. It doesn't work at all..... It simply adds another entry for the username without updating the points.

bradgrafelman

As I said above...

bradgrafelman wrote:
as long as you've added either a PRIMARY or UNIQUE index on the Username column.

Did you add one of those types of indexes on the Username column?

Clarkie

Yes, Username is set as Index.

Clarkie

Ah sorry I changed it to unique and it still makes duplicates.

bradgrafelman

That's impossible, unless a) the index didn't actually get changed to a UNIQUE index (e.g. you already had duplicate data in the table, and trying to add the UNIQUE index resulted in an error), or b) your database collation is case sensitive, and you're thinking that "Joe" and "joe" are duplicates.

Clarkie

Can I give you any information that will help with this?
It's case sensetive, how can I make it case insensitive?

Clarkie

AHA! I set it to Primary and it works 🙂 Must have not clicked something properly. Can I make it case insensitive?

Clarkie

Can you make the entries case insensitive?

dagon

change the db field Collation to something that ends with ci (case insensitive), i assume it currently ends with cs (case sensitive)

Clarkie

Thanks 🙂