Welcome Guest, Not a member yet? Register   Sign In
Need Help With Exploding CSV Data Within DB
#1

[eluser]Jay Logan[/eluser]
Trying to figure out the best practice here. I have a table that stores several zip codes for a store location. The zip codes are CSV. They SHOULD all be unique zip codes for each location but I'm not banking on that.

I just added a table full of every zip code in USA (over 80,000 rows) and it has data such as city name, county name, latitude, longitude, etc. What I want to do is look up a location and display geographic information based on which zip codes are stored in the database with the location. So if a user goes to one of my locations web page, they will see all the counties and cities that are associated with it.

Any suggestions?
#2

[eluser]TheFuzzy0ne[/eluser]
I have absolutely no idea how zip codes work, so I cannot suggest anything. Hopefully someone else may have an answer for you.
#3

[eluser]pistolPete[/eluser]
Have a look at this one: http://www.phpclasses.org/browse/package/1640.html
#4

[eluser]Jay Logan[/eluser]
I don't really need anything to help with locating zip codes. In fact, you can forget that zip codes are being used. Let's just say the I have a table with 100 store locations. For each store location record, there is a column which has a several 5 digit numbers separated by a comma. They could be zip codes, product ID's, whatever. What I need to do with CI is read the column with all the CSV numbers and match each CSV number to another table which will look up info on that number (zip code info, product info, whatever).
#5

[eluser]TheFuzzy0ne[/eluser]
Sounds more like a problem with your database design. I would strongly suggest either having a separate table with 1 row for each each location and each five numbers (so that would be five rows per location). It's generally considered bad practice to store more than one atomic value in a database column. If you are storing 5 values, you really need that extra table, and then you can use a LEFT/RIGHT JOIN to join the tables.

Hope this helps.
#6

[eluser]Jay Logan[/eluser]
Yea, I was trying to avoid that. Because it's more like 200 "numbers" per location. Originally, the table was design so that a user could simply search for there zip and the site would return the location(s) that had that zip in the field. But now I'm trying to pull zip code information from another table to show info like county name, city name, and incorporate Google maps to show latitude and longitude. Thanks though.
#7

[eluser]TheFuzzy0ne[/eluser]
Well, I don't see why you can't do it in two queries. The first query can get the column with the CSV numbers, and the second would get all rows with the specified IDs.

Code:
// Get the IDs from the table.
$this->db->where('zip_code', $zip_code);
$result = $this->db->get('my_table');

if ($result->num_rows() != 1)
{
    // Entry doesn't exist, or there are more than one row for that entry.
}
else
{
    // Explode the ids into an array.
    $ids = explode(",", $result->row()->ids);

    // Iterate through the exploded IDs, and use supply them to $this->db->or_where()
    foreach ($ids as $id)
    {
        $this->db->or_where('id', $id);
    }
    
    $result = $this->db->get('my_other_table');

    // Do something with the resulting rows.    
}
The above code is untested and designed to work with imaginary tables.

Hopefully it should help illustrate what you might need to do.
#8

[eluser]johnwbaxter[/eluser]
If you have a field with data in it like this 101,512,600,965 then surely all you need to do is explode() the data and do a where_in query which accepts an array?

That of course is if i understand your problem correctly....

ie.
Code:
$this->db->where_in('table.column', explode(',', $data_from_table));
#9

[eluser]TheFuzzy0ne[/eluser]
You're right. That'd work too. I've never used where_in() yet, so it never occurred to me.
#10

[eluser]johnwbaxter[/eluser]
Don't worry about it, not everyones as cool as me....




Theme © iAndrew 2016 - Forum software by © MyBB