Search code examples
phpmysqldatabaseintegerzero

Keeping lead Zero in MySQL


I have a table with a varchar column. I use a select form with values 01, 02, 03, 04 .... 10, 11, 12 etc to store data in this column. My problem is that, when I submit the form, the leading 'Zero' is removed. How can I maintain the leading zero?

**

NB. I have checked other similar posts but they don't have the solution for my issue. I have tried var/char/text and still it's not working hence why I posted this question if there's another way to get this done.

**

<form method=post enctype="multipart/form-data">
<select name="dd">
    <option selected="selected">Day</option>
    <option value='01'>01</option>
    <option value='02'>02</option>
    <option value='03'>03</option>
    <option value='04'>04</option>
    <option value='05'>05</option>
    <option value='06'>06</option>
    <option value='07'>07</option>
    <option value='08'>08</option>
    <option value='09'>09</option>
    <option value='10'>10</option>
    <option value='11'>11</option>
  </select>

<select name="mm">
    <option selected="selected">Month</option>
    <option value='01'>01</option>
    <option value='02'>02</option>
    <option value='03'>03</option>
    <option value='04'>04</option>
    <option value='05'>05</option>
    <option value='06'>06</option>
    <option value='07'>07</option>
    <option value='08'>08</option>
    <option value='09'>09</option>
    <option value='10'>10</option>
    <option value='11'>11</option>
  </select>
</form>

MySQL is as follows;

    $caption = $_POST['dd'].-$_POST['mm'];
    
    $sql = "INSERT INTO pages SET caption = '$caption'";

Solution

  • Use sprintf to format your value:

    $caption = sprintf("%02d-%02d", $_POST['dd'], $_POST['mm']);
    

    The key here is to work with the data as strings and don't do math on it like -$_POST['mm'] which will treat it as a number. In numbers leading zeros are always omitted because they always exist. 10 is actually something like 0x0000000a as far as the CPU is concerned.