Update json in database mysql

Question

I want comparison between two table (table_foreign, table_submits) in database that if not existing some data from table table_foreign in table table_submits on database, deletded it data in table table_foreign or updated.

$query_tfhi = $this->db->query("SELECT * FROM table_foreign ORDER BY id desc");
foreach ($query_tfhi->result() as $row) {
    $data_hi = json_decode($row->how_id, true);
    foreach ($data_hi as $hitf) {
        foreach ($hitf['howinto_id'] as $val_hitf) {
            //echo $val_hitf.'<br>';

            $query_delhi = $this->db->query("SELECT * FROM table_submits WHERE id LIKE '$val_hitf'");
            if ($query_delhi->num_rows() == 0) {
                //echo $val_hitf;
                $this->db->query("DELETE how_id = array('howinto_id'=>$val_hitf) FROM tour_foreign WHERE id LIKE '$row->id'");
            } else {
            }
        }
    }
}

I have in table table_foreign on column how_id as(this data store(inserted) with json_encode on a column in row database table):

[{
    "howinto_id": ["14"]
},{
    "howinto_id": ["5"]
},{
    "howinto_id": ["4"]
}, {
    "howinto_id": ["3"]
}, {
    "howinto_id": ["2"]
}, {
    "howinto_id": ["1"]
}]

in table table_submits on column id:

1, 2, 3, 4

With comparison between two table in table_foreign value 14,5` should deleted. after this it is as:

[{
    "howinto_id": ["4"]
}, {
    "howinto_id": ["3"]
}, {
    "howinto_id": ["2"]
}, {
    "howinto_id": ["1"]
}]

In output above PHP code have error:

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('howinto_id'=>14) FROM tour_foreign WHERE id LIKE '1'' at line 1

DELETE how_id = array('howinto_id'=>14) FROM table_foreign WHERE id LIKE '1'

Filename: D:\xampp\htdocs\system\database\DB_driver.php

Line Number: 330

How can fix they?

possible duplicate of [Update and delete data in database mysql](http://stackoverflow.com/questions/7625055/update-and-delete-data-in-database-mysql) — ThiefMaster, Oct 02 '11 at 08:58
Why are you inserting JSON into MySQL in the first place? This is [bad design](http://stackoverflow.com/questions/7364803/storing-arrays-in-the-database/7364834#7364834) — NullUserException, Oct 02 '11 at 09:15
Do you want to compare against an element of a JSON value in a table? That's not possible, at least not any sane way. — Pekka, Oct 02 '11 at 09:15

score 0 · Answer 1 · answered Oct 02 '11 at 12:59

Well i think your problem should be fixed like this:

First , you need to retrieve data of two tables as arrays (anyhow) from json obj as it returns array
Second you can use array_diff($array1 , $array2) which returns array of differences between those tow arrays
Third you can select Ids from the difference array ( $ids )

and use

$this->db->where_in("Id",$ids)->delete("table_submits")

Like this you have deleted the diff between the tow tables from the database , and tell me if this is ok with you

Update json in database mysql

1 Answers1