Populating Multi-Select Boxes From Your DB Using an All-in-One PHP Form 5

*Author’s note: this post has been updated and rewritten as of Nov. 8 based on comments in the comment section.

So you have a database that you want to enter information into via a form and you want the form to have a multi-select box that both gets populated from your database and will have the associated data to an entry pre-populated when you edit an entry. It would look something like this in new entry mode:

and something like this in ‘Edit’ mode:

This tutorial is designed to teach you three things: [1] How to create a multi-select box that’s populated by your database. [2] How to have items in your multi-select box preselected when you go to edit an entry. [3] How to change your selections when you edit an entry.

This tutorial is assuming a basic level of knowledge of PHP, MySQL, and relational database structure. If any terms I use confuse you I recommend this tutorial at teamtutorials.com for any questions you may have on form building and using PHP to enter information into a MySQL database. Tizag.com has an excellent intro to PHP guide that explains in greater detail any term I use in this tutorial. Tizag.com also has an excellent guide on MySQL databases if you need it.

One last brief note: my form is an all-in-one. I use the same form to both update an entry and insert a new one into my database. Therefore all my code is housed in one file, which you can see in its entirety in my Github repository. It’s the file entitled, RealDBForm.php. This form is adapted from the code in killersite’s tutorial. My database is structured to contain records of cartoons and various elements of meta data that I want to keep track of. In my full database I have three types of meta data that I track. I will be focusing on only one (Characters) in this tutorial for simplicity’s sake. The code in my repository contains all of my code while what is located in this tutorial is a much smaller chunk. For clarification I will be referring to each new entry in my database as either an entry or a cartoon from here on out. All table names and field names will also be in italics.

Below is a picture of my database structure as I will be referring to various foreign keys, primary keys, tables and fields and this may assist you in understanding the relationship between the tables. Again, I stress that this is a smaller chunk of a much larger whole. The first line of each table in the image below is a descriptor while the second line is the corresponding field name in my database.

Crucial Elements Needed When You Display Your Database

While this is not exactly under the purview of this tutorial it contains necessary elements to ensure that later sections of the tutorial work properly so I will briefly explain what you must have in how you display your database if you want to use the same form to add and edit an entry and have your form pre-populated from your database.

The important thing to have is a properly structured “Edit” link. However or wherever you display how you want to edit an entry (and all its corresponding data from join tables) you must ensure that your “Edit” link looks like this:

echo "<a href="__(file name on your server)__.php?toon_no=&quot; . $row->toon_no . &quot;">Edit Toon</a>
";

This is very important because this code is altering the URL of your form so when you click the link to edit an entry it adds the the primary key of that particular entry to the end of the URL, after the ‘?’.

I choose to have the term ‘toon_no’ in my URL which is the same term I use in my database to refer to the primary key field of my cartoon table to avoid confusion, but you can call it anything you like. Don’t forget that before you can insert the primary key from your entry in the

$row->toon_no

you need to first get it from your database with a SQL query and result statement. You need to do this because you need the primary key number to place in the URL.

*              *                *

Now that that’s taken care of let’s move on to the real tutorial. As I mentioned, I use an all-in-one form. Below is all the code I will use in this tutorial. I will break down key sections of it later. This is so you can see where everything goes within the file and has some bits of code I will not go over but refer to later.

<?php
// Allows the user to both create new records and edit existing records

// connect to the database
include("connect-real-db.php");

function renderForm($id = '', $actor_id = '') {

    echo "<form action='' method='post'>";

    $characters = mysql_query("SELECT * FROM characters ORDER BY actor asc");
        echo "<select name='actors[]' multiple='yes' size='20'>";
            echo "<option value=''> --- </option>";
                while ($row = mysql_fetch_array($characters)) {
                    echo "<option value ='$row['actor_no']'" ;
                        if ($id != '') {
                            if ($actor_id != '') {
                                foreach ($actor_id as $element) {
                                    if ($element == $row[$'actor_no']) {
                                        echo " selected ";
                                    }
                                }
                            }
                        }
                    echo ">";
                        echo $row['actor'];
                    echo "</option>";
                }
        echo "</select>";
    echo "</form>";
} // ends function renderForm

// EDIT RECORD
// if the 'id' variable is set in the URL, we know that we need to edit a record
if (isset($_GET['toon_no'])) {
    // if the form's submit button is clicked, we need to process the form
    if (isset($_POST['submit'])) {
        // make sure the 'id' in the URL is valid
        if (is_numeric($_POST['toon_no'])) {
            // get variables from the URL/form
            $id = $_POST['toon_no'];
            $cartoon_actors=$_POST['actors'];

            $delete_meta="DELETE FROM cartoon_characters WHERE fk_toon_no=".$id;
                mysql_query($delete_meta) or die('Error deleting joiner table');

            foreach($cartoon_actors as $cartoon_actor) {
                if ($cartoon_actor>= 1) {
                    join_table('cartoon_characters', 'fk_actor_no', $cartoon_actor, $id); } }
            }

            // redirect the user once the form is updated
            header("Location: _(file name of full database table)_.php");

        } else {
            // if the 'id' variable is not valid, show an error message
            echo "Error!";
        }
    }

    // if the form hasn't been submitted yet, get the info from the database and show the form
    else {
        // make sure the 'id' value is valid
        if (is_numeric($_GET['toon_no']) && $_GET['toon_no'] > 0) {
            // get 'id' from URL
            $id = $_GET['toon_no'];
                // get the record from the database
                $actor_query = "SELECT * FROM cartoon_characters WHERE fk_toon_no=".$id;
                    $actor_array = mysql_query($actor_query);
                        while ($row = mysql_fetch_assoc($actor_array)) {
                            $actor_id[] = $row['fk_actor_no']; }
                // show the form
                renderForm($id, $actor_id);
        // if the 'id' value is not valid, redirect the user back to the viewcartoons.php page
        } else {
            header("Location: _(file name of full database table)_.php");
        }
    }
}

// NEW RECORD
// if the 'id' variable is not set in the URL, we must be creating a new record
else {
    // if the form's submit button is clicked, we need to process the form
    if (isset($_POST['submit'])){
        // get the form data and insert that into the database as a new record.
        // This is different for everyone and not the aim of this tutorial.
        // You can see all my code on my GitHub repository linked to earlier if you want to see everything.
    // if the form hasn't been submitted yet, show the form
    } else {
        renderForm();
    }
}
    // close the mysqli connection
    $mysqli->close();
?>

How to Create a Multi-Select Box

First, I create my form and make it a function — Line 7 and 9 of the code above. Then I create my multi-select box. Here’s that specific code — line 11-29 of the code above. I’ll break it down afterward.

$characters = mysql_query("SELECT * FROM characters ORDER BY actor asc");
    echo "<select name='actors[]' multiple='yes' size='20'>";
        echo "<option value=''> --- </option>";
            while ($row = mysql_fetch_array($characters)) {
                echo "<option value ='$row['actor_no']'" ;
                    if ($id != '') {
                        if ($actor_id != '') {
                            foreach ($actor_id as $element) {
                                if ($element == $row[$'actor_no']) {
                                    echo " selected ";
                                }
                            }
                        }
                    }
                echo ">";
                    echo $row['actor'];
                echo "</option>";
            }
    echo "</select>";

So, what is this code doing exactly? I’ll break it down line by line.

  • Line 1: This line is fairly straight forward. I want to get all my characters from the characters table and put them into a multi-select box. This table has only two columns. The primary key for the table which is actor_no and the character name field which is actor. I am also making my characters appear in alphabetical order with the “ORDER BY actor asc” snippet.
  • Line 3: This is the beginning of the multi-select box and the [ ] after the ‘name’ turns the characters the user selects into an array for later. This is imperative when we want to update the cartoon_characters table. You also need to indicate that multiple items can be selected. This is done simply by writing “multiple=’yes’.”
  • Line 4: This creates the first line of the multi-select as only having – – – – in case you don’t have any existing characters for this particular cartoon.
  • Line 5: Here we start a WHILE loop that will display all the characters in the characters table until there are no more to display.

