SQL throws an error when two columns have the same value

Question

I want to insert a record if it does not exist without using unique ID, and I found this answer here: MySQL: Insert record if not exists in table.

In my case I have a table:

+----+---------+---------+---------+---------+-----------+-------------+-----------+
| ID | dRelIns | vRelIns | dRelDel | vRelDel | cRelAktiv | iRelDateiId | iRelKatId |
+----+---------+---------+---------+---------+-----------+-------------+-----------+
| 1  |  blabla |  blabla |  NULL   |  NULL   |    J      |      3      |     5     |
+----+---------+---------+---------+---------+-----------+-------------+-----------+
| 2  |  blabla |  blabla |  blabla |  blabla |    N      |      3      |     1     |
+----+---------+---------+---------+---------+-----------+-------------+-----------+
| 3  |  blabla |  blabla |  NULL   |  NULL   |    J      |      3      |     2     |
+----+---------+---------+---------+---------+-----------+-------------+-----------+

I am getting an array ($_POST) with id iRelKatId and another on id iRelDateiId. I check if the id already exists and cRelAktiv = 'J', if not I want to insert a new one. If some entry exists but it's not in the list, I want to set cRelAktiv to 'N'.

My PHP script with the SQL queries:

$list=implode("','", $_POST["kat"]);
        $sql="UPDATE tabRel_UDK SET dRelDel=NOW(),
                                    vRelDel=USER(),
                                    cRelAktiv='N'
                                WHERE iRelDateiId='$_POST[id]' AND cRelAktiv='J' AND iRelKatId NOT IN('$list')";
        $result =  mysql_query($sql) or die(Error (" . mysql_error() . ").");

        foreach ($_POST["kat"] as $value) {
            $sql="INSERT INTO tabRel_UDK (dRelIns, vRelIns, cRelAktiv, iRelDateiId, iRelKatId)
                SELECT * FROM (SELECT NOW(), USER(), 'J', '$_POST[id]','$value') AS tmp
                WHERE NOT EXISTS (
                SELECT iRelDateiId,iRelKatId,cRelAktiv FROM tabRel_UDK 
                WHERE iRelDateiId = '$_POST[id]' AND iRelKatId='$value' AND cRelAktiv='J') LIMIT 1;";
            $result =  mysql_query($sql) or die("Error (" . mysql_error() . ").");
        }

This script works for me, but when both ids have the same value(for example 5), it throws an error Duplicate column name '5' because of SELECT * FROM (SELECT NOW(), USER(), 'J', '$_POST[id]','$value')

Any ideas how to make it works, or should I make 2-3 SQL queries and check the ids manually in PHP?

**WARNING!** Your code contains an [SQL injection vulnerability](http://en.wikipedia.org/wiki/SQL_injection) -- you are passing raw, unfiltered, unvalidated user input directly into an SQL string. SQL injection is [very easy to fix](http://stackoverflow.com/q/60174/168868). Consider [switching to PDO](http://php.net/book.pdo) or [mysqli](http://php.net/book.mysqli) so you can use [prepared statements with parameterized queries](http://en.wikipedia.org/wiki/Prepared_statement). — Charles, Jan 07 '13 at 10:51

halfer · Accepted Answer · 2013-01-07T14:49:14.080

1

I suspect $_POST[id] and $value have the same value, and so you appear to be selecting the same column twice. This should be suppressed in code, so you only select it once - or you should be giving each an alias so this does not happen.

I wouldn't recommend returning a resultset with column names starting with a number anyway - in some database systems that would not be permitted, unless it is quoted. Give them string prefixes as aliases, ending in _<number> if you must.

Thus, your subselect string might look like this:

"SELECT * FROM (
    SELECT
        NOW(),
        USER(),
        'J',
        '{$_POST['id']}' AS val_1,
        '{$value}' AS val_2
) WHERE ..."

More importantly, having $_POST[id] in your code will open you up to SQL injection vulnerabilities - always escape data before using it. Even better, switch to MySQL PDO and use parameterisation.

Lastly, the variable should be $_POST['id'] - PHP assumes that you meant a string index, but it will raise a warning if you skip the quotes. Turn on warnings so you can see mistakes like this.

edited Jan 07 '13 at 14:49

answered Jan 07 '13 at 10:45

halfer

18,701
13
79
158

when $_POST[id] and $value have different values, it works ok, but when they are the same it throws an error. I understand why there is an error but don't know how to make it right. And the column name is not a number, it's just a temp-table – Dirty-flow Jan 07 '13 at 10:51
From what I can tell, your subselect effectively becomes `SELECT * FROM (SELECT NOW(), USER(), 'J', '5', '5')` - thus the string numbers are being treated as column names. Try printing `$sql` to the screen before you run it, to see if it is as you intended? – halfer Jan 07 '13 at 10:59
yes that's the problem, and my question is how can I rewrite the query without using this subselect – Dirty-flow Jan 07 '13 at 11:00
now I see that giving each column an alias solve my problem. can you edit your answer with a short example of this subselect? – Dirty-flow Jan 07 '13 at 14:45
I've used `val_1` AND `val_2` as example aliases here, since of course I don't know what names would be suitable. Glad you fixed it. – halfer Jan 07 '13 at 14:50

SQL throws an error when two columns have the same value

1 Answers1