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
– jdog, Commented Oct 29, 2013 at 2: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
– Hunza Ali, Commented Oct 29, 2013 at 10:20

jdog · Accepted Answer · 2013-10-28 23:28:35Z

3

Remove the "," after col3=?

This will fix the syntax error

answered Oct 28, 2013 at 23:28

jdog

2,5347 gold badges44 silver badges80 bronze badges

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

3 Comments

Hunza Ali Over a year ago

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.

jdog Over a year ago

No, its good practice to update all columns in one go. Can you paste your query here?

Hunza Ali Over a year ago

I have pasted my real code block and edited my post. Please have a look.

user2376117 · Accepted Answer · 2013-10-28 23:45:51Z

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, 2013 at 23:45

user2376117

234 bronze badges

answered Oct 28, 2013 at 23:28

ReQwire

1236 bronze badges

1 Comment

Hunza Ali Over a year ago

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.

Your Common Sense · Accepted Answer · 2013-10-29 00:18:16Z

0

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);

edited Oct 29, 2013 at 0:18

answered Oct 28, 2013 at 23:49

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:01:47Z

0

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

Update a row mysql in php

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Oct 28, 2013 at 23:31

SBD

4447 silver badges20 bronze badges

Collectives™ on Stack Overflow

update mysql database table fields with a single mysqli statement query

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related