mySQL Prob

[amrknt]

$result = mysql_query("SELECT name, email, userid, address, phone, FROM tbluser WHERE status!=9 AND slno='$regno' ORDER BY joining DESC") or die('ERROR#023'); 

Well I'm using above query to retrieve the data from row having a column 'slno' value equal to $regno.
Column 'slno' in my table is a int(5) type and UNASIGNED ZERO FILL

Now the problem is
EXAMPLE
if $regno = '00002sdfgsdfg' //showing data of the row having slno value 00002
if $regno = '00005hhjkjkj' //showing data having slno value 00005
if $regno = 'ewre00005' //dosen't show anything
if $regno = '000051' //dosen't show anything

Now question is why first to examples showing the data of that particular row instead of returning a blank value. And how I can correct it.

Thanks everyone to giving your time & effort.

[amrknt]

Is there any hope?????????

[Lats]

It looks like you're trying to compare strings with ints, try changing slno to a var.

[mullen]

I think the first two work because an integer is expected, so the variable is parsed and read up to the first non-numeric character. The preceding part is the integer that is checked in the DB, i.e. 2 followed by 5.

The third example doesn't return anything as it reads from the beginning of the string but hits a letter immediately, so no number is found.

The last one is converted to a numeric format, which gives the value 51 - I'm guessing your DB contains records 2 and 5 but nothing with 'slno' 51, yes?

If you don't want the first two to return records 2 and 5, you should add some sort of validation checks to sanitise the $regno var before retrieving data from the DB.

Cheers,
Dan

[amrknt]

I'm guessing your DB contains records 2 and 5 but nothing with 'slno' 51, yes?
Cheers,
Dan

Thanx Dan,
You partialy are right. As there is a record having column 'slno' value 00051 not 000051, as 'slno' column is INT and length is 5 with ZEROFILL so any value of 'slno' length can't exceed than 5 and if the length is less than 5 charchter(INT) it will be prefixed by 0 to get the desired length, i.e. 5.

Now even I get the solution by replacing slno='$regno' with slno=$regno in my query, but I'm afraid either it's a right decision or not. Someone suggest me to convert that INT column in VARCHAR, well in this case AUTOINCREMENT will work or not.

I really need a better and stable solution.

Thanx Friends
Amarkant

[samsm]

First of all, let me say that I am in total disbelief that MySQL accepts '00002sdfgsdfg' on a numeric column and converts it to the leading number. Mind boggling, I can't think of any possible rationale for that. Should be a query error all the way.

Then again, purists would point out that you should never put data intended for a numeric column in quotes, so perhaps the fact that MySQL does some crazy *** **** with a string beginning with integers isn't all that unsurprising. With that in mind, I would think that removing the quotes would be appropriate, as MySQL would then act relatively logically.

What's the rationale for "zero fill"? What does that gain? Why are you using it?

[mullen]

Do some validation on $regno to ensure it is no longer than 5 chars and contains numeric data only. Does $regno actually have to be zero-filled? As you are comparing integers, MySQL won't care whether or not $regno is padded with zeroes.

[r937]

...as 'slno' column is INT and length is 5 with ZEROFILL so any value of 'slno' length can't exceed than 5

no, this is a common misunderstanding

any INT field will store an integer, regardless of the number in parentheses

integers are numbers between -2147483648 and 2147483647
(or 0 and 4294967295 if UNSIGNED)

the number in parentheses merely indicates how many digits to display

here's the relevant excerpt from the mysql docs:

Another extension is supported by MySQL for optionally specifying the display width
of an integer value in parentheses following the base keyword for the type (for example, INT(4)).
This optional width specification is used to left-pad the display of values whose width is less
than the width specified for the column, but does not constrain the range of values that can
be stored in the column, nor the number of digits that will be displayed for values whose width
exceeds that specified for the column. When used in conjunction with the optional extension
attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column
declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger
values than the display width in an integer column, you may experience problems when
MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts
that the data did fit into the original column width.