How to update multiple rows with condition using mysql database?

Question

Hello guys I just want to ask. How can I update multiple rows in database with a condition. Because I have problem updating my table. Here's what i need to do. I have 2 tables, one is ref_cities and one is ref_draft

In my ref_cities I have a column

mysql> desc ref_cities
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int(11)      | NO   | PRI | NULL    |       |
| name          | varchar(255) | YES  |     | NULL    |       |
| province_id   | int(11)      | YES  |     | NULL    |       |
| province_code | varchar(5)   | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

In my ref_draft I have this

mysql> desc ref_draft;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(10)     | NO   | PRI | NULL    | auto_increment |
| municipality | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(50) | YES  |     | NULL    |                |
| city_code    | int(11)     | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

In my ref_cities values (i listed only 1 row for example purposes)

mysql> select * from ref_cities where name like '%manila%';
+------+--------+-------------+---------------+
| id   | name   | province_id | province_code |
+------+--------+-------------+---------------+
| 1976 | Manila |          58 | MNL           |
+------+--------+-------------+---------------+
1 row in set (0.00 sec)

In my ref_draft values

mysql> select * from ref_draft where municipality like '%manila%';
+-----+----------------+----------------+-----------+
| id  | municipality   | name           | city_code |
+-----+----------------+----------------+-----------+
|   1 | CITY OF MANILA | Barangay 1     |      NULL |
|   2 | CITY OF MANILA | Barangay 2     |      NULL |
|   3 | CITY OF MANILA | Barangay 3     |      NULL |
|   4 | CITY OF MANILA | Barangay 4     |      NULL |
|   5 | CITY OF MANILA | Barangay 5     |      NULL |
|   6 | CITY OF MANILA | Barangay 6     |      NULL |
|   7 | CITY OF MANILA | Barangay 7     |      NULL |
|   8 | CITY OF MANILA | Barangay 8     |      NULL |
|   9 | CITY OF MANILA | Barangay 9     |      NULL |
|  10 | CITY OF MANILA | Barangay 10    |      NULL |
|  11 | CITY OF MANILA | Barangay 11    |      NULL |
|  12 | CITY OF MANILA | Barangay 12    |      NULL |
|  13 | CITY OF MANILA | Barangay 13    |      NULL |
|  14 | CITY OF MANILA | Barangay 14    |      NULL |
|  15 | CITY OF MANILA | Barangay 15    |      NULL |
|  16 | CITY OF MANILA | Barangay 16    |      NULL |

THIS SHOULD BE THE SAMPLE OUTPUT

+-----+----------------+----------------+-----------+
| id  | municipality   | name           | city_code |
+-----+----------------+----------------+-----------+
|   1 | CITY OF MANILA | Barangay 1     |      1976 |
|   2 | CITY OF MANILA | Barangay 2     |      1976 |
|   3 | CITY OF MANILA | Barangay 3     |      1976 |
|   4 | CITY OF MANILA | Barangay 4     |      1976 |
|   5 | CITY OF MANILA | Barangay 5     |      1976 |
|   6 | CITY OF MANILA | Barangay 6     |      1976 |
|   7 | CITY OF MANILA | Barangay 7     |      1976 |
|   8 | CITY OF MANILA | Barangay 8     |      1976 |
|   9 | CITY OF MANILA | Barangay 9     |      1976 |
|  10 | CITY OF MANILA | Barangay 10    |      1976 |
|  11 | CITY OF MANILA | Barangay 11    |      1976 |
|  12 | CITY OF MANILA | Barangay 12    |      1976 |
|  13 | CITY OF MANILA | Barangay 13    |      1976 |
|  14 | CITY OF MANILA | Barangay 14    |      1976 |
|  15 | CITY OF MANILA | Barangay 15    |      1976 |
|  16 | CITY OF MANILA | Barangay 16    |      1976 |

BUT I GOT

+----+----------------+-------------+-----------+
| id | municipality   | name        | city_code |
+----+----------------+-------------+-----------+
|  1 | CITY OF MANILA | Barangay 1  |      1301 |
|  2 | CITY OF MANILA | Barangay 2  |      1301 |
|  3 | CITY OF MANILA | Barangay 3  |      1301 |
|  4 | CITY OF MANILA | Barangay 4  |      1301 |
|  5 | CITY OF MANILA | Barangay 5  |      1301 |
|  6 | CITY OF MANILA | Barangay 6  |      1301 |
|  7 | CITY OF MANILA | Barangay 7  |      1301 |
|  8 | CITY OF MANILA | Barangay 8  |      1301 |
|  9 | CITY OF MANILA | Barangay 9  |      1301 |
| 10 | CITY OF MANILA | Barangay 10 |      1301 |

The condition is first I will get the name and id from ref_cities. After that next is i will find if the name is found in the municipality column. If found it will inserted the id from my ref_cities into city_code from my ref_draft.

Here's my PHP code for updating the rows. (im using codeigniter)

<?php

$findCityName = "SELECT id,name FROM ref_cities";
$resultFindCity = $this->db->query($findCityName);

foreach($resultFindCity->result_array() as $row){

        $name = $row['name'];
        //$name = 'Manila';

        $update =  array(
            'city_code' => $row['id']
        );
        $this->db->like('municipality','{$name}');
        $this->db->update('ref_draft',$update);

}
?>

Please var_dump($row); inside of foreach and get dump data. — Bora
– Bora, Commented Jul 30, 2013 at 6:57

Yogesh Suthar · Accepted Answer · 2013-07-30 06:20:13Z

3

$this->db->like('municipality','{$name}');

should be

$this->db->like('municipality',"{$name}");

OR

$this->db->like('municipality',$name);

Inside single quotes variable is treated as a string, not as actual variable. Either enclose it inside double quotes OR don't use any quotes.

answered Jul 30, 2013 at 6:20

Yogesh Suthar

30.5k18 gold badges75 silver badges100 bronze badges

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

3 Comments

Yogesh Suthar Over a year ago

@user2585508 Is 1301 also present in that ref_cities table? And if yes, do you want to select only 1976 ids data?

rochellecanale Over a year ago

Yes it is present in that table. No, I need to compare the name in the ref_cities table to ref_draft names. I used the 'LIKE' command for comparing. After comparing I need to get the specific id from my ref_cities table and add it to city_code for that row.

Yogesh Suthar Over a year ago

@user2585508 For that see what query is generated by codeigniter.

Collectives™ on Stack Overflow

How to update multiple rows with condition using mysql database?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related