Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jul 2012
    Posts
    67
    Thanks
    14
    Thanked 6 Times in 6 Posts

    Inserting Checklist Form Data into mySQL Database

    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:

    Code:
    <html>
    
    <head>
    
    <title>Call Tracking Log</title>
    
    <style>
    
    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;}
    
    </style>
    
    </head>
    
    <body>
    
    <form action="test.php" method="POST">
    
    <table>
    	<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>
    	<tr style="background: #bedfff;">
    		<td  style="vertical-align: center; font-weight: bold;">
    			<p>OPID:
    		</td>
    		<td colspan="3" width="28%">
    			<p><input type="text" name="OPID" length="40">
    <input type="hidden" name="date_time" ID="dateTime" size=33>
    <script>
    document.getElementById("dateTime").value = new Date()
    </script>
    
    		</td>
    	</tr>
    	<tr style="background: #e2f1ff;">
    		<td  style="vertical-align: center; font-weight: bold;">
    			<p>Product:
    		</td>
    		<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
    		</td>
    
    	</tr>
    	<tr style="background: #bedfff;">
    		<td style="vertical-align: top;">
    			<p><font style="font-weight: bold;">Caller</font>	
    		</td>
    		<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
    		</td>
    	</tr>
    	<tr style="background: #e2f1ff;">
    		<td width=15% style="vertical-align: top;">
    			<p><font style="font-weight: bold;">Call Type</font>
    		</td>
    		<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
    		</td>
    
    	</tr>
    	<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>
    		</td>
            </tr>
    	<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()">
    		</td>
    	</tr>
    </table>
    </form>
    
    </body>
    
    </html>
    ...and the code for test.php...

    Code:
    <?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)
     VALUES
     ('$_POST[date_time]','$_POST[product]','$_POST[caller]','$_POST[calltype]','$_POST[OPID]','$_POST[other_input]')";
     
    if (!mysql_query($sql,$con))
       {
       die('Error: ' . mysql_error());
       }
     echo "1 record added.";
     
    mysql_close($con);
     ?>
    Last edited by superwookie; 03-11-2013 at 09:24 PM. Reason: I took out some code that didn't affect the issue at hand. It may make the HTML table look weird... please disregard.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    superwookie (03-11-2013)

  • #3
    New Coder
    Join Date
    Jul 2012
    Posts
    67
    Thanks
    14
    Thanked 6 Times in 6 Posts
    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.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Mkay.
    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:
    PHP Code:
    $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.

    Edit:
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by superwookie View Post
    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.
    Last edited by Old Pedant; 03-12-2013 at 12:32 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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":
    Code:
    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?)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Jul 2012
    Posts
    67
    Thanks
    14
    Thanked 6 Times in 6 Posts
    Thanks for everyone's responses.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •