Databases
StackOverflow2013
Postsdbo
POMySQL Not Storing Zero Before Integers (fields are set to text (or varchar))
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Not Storing Zero Before Integers (fields are set to text (or varchar))
    text
    copied!<p>I'm submitting a form via HTML into a POST PHP page, which is then storing that info into the MySQL database. One of the input fields is a number field that can start with zero. I've set the HTML data type to text on the form and tried setting the MySQL data type to text and varchar, and both times the zero before the integer gets dropped. I'm not quite sure what I'm doing wrong.</p> <p>Here is my PHP code for table creation:</p> <pre><code>$sql = "CREATE TABLE IF NOT EXISTS $tablename_db ( ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), num text(4), amnt DECIMAL(8,2) );"; </code></pre> <p>And here is what the form field looks like:</p> <pre><code>&lt;div id="input1" class="cinput"&gt; # (last 4 digits): &lt;input id="cnum" type="text" name="num[]" maxlength="4" size="4" /&gt; Amount: &lt;input id="camnt" type="int" name="amnt[]" /&gt; &lt;/br&gt; &lt;/div&gt; </code></pre> <p>Using this, a number like 0125 inputted to 'cnum' is saved as 125. What am I doing wrong?</p> <p>EDIT: Here is the code in its entirety, just so it's clear what I'm doing. It's not a very long code (possible typos as I tried to transfer things onto here).</p> <pre><code>&lt;?php if(isset($_POST['submit'])) { //Get Current User Login global $current_user; $current_user = wp_get_current_user(); $ulog = $current_user-&gt;user_login; $tablename = "db_".$ulog; //Check To See If User Has Already Created Table $sql = "CREATE TABLE IF NOT EXISTS $tablename ( ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), num text(4), amnt DECIMAL(8,2) );"; mysql_query($sql); $num = $_POST['num']; $amnt = $_POST['amnt']; $items = array_combine($num,$amnt); $pairs = array(); foreach($items as $key=&gt;$value) { if($key != 'submit') { if($value != '') { $pairs[] = '('.intval($key).','.intval($value).')'; } } } if (!mysql_query('INSERT INTO ' .$tablename. '(num, amnt) VALUES '.implode(',',$pairs))) die('Error: ' . mysql_error()); else echo '&lt;strong&gt;', "Your information has been submitted and will be added to your account upon approval.", '&lt;/strong&gt;', "/n"; } ?&gt; &lt;html&gt; &lt;head&gt; &lt;title&gt;&lt;/title&gt; &lt;script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"&gt;&lt;/script&gt; &lt;script type="text/javascript"&gt; $(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',''); // 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'); }); $('#btnDel').attr('disabled','disabled'); }); &lt;/script&gt; &lt;/head&gt; &lt;body&gt; 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. &lt;form method="post" action="&lt;?php echo htmlentities($PHP_SELF); ?&gt;"&gt; &lt;fieldset&gt; &lt;legend&gt;Information:&lt;/legend&gt; &lt;div id="input1" class="ccinput"&gt; # (last 4 digits): &lt;input id="cnum" type="text" name="num[]" maxlength="4" size="4" /&gt; Amount: &lt;input id="camnt" type="int" name="amnt[]" /&gt; &lt;/br&gt; &lt;/div&gt; &lt;div&gt; &lt;input type="button" id="btnAdd" value="Add" /&gt; &lt;input type="button" id="btnDel" value="Remove" /&gt; &lt;/div&gt; &lt;/fieldset&gt; &lt;input type="submit" value="Submit" name="submit" /&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>After much debugging, I believe that the error is somewhere in these two sections.</p> <p>Section 1:</p> <pre><code>$sql = "CREATE TABLE IF NOT EXISTS $tablename_db ( ID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), num text(4), amnt DECIMAL(8,2) );"; </code></pre> <p>Or it might be in this line:</p> <pre><code>if (!mysql_query('INSERT INTO ' .$tablename. '(num, amnt) VALUES '.implode(',',$pairs))) </code></pre> <p>The code seems to maintain the format perfectly all the way up to this line. Therefore I think that the zero is being dropped while being inserted into the MySQL database. I'm not sure why it's doing it though...</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload