Geek Answers Handbook

Updating minimum values ​​based on another value - MySql

I have a set of information in which I will blow it up with id, code and number.

list ($id,$code,$num) = explode("~",$data);

I need to upgrade it to MySql based on a unique code with a minimum num value

Suppose my first 3 queries look like

 id = 9267399 code = 5D:148 num = 64 id = 9267398 code = 5D:186 num = 71 id = 9267397 code = 5D:122 num = 93

Then my 4th, 5th requests have a duplicate code 5D:148 with different identifiers and numbers.

 id = 9267402 code = 5D:148 num = 22 id = 9267563 code = 5D:148 num = 5

Now I need to find min(num) for the duplicate code and update this entry back to mysql. My queries should look like

 $sql = "UPDATE table SET id = '9267398', num = '71' WHERE code = '5D:186' "; $sql = "UPDATE table SET id = '9267397', num = '93' WHERE code = '5D:122' "; $sql = "UPDATE table SET id = '9267563', num = '5' WHERE code = '5D:148' ";

here 5D:148 has 3 queries in which min(num) is 5.

I tried to find duplicate code

 $temp = array(); foreach ($code as $c) { isset($temp[$c]) or $temp[$c] = 0; $temp[$c] ++; } var_dump($temp);

It gives me

 array(3) {["5D:148"]=> int(3) ["5D:186"]=> int(1) ["5D:122"]=> int(1)}

I'm stuck here how to find min(num) and run my update request on it

+5
source share
3 answers

Looping through an array and checking if num is lower than the previous one.

An example of the $ data array:

 $data = array( [0] => array('id' => 9267399, 'code' => '5D:148', 'num' => 64), [1] => array('id' => 9267398, 'code' => '5D:186', 'num' => 71) );

-

 <?php $array_to_add = array(); foreach($data AS $val) { if(array_key_exists($val['code'], $array_to_add)) { if((int) $val['num'] < (int) $array_to_add[$val['code']]['num']) { // Check if num is lower than the previous one $array_to_add[$val['code']] = $val; // If yes, overwrite code key with new values } } else { $array_to_add[$val['code']] = $val; // Add values to array if code doesn't exist in array_to_add } } var_dump($array_to_add); // ARRAY TO ADD IN DATABASE foreach($array_to_add AS $val) { mysql_query("UPDATE table SET id = ".mysql_real_escape_string($val['id']).", num = ".mysql_real_escape_string($val['num'])." WHERE code = '".mysql_real_escape_string($val['code'])."' "); } ?>

Tip. Take a look at PDO and mysqli for better use of mysql queries.

+1
source

I think there is a simpler solution ... In fact, I just tested it ...
UPDATE table SET id = '9267398', num = '71' WHERE code = '5D: 186'
ORDER BY code ASC LIMIT 1;

+2
source

So, how about this - you can run the query for all queries (you don't need to find min in php), and finally you will have min in DB (due to "and num> 5") I suppose min is integer, not varchar:

 UPDATE table SET id = '9267563', num = 5 WHERE code = '5D:148' and num > 5 UPDATE table SET id = '9267563', num = 93 WHERE code = 'SD:122' and num > 93 etc.

Not so difficult, but it should work for you.

+1
source

More articles:


All Articles