Checkbox help....how can I do this???

[Bl4ckMaj1k]

Here is the 'want' right now:

 

I have a database with a table for customers and another one for projects. I want the user to have the ability to select which customer they want to be associated with their project. I understand how to do this logically (set up of the tables relationships) but I cant seem to figure out how to do this via checkbox arrays. For example, there should be a list of checkboxes with the values being pulled from the customer database. Whichever customers are selected, their IDs should get placed into a row in the project table called cust id. So the project table would read like the following:

 

ID: 1

Project ID: 3-002-055

Customer ID: Whatever the ID number is in the customers table (I have customer IDs being pulled from the URL)

 

ID: 2

Project ID: 3-002-055 (same as above)

Customer ID: Whatever the ID number is

 

So in this case, both customers are working on the same project.

 

With that being said, I know for a fact that this is possible via some kind of checkbox statement. Something that pretty much says the following (I already started my lame attempt at coding this....I am no expert to bare with me!):

 

<?php 

$staff_id = $_SESSION['sid'];

$company_id = $_GET['cid'];
$company_id  = mysql_real_escape_string($company_id );
$company_id = eregi_replace("`", "", $company_id);


$sql = mysql_query("SELECT contact_fname, contact_lname FROM contact_info WHERE company_id='$company_id'");

while($row = mysql_fetch_array($sql)){

$contact_fname = $row["contact_fname"];
$contact_lname = $row["contact_lname"];

$select_contacts .= "
<input name=\"contacts[]\" type=\"checkbox\" value=\"' . $contact_fname . ' ' . $contact_lname . '\" /><br /><br />

<input name=\"submit_contacts\" type=\"submit\">
";

}
?>

 

This is where I am lost at. How do I say that whenever anything is checked, grab the value and store it into the database but in a separate record???

 

Bl4ck Maj1k

[gizmola]

What you describe is called a Many - to - Many relationship.  A customer can "be a part of"  1 to many projects.  A Project can have 1 to many Customers.

 

You need a table that sits between the 2 tables to resolve the many to many relationship.  I usually will call a table like this projectcustomers or customerprojects.  It will often only require the primary keys from each table:

 

customerprojects

-----------------

customer_id (pk)

project_id (pk)

 

When you create your form with the list of customers and the accompanying checkboxes, give them a name like cb_customer_{customer_id}, so if you looked at the raw html you might see something like:

 

 

With PHP, when you process the form, you will only get the name of the checkbox in the $_POST[] if it was checked, so all you need to do is go through the $_POST looking for variables that start with 'cb_customer' and parse the customer id off the end.  Then build an INSERT statement and create all the rows you need in the customerprojects table.  Frequently if I do something like this, I'll DELETE FROM customerprojects WHERE project = $projectid first, to clear out any existing rows, so that if people uncheck a row that was previously checked it gets removed.

 

 

[Bl4ckMaj1k]

Thanks that helps....I think I understand. Also you are right about the many to many relationship type.

 

Would it matter if I made a table called projects? My project ID is a unique ID being identified via PHP by me. So I could have a project in the projects table that reappears 100 times but all the customer IDs would be different. This way, I won't have to create a third table.

 

Your thoughts?

Bl4ck Maj1k

[gizmola]

If what you are saying is that the project table would have nothing in it other than the projectid, then yes you could just have that table be the equivalent of the many to many and omit the need for a separate project table.

[Bl4ckMaj1k]

Somewhat valid. It will have project ID along with the customer related to that project. Row 1 may have customer 11 associated with project 3-005-023 and then row 4 may have same project ID with a different customer ID associated with it. Seems to work fine for the time being. Whenever I need to call to it I just say something like show me all customers in project table where customer id = customer id from the customers table and project ID = current project we are viewing....would that work in a php statement?

[gizmola]

Yes, that' s exactly what I meant ... it is essentially the many to many table, having the two keys, (project_id, customer_id).  Then you do a standard inner join between that table and customer, as you mocked up, specifiying AND project_id = 'whateverprojectid'.