Query to insert or if needed update record

Question

  +----------------+-----------+
  |name(varchar)PK |money(int) |
  +----------------+-----------+

How can i formulate a query so that it can:

1.Be able to insert a name and money value

2.If the said name already exists it should only update the money column by adding its value with that it gets from a form. tried using:

   REPLACE INTO practable SET name = '$name', money = 'money' + $amount

It fulfills the first requirement but while updating the money column it simply replaces the old value with the new one, doesn't add them.

Here is the full code(Draft version):

        <?php
           //This script by another form which takes the amount and name paramters from user
    if(!empty($_GET['nameg']) && !empty($_GET['amountg'])){

        $user="root";
        $pass="password";
        $db="practice";
        $name=$_GET['nameg'];
        $amount=$_GET['amountg'];

        mysql_connect('localhost',$user,$pass) or die("Connection Failed!, " . mysql_error());
        $query="REPLACE INTO practable SET name = '$name', given = 'given' + $amount";
        mysql_select_db($db) or die("Couldn't connect to Database, " . mysql_error());
        mysql_query($query) or die("Couldn't execute query! ". mysql_error());
        mysql_close() or die("Couldn't disconnect!");



    }


    ?>

Did you try: REPLACE INTO practable SET name = '$name', money = SUM(money,$amount) By the way. Your code is extremely dangerous! It allows for SQL injection attacks. — d_inevitable, Mar 17 '12 at 19:56
Please read up on SQL-injection... See: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain — Johan, Mar 17 '12 at 20:01

score 3 · Accepted Answer · answered Mar 17 '12 at 19:57

3

Try insert ... on duplicate key update:

INSERT INTO practable(name,money) VALUES('$name',$amount) ON DUPLICATE KEY UPDATE
 name = '$name', money = `money` + $amount

I assume your variables $name and $amount are properly escaped

answered Mar 17 '12 at 19:57

a1ex07

36,826
12
90
103

score 1 · Answer 2 · answered Mar 17 '12 at 19:58

1

You're looking for the INSERT ... ON DUPLICATE UPDATE syntax.

From the documentation:

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

UPDATE table SET c=c+1 WHERE a=1;

answered Mar 17 '12 at 19:58

Matt Beckman

5,022
4
29
42

score 0 · Answer 3 · answered Mar 17 '12 at 19:58

0

try leaving out the '' on money. That will keep it as a colmmn and not as a string

answered Mar 17 '12 at 19:58

yehuda

1,254
2
11
21

score 0 · Answer 4 · answered Mar 17 '12 at 19:58

0

Try this:

$query="REPLACE INTO practable SET name = '$name', given = given+" . (int)$amount;

answered Mar 17 '12 at 19:58

Rick Kuipers

6,616
2
17
37

score 0 · Answer 5 · answered Mar 17 '12 at 20:01

0

Replace will delete the old row if the primary key exists so you are adding to nothing.

answered Mar 17 '12 at 20:01

qitch

829
3
12
21

Query to insert or if needed update record

5 Answers5