Update query works but not insert query

Question

I'm having a problem with inserting info into the database. Strangely the update query works but not the insert query. I don't get any error either when submitting, it goes through correctly and echo account saved but nothing is inserted. What am i missing or doing wrong. please assist

if(isset($_POST['Submitaccount'])){
$allowedusers = $_POST['users'];
$accountid = trim($_POST['accountid']);
if(!$_POST['copyperms']) $_POST['copyperms']='N';
if(!$_POST['allusers']) $_POST['allusers']='N';
if(!$_POST['enabled']) $_POST['enabled']='N';
if(!$_POST['servertime']) $_POST['servertime']='N';
if(!$_POST['delremovals']) $_POST['delremovals']='N';

unset($_POST['Submitaccount']);
unset($_POST['accountid']);
unset($_POST['users']);

$notmust = array("email" , "skip" , "comments" , "firstmod");

foreach($_POST as $key=>$val){
    if(!trim($val) && !in_array($key , $notmust)) {
        $err = 1;
        $empty = "$key";
        break;
    }
    $qpart .= "`$key` = '".mysql_escape_string($val)."' , " ;
}
if($qpart) $qpart = substr($qpart , 0 , -2);

if(!$err){
    $chk = mysql_num_rows(mysql_query("SELECT * from accounts WHERE name = '".mysql_escape_string($_POST['name'])."' and id <> '$accountid'"));
    if($chk >0){
        $err = 2;
    }
}

if(!$err){
    if(!$accountid){
        $q = "INSERT into accounts SET $qpart ";
        mysql_query($q) or die("Error inserting the record :".mysql_error()."<br>".$q);
        $accountid = mysql_insert_id();
    }else{
        $q = "UPDATE accounts SET $qpart WHERE id = '$accountid'";
        mysql_query($q) or die("Error updating the record :".mysql_error()."<br>".$q);
    }
}

Does it work if you execute the INSERT query by hand?

Barmar
– Barmar

2015-01-23 06:50:49 +00:00
Commented Jan 23, 2015 at 6:50 — Barmar
– Barmar, Commented Jan 23, 2015 at 6:50
Can you provide a $qpart value itself?

VMAtm
– VMAtm

2015-01-23 06:58:54 +00:00
Commented Jan 23, 2015 at 6:58 — VMAtm
– VMAtm, Commented Jan 23, 2015 at 6:58
Related stackoverflow.com/a/12516294/213550

VMAtm
– VMAtm

2015-01-23 07:19:05 +00:00
Commented Jan 23, 2015 at 7:19 — VMAtm
– VMAtm, Commented Jan 23, 2015 at 7:19

VMAtm · Accepted Answer · 2015-01-23 06:54:05Z

3

This is because the INSERT command has different syntax:

INSERT into accounts SET $qpart "

is not usual, you can write it like this:

INSERT into accounts (column names) VALUES your values"

13.2.5 INSERT Syntax

answered Jan 23, 2015 at 6:54

VMAtm

28.4k17 gold badges85 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Herman Nz · Accepted Answer · 2015-01-23 07:09:52Z

0

You have double if(!$err){. Do you want both (!$err) into one? If the first (!$err) is for indicator for the second to insert, function SELECT can not be placed above the function INSERT indirectly.

try this:

if(!$err){
    $chk = mysql_num_rows(mysql_query("SELECT * from accounts WHERE name = '".mysql_escape_string($_POST['name'])."' and id <> '$accountid'"));
    if($chk >0){
        $err = 2;
        // if(!$err){ again ...
        if(!$accountid){
            $q = "INSERT into accounts SET (column1) VALUES ($var1)";
            mysql_query($q) or die("Error inserting the record :".mysql_error()."<br>".$q);
            $accountid = mysql_insert_id();
            }
        else{
            $q = "UPDATE accounts SET $qpart WHERE id = '$accountid'";
            mysql_query($q) or die("Error updating the record :".mysql_error()."<br>".$q);
            }
        }
}
else{
    //other code to handle if ($err)
}

Note: I would prefer using PDO to handle database, it's so simple scripting, besides, it's no longer supported

edited Jan 23, 2015 at 7:09

answered Jan 23, 2015 at 7:02

Herman Nz

2,2613 gold badges23 silver badges38 bronze badges

4 Comments

Donovan Duplessis Over a year ago

I have tried your suggestion but the result stays the same. nothing is inserted.

Herman Nz Over a year ago

change the $qpart into (column1,...) VALUES ($variable)

Donovan Duplessis Over a year ago

this is what i have tried but still no result. $q = "INSERT INTO users (name, username, password)\n" . "VALUES (\'$name\', \'$username\', \'$pass\')\n" . "\n" . "";

Herman Nz Over a year ago

do the variables in ($) already represent each of them correctly.?

Webjunkie · Accepted Answer · 2015-01-24 10:01:56Z

0

You have to understand that mysql functions have become deprecated. Either using mysqli or pdo would be the better option, but if you absolutely have to use mysql as a solution i would suggest not posting the form to itself, rather post to another php file as you will have less problems.In my environment it seems to work well as an interim solution while we are rewriting everything to use mysqli.If it a go and let me know.

answered Jan 24, 2015 at 10:01

Webjunkie

417 bronze badges

1 Comment

Donovan Duplessis Over a year ago

Thanks that did it. I was actually pleasantly surprised as i was hitting a brick wall with a hole lot of experimentation. I just created another php file with just posting 3 variables and low and behold it inserted to the database. Thanks again.

Collectives™ on Stack Overflow

Update query works but not insert query

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related