The forums have permanently moved to This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.

Results 1 to 3 of 3

Thread: mysql query with 'zero' returns non-zero as well.

  1. #1

    Afrostyle mysql query with 'zero' returns non-zero as well.

    Hey all!

    I have two simple mysql queries (below) that pull zip codes from a row in my DB that is set to varchar. For whatever reason, searches starting with 'zero' (ie 02311) also return results with the first number AFTER zero (ie 23116) which then of course displays regions nowhere near close to what the user was looking for. If the table data were numerically formatted I could see how that might affect this, but since it's varchar it should simply return what I asked for right? Can someone lend some insight into this?

    // First - $postalcode is the variable from the actual form
    $sql = 'SELECT * FROM tbl_dentists 'WHERE '.$countrycode.$city.$postalcode ORDER BY RAND() LIMIT 30 ';
    // Second - This is run if the first query doesn't produce at least 10 records. It'll then pull postal codes +/- 15 from the actual code input in the form.
    $postalcode = ' AND den_postalcode < "'.($postal+15).'" AND den_postalcode >"'.($postal-15).'"';
    'SELECT * FROM tbl_dentists 'WHERE '.$countrycode.$postalcode ORDER BY RAND() LIMIT 10 ';
    Thanks in advance!

  2. #2
    This happens since you are using numerical comparison(> and < ). Use 'LIKE' for string comparison.
    WHERE code LIKE '$postal%'

  3. #3
    hugeExplosions = true;
    Quote Originally Posted by prasanthmj View Post
    This happens since you are using numerical comparison(> and < ). Use 'LIKE' for string comparison.
    WHERE code LIKE '$postal%'
    I don't think this is the case - the problem is that your leading zeroes are being chopped off when you are manipulating the string -

    Strings can still be compared using > and < and string comparison is exactly what you need here as casting the values to numeric and using numeric comparison will just create the same problem.

    The database engine knows that for strings 'E' is bigger than 'A' and that '1' is smaller than '2'

    When you do $postal + 15, check the value that's returned by echoing it to the screen - is this a string value initially? You'll probably find that PHP is casting the string value to a numeric implicitly for you and then adding 15 to it - of course numeric values don't include the leading zero (it's irrelevant)

    What you might want to do is use some sort of string format command to re-add the leading zeroes (all zips must be 5 numerics is that right?) to the string.

    I'm not sure which function to use but try something like

    I think sprintf can pad with zeroes as shown by this example:

    PHP Code:
    printf("[%05s]"$postal); // zero-padding on string 
    So your code should read

    PHP Code:
    $postalcode ' AND den_postalcode < "' printf("[%05s]", ($postal 15)) . '" AND den_postalcode >"' printf("[%05s]", ($postal 15)) . '"'
    Give that a try
    Last edited by Charleh; September 11th, 2009 at 04:43 AM.
    MS Paint FTW!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About Meet the Moderators Advertise

 Link to Us


Copyright 1999 - 2012