[eluser]rochellecanale14[/eluser]
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.
Code:
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);
}
?>
