SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: mySQL Prob

  1. #1
    SitePoint Zealot amrknt's Avatar
    Join Date
    Nov 2003
    Location
    india
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mySQL Prob

    PHP Code:
    $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.

  2. #2
    SitePoint Zealot amrknt's Avatar
    Join Date
    Nov 2003
    Location
    india
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any hope?????????

  3. #3
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like you're trying to compare strings with ints, try changing slno to a var.
    Lats...

  4. #4
    SitePoint Enthusiast mullen's Avatar
    Join Date
    Jul 2004
    Location
    Durham, UK
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Zealot amrknt's Avatar
    Join Date
    Nov 2003
    Location
    india
    Posts
    192
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mullen
    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

  6. #6
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  7. #7
    SitePoint Enthusiast mullen's Avatar
    Join Date
    Jul 2004
    Location
    Durham, UK
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by amrknt
    ...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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •