CODEWITHSUNDEEP
phpmysql
OliVQ
OliVQ

Reputation: 11

Insert multiple values from select box to separate rows

I have a 'Catch Reporting' form for our local fishing club and currently users can only submit one catch at a time so often have to go back to the form multiple times to report a day's catches.

I'm trying to adapt it so that the Species list becomes a multiple option select box instead.

Each catch needs to be its own row in the database.

This is the user-facing form which I have changed from a Select to Select Multiple:

<form action="addspecies.php" method="post" enctype="multipart/form-data">
    <input type="hidden" name="datecaught" id="todayDate"/>
    <script type="text/javascript">
    function getDate()
    {
        var today = new Date();
        var dd = today.getDate();
        var mm = today.getMonth()+1; //January is 0!
        var yyyy = today.getFullYear();
        if(dd<10){dd='0'+dd} if(mm<10){mm='0'+mm}
        today = yyyy+""+mm+""+dd;
        document.getElementById("todayDate").value = today;
    }
    //call getDate() when loading the page
    getDate();
    </script>
    <!--Date caught: <input type="date" name="datecaught"><p>-->
    <table width="600" cellpadding="5">
    <tr>
    <td>Boat Name:</td>
        <td>
            <select name="boatname">
                <option value = "">---Select---</option>
                <?php
                $queryusers = "SELECT BoatName FROM SpeciesHuntBoats WHERE CatchYear2=$year ORDER BY BoatName ASC";
                $db = mysqli_query($db, $queryusers);
                while ( $d=mysqli_fetch_assoc($db)) {
                    echo "<option value='".$d['BoatName']."'>".$d['BoatName']."</option>";
                }
                ?>
            </select>
        </td>
    </tr>
    <tr>
        <td>Species:</td>
        <td>
            <select name="species" multiple size="5">
                <option value = "">---Select---</option><br>
                <?php
                $queryspecies = "Select Species from SpeciesHuntSpecies ORDER BY Species ASC";
                $db2 = mysqli_query($db2, $queryspecies);
                while ( $s=mysqli_fetch_assoc($db2)) {
                    echo "<option value='".$s['Species']."'>".$s['Species']."</option>";
                }
                ?>
            </select>
        </td>
    </tr>
    <tr><td>Angler's Name:</td><td><input type="text" name="angler" size="40"></td></tr>
    <tr><td>Notes:</td><td><textarea rows="2" cols="40" name="notes"></textarea></td></tr>
    <tr>
        <td>Photo (optional):</td>
        <td><input type="file" name="file"></td>
    </tr>
    <tr><td></td><td><input type="submit" value="Submit"></td></tr></table>
</form>

Then the relevant part of addspecies.php is here:

$datecaught = $_POST['datecaught'];
$boatname = $_POST['boatname'] ;
$species = $_POST['species'];
$angler = $_POST['angler'];
$notes = $_POST['notes'];

$bm = "SELECT BoatMake FROM SpeciesHuntBoats WHERE BoatName='$boatname'";
$q = mysql_query($bm);
$n = mysql_fetch_assoc($q);
$boatmake = $n['BoatMake'];

$skip = "SELECT Skipper FROM SpeciesHuntBoats WHERE BoatName='$boatname' AND CatchYear2='$year'";
$qq = mysql_query($skip);
$nn = mysql_fetch_assoc($qq);
$skipper = $nn['Skipper'];

$http_referrer = getenv( "HTTP_REFERER" );
// Image add
$imagename=$_FILES["file"]["name"]; 
//Get the content of the image and then add slashes to it 
$imagetmp=addslashes (file_get_contents($_FILES['file']['tmp_name']));

$query_rsCatch = "INSERT INTO SpeciesHunt (DateCaught, CatchYear, BoatName, BoatMake, Species, Species, Angler, Skipper, Notes, PhotoName, Photo) VALUES('$datecaught','$year','$boatname','$boatmake','$species','$species2','$angler','$skipper','$notes','$imagename','$imagetmp')";
$rsCatch = mysql_query($query_rsCatch, $webdb) or die(mysql_error());

How would I make it so a user can select, say 4 species and submit only once, but creating 4 separate entries in the database?

Upvotes: 0

Views: 816

Answers (1)

Barmar
Barmar

Reputation: 781706

A multiple select should have an array style name:

<select name="species[]" multiple size="5">

Then in PHP, $_POST['species'] will be an array containing all the selections. You can loop through the array and insert each of them.

foreach ($species as $s) {
    $query_rsCatch = "INSERT INTO SpeciesHunt 
        (DateCaught, CatchYear, BoatName, BoatMake, Species, Angler, Skipper, Notes, PhotoName, Photo) 
        VALUES('$datecaught','$year','$boatname','$boatmake','$s','$angler','$skipper','$notes','$imagename','$imagetmp')";
    $rsCatch = mysql_query($query_rsCatch, $webdb) or die(mysql_error());
}

You should also stop using the mysql_* functions. They were deprecated many years ago, and finally removed completely in PHP 7. Convert to PDO or mysqli, and also learn to use prepared statements to prevent SQL injection.

Upvotes: 2

Related Questions