CODEWITHSUNDEEP
phpmysqlajax
Misael Tovar
Misael Tovar

Reputation: 77

MYSQL column specified twice INSERT

I hope I can get some help.

I am making a add task table where a user can input the title, a desc, user assigned and other things into the specifications of the task. There's an option to add another row (task) and this is generated using ajax.

Each input in the row has an original name (name="tasktitle0", name="privacy0") and when you add a new row they have the same name except a variable is incremented and is attached to the end of if ex (tasktitle1, and taskprivacy1 so on and so forth).

when I submit the form I subst() the name of the input, trimming the first four characters and then trimming the last character so the name becomes "title", "privacy" just as the columns in the database that im going to insert them.

if I only submit one task, the app works. but if I create a new row(task) MySql gives me an error saying that

There was an error. Error #Column 'privacy' specified twice

i know why is doing it, I just need to know how I could fix this issue.

heres the php where im trimming the names: 

if ($_GET['s'] == "Y") {
    $str2 = '';
    foreach ($_POST as $p => $v) {
        if (substr($p, 0, 4) != "flag" && $p != "key" && substr($p, 0, 4) == "task") {
            if (!empty($v)) {
                $str2 .= mb_substr(substr($p, 4), 0, -1) . "='" . addslashes($v) . "',";
            } else {
                $str2 .= substr($p, 4) . "=NULL,";
            }
        }
    }
}

Here's where I'm inserting it:

$sql3 = "INSERT INTO nameofDB.tablename SET ";
$sql3 .= $str2;
$sql3 = substr($sql3, 0, -1);

if ($str2 != '') {
    if ($r2 = mysql_query($sql3, $Link)) {
        $mess = "Successfully saved support ticket.";
    } else {
        $mess = "There was an error. Error #" . mysql_error($Link);
        echo $mess . "<br>";
        echo $sql3;
        exit;
    }
}

and here's the error it displays:

this is when I do 2 rows (tasks), I know that in one query im telling it to instert into the columns twice but how could I fix this? ideas?

There was an error. Error #Column 'privacy' specified twice INSERT INTO dbname.tablename SET ticketNum='95240560',title='d',tdesc='asd',datefrom='02/26/2013',cat='PERSONAL',userid='3171',status='INACTIVE',priority='MEDIUM',privacy='PUBLIC',ticketNum='95240560',title='sad',tdesc='d',datefrom='02/28/2013',cat='BEREAVEMENT',userid='1949',status='COMPLETED',priority='HIGH',privacy='PRIVATE'

Upvotes: 0

Views: 32066

Answers (2)

DaveRandom
DaveRandom

Reputation: 88647

The SQL syntax for inserting more than one row at once into a MySQL table is:

INSERT INTO nameofDB.tablename
    (columnName1, columnName2, columnName3, columnName4)
VALUES
    ('row1value1', 'row1value2', 'row1value3', 'row1value4'),
    ('row2value1', 'row2value2', 'row2value3', 'row2value4');

You cannot use INSERT ... SET syntax to insert multiple rows in a single query.

For more information, see http://dev.mysql.com/doc/refman/5.5/en/insert.html

Upvotes: 2

John Conde
John Conde

Reputation: 219804

The error message says it all: you're trying to insert two values into the column privacy

INSERT INTO dbname.tablename SET ticketNum='95240560',title='d',tdesc='asd',datefrom='02/26/2013',cat='PERSONAL',userid='3171',status='INACTIVE',priority='MEDIUM',privacy='PUBLIC',ticketNum='95240560',title='sad',tdesc='d',datefrom='02/28/2013',cat='BEREAVEMENT',userid='1949',status='COMPLETED',priority='HIGH',privacy='PRIVATE'

Even worse, you're assigning two different values. You really need to check your logic as that's not a good sign that you're getting conflicting values.

Upvotes: 1

Related Questions