I have a form with many multiple select lists,How can i submit the multiple select data to the database.
<?php
$db = mysqli_connect('localhost','root','','trial') or die($db);
if (isset($_POST['submit'])) {
$marks = mysqli_real_escape_string($db,$_POST['marks']);
$subjects= $_POST['subject'];
$farming= $_POST['farming'];
foreach ($subjects as $i) {
$subject = $i;
$sql = "INSERT INTO `trial_table` (`subjects`, `marks`) VALUES ('".mysqli_real_escape_string($db,$subject)."', '$marks')";
mysqli_query($db,$sql);
}
$sql1 = "INSERT INTO `trial_table` (`marks`) VALUES ('$marks')";
mysqli_query($db,$sql1);
}
?>
<!DOCTYPE html>
<html>
<meta charset="UTF-8">
<meta name="description" content="">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<head>
<title>Trial
</title>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<form method="POST" action="test.php">
<select id="multiselect" name="farming[]" multiple="multiple" required>
<option value="Irrigation">Irrigation
</option>
<option value="Fertilizer">Fertilizer
</option>
<option value="Pesticide">Pesticide
</option>
<option value="Herbicide">Herbicide
</option>
<option value="Row-planting">Row-planting
</option>
<option value="Broad-casting">Broadcasting
</option>
<option value="Pure-stand">Pure Stand
</option>
<option value="Inter-cropping">Inter cropping
</option>
</select>
<select id="multiselect" name="subject[]" multiple="multiple" required>
<option value="Irrigation">Technology
</option>
<option value="Fertilizer">Science
</option>
<option value="Pesticide">Arts
</option>
<option value="Herbicide">Agric
</option>
<option value="Row-planting">Math
</option>
<option value="Broad-casting">BIZ
</option>
</select>
<div class="input-group">
<label>Marks
</label>
<input type="number" name="marks">
</div>
<button type="submit" name="submit" class="btn">SUBMIT
</button>
</form>
</body>
</html>
Hi there
[off-topic]
When you post code in the forum, you need to format it. To do so you can either select all the code and click the </> button, or type 3 backticks ``` on a separate line both before and after the code block.
I have done it for you this time.
[/off-topic]
1 Like
Are you saying that this code doesnât work? If it does not, what is the problem with it?
As I read it, you run through each selected value for the farming input, and insert it into the trial_table. How does that differ from what you want to achieve, other than you arenât running through the second select values?
I think we probably need a more detailed description of the database table layout and how it relates to the form inputs to really be able to offer more advice. In the first query, youâre inserting two values into each table row, but in the second query, you only insert one.
One thing I would change:
foreach ($subjects as $i) {
$subject = $i;
$sql = "INSERT INTO `trial_table` (`subjects`, `marks`) VALUES ('".mysqli_real_escape_string($db,$subject)."', '$marks')";
mysqli_query($db,$sql);
}
Inside this loop, you assign the value of $i to a new variable called $subject, for no reason that I can see - you could just use $i as it comes. However, you then call a function on it within the query definition - in your place, I would call that function within the original line, so as to keep the query definition a bit âcleanerâ.
You should also look at using prepared statements rather than just concatenating string values into the query. For one thing there is additional security that comes with that approach, for another you could prepare the statement once (before the loop) and then just keep calling it with each value inside the loop, which might make a very small (and probably not noticeable) performance improvement. And, I believe thereâs no need to call mysqli_real_escape_string() if youâre using prepared statements as thatâs done for you.
I mean in the foreach loop i need how i can include $subject=$_POST and $farming=$_POST[farming].
or something that sounds like this `
foreach (($subjects as $i),($farming as $f)) {
$subject = $i;
$subject1=$f;
$sql = "INSERT INTO `trial_table` (`subjects`, `marks`) VALUES ('".mysqli_real_escape_string($db,$subject)."', '".mysqli_real_escape_string($db,$subject)."', '$marks')";
mysqli_query($db,$sql);
}
`
Thatâs what I mean about needing to know how the database table is laid out - I canât see in my head how you would relate the two values in separate drop-downs. How do you know which âpairâ you want to put in the same row? What happens if the user selects four in the first drop-down, and only two in the second? Do you want it to stop the loop at the smallest number of array elements, or continue until the largest number and have blanks in the smaller array?
1 Like
The table structure is id,subject,farming,marks
So if I select options 1, 2, 4, 5 and 8 from the first selection, and only select options 2 and 4 from the second selection, what do you want to see in the database?
I want to see what you have selected from both lists.
How would you do that in your table? Like this?
id subject farming marks
1 1 2 3
2 2 4 3
3 4 (null) 3
4 5 (null) 3
5 8 (null) 3
(assuming Iâd typed in â3â in the marks box, and obviously with your values that I donât want to type out in full here)
1 Like
Yes that is what i want. The truth is i have a survey form where user will have to select more options and there are in many questions
If thatâs really what you want to achieve, the way Iâd do it is this (for your two options):
count the elements in both arrays
interate through the one with the largest count
check if the other one has an element, otherwise assign a blank string to it
call the insert query
end of loop
But, that will get very complicated very quickly for multiple options, and I suspect that what you really want to do is change the layout of your database. The way Iâd do it would be:
main table:
id - auto-increment id
marks - marks value
subject table:
id - auto-increment id
main_id - reference to id in main table
value - drop-down value
type - type of entry - subject / farming / whatever other field type
The idea being that youâd have one main entry, and a varying number of entries in the secondary table. You can retrieve all of them, or just retrieve the specific values you need by âtypeâ. Have a read up on âdatabase normalisationâ for more information.
In the example above, youâd end up with
main table:
id marks
1 3
sub table:
id main_id value type
1 1 1 subject
2 1 2 subject
3 1 4 subject
4 1 5 subject
5 1 8 subject
6 1 2 farming
7 1 4 farming
Obviously youâd have a code for the subject, as well, rather than text. As you would for your drop-down values, rather than using text as you do in the HTML.
is there a way i would make for each loop for every select list some thing like this
foreach ($subjects as $i){
$subject = $i;
$subject1=$f;
$sql = "INSERT INTO `trial_table` (`subjects`,) VALUES ('".mysqli_real_escape_string($db,$subject)."',)";
mysqli_query($db,$sql);
}
foreach ($farming as $f) {
$subject1=$f;
$sql = "INSERT INTO `trial_table` (`farming`, `marks`) VALUES ('".mysqli_real_escape_string($db,$subject)."',)";
mysqli_query($db,$sql);
}
$sql1 = "INSERT INTO `trial_table` (`marks`) VALUES ('$marks')";
mysqli_query($db,$sql1);
}
In your first loop, where does $f come from? In your second query, where does $subject come from?
Typos aside, you could loop through each selection in the way you have, but your resulting data will be impossible to link together.
Try it. Write the code above without the typos, insert some data from your form half a dozen times, then have a look at how youâre going to retrieve the data. Iâm presuming that you want the data for each time the form is filled in to be linked some way in the database? If not, then your way will work, but the data will be in a way that it will be difficult to use.
I have removed typos and this is how it looks.It indicates that no error but i see no data in the table what could have caused that
<?php
$db = mysqli_connect('localhost','root','','trial') or die($db);
if (isset($_POST['submit'])) {
$marks = mysqli_real_escape_string($db,$_POST['marks']);
$subjects= $_POST['subject'];
$farmings= $_POST['farming'];
foreach ($subjects as $i){
$sql = "INSERT INTO `trial_table` (`subjects`) VALUES ('".mysqli_real_escape_string($db,$i)."')";
mysqli_query($db,$sql);
}
foreach ($farmings as $f) {
$sql1 = "INSERT INTO `trial_table` (`farming`) VALUES ('".mysqli_real_escape_string($db,$f)."')";
mysqli_query($db,$sql1);
}
$sql2 = "INSERT INTO `trial_table` (`marks`) VALUES ('$marks')";
mysqli_query($db,$sql2);
}
?>
Any number of things could cause it. Does your database table allow columns to be left blank? You have three columns (other than the id, which I presume is auto-incrementing) but each of your queries only specifies one value. If the other columns donât allow null values, then the insert wonât work until all fields are filled, unless you have defaults specified.
Well, you have no error-checking code in there, so unless you have a parse error or something fairly basic, it wonât.
You could try checking the return value from each time you run a query, see if the query has thrown an error. Or you could run the query with some sample data from phpmyadmin and see if it works OK in that scenario. You could var_dump the arrays that are coming into the code, and see if they have values you are expecting.
ETA - I just noticed that both the <select> tags in your HTML have the same id - thatâs not allowed, ids must be unique. It probably wonât be making any difference to the problem youâre having here, but if you later go on to submit the form with Ajax, for example, you might run into some issues.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.