MySQL datatype problem....

[nil_gh_80]

Hi all,
I'm facing a big problem using MySQL...i've created a table using this code:

create table table1(name char(20), age int(3));

Now this table can insert ineteger values in name field........which i don't want and the age field can take a value like e.g. 123456789...although it's length is 3.....will you help me to solve this problem.....plz.

[Ezzaral]

Since numbers are valid characters, if you don't want them in the field then you will have to check for that prior to saving them to the table.

The int(3) spec does not limit the values to three or less digits. Here is the section describing what it does from the MySQL docs:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

Once again, you will need to validate the data before saving it.

[nil_gh_80]

How can I check those attributes......plz give me some code..

[Ezzaral]

See section 6.4 Validating User Input here:
http://hudzilla.org/phpwiki/index.php?title=HTML_forms

More complex validation can be performed with regex functions:
http://www.php.net/manual/en/ref.pcre.php

A tutorial showing form validation with preg_match can be found here:
http://www.htmlcenter.com/tutorials/tutorials.cfm/149/PHP/

[mikeSQL]

Hi all,
I'm facing a big problem using MySQL...i've created a table using this code:

create table table1(name char(20), age int(3));

Now this table can insert ineteger values in name field........which i don't want and the age field can take a value like e.g. 123456789...although it's length is 3.....will you help me to solve this problem.....plz.

Hi nil_gh_80, try this below. Im sure it will work for I have tested this myself before replying here:

CREATE  TABLE  `table1` ( `name` VARCHAR( 20  )  NOT  NULL ,
 `age` VARCHAR( 3  )  NOT  NULL 
) ENGINE  =  MYISAM ;

and for the PHP Code its:

$sql  = 'CREATE TABLE `table1` ('
        . '   `name` VARCHAR(20) NOT NULL, '
        . '   `age` VARCHAR(3) NOT NULL'
        . ' )'
        . ' ENGINE = myisam;';

I hope this helps you.