Extracting part of a variable to use RLIKE

[will_]

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

$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.

[r937]

i'm going to assume the zipcode column in the database is varchar(9)

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,

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

[will_]

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.

[r937]

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)

[will_]

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
    $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) == 0 ) {
                     echo $bgcolor1;
                     } else {
                     echo $bgcolor2;
                     }
                $j++;
            echo "\"><td>";
            echo htmlspecialchars( stripslashes($row["store_number"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($row["address"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($row["city"]));
            echo "</td><td>";
            echo htmlspecialchars( stripslashes($row["state"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($row["zip"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($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) == 0 ) {
                     echo $bgcolor1;
                     } else {
                     echo $bgcolor2;
                     }
                $j++;
            echo "\"><td>";
            echo htmlspecialchars( stripslashes($row["store_number"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($row["address"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($row["city"]));
            echo "</td><td>";
            echo htmlspecialchars( stripslashes($row["state"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($row["zip"]));
            echo "</td><td class=\"tinytext\">";
            echo htmlspecialchars( stripslashes($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.

[r937]

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

[will_]

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'?

[r937]

in coldfusion, left(zipcode,3)

i imagine php has something similar

(i don't do php, sorry)

[will_]

I think substr($zip,0,3); would be the PHP equivalent. Working on it now.

Thanks for all the help.

[will_]

This works:

$zip3 = substr($zip,0,3);
$query2 = "select * from ingles_loc WHERE zip LIKE '$zip3"."__' order by '$order_by'";

[maaxiim]

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)

[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.

[maaxiim]

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