PHP MySQL Insert Into & On Duplicate Key Problems

Question

Is my code/syntax wrong? I'm not sure what is wrong and am new to this. I have created a table in PHPMyAdmin. It has two columns. One is "id" and is the primary key/auto-increment. The other column is "steamname".

This code is supposed to take a person's online name and enter it into the database. If there is already a record, it should update it anyways with the same/latest name.

The name of the table in phpmyAdmin is names

        <?php
        // Capture person's name from XML file
        $profile = simplexml_load_file('UrlGoesHere.Com/?xml=1', 'SimpleXMLElement', LIBXML_NOCDATA);
        echo (string)$profile->steamID;

        //Enter name into databse and overwrite it if same/duplicate
        $con=mysqli_connect("localhost","username","password","databaseName");
        // Check connection
        if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        // Perform queries
        mysqli_query($con,"INSERT INTO names (steamname) VALUES ('$profile') ON   DUPLICATE KEY UPDATE steamname = VALUES('$profile')");
        mysqli_close($con);
        ?>

I tested this by manually changing the value of a row in "steamname" within PHPMyAdmin to "woogy" and then running this script to see if it would update that database value... nothing is happening. It should update "woogy" to the proper name.

-----------Updates---------------

Hi all. Thank you for the input. I now have my code as follows. Sorry if it's still wrong. I'm learning.

            <?php
        $profile = simplexml_load_file('http://steamcommunity.com/profiles/76561198006938281/?xml=1', 'SimpleXMLElement', LIBXML_NOCDATA);
        echo (string)$profile->steamID;


        $con=mysqli_connect("localhost","userHere","passwordHERE","DBName");
        // Check connection
        if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        // Perform queries
        mysqli_query($con,"INSERT INTO names (steamname) VALUES ('$profile') ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname)");
        mysqli_close($con);
        ?>

This is how the database looks before running the script: (woogy should change to Chatyak)

Click image here

Now, when I run the PHP page, this is what happens to my database.

How database looks after running script

Not sure why it didn't update - and also why there is such a huge space?

Answer 1

Right syntax of INSERT ... ON DUPLICATE KEY would be like below, you need to mention the column name in VALUES like VALUES(column_name) instead of VALUES('$profile'). Also, you are missing the PK column ID in case of UPDATE. Cause you want to update the steamname for particular ID value.

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname)

(OR)

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE steamname = VALUES(steamname)

Quoted from MySQL documentation

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr).

Answer 2

You can't use INSERT ON DUPLICATE KEY UPDATE (IODKU) to update instead of inserting a new row unless you try to insert a value that conflicts with an existing primary or unique key column.

Since you're not specifying any value for ID in this INSERT, it will always increment the auto-increment primary key and insert a new row.

If you want the INSERT to replace the steamname for an existing row, you must specify the ID value in your INSERT.

---

Re your first comment:

I looked at your samples. It's not surprising that it created a new row. It generated a new auto-increment ID value because you didn't specify an ID in your INSERT. So it naturally created a new row. It has no way of telling which row you are trying to replace.

Here's a demo:

mysql> create table names (id serial primary key, steamname text);

mysql> insert into names (steamname) values ('Woogy') 
  on duplicate key update ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname);

Ignore the ID = LAST_INSERT_ID(ID) part, this has no effect. It's a no-op. @Rahul suggested it, but unfortunately he or she is mistaken. I'm going to take out that term in subsequent tests.

So what happens in this INSERT? You specify a value for steamname, but no value for ID. So MySQL generates a new value for ID. That becomes the primary key value for a new row, and that row is inserted with the steamname 'Woogy'.

mysql> select * from names;
+----+-----------+
| id | steamname |
+----+-----------+
|  1 | Woogy     |
+----+-----------+

Next we try to change the name to 'Chatyak':

mysql> insert into names (steamname) values ('Chatyak') 
  on duplicate key update steamname = VALUES(steamname);

Which row does this apply the change to? The INSERT does not specify an ID value, so MySQL auto-increments another new ID value. That value is the primary key for a new row, and that's the row that gets the steamname 'Chatyak'.

mysql> select * from names;
+----+-----------+
| id | steamname |
+----+-----------+
|  1 | Woogy     |
|  2 | Chatyak   |
+----+-----------+

What this means is that you can never trigger the ON DUPLICATE KEY part if you let the auto-increment generate a new ID value every time you INSERT. Every INSERT will result in a new row.

As I said above, ON DUPLICATE KEY does nothing unless you try to insert a value that conflicts with a primary or unique key for a row that already exists in the table. But in this case you aren't conflicting, you're always generating a new ID value. So it inserts a new row.

If you had a UNIQUE KEY constraint on steamname, then that would be another opportunity for triggering ON DUPLICATE KEY. But it still won't do what you want.

mysql> alter table names add unique key (steamname(20));

Then if you try to insert the same steamname as one that already exists, it will not insert a new row. It'll update the existing row.

mysql> insert into names (steamname) values ('Chatyak') 
  on duplicate key update ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname);
Query OK, 0 rows affected (0.02 sec)

Note it says "0 rows" were inserted by that statement.

