Ad:

PHP Discussion Thread
Unsolved
Views: 2283

Aug 23rd, 2007

MySQL datatype problem....

Expand Post »

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

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.

Similar Threads

MySql installing problem in MySQL
Date type problem JSP, mySQL in Java
php/mysql communication problem in PHP
PHP-MySQL Conn Problem in Win XP in PHP
Problem printing from IE6 in Web Browsers

nil_gh_80

Reputation Points: 8

Solved Threads: 2

Junior Poster in Training

Offline

64 posts
since Aug 2007

Permalink

Aug 23rd, 2007

Re: MySQL datatype problem....

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:

Quote ...

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.

Ezzaral

Moderator

Featured Poster

Reputation Points: 3239

Solved Threads: 838

Posting Genius

Offline

6,757 posts
since May 2007

Permalink

Aug 23rd, 2007

Re: MySQL datatype problem....

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

nil_gh_80

Reputation Points: 8

Solved Threads: 2

Junior Poster in Training

Offline

64 posts
since Aug 2007

Permalink

Aug 23rd, 2007

Re: MySQL datatype problem....

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.

Ezzaral

Moderator

Featured Poster

Reputation Points: 3239

Solved Threads: 838

Posting Genius

Offline

6,757 posts
since May 2007

Permalink

Aug 24th, 2007

Re: MySQL datatype problem....

Click to Expand / Collapse Quote originally posted by nil_gh_80 ...

Hi all,
I'm facing a big problem using MySQL...i've created a table using this code:
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:

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.

mikeSQL

Reputation Points: 16

Solved Threads: 3

Junior Poster

Offline

196 posts
since Dec 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Form syntax -driving me nuts

Next Thread in PHP Forum Timeline: Mail to a list of people

DaniWeb Message

Cancel Changes

User Name
Password