The next chunk of lines is where we are telling the multi-select box what to display on each line and that a particular line should be selected if we are in “Edit” mode on our form.

  • Line 6: Here we open the ‘option’ tag that won’t be finished until line 16 and closed until line 18. This line is also saying that the value of each line is the primary key field actor_no from the characters table. I pulled this information into an array in line 1 and line 5 respectively.
  • Line 7: This line is checking to see if we are in “Edit” mode in the form. Specifically it is checking if $id is NOT empty. If it is not empty then we are editing the form and need to pre-populate the multi-select box with the selections attached to this particular cartoon from the join (cartoon_characters) table. (At some point in your form you will declare the $id variable to grab the “?toon_no=__” from the URL. In the full form code above this is located in line 41.)
  • Line 8: Now we are checking to see if there are any existing characters in the cartoon_characters join table to display. As of this line of code those selections are in an array I’ve named $actor_id. I will go over how to establish this in a little while. For now, let’s assume that there are existing characters associated with this cartoon.
  • Line 9: Here we begin a FOREACH loop where we examine each element of our array $actor_id and do something with it.
  • Line 10: Here we are checking if the $element (a foriegn key in my join table and the number that got passed into my multi-select box via the array $actor_id) matches the primary key actor_no that is assigned to each option in line 6.
  • Line 11: If the above statement is true, this means that this character has already been associated with the cartoon and I want it to be ” selected “. (Note – the spaces before and after the quotation marks is important due to this being an HTML statement but the code is written in PHP.)
  • Line 12: Ends the IF statement begun in line 10.
  • Line 13: Ends the FOREACH loop begun in line 9.
  • Line 14: Ends the IF statement begun in line 8.
  • Line 15: Ends the IF statement begun in line 7.
  • Line 16: Finishs the ‘option’ tag opened in line 6.
  • Line 17: Here is where we actually declare what we want to display on each line of the multi-select box. This is the equivalent of the – – – – in line 4 but we are grabbing it from our database from our array we established in line 1 and 5.
  • Line 18: Closes the ‘option’ tag opened in line 6.
  • Line 19: Closes the WHILE loop begun in line 5.
  • Line 20: Closes the ‘select’ tag opened in line 3 and ends our multi-select box.

How to Pre-Populate Your Multi-Select Box When You Edit an Entry

Now, lets go back to the array associated with the variable $actor_id that tells the form what characters to select. This code is located in the full form above in lines 68-73 but I will reproduce it below as well. When I create my form function it looks like this:

