Results 1 to 6 of 6

Thread: User Define Custom Fields

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    790

    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.

  2. #2
    Frenzied Member
    Join Date
    Apr 2009
    Location
    CA, USA
    Posts
    1,516

    Re: User Define Custom Fields

    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).

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    790

    Re: User Define Custom Fields

    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.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    790

    Re: User Define Custom Fields

    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.

  5. #5
    Frenzied Member
    Join Date
    Apr 2009
    Location
    CA, USA
    Posts
    1,516

    Re: User Define Custom Fields

    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.

    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.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    790

    Re: User Define Custom Fields

    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width