duandi8613 2014-05-22 05:48 采纳率: 100%
浏览 69
已采纳

PHP MySQL转换为SMALLINT

My apologies if this has already been answered, I used the search function and could not find a question which matched this exactly.

I am trying to insert a postcode field (Australian) into my database via mysql_query, I have confirmed that everything works, however a valid postcode could be, for example 0021 or 0214.

The problem is, that it is automatically discarding the leading zero's when inserting into the database which is obviously problematic, if memory serves me this could be because the integer is unsigned?

I need the value to be of the SMALLINT type, and retain the leading zeros, I understand that mySQL has a CONVERT function, however I never could get it to work.

This is my current insert query:

$insertquery = "INSERT IGNORE INTO user (username, password, firstname, surname, address, state, postcode)
                VALUES ('$username', '$password', '$fname', '$surname', '$address', '$state', '$postcode')";
$insertresult = mysql_query($insertquery);
  • 写回答

4条回答 默认 最新

  • doumeng3188 2014-05-22 05:52
    关注

    The removal of leading zeroes is not because of an integer being signed or small. It has to do with the fact that it is an integer all-together. If you want to retain leading zeroes, you will need to convert to using VARCHAR, or you will have to use lpad() when fetching the postcodes.

    In my opinion, just go with VARCHAR.

    edit : Please read Leonardo Phinheiro's answer for an alternative! As he says, you can set your integer to ZEROFILL. Though I would still use VARCHAR for something like postcodes. Here in The Netherlands we even have letters in our postcodes.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥20 校园网认证openwrt插件
  • ¥15 以AT89C51单片机芯片为核心来制作一个简易计算器,外部由4*4矩阵键盘和一个LCD1602字符型液晶显示屏构成,内部由一块AT89C51单片机构成,通过软件编程可实现简单加减乘除。
  • ¥15 某东JD算法逆向算法
  • ¥15 求GCMS辅导数据分析
  • ¥30 SD中的一段Unet下采样代码其中的resnet是谁跟谁进行残差连接
  • ¥15 Unet采样阶段的res_samples问题
  • ¥60 Python+pygame坦克大战游戏开发实验报告
  • ¥15 R语言regionNames()和demomap()无法选中中文地区的问题
  • ¥15 Open GL ES 的使用
  • ¥15 我如果只想表示节点的结构信息,使用GCN方法不进行训练可以吗
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部