Create mulitple SQL query commands from a single array

Hi,

I have a $_POST array which consists of multiple values of a form. Below is the sample array. This array can consist values for multiple users. But the format would remain the same.

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 )

Open in new window


I want to write a php code which would achieve the below.
1. Create an sql query for the first user user1 example UPDATE user1 SET 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)', user2_sat_shift = 'On Call',  user2_sun_shift = 'On Call' WHERE condition. Here condition i can define.
2. Afterwards execute the query, then create a sql query for next user for example user2 in similar way and execute and so on for other users.

Kindly let me know if anything else is required.

Thanks in advance.
LVL 1
Vipin KumarSr. Network EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vimal DMSenior Software EngineerCommented:
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;

}
0
Vipin KumarSr. Network EngineerAuthor Commented:
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.
0
Ray PaseurCommented:
Please post the HTML document that contains the form to submit this information, thanks.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Ray PaseurCommented:
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

Open in new window

0
Vipin KumarSr. Network EngineerAuthor Commented:
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.
0
Ray PaseurCommented:
When I first read this question, I thought you were on the threshold of a dangerous design.  That is why I asked to see the HTML form and you didn't provide that information, but the recent explanation clarifies things a bit.

Now I know you're on the threshold of a dangerous design.

So I'm just going to say, "Don't do this - get a database administrator involved to help you design the application."  There are so many things that can go wrong when you have unknown external inputs that I can't even begin to list them all.  Nobody would build anything like this; you cannot sanitize the input.  

For your own good, please discard this design and start over with a conventional design grounded in principles of computer science.  

If you want some advice on how to design an application like this, please post a new question.  You can describe the application in plain, non-technical terms and we may be able to help with some ideas about the client interface and the database structure.  Once we know what the client request and database look like, the code will usually write itself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vipin KumarSr. Network EngineerAuthor Commented:
@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.
0
Vipin KumarSr. Network EngineerAuthor Commented:
@Ray.. I have posted a new question. http://www.experts-exchange.com/questions/28692004/Web-Application-Ideas-Required.html. Kindly help me with your ideas and inputs.

Thanks in advance.
0
Ray PaseurCommented:
Yes, I will be glad to look at it!  Thanks for the points and thanks for using E-E, ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.