DaniWeb Forum Index > Web Development > PHP

MySQL datatype problem....

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2007

Posts: 64

Reputation: nil_gh_80 is an unknown quantity at this point

Solved Threads: 0

nil_gh_80

Offline

Junior Poster in Training

MySQL datatype problem....

Aug 23rd, 2007

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE TABLE table1(name CHAR(20), age INT(3));
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.

Last edited by nil_gh_80; Aug 23rd, 2007 at 3:00 pm.

•

Join Date: May 2007

Posts: 4,438

Reputation: Ezzaral has much to be proud of

Solved Threads: 509

Ezzaral

Offline

Industrious Poster

Re: MySQL datatype problem....

Aug 23rd, 2007

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.

•

Join Date: Aug 2007

Posts: 64

Reputation: nil_gh_80 is an unknown quantity at this point

Solved Threads: 0

nil_gh_80

Offline

Junior Poster in Training

Re: MySQL datatype problem....

Aug 23rd, 2007

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

•

Join Date: May 2007

Posts: 4,438

Reputation: Ezzaral has much to be proud of

Solved Threads: 509

Ezzaral

Offline

Industrious Poster

Re: MySQL datatype problem....

Aug 23rd, 2007

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/...s.cfm/149/PHP/

Last edited by Ezzaral; Aug 23rd, 2007 at 5:42 pm.

•

Join Date: Dec 2004

Posts: 196

Reputation: mikeSQL is an unknown quantity at this point

Solved Threads: 3

mikeSQL

Offline

Junior Poster

Re: MySQL datatype problem....

Aug 24th, 2007

•

Originally Posted by nil_gh_80

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

sql Syntax (Toggle Plain Text)
CREATE TABLE table1(name CHAR(20), age INT(3));
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:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
    $sql  = 'CREATE TABLE `table1` ('
        . '   `name` VARCHAR(20) NOT NULL, '
        . '   `age` VARCHAR(3) NOT NULL'
        . ' )'
        . ' ENGINE = myisam;';
    $sql  = 'CREATE TABLE `table1` ('
        . '   `name` VARCHAR(20) NOT NULL, '
        . '   `age` VARCHAR(3) NOT NULL'
        . ' )'
        . ' ENGINE = myisam;';

I hope this helps you.

dynastyCODERS#1 when it comes to Programming Tutorials, Database designs and discussions, Operating Systems, you name it, check us out and drop us a line to tell us your opinions on any and everything in mind!;)