function renderForm($id, $artist_id){

This means that when I call my function “renderForm” anywhere in my code I HAVE to have a string or variable that will take the place of wherever $id and $artist_id is referred to in my function. I use the same variable names throughout my form to avoid confusion but sometimes this is not possible or desirable. As you may have guessed the $id is for the primary key in my URL and is grabbed and assigned in line 66 of the full form code above and the $artist_id is the array of any existing characters associated with the particular cartoon I am editing.

$actor_query = "SELECT * FROM cartoon_characters WHERE fk_toon_no=".$id;
    $actor_array = mysql_query($actor_query);
        while ($row = mysql_fetch_assoc($actor_array)) {
   	    $actor_id[] = $row['fk_actor_no'];
	}
// show the form
renderForm($id, $actor_id);
  • Line 1: This line grabs all the characters in the join table cartoon_characters which match the cartoon ID from my URL.
  • Line 3: Now we pass the results from the above query into an array.
  • Line 4: This line begins a WHILE loop which will run through all the matching entries in the join table.
  • Line 5: This line takes only the foreign key fk_actor_no from the $actor_array and passes it into a new array called $actor_id.
  • Line 6: End the WHILE loop begun in line 4.
  • Line 8: Calls the function renderForm which will display the form and passes the newly created $actor_id array to it so the multi-select box can match the existing characters associated with the cartoon against all the characters to preselect the ones that match.

How to Edit Your Multi-Select Box Entries in Your Database

Finally, what if you want to change the selections when you are in ‘Edit’ form mode? This is actually quite simple. You simply delete all the existing entries in the join table associated with the particular entry and then repopulate it with the new selections.

The code to delete all the entries that match the entries in the database which correspond to the ID in the URL is located in the full form code on lines 44-45 and isolated below.

$delete_meta="DELETE FROM cartoon_characters WHERE fk_toon_no=".$id;
mysql_query($delete_meta) or die('Error deleting joiner table');

Then we grab the array of selections in the multi-select box — this code is located in the full form code on line 42 — and plug them into the join table in the database using a FOREACH loop.

foreach($cartoon_actors as $cartoon_actor) {
    if ($cartoon_actor>= 1) {
        $existing_char = "INSERT INTO cartoon_characters (fk_toon_no, fk_actor_no) VALUES (".$id.", ".$cartoon_actor.")";
            mysql_query($existing_char) or die('Error updating joiner table');
     }
}

And that’s how you create a multi-select box that’s populated from your database and use the same form to both insert new information into the database and edit it. It should be mentioned that when you insert a new character into the database using whatever code to do so, when you upload the form the next time that new character will become an existing character and be added to the multi-select box to be selected again in the future.

I hope this was helpful. Please feel free to ask any questions if anything was confusing in the comments below.

5 Comments

Laura O'Hara says:

I have yet to replicate this code for my own purposes and I FULLY admit that this is because I am not as advanced in PHP as you are. However, I think my points of confusion might be helpful for you to add to and organize the tutorial in a way that someone less advanced than you can better understand it.

1 – I need to see the front end of the finished product. You provide a link (“You can see how it looks here”), but that link is dead.

2 – Some sub-headers would help separate your different steps (i.e. – Rendering Your Form/ Updating the Joiner Table) and allow your reader to better work in smaller steps.

3- I looked at your full code on GitHub and immediately understood that this tutorial is covering only part of something larger; however, you might want to be clearer about that in your introduction.

4 – I got lost in all the variables, but ESPECIALLY with those representing fields and tables in your database. I think it might behoove you to illustrate or describe your database structure (at least the portion you are using here) in the beginning. Namely, this paragraph should go up top and get some flushing out or emphasis (I didn’t find it until late in the game. Things clicked a little more after that):

“The code I will be using as an example is creating my Characters dropdown menu and is pulling items from my characters table and my joiner table cartoon_characters which links a cartoon entry with the characters in my database.”

Since I am not familiar with your database structure, I need to know your table names, what fields they contain, and how they are joined. I was having trouble discerning what were primary keys and what were foreign keys in your code (and thus what is happening), even after reading the line-by-line descriptions.

5 – If you can, I would link to a good tutorial that you’ve found on functions. There is an assumption here that people know PHP functions fairly well. (I, for one, don’t!) If anything, I would give your reader a heads up at the top so they can find their own tutorial before diving in.

6 – When you describe function renderForm(), I would include the function “container” in your code [function renderForm($id = ‘’, $actor_id = ‘’) { . . . . } ]. I would explain, up-front what this function does and what its parameters do. The value of $id gets lost in your explanation of Line 7 for this section of code.

7 – The delete/ repopulate instructions for your joiner table are the clearest. And while my own code does not replicate this precisely, I understand the concepts enough to make this work.

Overall, your talent blows me away! Thank you for sharing (and hopefully some of it rubs off on the rest of us).


Erin says:

Sasha, this is so thorough, I only have one nit-picky comment. I do think a screenshot of the multi-select boxes (esp if you do a screenshot of an example of the Character boxes) would be very helpful toward the beginning, just to address the folks who know what they want but may not use your specific terminology. Otherwise, this is most impressive. Well done!


Michael says:

Can any one explain please whow the join-table () on line 56 works. I am stack on this, please help


Sasha Hoffman says:

Michael –
I’m terribly sorry this was an oversite on my part. the join_table is a function and I forgot to replace it with the real code. This is how it would look without the function:

foreach($cartoon_actors as $cartoon_actor) {
if ($cartoon_actor >= 1) {
$existing_entry = “INSERT INTO cartoon_characters (fk_toon_no, fk_actor_no) VALUES
(‘”.$id.”‘, ‘”.$cartoon_actor.”‘)”;
mysql_query($existing_entry) or die(‘Error updating joiner table’);
}
}

Hope that answers your question


Michael says:

Absolutely, thank you so much “Sasha Hoffman”. It did solve my problem.

Thanks for the good work.


Leave a Reply to Laura O'Hara Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>