MYSQL - Stored Procedure to update column based on input parameter

Question

I have a simple question,

I have table:

| val | type | status |
|-----|------|--------|
|  90 |    1 |      a |

i would like to have a stored proc that able to update above table based on variable given in input param.

for example, maybe in PHP i have array:

$data = ['val' => 80, 'status' => 'c']

i would like to send that data to mysql stored proc, so it'll knows that it need to update val to 80 and status into 'c'

or another example:

$data = ['type' => 1, 'nothing' => NULL, 'status' => NULL, 'val' => 77]

then it will update type to 1, status to null, val to 77 and ignore 'nothing'

*the $data input, shall not hardcoded in input param in Stored Proc :D

is this can be done 'simply' ?

Thanks!

Why do you want to use a stored proc instead of a simple UPDATE statement? — Paul Spiegel
– Paul Spiegel, Commented Oct 31, 2016 at 16:37
As @PaulSpiegel mentioned, what's stopping you from using an UPDATE statement? — Akshay
– Akshay, Commented Oct 31, 2016 at 16:40
@PaulSpiegel, Do you mean like 'generating' dynamic update statement, then send it to mysql to execute? - well yeah, that maybe can be done, but maybe could lead me to another problem since it's not the only statement of the process (i have quite a few process before and after this update statement). and it also make my standard different, since i put all of 'any related' db transaction in SP. unless it really can't be done, then I will apply exception on this... — AnD
– AnD, Commented Oct 31, 2016 at 16:49

Community · Accepted Answer · 2017-05-23 12:33:18Z

Simple answer than a stored procedure :

<?php

$data = ['val' => 80, 'status' => 'c'];

function auto($x){
$field_string ='';
$input_string='';
foreach  ($x as $userInfo=>$userInfo_value){
    if($userInfo_value !=''){
        echo $userInfo."->".$userInfo_value;
        if ($field_string == '') {
            $field_string = $field_string.$userInfo.' = '.$userInfo_value; 
        }
        else {
            $field_string = ','.$field_string.'='.$userInfo_value; 
        }
    }
}
$sql = "UPDATE protocole_test SET ".$field_string."WHERE (condition not specified in question) "; 
echo $sql ; //check query formed add a execution line after this

}

auto($data);
?>

Explanation :

Here we make a php function which handle your DATA ARRAY and form a dynamic update statement to update select fields with selected data.

Why not stored procedures ?

1) Stored procedures are stored in MYSQL engine

2) It cannot process DYNAMIC , VARIABLE LENGTH data arrays SIMPLY.

Still how to do procedural style :

Check out Passing string to Mysql Procedure.

Algorythem :

1) form a quamma seprated list like -> key,value,key,value with help of php as used in above php answer;

2) Call Procedure with QCM as parameter ;

3) Edit procedure to update fields (in answer insert operation is performed);

4)test.

Advantage of using php function :

1) Its simple . 2) it satify all purpouse of making a STORED PROCEDURE. 3) Its can also be called by ajax.

Thank you very much.

Collectives™ on Stack Overflow

MYSQL - Stored Procedure to update column based on input parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related