But this still doesn't allow you to change an existing steamname. If you specify a new name, it'll just insert a new row. If you specify the name that's already in use, it's a duplicate so it won't insert a new row, but it won't change the name either.

How do you expect the INSERT statement to apply to an existing row if you let it auto-increment a new ID value? Which existing row do you think it should conflict with?

---

Re your second comment:

You don't need a secondary unique key, I'm just trying to show you how IODKU works.

Before thinking about the SQL syntax, you have to think about the logic of the problem. In other words, if you specify a new name, which existing row do you want it to replace?

For example:

mysql> insert into names (id, steamname) values (123, 'Chatyak') 
  on duplicate key update steamname = VALUES(steamname);

This would work, because if a row exists with ID 123, this INSERT will cause a duplicate key conflict and therefore the ON DUPLICATE KEY clause will be triggered. But where did we get the value 123 in this example? Presumably in your application, you know what id you think you're updating. Is there a variable for that? Does it correspond to the user's session data?

Answer 3

**************************Edit:*************************

In response to the answer provided by @Rahul. This statement:

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname)

is technically valid but will always affect exactly 0 rows no matter what. Actually there are so many things wrong with it, that it is difficult to enumerate them all (which is the reason for this edit).

The usage of ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id) given in MySQL docs at the bottom of this page is an example of how to capture the id value of a row that has been updated i.e not inserted, since otherwise LAST_INSERT_ID() (with no argument) would return the last inserted id, which in the case of an update would not be meaningful in terms of the row in question.

So the above statement, if it ever worked — though it can't — would be effectively no different from this statement:

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE steamname = VALUES(steamname)

Except that you would also be updating that row's id value to the value it already was.

Aside from that, the reason the statement can never work is because the ON DUPLICATE KEY UPDATE clause will never be evaluated unless there is a duplicate key. And the same error that would occur without the ON DUPLICATE KEY UPDATE clause, will occur when we try to update steamname to equal the value of steamname i.e. the same value that tripped the ON DUPLICATE KEY UPDATE clause in the first place.

*****************************END OF EDIT***********************************

First: $profile is still an instance of SimpleXMLElement object when you try to use it in your query. So the SimpleXMLElement::__toString method is called, but SimpleXMLElement::__toString method only returns text content from directly inside the first element, and not text from any descendants. So if you have some element <steamname> and some element <steamID> nested within the parent element returned by your call to simplexml_load_file, their values will not be returned. See manual.

Second: when using the VALUES function with an UPDATE clause, the correct syntax is to refer to a column name/s like so DUPLICATE KEY UPDATE colname = VALUES(some_col_name). If you want to use an explicit value the syntax is DUPLICATE KEY UPDATE colname = 'value'. See manual.

Third: since you have id set to AUTO_INCREMENT, any rows inserted into the names table without explicitly inserting an id value or using a WHERE clause to specify an id value will create a new row with a new auto incremented id value, regardless of whether you use DUPLICATE KEY UPDATE or not because AUTO_INCREMENT will ensure that you never generate a duplicate key.

I think you may be able to make use of mysql's REPLACE statement instead. It's the simplest query that works for your described needs.

REPLACE INTO `names` (`id`,`steamname`)
VALUES ($id, $steamname)

where $id is $profile->steamID, and $steamname is the new(or not new) value for that row's steamname column.

The REPLACE statement will simply DELETE and then INSERT where an id value already exists. But since you're inserting the same id the effect is of updating the value of steamname in the row that contains a matching id value. Just be aware that if you have other columns besides id and steamname in the same table and you don't include their values in the REPLACE statement's VALUES then those values will be lost.

Fourth: for debuging these kinds of things its important to a) know what values you're actually passing into your query string. b) know that the basic query syntax you're using is correct, apart from any specific values you are(or aren't) using with it.

And finally: make things easy on yourself; since you're already using object notation with SimpleXMLElement to access element values, why not use it with mysqli also? Try this. It is UNTESTED, but it think it will work for you.

  <?php
    // Capture person's name and id from XML file
    $profile = simplexml_load_file('UrlGoesHere.Com/?xml=1', 'SimpleXMLElement', LIBXML_NOCDATA);
      $profile_id = (string)$profile->steamID;
      $profile_name = (string)$profile->steamName;

    //connect to database
    $mysqli = new mysqli("localhost","username","password","databaseName");

    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    //build query
    //IMPROTANT: this will delete other fields(if any)
    //> in the row, unless they are also refilled by this statement
    $q = ("
    REPLACE INTO `names` (`id`,`steamname`)
    VALUES (?, ?)
    ");

    //uncomment to debug query
      //echo "<pre>$q</pre>";

    //uncomment to debug values
      //echo "<pre>profile_name: \n $profile_name</pre>";
      //echo "<pre>profile_id: \n $profile_id</pre>";

    //prepare statement using above query
    $stmt = $mysqli->prepare($q);

    //fill in '?'s with actual values
    //first argument is the data types 
    //'i' [integer] 's' [string]
    //follownig aruments fill in '?'s in order
    $stmt->bind_param('is', $profle_id, $profile_name);

    // execute statement
    $stmt->execute();

    //close statement
    $stmt->close();
    //close connection
    $mysqli->close();
 ?>