Create mulitple SQL query commands from a single array

Hi,

Try the below method,

Your Array Values:

$arr_val = Array (
[user1_mon_shift] => S1(Morning)
[user1_tue_shift] => S1(Morning)
[user1_wed_shift] => S3(Night)
[user1_thu_shift] => Earned Leave
[user1_fri_shift] => S2(Afternoon)
[user1_sat_shift] =>
[user1_sun_shift] =>
[user2_mon_shift] => S1(Morning)
[user2_tue_shift] => S2(Afternoon)
[user2_wed_shift] => S3(Night)
[user2_thu_shift] => S3(Night)
[user2_fri_shift] => S3(Night)
[user2_sat_shift] => On Call
[user2_sun_shift] => On Call
[submit] => consolidatedss )


Update Query Statement:

foreach($i;$i<$user_count;$i++){

UPDATE user1 SET
user1_mon_shift=$arr_val["user$i_mon_shift"],
user1_tue_shift = $arr_val["user$i_tue_shift"],
user1_wed_shift = $arr_val["user$i_wed_shift"],
user1_thu_shift = $arr_val["user$i_thu_shift"],
user1_fri_shift = $arr_val["user$i_fri_shift"]
WHERE condition;

}

Hi Vimal,

This wont work as I already mentioned the array can contain values for multiple users, what you have provided is a static method, it is not dynamic. Also, the user name i.e. user1 here is for sample, the name can be anything.

Please post the HTML document that contains the form to submit this information, thanks.

Given what we have to work with, something like this would generate the appropriate query strings.  But that said, I feel like this application is misdesigned.  Hardcoded "userX" numbers are a code smell.  What would the application do if there were data for user1 and user3, but not user2?  Things like that argue for the use of multidimensional arrays or some kind of AJAX application so that the server would not have to update more than one userX at a time.

See http://iconoun.com/demo/temp_leovipin2.php

<?php // demp/temp_leovipin2.php
/**
 * See http://www.experts-exchange.com/questions/28688215/Create-mulitple-SQL-query-commands-from-a-single-array.html
 */
error_reporting(E_ALL);
echo '<pre>';

// SIMULATED REQUEST DATA
$_POST = Array
( 'user1_mon_shift' => 'S1(Morning)'
, 'user1_tue_shift' => 'S1(Morning)'
, 'user1_wed_shift' => 'S3(Night)'
, 'user1_thu_shift' => 'Earned Leave'
, 'user1_fri_shift' => 'S2(Afternoon)'
, 'user1_sat_shift' => ''
, 'user1_sun_shift' => ''
, 'user2_mon_shift' => 'S1(Morning)'
, 'user2_tue_shift' => 'S2(Afternoon)'
, 'user2_wed_shift' => 'S3(Night)'
, 'user2_thu_shift' => 'S3(Night)'
, 'user2_fri_shift' => 'S3(Night)'
, 'user2_sat_shift' => 'On Call'
, 'user2_sun_shift' => 'On Call'
, 'submit'          => 'consolidatedss'
)
;

/*******************************************
 *
 * VITALLY IMPORTANT RIGHT HERE
 * SANITIZE ALL OF THE REQUEST DATA
 * BEFORE USING IT IN ANY WAY
 * THIS IS ONLY A SIMULATION
 *
 *******************************************/
$post = $_POST;


// SET AN APPROXIMATE PRACTICAL LIMIT FOR THE MAXIMUM NUMBER OF 'user$i' ELEMENTS IN THE REQUEST
$users = (int)floor(count($post) / 7);


// DEFINE WHERE, ORDER, AND LIMIT CLAUSES FOR THE QUERIES
$where = NULL;
$order = NULL;
$limit = NULL;


// WITH EACH 'user$i' ELEMENT
for ($i = 1; $i <= $users; $i++)
{
    $key_mon = 'user' . $i . '_mon_shift';
    $key_tue = 'user' . $i . '_tue_shift';
    $key_wed = 'user' . $i . '_wed_shift';
    $key_thu = 'user' . $i . '_thu_shift';
    $key_fri = 'user' . $i . '_fri_shift';
    $key_sat = 'user' . $i . '_sat_shift';
    $key_sun = 'user' . $i . '_sun_shift';

    $sql = <<<EOD
UPDATE user$i SET
  $key_mon = '$post[$key_mon]'
, $key_tue = '$post[$key_tue]'
, $key_wed = '$post[$key_wed]'
, $key_thu = '$post[$key_thu]'
, $key_fri = '$post[$key_fri]'
, $key_sat = '$post[$key_sat]'
, $key_sun = '$post[$key_sun]'
  $where
  $order
  $limit
EOD;

    // SHOW THE QUERY
    echo PHP_EOL . $sql;

    /*******************************************
     *
     * RUN THE QUERY HERE
     *
     *******************************************/
} // END for() LOOP

Select allOpen in new window

Hi Ray,

Thanks for your input, as I already said that the user name user1 is for sample, the name could be anything like vipin or vipin.kumar etc. So the user name is dynamic. So can we design a for loop or anything which would create the sql query and then execute it.

Kindly let me know if you need anything else.

@Ray.. thanks for the input and advice. I will take your advice and rebuild my application. I will be posting a new question regarding my application and would require your inputs on the same. As my this question was abandon for long time so I will paste the link of my new question in the next comment.

@Ray.. I have posted a new question. https://www.experts-exchange.com/questions/28692004/Web-Application-Ideas-Required.html. Kindly help me with your ideas and inputs.

Thanks in advance.

Yes, I will be glad to look at it!  Thanks for the points and thanks for using E-E, ~Ray