0

I am trying to update my one table where there are several records but I want to update those all in a sequence from an array like as array is associative array so I want to update first record with first array items then 2nd record with 2nd array items but Instead what I have tried through following code is that my last array is updated into each record.I don't have any WHERE clause in my query..! Here is my code : 

$json = '[
    {
        "x": 0,
        "y": 0
    },
    {
        "x": 4,
        "y": 0
    }
]';

$decoded_json = json_decode($json,true);

foreach ($decoded_json as $row) {
  $x=$row['x'];
  $y=$row['y'];

 $query = mysqli_query($mysqli,"UPDATE clients SET x='$x',y='$y'");
}

Here is my clients Table SHOW CREATE :

CREATE TABLE `clients` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `full_name` text NOT NULL,
 `age` text NOT NULL,
 `city` text NOT NULL,
 `profession` text NOT NULL,
 `client_id` text NOT NULL,
 `info` text NOT NULL,
 `profile_photo` text NOT NULL,
 `x` text NOT NULL,
 `y` text NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
Umair Shah
  • 2,305
  • 2
  • 25
  • 50
  • 2
    What exactly do you expect to happen without WHERE clause to filter what row should be updated? – al'ein Sep 21 '15 at 20:06
  • Your `UPDATE` is running without a `WHERE`, so each time it updates *every* row. You need to tell it which row(s) to update. MySQL doesn't have any concept of *first* or *second*, rows are stored unordered. You need to tell it *exactly* which row you want. One way would be to add a `AUTO_INCREMENT` field to the table and use that field in your `WHERE`. – gen_Eric Sep 21 '15 at 20:06
  • @AlanMachado Thanks for your reply..I am not filtering anything at all..Instead I just want that the table records starts from first and then goes down updating one by one in an ascending sequence with also array sequence from associative array..! – Umair Shah Sep 21 '15 at 20:07
  • @RocketHazmat Well...`AUTO_INCREMENT` wouldn't exactly do what I want as when if there are some records deleted in between then there will be real problem..! – Umair Shah Sep 21 '15 at 20:09
  • put one column more in your table like id with primary key and auto_increment. So every record has a unique key and you can update it like : UPDATE clients SET x='$x',y='$y' where id =nr; where nr is the numer of the row – Bernd Buffen Sep 21 '15 at 20:09
  • 1
    It won't do what you want unless you use WHERE clause, even if its a "dummy" one. You **need** a column with unique identifier so SQL can **know** which column you're talking about when you command him to do something. – al'ein Sep 21 '15 at 20:12
  • @AlanMachado Besides Would I really need to take all ID's from database then back again to insert it into `WHERE` clause as there are some records deleted in between or is there any easy way? – Umair Shah Sep 21 '15 at 20:16
  • See if [it helps you](http://stackoverflow.com/questions/2635689/efficient-way-to-update-all-rows-in-a-table) – al'ein Sep 21 '15 at 20:18

2 Answers2

4

Some Important Fundamental Info First:

  1. Sql Update Statements operate on the entire table, in the absence of a where clause.
  2. So the last entry in the array is the last UPDATE statements, so it affects all the rows.
  3. That order in a MySql table is not guaranteed in absence of a Order By clause.

The Question then becomes how can we update first record with first array items then 2nd record with 2nd array items.

You would need some way to associate the table's rows to the json.

A possible hack would be to select the primary keys of the table and use that control the update.

// Assuming that 'ID' is the name of primary key column of the table clients
$pkResult = mysqli_query($mysqli, "SELECT ID from clients ORDER BY ID");
$pks = pkResult->fetch_all();
foreach ($decoded_json as $index=>$row)
{
    $x=$row['x'];
    $y=$row['y'];
    $pk = $pks[$index][0]; // The pk will be in index 0 of the returned array

    $query = mysqli_query($mysqli, "UPDATE clients SET x='$x',y='$y' WHERE id = $pk");
}

A better way to handle this type of problem, is to include the Primary Key, in the json. That way the json contains a better record of the action to perform on the DB. In addition, any column that allows you to associate the json row to the table row will work. (Preferably one with an index for speed and stability).

Finally, If you have no Primary Key, then perhaps you can just delete and renter the data from the json.

Community
  • 1
  • 1
SH-
  • 1,642
  • 10
  • 13
3

You have to add a id column to your table. It must be an integer with auto increment.

Then change your code to:

foreach ($decoded_json as $index=>$row)
{
  $x=$row['x'];
  $y=$row['y'];

 $query = mysqli_query($mysqli,"UPDATE clients SET x='$x',y='$y' WHERE id = $index");
}

The way you are doing, every update change the values of all rows in your table. That's why you need a unique column, to distinct the rows.

About the foreach syntax:

The use of the => in the foreach will separate the index of the data and give you both informations. If you execute the code below:

$data = array('a','b','c');

foreach($data as $index=>$value)
{
    echo 'INDEX: ', $index, ' VALUE: ', $value, '<br>';
}

You will get the result:

INDEX: 0 VALUE: a
INDEX: 1 VALUE: b
INDEX: 2 VALUE: c
Marcelo Bruzetti
  • 96
  • 9
  • @Bruzetti Can't use `$index` as ID as because there are some records missing in between and so that's why..! – Umair Shah Sep 21 '15 at 20:19
  • Then why don't you add the ID to the json? `{'id': 1 , 'x': 0, 'y': 0 }` – Marcelo Bruzetti Sep 21 '15 at 20:25
  • @Bruzetti I am trying to save gridstack positions permanent to a mysql database and there are not any ID's besides I just need to update the correct x and y dimenstions for saving positions to each record just..! – Umair Shah Sep 21 '15 at 20:27