PHP - Insert data from checkbox forms in MYSQL

Question

This is my first website that uses server technology. And this is also my first PHP dive working along the MYSQL side. I work and study when I go.

I created a basic management page for the product database for a small business dress.

I worked on the functionality of a form that the administrator can use to upload new records to the database.

Some of these entries include:

Dress Name [text entry]

Color Availability [checkboxes]

I'm having trouble getting data from multiple checkboxes from an HTML form to a database.

The choice in the flags should contain a logical value (yes / no - 1/0)

Here's the structure of the form:

  <form action="inventory_list.php" enctype="multipart/form-data" name="myForm" id="myform" method="post"> <table width="90%" border="0" cellspacing="0" cellpadding="6"> <tr> <td width="20%" align="right">Dress Name</td> <td width="80%"><label> <input name="Dress_name" type="text" id="Dress_name" size="64" /> </label> </td> </tr> <tr> <td align="right">Collections</td> <td> <label> <select name="collections" id="collections"> <option value="other">Other</option> <option value="hermione">Hermione</option> <option value="jora">Jora</option> <option value="manon">Manon</option> </select> </label></td> </tr> <tr> <td align="right">Available Sizes</td> <td> <input type="checkbox" name="sizes[]" value="1" /> Sizes 2-6<br /> <input type="checkbox" name="sizes[]" value="1" /> Sizes 6-10 <br /> <input type="checkbox" name="sizes[]" value="1" /> Sizes 10-14<br /> <input type="checkbox" name="sizes[]" value="1" /> Sizes 14-18 <br /> <input type="checkbox" name="sizes[]" value="1" /> Sizes 18-22<br /> <input type="checkbox" name="sizes[]" value="1" /> Sizes 22-26 <br /> </td> </tr> <tr> <td align="right">Dress Colours</td> <td> <input type="checkbox" name="colours[]" value="1" /> Pinks/Reds <br /> <input type="checkbox" name="colours[]" value="1" /> Blues/Greens <br /> <input type="checkbox" name="colours[]" value="1" /> Violet/Purple<br /> <input type="checkbox" name="colours[]" value="1" /> Yellow/Orange/Brown/Gold <br /> <input type="checkbox" name="colours[]" value="1" /> Black/White<br /> </td> </tr> <tr> <td align="right">Product Image</td> <td><label> <input type="file" name="fileField" id="fileField" /> </label></td> </tr> <tr> <td>&nbsp;</td> <td><label> <input type="submit" name="button" id="button" value="Add This Item Now" /> </label></td> </tr> </table> </form> 

