Moving records to new table structure

[pjh1985]

Hi, I am trying to transfer values in one table to a new table with a different structure. The current table has 3 fields id, content_id and value and for each content_id there are several records.

I would like to transfer the values for each content_id to just 1 record on the new table but I am unsure how this could be done. I'm not sure I have explained it that great but hopefully this poor example will help.

current table
id |cid| value
1 | 3 | a
2 | 3 | g

new table
id |cid|val1|val2
1 | 3 | a | g

[smantscheff]

You are asking for problems. In effect you are going to de-normalize your table, which may ease a short-term purpose, but in in the long run you will stumble into problems because auf bad design.
Then from your example it is not clear which value for "id" should be saved - the first one encountered? Or which?
And how many columns should your new table have? As many as the group with the most rows for the same cid? And the other fields stay empty? Bad, bad design.
Have a look at the group_concat function. Maybe a query like

select cid,group_concat(value) from mytable group by cid

might suit your purpose.

[pjh1985]

Well then maybe if I tell you what I am trying to achieve you could suggest a better way of doing it.

I am building a new website for someone and due to the amount of content, they have asked if it would be possible to re-use the data from the old website. I was able to adapt the content table to suit the needs of the CMS i use for my websites (made by me). The table that stores form data however is stored with a separate record for each question, using the content_id to link them to the content page. This form data was displayed on the website but was unable to be edited after it was completed, i want to now add the ability to the new website so the data can be edited in the admin system.

I was going to create the forms with code similar to below that would show the currently selected options etc and needs to use . If the data is stored separately then I am unsure how i could make it work. Would it be bad practice to store data from 1 form in a table which is linked to a content page in 1 record, or would it be better to store each data item from the form in separate records like it is in the old website?

<?

 
$aGender = array("Male", "Female");
 
//converting comma separated into array using explode function
    $dbGender= explode(',',$row_details['gender']);
 
  foreach ($aGender as $gender) {
 
     if(in_array($gender,$dbGender)) {
      echo "<input name=\"gender[]\" type=\"checkbox\" value=\"$gender\" CHECKED> $gender";
          } else
          {
      echo "<input name=\"gender[]\" type=\"checkbox\" value=\"$gender\"> $gender";
                  }
              }
?>

[smantscheff]

What does $row_details contain?
If I understand you correctly, you want to make the value set for the field gender (for example) editable. You also want that the field gender has only a limited set of possible values. The correct datatype would be an enum field. You could read the definitions and possible values using the "show create table" statement into your application for form display, and you could expose them for editing in an admin interface which allows for changing the table structure.
An alternative would be a lookup table for the field gender (and all other fields with limited datasets) - this is where you might use the group_concat function.