[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert array

Posted on 2011-05-02
10
Medium Priority
?
400 Views
Last Modified: 2013-12-12
The snippet shows a multiple select form element. What I would like to do is insert the resulting array into as a row containing corresponding fields. IE the table would have 6 fields, an auto increment id, option 1, option 2 option 3 up to option 5. The default value of an option would be "0" unless the array value of for  "option 1" was selected and than it would be "1"

In simple terms the query would look like this:
Insert into "myTable" id, option 1, option 2 option 3 etc.. values "my selections from the form".

To illustrate, the resulting row from options 1, 4 and 5 being selected would look like the attached image.
 

Hope this is clear..
N
<select size="5" name="options[]" multiple="multiple"> 
<option>Option 1</option> 
<option>Option 2</option> 
<option>Option 3</option> 
<option>Option 4</option> 
<option>Option 5</option> 
</select>

Open in new window

screen-shot.jpg
0
Comment
Question by:Neil_Bradley
10 Comments
 
LVL 3

Expert Comment

by:Mrugesh1
ID: 35503527
Follow the below steps, you may get required result.

- Take an array of length 5 (number of items in drop down list)
- Assign all with 0 value
- Now check for selection by looping the drop down list. If value is selected then assign 1 to array item with loop index. i.e.  
 
for(int i=0; i<ddl.Items.Count;i++)
{
 if(ddl.SelectedIndex == i) 
 {  
   arr[i] = 1; 
 }
}

Open in new window

- use array values while inserting respectively.

0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 35503757
Create your SQL Non-Query statement by using the following code:
(Change TableName to your table name)
// Build SQL statement
$i = 0;
$sqlStatement = "INSERT INTO TableName VALUES (";
$boolStatementUpdated = false;
foreach($optionsArray as $option)
{
    if ($boolStatementUpdated)
        $sqlStatement .= ",";
    $sqlStatement .= "'$option'";
    $boolStatementUpdated = true;
}
$sqlStatement .= ")";
// Execute SQL statement
mysql_query($sqlStatement);

Open in new window

0
 
LVL 8

Expert Comment

by:kumaranmca
ID: 35503923
Hi,

First you gave the value of select listing..refer below code

<select size="5" name="options[]" multiple="multiple">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
<option value="4">Option 4</option>
<option value="5">Option 5</option>
</select>

after that you customize your needs.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Accepted Solution

by:
Scott Madeira earned 668 total points
ID: 35504429
Not sure what you are trying to do but this structure is a limiting structure. If you ever add an option 6 then you need to alter your database structure and your queries to get the data.  I'd suggest a table structure like the following instead where each selected option has its own row and the rows from one submission are tied together:

tableID, submissionID, opitionValue

tableID is an autoincrement field to uniquely identify each row in the table
submissionID is an integer field or possibly a varchar field if you group options by a username.  
optionValue - the option that was selected.

So, if a form was submitted with options 2, 4 and 5 selected you would have three rows in the table:

1, 1, 2
2, 1, 4
3, 1, 5

If another form was submitted with options 1and 3 selected you would have three rows in the table:

4, 2,  1
5, 2, 3

you would need to determine how you group responses from the same form.  I used an id field.  you could use a username or some other unique value.

You can do select statements to get all options:

select optionValue from options where submissionID = 2  or something similat
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35505061
Why not use checkboxes - so much easier on the client!
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1332 total points
ID: 35505135
I think that may show the design pattern that is responsive to the question, but I tend to agree with smadeira here, the overall structure may need to be reconsidered.  Example online here:
http://www.laprbass.com/RAY_temp_neil_bradley.php

HTH, ~Ray
<?php // RAY_temp_neil_bradley.php
error_reporting(E_ALL);

if (!empty($_POST["options"]))
{
    // GENERATE A QUERY
    $a = array( 0, 0, 0, 0, 0, 0 );
    foreach ($_POST["options"] as $o)
    {
        $a[$o] = 1;
    }
    unset($a[0]);
    $sql = "INSERT INTO myTable (option_1, option_2, option_3, option_4, option_5) VALUES (";
    $sql .= implode(',', $a);
    $sql .= ")";
    echo "<br/>THE QUERY IS $sql";
}

$htm = <<<HTM
<form method="post">
<select size="5" name="options[]" multiple="multiple">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
<option value="4">Option 4</option>
<option value="5">Option 5</option>
</select>
<input type="submit">
HTM;
echo $htm;

Open in new window

0
 
LVL 5

Author Comment

by:Neil_Bradley
ID: 35508215
Ok, I get your drift Ray and smadeira regarding the limiting nature of my table set up. To test your suggestion I have set up a new table which will contain a new row for each item selected (image attached shows how the table might look with two options from the updated multiple selection field selected).
At first glance the new insert query would run something like this..

foreach($option as $value)
{
$insert="INSERT INTO myTable $username, (option) VALUES ('$value')";}

I also see how this might be more aesthetically pleasing for the client as a check box set up (I can modify this once I have my code working). Just for background info the table is a small part of an online exams system. This particular table contain a list of authorized persons who can validate certain exams. myUsername for example will be authorized to validate the exams called "apples" or "goats".

Thanks for your input and suggestions..
N
New form field
<select size="5" name="options[]" multiple="multiple">
  <option value="apples">Option 1</option>
  <option value="goats">Option 2</option>
  <option value="pears">Option 3</option>
  <option value="chickens">Option 4</option>
  <option value="cows">Option 5</option>
</select>

Open in new window

screen-shot.jpg
0
 
LVL 5

Author Comment

by:Neil_Bradley
ID: 35508726
To be more specific this would then be the new query to accompany the multiple select form..
foreach($options as $key => $value){
  $result = $conn->query("insert into ap values
                         ('', '".$username."','".$value."')");
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1332 total points
ID: 35511992
Conspicuous by its absence in the foreach() statement is the assignment of $options.  It might be like this:

$options = $_POST["options"];

If the code works without that statement, then your PHP installation depends on register_globals, and that is a very bad thing that you should correct ASAP.
http://php.net/manual/en/security.globals.php

Also conspicuous by its absence is the use of MySQL_Real_Escape_String() or the equivalent.  Maybe this happens automatically in your class?  Not sure about that, but you want to check and be sure before you release this application into the wild.

You do not need to use an empty string as a placeholder for the AUTO_INCREMENT keys if you use named columns in the INSERT query.

Some developers like to capitalize the MySQL reserved words like INSERT INTO and VALUES, just as a habitual way of being sure that these words stand out.

A stronger design pattern would not use the POST information in the foreach iterator.  Instead it would use an internal table of options, testing each element of the POST data to see if it matched one of the internally known options.  If you do it this way, you can catch an attack before it causes you to pollute your data base.  If you do it the way written above, you will be inserting unfiltered information into your data base, and that is a no-no.

Also, you might want to test for success in the queries.  MySQL is not a black box; it can and will fail for reasons that are outside of your control.  But that aside, a many-to-many junction table between proctors and examinations is a design pattern that makes sense.  You might want to consider using some kind of column names that are less generic and more specific than things like username and option -- in the future, meaningful names will help you (or your successor) understand the intent of the code.

With best regards, over-and-out, ~Ray
0
 
LVL 5

Author Closing Comment

by:Neil_Bradley
ID: 35517052
Thanks for the advice Ray. Your comments have not fallen on deaf ears. My intention is to build upon the skeleton the solution show in my last posting into a more robust piece of script  fit for public use). In essence the core message I have taken away with me from this question has been to re structure my table set up.
I have split the points as it twas smadeira who first suggested the new table structure and Ray who not only answered my original question but also assisted with additional advice regarding the final solution. Thanks all for the interesting feedback
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month19 days, 13 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question