Mysql and 0's!

odedta · February 2, 2015, 3:05pm

Let’s say I have a table with field ID (not auto_increment and not primary), a regular int(9) field.

I have an input of type “text” and I want a user to input some 9 digit number and this number will be stored in the database. Everything works well until the user types in 000000000, then what gets stored in the database in “0” and not “000000000”.
Is it possible to store “000000000” inside an int(9) field or I have to change that to a TEXT/VARCHAR field?

In any case, how do I prevent PHP/MySQL from omitting the 0’s that come before the rest of the digits?

Thanks

JimL · February 2, 2015, 3:25pm

Integers don’t have leading zeroes so you should store it as a string field (varchar)

Remember to add an unique index so you dont kill performance

odedta · February 2, 2015, 3:29pm

Cool, could you elaborate on the index/performance part?

JimL · February 2, 2015, 3:49pm

Your app will probably do most of the queries based on the ID of the records you want to fetch. It makes sense to have the ID unique for two reasons.

1: it makes queries a lot faster
2: your app logic will be a lot simpler if you know “article” id 000123 can only belong to one article.

What/how to index a db is close to a profession on it’s own. But you would almost always want to index the columns you query data for. If you do not have an index then the db will have to check every single row of the table to find your matches.

articles id, title, image, author 000123, "some title", "img_2022.jpg", 1 000786, "other title", "landscape.jpg", 2 010206, "another title", "horizon.jpg", 2

If you consider a table like this with millions of rows, then we can clearly see the benefit of indexing “author” if you have a query like “select * from articles where author = :author”. Instead of having to search through millions of rows in the article to pick out the ones it need, it can go straight on the index and see only the rows belonging to that author.

you can read more online, stackoverflow etc have the topic covered pretty well

odedta · February 2, 2015, 3:57pm

Oh! I didn’t know that you can view only a single column(field) of a table, that is definitely a huge performance improvement!
My app is rather small though, I doubt it will have any effect, in any case it’s good to know for future apps.

Caught you on a nice post number: 1234…

Thanks a lot for your detailed information and help, much appreciated!