Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

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?

    Code:
    // 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.
    Example:
    WHERE code LIKE '$postal%'

  3. #3
    1,627
    posts
    hugeExplosions = true;
    Quote Originally Posted by prasanthmj View Post
    This happens since you are using numerical comparison(> and < ). Use 'LIKE' for string comparison.
    Example:
    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

    http://uk.php.net/sprintf

    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 kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012