View Full Version : Inserting Checklist Form Data into mySQL Database

03-11-2013, 09:14 PM
I have an HTML page with a form that points to a PHP file to add data into a mySQL table. I've got everything figured out, except for how to store an array. I'm a bit new with mySQL, so I'm trying to get this figured out. I've seen quite a bit of good information on using the implode() function, but I'm unsure of how to implement it in my code.

FYI, I haven't done HTML validation and I know that I have some other things in the PHP form to worry about (preventing SQL injections, etc.) that I'm not worry about at this point, as I'm in a dev environment and will address those issues separately before implementation in production.

Everything is storing fine, except for the "calltype" array, which is what I'm struggling with. In mySQL, it returns as "array" in the SQL table.

Here's what I have:



<title>Call Tracking Log</title>


body {
font-family: Verdana, Arial, Sans Serif;
font-size: 14px;}

table {
border-collapse: collapse;
color: #336699;
font-size: 14px;
font-family: Verdana, Arial, Sans Serif;
width: 100%;}

td {
padding: 5px;
spacing: 5px;
border: 2px solid #336699;}




<form action="test.php" method="POST">

<tr style="background: #e2f1ff;">
<td width="15%" colspan="4" style="padding: 3px; text-align: center; font-size: 24px;">
<p><font style="font-weight: bold;">Call Tracking Log</font>
<tr style="background: #bedfff;">
<td style="vertical-align: center; font-weight: bold;">
<td colspan="3" width="28%">
<p><input type="text" name="OPID" length="40">
<input type="hidden" name="date_time" ID="dateTime" size=33>
document.getElementById("dateTime").value = new Date()

<tr style="background: #e2f1ff;">
<td style="vertical-align: center; font-weight: bold;">
<td colspan="3">
<p><input type="radio" name="product" value="retail"> Retail &nbsp; <input type="radio" name="product" value="visa">Visa &nbsp; <input type="radio" name="product" value="debit">Debit Card <input type="radio" name="product" value="giftcard">Gift Card&nbsp; <input type="radio" name="product" value="na">N/A

<tr style="background: #bedfff;">
<td style="vertical-align: top;">
<p><font style="font-weight: bold;">Caller</font>
<td colspan="3">
<p><input type="radio" name="caller" value="store">Store
<br><input type="radio" name="caller" value="customer">Customer
<br><input type="radio" name="caller" value="third">Third Party
<br><input type="radio" name="caller" value="catalog">Catalog/Direct
<tr style="background: #e2f1ff;">
<td width=15% style="vertical-align: top;">
<p><font style="font-weight: bold;">Call Type</font>
<td style="vertical-align: top;" width="28%">
<p><font style="font-weight: bold;">Account Maintenance</font>
<br><input type="checkbox" name="calltype[]" value="add_change">Address Change
<br><input type="checkbox" name="calltype[]" value="autopay">Auto Pay
<br><input type="checkbox" name="calltype[]" value="add_remv_user">Add/Remove User
<br><input type="checkbox" name="calltype[]" value="activations">Activations

<tr style="background: #bedfff;">
<td colspan="4" style="vertical-align: top; text-align: center; padding: 10px;">
<p><input type="checkbox" name="calltype[]" value="other_call">&nbsp;<font style="font-weight: bold;">Other Call (Please Explain):</font>
<br><textarea name="other_input" rows="2" cols="100" name="other_input"></textarea>
<tr style="background: #e2f1ff;">
<td colspan="4" style="vertical-align: top; text-align: center; padding: 10px;">
<p><input type="submit" value="Submit">&nbsp;<input type="button" value="Reset" onClick="window.location.reload()">



...and the code for test.php...

$con = mysql_connect("localhost","root","password");
if (!$con)
die('Could not connect: ' . mysql_error());

mysql_select_db("infocall", $con);

$sql="INSERT INTO incidentcall (date_time, product, caller, calltype, OPID, other_input)

if (!mysql_query($sql,$con))
die('Error: ' . mysql_error());
echo "1 record added.";


03-11-2013, 10:13 PM
The checkboxes in calltype are an array. Since you are allowing many options to one record, the best thing to do here is to pull a unique record id off of the incidentcall, and create a second table in which to contain the multiple types in a many to one relationship. Don't group them into the same field as it doesn't allow it to be easily searched.
Then you insert all the data into the incidentcall (pull a surrogate key if you used that, or whatever unique identifier for it), then insert multiple records into a secondary table that is a composite of the entry + the selected calltype.

03-11-2013, 11:30 PM
I appreciate your reply, and I agree that, for most instances, that would be the way to go. However, my managers want the array data in the calltype column; I'm simply trying to figure out how to get it to work.

03-11-2013, 11:46 PM
So to do that then, you'll need to use a delimiter.
Taking the value of the array, you simply implode it on a delimiter. Common delimiters are a comma or a pipebar, just something that you know won't conflict with the data that it could possibly have:

$sCallTypes = '';
if (isset($_POST['calltype']))
$sCallTypes = implode(',', $_POST['calltype']);

Now simply insert $sCallTypes.

You'll need to help protect your data as well, so you'll need to accommodate SQL Injections. With mysql, that would be making use of the mysql_real_escape_string which can wrap any input from the user, but with an array you'll need to use an array_map to apply a function to each item.

BTW, to go the other way around, once you've queried the data you can then explode on the delimiter.

Oh also I should mention that you can use serialize and unserialize as well which creates a string of the array representation completely. It does take more space than a delimited item does, but is a lot easier to work with IMO.

Old Pedant
03-12-2013, 12:26 AM
my managers want the array data in the calltype column

Your managers are idiots, then, who have apparently never heard of *CORRECT* database design and *NORMALIZED* databases.

I would try to find another job, not working for those bozos, as soon as possible.

Either that or go above their heads to their manager and tell him what kind of incompetents he has working for him. If you have such a thing as a DBA on staff, enlist his/her opinion. I guarantee you it will agree with ours.

Ask your doofus managers how they expect to be able to make reasonable queries against that data, if it's all bunched up in one column. Just for the very very simplest example, how will you get (say) a count of the number of AUTOPAY calltypes? Or how would you get a count of the number of records that are both AUTOPAY and ADDRESSCHANGE? Oh, it can be done. But the query is much much clumsier and much much slower and will *NOT* be able to use an index to speed things up. It will mean doing a full table scan using an ugly query, each and every time.

Sheesh. You wonder how some people get promoted beyond intern level, let alone to managers.

03-12-2013, 12:47 AM
Lols, +1. Although I wouldn't be quite so. . . blunt. Lol.

If you want to approach about it, I'd suggest indicating that searching through records of a certain call type (ie: find all records of type autopay) is a very difficult task to do, and that modifications of a type (ie: add_change becomes addchange) are near impossible to do without proper normalization of the database. Make specific note of the amount of time it takes to do this (both labour wise and processing wise), after all, time = money :D

Old Pedant
03-12-2013, 01:37 AM
Yeah, to expand further on that: The only practical way to change a set of CALLTYPE selections is to completely wipe out whatever is in the record and update it with an all new list.

FWIW, here's an example of what you would need to do to implement the query "Find a count of all records that are both AUTOPAY and ADDRESSCHANGE":

SELECT COUNT(*) FROM incidentcall
WHERE calltype LIKE '%autopay%'
AND calltype LIKE '%add_change%'

LIKE is one of the slowest matching operations you can do, especially in MySQL, where LIKE can't use an index if the LIKE condition starts with a wildcard. (That is, MySQL *sometimes* can use an index if you use field LIKE 'stuff%' but never if you use field LIKE '%stuff' or field LIKE '%stuff%'. How much do you want to be the so-called managers have no idea that is true?)

Old Pedant
03-12-2013, 01:40 AM
And yeah, I of course wouldn't be so blunt approaching the managers or their boss, but I was purposely blunt--even obnoxious--here to drive home the point about how bad a technical decision this is. A true "Dilber" moment. Do all your managers have pointy hair?

03-14-2013, 09:56 PM
Thanks for everyone's responses.