Exploding items from Table

kbc1

Hi all,

For each record in my table there is a varchar field called countryids which stores x amount of values in the format of '1 2 3 4' lets say.

These numbers actually represent the ID field of a different linked table called tblcountries. How these ID's get into this field is not a concern. Its getting them out which is!

On my php form I have a list of check boxes which in this example is a list of countries and I need to know how to extract the values mentioned above and dynamically tick the appropriate checkbox of the country on my form (obvously if the value exists in my record). My guess is this is done by the ID somehow but I simply cant visualise how its done.

Can anyone help?

Thanks in advance.

jkurrle

Are the values stored all in one field or are values stored in separate fields. If in one field, how are you separating them? How the data is stored will determine how you will process it.

Let's say all the values are stored into one varchar field as a series of comma delimited data. I would extract that field into a variable, then use explode() to break it into an array of selected country ids, for instance:

$country_array=explode(",",$extracted_value);

From there, I would process my checkboxes one at a time. On each one, I would use something like this:

$countries=array("USA","Canada","Mexico","Japan","England")
foreach($countries as $checkbox)
   {
   $is_checked=0;
   foreach($country_array as $selected)
      {
      if ($selected==$checkbox) $is_checked=$is_checked+1;
      }
   if ($is_checked==1)
      {
      echo "<input type=\"checkbox\" name=\"Country[$checkbox]\" value=\"$checkbox\" checked />";
      }
   else
      {
      echo "<input type\"checkbox\" name=\"Country[$checkbox]\" value=\"$checkbox\"  />";
      }
   }

Hope this gives you some ideas...

Weedpacket

Like you said, I guess you'd start with [man]explode[/man]....

devinemke

a little advice:

that is very bad data design, storing an array of foreign keys in a string like that. what you have there is a many-to-many data relationship between two tables. the common way to do this is to have a third table that links them together. the link table essentially just has the primary key fields from each of the other tables for each link. then it is a simple matter of querying the link table to see how your rows match up. this design (with proper indexing) will be a much faster and more scalable way to do this then the way you are doing it now.

jkurrle

I was simply going by kbc1's initial design spec, dm. I do basically what you expressed when I build my own edit pages that contain checkboxes. If he has the ability to break the data out into a separate table, great. If not, then the code I provided should be enough for him to work with.

That being said, I should have mentioned the design inefficiency of what he was doing. You have the right point there.