[SOLVED] can MySQL do this or should I leave it to my script?

[boo_lolly]

Sorry for the ellusive title but I didn't know how to put it. Here's the situation. If I have a column in my database table that looks like this:

 

TX, UT, NY, ID, LA, MA, OK, WV, VT

 

State abbreviations, you know. so all of that is in one cell. If I'm pulling a single state abbreviation from the address bar using $_GET, can I put that into my sql query as a LIKE statement? as in SELECT * FROM my_table WHERE states LIKE $_GET? Because what I have now looks like this:

<?php

$db_host = 'localhost';
$db_user = 'user';
$db_pass = 'pass';

$conn = mysql_connect($db_host, $db_user, $db_pass) OR die('Error connecting to mysql');

$db_name = 'mk_store_locator';
mysql_select_db($db_name);

$sql = 'SELECT * FROM states_stores';
$result = mysql_query($sql);

    $state_list = array(
                'AL' => 'Alabama', 'AK' => 'Alaska', 'AZ' => 'Arizona', 
                'AR' => 'Arkansas', 'CA' => 'California', 'CO' => 'Colorado', 
                'CT' => 'Connecticut', 'DE' => 'Delaware', 'DC' => 'District Of Columbia', 
                'FL' => 'Florida', 'GA' => 'Georgia', 'HI' => 'Hawaii', 
                'ID' => 'Idaho', 'IL' => 'Illinois', 'IN' => 'Indiana', 
                'IA' => 'Iowa', 'KS' => 'Kansas', 'KY' => 'Kentucky', 
                'LA' => 'Louisiana', 'ME' => 'Maine', 'MD' => 'Maryland', 
                'MA' => 'Massachusetts', 'MI' => 'Michigan', 'MN' => 'Minnesota', 
                'MS' => 'Mississippi', 'MO' => 'Missouri', 'MT' => 'Montana',
                'NE' => 'Nebraska', 'NV' => 'Nevada', 'NH' => 'New Hampshire',
                'NJ' => 'New Jersey', 'NM' => 'New Mexico', 'NY' => 'New York',
                'NC' => 'North Carolina', 'ND' => 'North Dakota', 'OH' => 'Ohio', 
                'OK' => 'Oklahoma', 'OR' => 'Oregon', 'PA' => 'Pennsylvania', 
                'RI' => 'Rhode Island', 'SC' => 'South Carolina', 'SD' => 'South Dakota',
                'TN' => 'Tennessee', 'TX' => 'Texas', 'UT' => 'Utah', 
                'VT' => 'Vermont', 'VA' => 'Virginia', 'WA' => 'Washington', 
                'WV' => 'West Virginia', 'WI' => 'Wisconsin', 'WY' => 'Wyoming'
            );

print $state_list[$_GET['state']];

while ($row = mysql_fetch_array($sql)) {
    $states_array = explode(',' $row['states']);
        foreach ($states_array as $state){
            if ($_GET['state'] == $state) {
                print $row['store'] .' '. $row['phone'] .' '. $row['url'] .'<br />';
            }
        } 
}

?>

 

I haven't tested it yet, but I'm sure it will suffice. I just want to make sure I'm not doing the work that MySQL could be doing for me. What do you guys think?

[Third_Degree]

I don't understand your database structure, there is one row with all the states in it, but separate rows for each store, phone, and url?

[boo_lolly]

Third_Degree. Exactly. it looks like this for example:

 

ID     store                   states                        url                             phone
1  	kroger  	TX, MA, NY, PA, SD, TN  	www.kroger.com  	1-555-555-5555
2 	Albertson's 	CN, TX, NM, UT, CA, CO, KS 	www.albertsons.com 	1-555-555-5555
3 	Sam's 	WA, ND, WI, MI, NC, PA, NJ 	www.sams.com 	1-555-555-5555

[Third_Degree]

ah, ok. Well, google fulltext sql (LIKE may also work I think)

[boo_lolly]

yeah i figured it out bro. LIKE statement will work. Thanks. I just didn't want someone to go in behind me and look at the script later and think, 'what the hell was this guy thinking?' hahah. take care.

[Third_Degree]

haha alright dude