And here is the PHP that I created to process the form for sending data to the database.

 <?php if (isset($_POST['Dress_name'])) { $dress_name = mysql_real_escape_string($_POST['Dress_name']); $collections = mysql_real_escape_string($_POST['collections']); $sizesArray = $_POST['sizes']; $coloursArray = $_POST['colours']; foreach ($sizesArray as $key => $value) { echo "Key = $key || Value = $value<br />"; } foreach ($coloursArray as $ckey => $cvalue) { echo "Key = $ckey || Value = $cvalue<br />"; } //See if that product name is an identical match to another product in the system $sql = mysql_query("SELECT ID FROM names WHERE Dress='$dress_name' LIMIT 1"); $productMatch = mysql_num_rows($sql); // count the output amount if ($productMatch > 0) { echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>'; exit(); } //Add this product into the database now $sql = mysql_query("INSERT INTO names (Dress) VALUES('$dress_name')") or die (mysql_error()); $pid = mysql_insert_id(); //Place image in the folder $newname = "$pid.jpg"; move_uploaded_file( $_FILES['fileField']['tmp_name'], "../inventory_images/$newname"); header("location: inventory_list.php"); exit(); } ?> 

As you can see, I pushed the data to an array. And now I hit a little brick wall, I can’t find an answer that makes sense anywhere.

How to get boolean values ​​to the correct columns in a database table?

Appreciate your time!

Answer 1

The first thing to know is that the HTML checkboxes will return the result if it is set, and will not return anything if it is not checked. Therefore, in your html it is important to increase your values:

 <td align="right">Available Sizes</td> <td> <input type="checkbox" name="sizes[]" value="1" /> Sizes 2-6<br /> <input type="checkbox" name="sizes[]" value="2" /> Sizes 6-10 <br /> <input type="checkbox" name="sizes[]" value="3" /> Sizes 10-14<br /> <input type="checkbox" name="sizes[]" value="4" /> Sizes 14-18 <br /> <input type="checkbox" name="sizes[]" value="5" /> Sizes 18-22<br /> <input type="checkbox" name="sizes[]" value="6" /> Sizes 22-26 <br /> </td> 

A fully normalized database schema will look something like this:

 //Note that 'names' and 'collections are overloaded //words and should be avoided table BRANDS id INT AUTOINCREMENT collectionname VARCHAR table SIZES id INT AUTOINCREMENT sizecategory VARCHAR //ie 'Sizes 18-22' table COLOURS id INT AUTOINCREMENT colourname VARCHAR table INVENTORY id INT AUTOINCREMENT brandid INT FOREIGN KEY (BRAND) imageurl VARCHAR table INVENTORY_SIZES inventoryid INT FOREIGN KEY sizeid FOREIGN KEY table INVENTORY_COLOURS inventoryid INT FOREIGN KEY colourid FOREIGN KEY 

Your BRAND , COLOURS and SIZES tables should be correctly populated with your available parameters, and you could theoretically dynamically load your page with data from these tables. The id of each row in these tables should end with the values ​​in your checkbox cells.

 //get your inputs $query_values = array(); $query_values[':brandid'] = $dress_name = $_POST['Dress_name'];//note that you should change the value in your options here to the unique ids in your database. $query_values[':sizeid'] = getSize($_POST[]); $query_values[':colourid'] = getColour($_POST[]); $query_values[':quantity'] = $_POST['quantity']; $query_values[':imageurl'] = $_POST['imageurl']; //Prepare and execute your sql //Note that PDO will handle escaping problematic inputs. $sql = "INSERT INTO INVENTORY ('brandid', 'imageurl') VALUES (:brandid, :sizeid, :colourid, :quantity, :imageurl)"; executeInsert($sql, $query_values); $inventoryid = mysql_insert_id(); saveSizes($_POST['sizes'], $inventoryid); saveColours($_POST['colours'], $inventoryid); function saveSizes($size_array, $inventoryid) { $sql = "INSERT INTO INVENTORY_SIZES ('inventoryid', 'sizeid') VALUES (:inventoryid, :sizeid)"; foreach ($size_array as $sizeid) { $query_values = array(); $query_values[':inventoryid'] = $inventoryid; $query_values[':sizeid'] = $sizeid; executeInsert($sql, $query_values); } } function saveColours($colour_array) { $sql = "INSERT INTO INVENTORY_COLOURS ('inventoryid', 'colourid') VALUES (:inventoryid, :colourid)"; foreach ($size_array as $sizeid) { $query_values = array(); $query_values[':inventoryid'] = $inventoryid; $query_values[':colourid'] = $colourid; executeInsert($sql, $query_values); } } function executeInsert($sql, $query_values) { $query = $conn->prepare($sql); $query->execute($query_values); } 

Be sure to use PHP PDO to protect against security issues. I am sure that some of them could be abstracted / cleaned better, but this should do the job. (Although I am also sure that there are small errors, as I do it coldly and without testing.)

Answer 2

You need to put the insert statement in the else statement after the if statement, which checks if the name exists in the database.

I'm not sure what exactly you are trying to do, since there is no instruction that you follow to put the "checkbox" values ​​in the database. You send the sizes array as output, which the administrator can see after submitting this form, but you do not put it in the database at all.

Which will help if you can separate the database structure (column names). If you do not have columns for sizes 2-5 and other categories, add them, and then change the insert statement to the following:

INSERT INTO names (Dress,Size2to5,Size5to10,Size10to15,Size15to20) VALUES('$dress_name','$size[0]','$size[1]','size[2]','size[3]')

Hope this helps. Let us know if this works for you.