In this demo we will see how to create a table with unsigned zerofill to understand more about what int(11) means. We will also see how data is stored in the table and how to display it.
Command to create the table
CREATE TABLE zerofill_demo (
id INT(11) NOT NULL AUTO_INCREMENT,
a INT(11) NOT NULL,
b INT(11) UNSIGNED ZEROFILL NOT NULL,
c INT(5) DEFAULT NULL,
d INT(5) UNSIGNED ZEROFILL NOT NULL,
e INT(15) DEFAULT NULL,
PRIMARY KEY (`id`)
)
Commands to insert data into the table:
INSERT INTO zerofill_demo (a, b, c, d, e) VALUES (1, 1, 1, 1, 1);
INSERT INTO zerofill_demo (a, b, c, d, e) VALUES (1234567890, 1234567890, 1234567890, 1234567890, 1234567890);
Data as stored in the table
| id |
a |
b |
c |
d |
e |
| 1 |
1 |
00000000001 |
1 |
00001 |
1 |
| 2 |
1234567890 |
01234567890 |
1234567890 |
1234567890 |
1234567890 |
Fetch and display the data
Queries
$result = mysqli_query($link, 'SELECT e FROM zerofill_demo WHERE id = 1');
$metadata = $result->fetch_field_direct(0); // fetch metadata from first field (e)
$length = $metadata->length; // get the length from it
$row = $result->fetch_assoc();
Display the data:
| Command |
Output |
Description |
printf("%s", $row['e']) |
1 |
prints value of column e as a standard string |
printf("%0{$length}s", $row['e']) |
000000000000001 |
prints value of e in a column 15 chars wide with zero-padding |
printf("%{$length}s", $row['e']) |
1 |
prints value of e in a column 15 chars wide |
printf("%'#{$length}s", $row['e']) |
##############1 |
prints value of column e with custom padding character '#' |
