PHP code can UPDATE MySQL database, but can't INSERT

Question

My update of the database has no problems whatsoever. And down in my local development environment a new record insert also has no problems. I have tried changing my MySQL user many times because I thought it might be about permissions (maybe that is still the problem, I don't know). I have even made the user root and supplied my correct root password: again, I can perform updates, but no new record inserts. Here is my code which I have shortened (i.e taken out the large number of fields. It works locally, but not on the server).

I am on Ubuntu Linux, 16.04, PHP 7

$message = '';

$db = new mysqli('localhost', 'root', 'notrealpword', 'mydatabase');


if ($db->connect_error){

  $message = $db->connect_error;

}else{

//    echo $message ;

}

$prep = $db->prepare("INSERT INTO users (userid, username) VALUES 
('0',?)");
//Now, you can bind some parameters.
$prep->bind_param('s',$username);

$username = "atservertest";


$prep->execute();

$numaffected = $prep->affected_rows ;

echo $numaffected;

//        echo $numaffected . " row(s) affected." ;

$prep->close();

php.net/manual/en/mysqli.error.php

Iłya Bursov
– Iłya Bursov

2018-01-08 04:26:56 +00:00
Commented Jan 8, 2018 at 4:26 — Iłya Bursov
– Iłya Bursov, Commented Jan 8, 2018 at 4:26

WebDev-SysAdmin · Accepted Answer · 2018-01-08 05:50:50Z

2

FIXED MY OWN PROBLEM:

This is caused by the STRICT_TRANS_TABLES SQL mode.

Open phpmyadmin and goto More Tab and select Variables submenu. Scroll down to find sql mode. Edit sql mode and remove STRICT_TRANS_TABLES Save it.

My local phpmyadmin settings were different than the settings for phpmyadmin up at my new server. ... Note that to made the configuration changes above I needed to login as root at phpmyadmin ...

answered Jan 8, 2018 at 5:50

WebDev-SysAdmin

3694 silver badges12 bronze badges

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

1 Comment

Howard Wu Over a year ago

You saved my life. I’ve been working hours and couldn’t find a solution. This one works.

JJJJ · Accepted Answer · 2018-01-08 04:39:14Z

1

$username must initialize first.

//Call this first
$username = "atservertest";
//Then use bind_param
$prep->bind_param('s',$username);

answered Jan 8, 2018 at 4:39

JJJJ

1,3682 gold badges9 silver badges25 bronze badges

1 Comment

WebDev-SysAdmin Over a year ago

Thanks. But I just tried that, but I still get -1 for the number of rows effected. Also, I was using it on my local development environment initializing the $username AFTER and it was working fine.

wangwenming1985 · Accepted Answer · 2018-01-08 05:06:02Z

0

It works, my environment is MacOS and PHP 7.1.12, MySQL 5.6.21

Add code:

$prep->execute();
// see if any error happend
var_dump($db->error_list, $db->error);

to see if any error happend. If no error, then the result should be

array(0) {
}
string(0) ""

answered Jan 8, 2018 at 5:06

wangwenming1985

111 silver badge4 bronze badges

Collectives™ on Stack Overflow

PHP code can UPDATE MySQL database, but can't INSERT

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related