User Define Custom Fields

Using php/mysql.

I have a contacts table for a CRM software I made.

I want Companies to be able to create custom fields for their contacts.

The contacts table holds basic information like: ID, Name, Date_of_Birth, Address, and so on. The contacts table is filtered by Company_ID to find the company specific contacts.

There can be certain company related fields/groups that can be assigned to contacts for later filtering/reporting.

So to make this happen, here's what I'm thinking.

I created a 'groups' table with fields: ID, Name, Company_ID

I then created another table, groups_contacts, to join the group with the contact: ID, Group_ID, Contact_ID

I will make a page for companies to add/update/delete groups into the groups table.

When a new contact is added, right now I have a form users must fill out the basic contact info form. I then have the SQL to "INSERT INTO contacts...."

If my logic above is correct, I would then have to "INSERT INTO groups_contacts...."

But that's where I'm stuck. How do I get this to work?

I'd like to have check boxes next to all the company's custom group names. If any/all/some are checked then add them to the groups_contacts table.

Is that a good idea? I'd appreciate some help.

Sounds fine to me. For the initial insert into contacts, you could either skip inserting anything to groups_contacts (have that be a secondary form after the contact is created), or get the last inserted id to use for it. When adding a contact to a group, you may want to check that the contact and the group are both associated with a common company (ie. company A's contacts can't get added to company B's groups).

Well, on the contact form where the contact information is selected, I was going to "SELECT --- FROM groups WHERE Company_ID = 'somevalue'". The contact information is also based on that WHERE statement.

This would filter group names based on the company using the system. So therefore, the contact and the group can only be associated with appropriate groups.

I was thinking about a secondary form... maybe that would be easiest. Especially because in the join table, I am linking the Group_ID to a Contact_ID. The Contact_ID is based on the autoincremented ID field (primary key) in the contact table. That won't be generated until a record is added. I am going to think it through a bit more before I start.

Let's say I have a query of all the group names for a certain company:

Code:

---

SELECT Name, ID FROM groups WHERE Company_ID = 'somevalue'

---

How do I then display each Name, ID using checkboxes where the label of the checkbox is associated with each Name in the recordset, and the ID is the value?

Thanks.

Code:

---

<?php
    $result = mysql_query("SELECT Name, ID FROM groups WHERE Company_ID = 'somevalue'");
    if (!$result) {
    die("Database query failed: " . mysql_error());
    }
while($row = mysql_fetch_array($result)) {
    $id= $row['ID'];
    $name = $row['Name'];
    echo '<input type="checkbox" value="' . $id . '" />' . $name;
}
?>

---

Would that do? Then I'd need a way to insert each checked value into the groups_contacts join table.

Quote:

---

I was thinking about a secondary form... maybe that would be easiest. Especially because in the join table, I am linking the Group_ID to a Contact_ID. The Contact_ID is based on the autoincremented ID field (primary key) in the contact table.

---

That's not really a problem; you can insert the contact, get its newly-generated Contact_ID and use that for your next insert (into groups_contacts). One form or two is mostly a consideration of what you want (or think is better) for user experience.

Quote:

---

Would that do? Then I'd need a way to insert each checked value into the groups_contacts join table.

---

That's essentially what you want, just need to add names to the checkboxes:

PHP Code:

---

while($row = mysql_fetch_array($result)) {
  echo '<input type="checkbox" name="group[]" value="' . $row['ID'] . '" />' . $row['Name'];
} 


---

Upon processing the form, that'll give you an array of the selected group ids to insert for the contact.

Here's what I have to display the custom groups inside a table:

Code:

---

<?php do { ?>
  <tr>
    <td align="right"><label>
      <input name="Groups[]" type="checkbox" id="Group_ID" value="<?php echo $row_groups['ID']; ?>" />
      </label></td>
    <td>
        <?php echo $row_groups['Group']; ?></td>
    </tr>
        <?php } while ($row_groups = mysql_fetch_assoc($groups)); ?>

---

... works just fine.

What I am struggling with, as I have no experience with this:

How do I then insert each checked value (the group_id) into my join table?

Here's what I'm working on:

Code:

---

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
        $group= $_POST['Group_ID'];
        foreach($group as $value)
{
  $insertSQL =("INSERT INTO patient_group_join (Group_ID, Patient_ID, Company_ID) VALUES ('$value', '$_GET[Patient_ID]','$_POST[Company_ID]')");
 
}
  mysql_select_db($database_connect, $connect);
  $Result1 = mysql_query($insertSQL, $connect) or die(mysql_error());

  $insertGoTo = "admin_addpatcustomgroup.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

---

So I have the checkboxes displaying properly in a repeat region in a table. The checkbox value is equal to the Group_ID from this query:

Code:

---

SELECT ID, `Group` FROM groups WHERE Company_ID = %s ORDER BY `Group` ASC

---

When I check a few of the group boxes, only the first record is inserted into my db.

How can I modify the above to insert ALL checked values in separate records? Something is not right with my foreach statement just before INSERT...