StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL INSERT command is converting inputs into different formats/values
primarykey
Id
11852565
data
AcceptedAnswerId
11852647
AnswerCount
2
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-08-07T19:15:12.007
FavoriteCount
0
LastActivityDate
2012-08-07T20:08:42.180
LastEditDate
LastEditorUserId
0
OwnerUserId
1562781
ParentId
0
PostTypeId
1
Score
1
ViewCount
571
LastEditorDisplayName
text
Body
<p>I have an HTML input form that is being sent as a PHP_SELF and then stored on a MySQL table. The input field is dynamically controlled with JS. There are 4 inputs: number (text), amount (int), Approval (text), and Date (text). There inputs are all arrays when they are sent to the PHP file via POST. They're stored in a MySQL table with column fields as: ID (primary key), num (text(4)), amnt (decimal(8,2)), app (text(10)), and date (varchar(10)). I'm getting three significant errors when I try to run the page.</p> <ol> <li><p>If a number such a 0125 is entered in the number field on the form, it is stored as 125 in the MySQL table. This is regardless of the fact that the field is being stored as a text string.</p></li> <li><p>The Approval field works perfectly fine when only integers are entered, however, when only text or a combination of text and numbers is inserted, the MySQL query produces the following error: Error: Unknown column 'aft859' in 'field list'. For example, when 853234 is entered, everything goes through fine, but when aft859 is entered, the error is produced.</p></li> <li><p>When a date is entered, it gets inputted into the MySQL table as a decimal value. For example, a date of 08/07/2012 is saved as '0.00056802'.</p></li> </ol> <p>I have checked every line to make sure that nothing is being converted in the PHP or HTML process, and I've echoed every line to make sure that the values are being handled properly. After much debugging, I'm lead to believe that the following two sections might be causing my problem:</p> <pre><code>//Check To See If User Has Already Created Table $sql = "CREATE TABLE IF NOT EXISTS $tablename_cc ( ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), cc_num TEXT(4), cc_amnt DECIMAL(8,2), cc_app TEXT(10), cc_date VARCHAR(10) );"; </code></pre> <p>or it might be this:</p> <pre><code>if (!mysql_query('INSERT INTO ' .$tablename_cc. '(cc_num, cc_amnt, cc_app, cc_date) VALUES '.implode(',',$pairs))) die('Error: ' . mysql_error()); else echo '<strong>', "Your information have been submitted and will be added to your account upon approval.", '</strong>', "</br>", "</br>", "</br>"; </code></pre> <p>I'm not too familiar with PHP, HTML, or MySQL (this is my first program ever), and I'm not sure if I've missed something. I tried to check all quotes and make sure they were right. I'm running all this in Wordpress, just in case that's culprit. Here is my code in its entirety for reference:</p> <pre><code><?php if(isset($_POST['submit'])) { //Get Current User Login global $current_user; $current_user = wp_get_current_user(); $ulog = $current_user->user_login; $tablename_cc = "cc_".$ulog; //Check To See If User Has Already Created Table $sql = "CREATE TABLE IF NOT EXISTS $tablename_cc ( ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), cc_num TEXT(4), cc_amnt DECIMAL(8,2), cc_app TEXT(10), cc_date VARCHAR(10) );"; mysql_query($sql); $cc_num = $_POST['cc_num']; $cc_amnt = $_POST['cc_amnt']; $cc_app = $_POST['cc_app']; $cc_date = $_POST['cc_date']; $items = array_map(null,$cc_num,$cc_amnt,$cc_app,$cc_date); $pairs = array(); foreach ($items as $sub) { if(implode(',', $sub) != ",,,") $pairs[] = '('.implode(',', $sub).')'; } echo implode(',',$pairs); if (!mysql_query('INSERT INTO ' .$tablename_cc. '(cc_num, cc_amnt, cc_app, cc_date) VALUES '.implode(',',$pairs))) die('Error: ' . mysql_error()); else echo '<strong>', "Your information has been submitted and will be added to your account upon approval.", '</strong>', "</br>", "</br>", "</br>"; } ?>  <html> <head> <title></title> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script> <script src="jquery.maskedinput.js" type="text/javascript"></script> <script type="text/javascript"> $(document).ready(function() { $('#btnAdd').click(function() { var num = $('.ccinput').length; // how many "duplicatable" input fields we currently have var newNum = new Number(num + 1); // the numeric ID of the new input field being added // create the new element via clone(), and manipulate it's ID using newNum value var newElem = $('#input' + num).clone().attr('id', 'input' + newNum); // insert the new element after the last "duplicatable" input field $('#input' + num).after(newElem); // enable the "remove" button $('#btnDel').attr('disabled',''); $("*#date").mask("99/99/9999"); // business rule: you can only add 20 names if (newNum == 20) $('#btnAdd').attr('disabled','disabled'); }); $('#btnDel').click(function() { var num = $('.ccinput').length; // how many "duplicatable" input fields we currently have $('#input' + num).remove(); // remove the last element // enable the "add" button $('#btnAdd').attr('disabled',''); // if only one element remains, disable the "remove" button if (num-1 == 1) $('#btnDel').attr('disabled','disabled'); }); $("*#date").mask("99/99/9999"); }); </script> </head> <body> Please fill in your information in the form below and press submit. If you need to add more, please click the "Add" button at the bottom of the form. You may enter a maximum of 20 at a time. Leave all unused fields blank. <form method="post" action="<?php echo htmlentities($PHP_SELF); ?>"> <fieldset> <legend>Information:</legend> <div id="input1" class="ccinput"> # (last 4 digits): <input id="cnum" type="text" name="cc_num[]" maxlength="4" size="4" /> CC Amount: <input id="camnt" type="int" name="cc_amnt[]" /> Approval Code: <input id="appr" type="text" name="cc_app[]" maxlength="10" size="10" /> Date: <input id="date" type="text" name="cc_date[]" /> </br> </div> <div id="input2" class="ccinput"> # (last 4 digits): <input id="cnum" type="text" name="cc_num[]" maxlength="4" size="4" /> CC Amount: <input id="camnt" type="int" name="cc_amnt[]" /> Approval Code: <input id="appr" type="text" name="cc_app[]" maxlength="10" size="10" /> Date: <input id="date" type="text" name="cc_date[]" /> </br> </div> <div id="input3" class="ccinput"> # (last 4 digits): <input id="cnum" type="text" name="cc_num[]" maxlength="4" size="4" /> CC Amount: <input id="camnt" type="int" name="cc_amnt[]" /> Approval Code: <input id="appr" type="text" name="cc_app[]" maxlength="10" size="10" /> Date: <input id="date" type="text" name="cc_date[]" /> </br> </div> <div id="input4" class="ccinput"> # (last 4 digits): <input id="cnum" type="text" name="cc_num[]" maxlength="4" size="4" /> CC Amount: <input id="camnt" type="int" name="cc_amnt[]" /> Approval Code: <input id="appr" type="text" name="cc_app[]" maxlength="10" size="10" /> Date: <input id="date" type="text" name="cc_date[]" /> </br> </div> <div id="input5" class="ccinput"> # (last 4 digits): <input id="cnum" type="text" name="cc_num[]" maxlength="4" size="4" /> CC Amount: <input id="camnt" type="int" name="cc_amnt[]" /> Approval Code: <input id="appr" type="text" name="cc_app[]" maxlength="10" size="10" /> Date: <input id="date" type="text" name="cc_date[]" /> </br> </div> <div> <input type="button" id="btnAdd" value="Add" /> <input type="button" id="btnDel" value="Remove" /> </div> </fieldset> <input type="submit" value="Submit" name="submit" /> </form> </body> </html>  </code></pre>
Tags
<php><javascript><mysql><html><arrays>
Title
MySQL INSERT command is converting inputs into different formats/values
singulars
PostAcceptedAnswerId
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USuser1562781
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
2. PO
  singulars
  PostTypePostTypeId
  PTAnswer
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  POMySQL INSERT command is converting inputs into different formats/values
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.