• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need Help With Exploding CSV Data Within DB

#11
[eluser]Jay Logan[/eluser]
That looks like something that could almost work. I'm trying to understand it better. The way the site works is a page is loaded based on the id_name. EX: www.site.com/profile/houston. The id_name is houston. So I need to retrieve all zip codes associated with houston in the database. The column containing the zip codes is addr_assoc. In another table, I have information about all zip codes. So if houston is associates with 12345, 12346, 12347, 1238, etc. then I need to pull all records from the zip code table that have those as the zip_code. Some zip codes will be in there more than once and I would want to pull that info too.

#12
[eluser]johnwbaxter[/eluser]
Then just do the query to bring back all records that match houston and do a for each on the result set to make a where_in select statement for each one.

#13
[eluser]Jay Logan[/eluser]
I think I have it figured out. New problem is removing the duplicates. In the zip code table, zip_codes can be different but county_names could be the same. Or even zip_codes could be the same and county_names could be different. Thank you guys for your help. I learned a lot from this.

#14
[eluser]Jay Logan[/eluser]
And here is the code I ended up using.

Code:
function get_zips($id_name)
        {
            $this->db->select();
            $this->db->where('id_name', $id_name);
            $get_data = $this->db->get('VSS_src_network');
            $zips = explode(", ", $get_data->row()->addr_assoc);
            foreach ($zips as $zip)
            {
                $this->db->or_where('zip_code', $zip);
            }
            $result = $this->db->get('JL_zip_codes as Zip_Code');
            return $this->clean_result($result);
        }

#15
[eluser]Jay Logan[/eluser]
Figured it out. Here is the final code I will be using.

Code:
function unique_counties($id_name)
        {
            $this->db->select();
            $this->db->where('id_name', $id_name);
            $get_data = $this->db->get('VSS_src_network');
            $this->db->where_in('zip_code', explode(', ', $get_data->row()->addr_assoc));
            $this->db->distinct();
            $this->db->select('county_name');
            $result = $this->db->get('JL_zip_codes as County');
            return $this->clean_result($result);
        }

I have to make a new function for each item I want to be unique (city, state, etc.) but it works well. Let me know there is any better way of doing this. Thanks again.

#16
[eluser]johnwbaxter[/eluser]
What is this, what does it do? "$this->clean_result($result);"

You could put in $this->db->free_result directly after this line:$get_data = $this->db->get('VSS_src_network'); which would be errrr good.

#17
[eluser]Jay Logan[/eluser]
clean_result is this function that was used on one of my other sites. Another programmer made it and it was the only way I knew to get the result to display in my view correctly using code like:

Code:
<? foreach ($counties as $row): ?>
    <li>&lt;?= $row['County']['county_name'] ?&gt;</li>
&lt;? endforeach; ?&gt;

Here is the function.

Code:
function clean_result(&$ci_result)
        {
            $result = $ci_result->result_id;
            $array = array();
            $row = 0;
            while ($values = mysql_fetch_array($result, MYSQL_NUM))
            {
                for ($i=0; $i<mysql_num_fields($result); $i++)
                {
                    $meta = mysql_fetch_field($result, $i);
                    if ($meta->table != '')
                    $array[$row][$meta->table][$meta->name] = $values[$i];
                    else
                    $array[$row]['as'][$meta->name] = $values[$i];
                }
                $row++;
            }
            return $array;
        }

I'll try out free_result.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.