update mysql database table fields with a single mysqli statement query

Question

I have a database table and i am updating the table columns this way.

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

        if (mysqli_connect_errno()) {
            echo 'failed to connect to db.. <br>' . mysqli_connect_errno();
            return 'error';
        }

   $username = $data['username'];
   $data['image'] = $this->replace_whitespace($data['image']);

   foreach($data as $key=>$value){

       $this->query = "UPDATE users SET $key=? WHERE username='$username'";
       $this->statement = $mysqli->prepare($this->query);

       if($this->statement){

           $this->statement->bind_param('s', $value);
           $this->statement->execute();
           $this->statement->close();
       }

   }

Is it possible to update more than one table columns in one go. I tried this but in-vain.

   $this->query = "UPDATE users SET col1=?, col2=?, col3=? WHERE username='$username'";
   $this->statement = $mysqli->prepare($this->query);

   if($this->statement){

       $this->statement->bind_param('sss', $value1, $value2, $value3);
       $this->statement->execute();
       $this->statement->close();
   }

Is there a better way doing this?

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

        if (mysqli_connect_errno()) {
            echo 'failed to connect to db.. <br>' . mysqli_connect_errno();
            return 'error';
        }
        $username = $data['username'];
        $this->query = "UPDATE users SET fname=?, lname=?, email=?, tpin=?, image=?, address=? country=?, city=?, state=?, postal=? WHERE username='$username'";
        $this->statement = $mysqli->prepare($this->query);

        if ($this->statement) {
            $this->statement->bind_param('ssssssssss', $data['fname'],$data['lname'],$data['email'],$data['tpin'], $data['file'], $data['address'],$data['country'],$data['city'],$data['state'], $data['post_code']);
            $this->statement->execute();
             $this->statement->close();
       }

This is my real code.

You have 10 parameter placeholders and 11 paramaters. The numbers need to match up! — jdog, Oct 29 '13 at 02:33
please ignore the syntax errors in this code since its just an example to show you guys. I have been trying with same number of parameter holders and parameters. I just wanted to know the better way doing this if possible. Thanks for your reply. — Hunza Ali, Oct 29 '13 at 10:20

score 3 · Answer 1 · answered Oct 28 '13 at 23:28

3

Remove the "," after col3=?

This will fix the syntax error

answered Oct 28 '13 at 23:28

jdog

2,465
6
40
74

The extra comma was just a typing mistake not in my script but over here. Actually the problem is, i need to update more then 12 columns in one go. I tried this way but it did not work. Is good practice if i use the loop and let it taking care of all updates. – Hunza Ali Oct 28 '13 at 23:38
No, its good practice to update all columns in one go. Can you paste your query here? – jdog Oct 28 '13 at 23:40
I have pasted my real code block and edited my post. Please have a look. – Hunza Ali Oct 29 '13 at 00:19

score 1 · Answer 2 · edited Oct 28 '13 at 23:45

1

 $this->query = "UPDATE users SET col1=?, col2=?, col3=?, WHERE username='$username'";

You have an extra comma, meaning your SQL is reading "WHERE" as another column and everything gets messed up.

 $this->query = "UPDATE users SET col1=?, col2=?, col3=? WHERE username='$username'";

Should work fine.

In response to the comment below, this is the correct way of going about it, so it must be a faulty variable somewhere, what error messages are you getting? (If any)

It could also be that one of the parameters you are binding is not a string. Regardless, we'd need a more in-depth example.

edited Oct 28 '13 at 23:45

user2376117

23
4

answered Oct 28 '13 at 23:28

ReQwire

123
6

The extra comma was just a typing mistake not in my script but over here. Actually the problem is, i need to update more then 12 columns in one go. I tried this way but it did not work. – Hunza Ali Oct 28 '13 at 23:37

score 0 · Answer 3 · edited May 23 '17 at 12:01

0

I assume it works the same way as putting new values into the database.

Update a row mysql in php

edited May 23 '17 at 12:01

Community

1
1

answered Oct 28 '13 at 23:31

SBD

446
6
17

Your Common Sense · Accepted Answer · 2013-10-29T00:18:16.080

Is it possible to update more than one table columns in one go

Yes. Actually, updating many fields in one query is a very core feature of any DBMS. You can always expect it to be supported.

I tried this but in-vain.

Well, you have to try more, like we all do. After all, it's your job.

Two notes regarding your "real" code:

You have to bind ALL variables in the query, not only some of them

you have to configure mysqli to report errors:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

update mysql database table fields with a single mysqli statement query

4 Answers4