SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Extracting part of a variable to use RLIKE

    This may be the wrong wauy for me to approach this, but what I have is this:

    PHP Code:
    $query "select * from locations where zipcode = '$zipcode' order by '$order_by'"
    What I want to do is run a second query if this one returns no results, where the first 3 numerals of $zipcode are used to pull out locations with matching results.

    I'm having problems creating this query, as I do not know how to use only the first 3 numerals of the 5 digit $zipcode variable.

    I understand using RLIKE and REGEX to a degree, which I could do if I knew how to turn $zipcode into something I could use.

    Hope this question makes sense, and thanks for your time.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm going to assume the zipcode column in the database is varchar(9)
    Code:
    select * 
      from locations 
     where zipcode = '$zipcode' 
    order 
        by $order_by
    note the zipcode value is in quotes to make it a string

    but the order_by isn't

    to do a partial seearch,
    Code:
    select * 
      from locations 
     where zipcode like '$zip3%' 
    order 
        by $order_by
    note the LIKE keyword and the wildcard % behind the first 3 digits

    $zip3 could actually be any number of digits
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I tried your method, and the best I could do was for the query to pull out every entry in the database.

    I think I'll go back to the drawing board on this one. Thanks for the help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there is only one solution for searching within a single column -- LIKE

    i'm sure we can help you if if you show a few sample rows, plus the query you were running (after the variables have been substituted, not the php version of the syntax, the actual string that got passed)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,
    The purpose of this script is to create a Store Location finder: Someone enters their zipcode. If found, a list of stores in that zipcode is printed. If not found, a query is run against the first 3 digits of the zipcode, and then those stores are printed. If the first three didgits don't find a match, then the script prints "sorry" and quits.

    I have the first and last condition working fine, it's the middle one we are discussing.

    The person uses a form on page 1 to enter their zip, which is passed to page 2, where this happens:

    PHP Code:
    <?php
        $state 
    addslashes($state);

        
    // CONNECT TO MYSQL
        
    $db mysql_connect("localhost""blah""blah");

        
    // CHECK FOR CONNECTION
        
    if (!db)
        {
        
    ?>
        <h2>Could not connect to the database. Please try again later.</h2>
        <?
          
    exit;
        }
        
        
    $query "select * from locations where zip = '$zip' order by '$order_by'";
        
    $result =  mysql_db_query("storedb",$query);
        
    $num_results mysql_num_rows($result);
        
    $query2 "select * from ingles_loc where zip LIKE '$zip3%' order by zip";
        
    $result2 =  mysql_db_query("storedb",$query2);
        
    $num_results2 mysql_num_rows($result2);
        
        if (
    $num_results >= 1)
                {    
                    echo 
    "
                <table border=\"0\" cellpadding=\"4\" cellspacing=\"2\">"
    ;
                
    // DISPLAY THE RESULTS OF THE QUERY
                
    $j 1;
                for (
    $i=0$i<$num_results$i++)
                    {
                    
    $row mysql_fetch_array($result);
                
    // ALTERNATING BACKGROUND COLORS FOR ROWS
                    
    echo "<tr valign=\"middle\" align=\"left\" bgcolor=\"";
                    
    $bgcolor1 "#FFFFFF";
                    
    $bgcolor2 "#DDDDDD";
                      if ( (
    $j 2) == ) {
                         echo 
    $bgcolor1;
                         } else {
                         echo 
    $bgcolor2;
                         }
                    
    $j++;
                echo 
    "\"><td>";
                echo 
    htmlspecialcharsstripslashes($row["store_number"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["address"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["city"]));
                echo 
    "</td><td>";
                echo 
    htmlspecialcharsstripslashes($row["state"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["zip"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["phone"]));
                echo 
    "</td></tr>";
                }
                }
                else {
                    if (
    $num_results == 0)
                echo 
    "
                <p>No stores were found in your zip code.  These stores are in zip codes near yours:</p>
                <table border=\"0\" cellpadding=\"4\" cellspacing=\"2\">"
    ;
                
    // DISPLAY THE RESULTS OF THE QUERY
                
    $j 1;
                for (
    $i=0$i<$num_results2$i++)
                    {
                    
    $row mysql_fetch_array($result2);
                
    // ALTERNATING BACKGROUND COLORS FOR ROWS
                    
    echo "<tr valign=\"middle\" align=\"left\" bgcolor=\"";
                    
    $bgcolor1 "#FFFFFF";
                    
    $bgcolor2 "#DDDDDD";
                      if ( (
    $j 2) == ) {
                         echo 
    $bgcolor1;
                         } else {
                         echo 
    $bgcolor2;
                         }
                    
    $j++;
                echo 
    "\"><td>";
                echo 
    htmlspecialcharsstripslashes($row["store_number"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["address"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["city"]));
                echo 
    "</td><td>";
                echo 
    htmlspecialcharsstripslashes($row["state"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["zip"]));
                echo 
    "</td><td class=\"tinytext\">";
                echo 
    htmlspecialcharsstripslashes($row["phone"]));
                echo 
    "</td></tr>";
                }
                
                if (
    $num_results2 == 0)
                    echo 
    "<p>The zip code you entered does not match any records in our database.</p>";
                
        }


        
    ?>
    As mentioned before, I'm still fumbling my way through this, so it's likely there is a better way to do it.

    The zip field in the database is int(5).

    Hope that helps to convey what's going on. Let me know if you need any more info. Again, thanks for your time.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, try

    ... WHERE concat('',zipcode) LIKE 'xxx%'

    where xxx is your first three digits

    tip: would not be necessary if zipcode were stored as varchar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see...how does one extract the first 3 digits of the user-submitted zip code and insert it into the query in place of 'xxx'?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in coldfusion, left(zipcode,3)

    i imagine php has something similar

    (i don't do php, sorry)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think substr($zip,0,3); would be the PHP equivalent. Working on it now.

    Thanks for all the help.

  10. #10
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works:
    Code:
    $zip3 = substr($zip,0,3);
    $query2 = "select * from ingles_loc WHERE zip LIKE '$zip3"."__' order by '$order_by'";

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    PA, USA
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you need to do is use the 'Great Circle Algorithm' which will allow you to return the set of zipcodes which fall within a specified radius of your search zipcode. Since zipcodes are not contiguous searching numerically doesn't give the results you need. If you search a little, you can find a class for download which encapsulates this and if you download the census zipcode DB, it contains every US zipcode plus their latitude+longitude. If you need me to post any more info, I can try and dig out addresses and such (I don't have the info handy right now)

  12. #12
    SitePoint Addict will_'s Avatar
    Join Date
    Apr 2004
    Location
    Asheville, NC
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have looked into other options such as what you mention. Being beyond my skill level to code it, I went with what is above.

    That said, I definitely plan on improving it, and would ideally like to implement such a distance-based or zone-based method.

    I was unaware you could get the DB from the Census Beureau. All I had found with Google was people selling zip code maps and databases for outrageous sums of money.

    Thanks for the tip.

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    PA, USA
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by will_
    I have looked into other options such as what you mention. Being beyond my skill level to code it, I went with what is above.

    That said, I definitely plan on improving it, and would ideally like to implement such a distance-based or zone-based method.

    I was unaware you could get the DB from the Census Beureau. All I had found with Google was people selling zip code maps and databases for outrageous sums of money.

    Thanks for the tip.
    I have the DB and class on my hard drive, but the attachment is too large to provide here. If you're interested, let me know and I can maybe break it into chunks


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
  •