[Resolved] [Resolved] using multiple tick boxes to submit multiple information

[Calahan]

Now I might be going about this the wrong way (which is more than likely) but here's my problem.
I have a table called job_cat which holds a list of job catagories and job catagory numbers. I also have another table called Engineers which contains fields for the username, name, password, site, and job_cat_no. I have designed a page where admins can add new engineers to the database and (this is where it gets tricky for me) select the different catagories that that particular engineer will be responsible for. Here's the Add Engineer code:

$job_cat_query="SELECT * FROM " . $Table2 ."";

$job_cat_result=mysql_query($job_cat_query) OR die(mysql_error());

echo "<center><form action='add-engineer2.php' method='post' name='job_cat'>
<table width='22%'>
    <tbody>
      <tr>
        <td><b>Username:</b></td>
        <td><input name='username' type='text'></td>
      </tr>	  

	  <tr>
        <td><b>Name:</b></td>
        <td><input name='name' type='text'></td>
      </tr>
      <tr>
        <td><b>Password:</b></td>
        <td><input name='tel_no' type='text'></td>
      </tr>
      <tr>
        <td><b>Site:</b></td>
        <td><input name='site' type='text'></td>
      </tr>
    </tbody>
  </table>
<p>
<p align='center'><strong><font size='+1'>Select all the job catagories that this Engineer will be responsible for<p>
</font></strong></p><center>

<table width='20%'>
  <tr>";

while( $row = mysql_fetch_array($job_cat_result) )
{
echo "<td width='5%'><form name='assigned_job_cat' method='post' action='add-engineer2.php'>
      <input type='checkbox' name='".$row['job_cat_no']."' value='checkbox'>".$row['job_cat']."
      </td>
  </tr>";
}
echo "</tbody></table>";

mysql_free_result($job_cat_result);
echo "<input value='Search' type='submit' name='submit2'>
</form>

This code allows the user to enter the various details of the new engineer. It also generates a list of tick boxes which are labelled with the contants of the job_cat table (using the while loop).
My question is how can I add engineers into the db if they have more than one catagory assigned to their name (from a table point of view). I think I might also have a problem with the code itself but I'll get the first problem fixed first.

Thanks for any suggestions.

[Pig]

Your database structure is not normalized. You need to have another table (many-to-many relationship) to store the engineer id's, and the category id's. YOu have one entry for each category that the engineer should be part of.
eg:
id | eng_id | cat_id
1 | 1 | 2
2 | 1 | 4
3 | 2 | 1
4 | 2 | 2
5 | 2 | 3
6 | 3 | 1

Here, there are 3 engy's (1,2,3). Engy 1 has cat's 2,4. Engy 2 has 1,2,3. Engy 3 has only 1.

Do a join on your queries, and you are done.

Also, your method of inserting category data will need to be completely redone.

[lazzerous]

Calahan,

the answer is somewhat simple.

Each checkbox (or tickbox as you call it) has it's own name.

When processing the form, weed out unselected boxes, and create a comma-delimited string of selected categories.

so Joe blow would have an entry of '2,17,27,49'

when you pull that data from the database, you can use the explode function to create an array from the comma-delimited string, and reference to the actual category names that way

make sense?

[Pig]

It is a bad idea to store data that way. It makes it much more clumsy to search and manipulate. If you do not normalize your design, you will invetibaly cause yourself unecessary headaches later on.

[Calahan]

Pig,

I see what you're saying and have changed the way the DB works accordingly. The update of the engineer information works fine now and the only problem I have left with this part is a small structure of the PHP code. I create the tick boxes like so:

$job_cat_query = "SELECT * FROM " . $Table2 . "";

$job_cat_result = mysql_query($job_cat_query) OR die

(mysql_error());
<form name='assigned_job_cat' method='post' action='add-engineer-confirmed.php'>
<table width='20%'>
  <tr>";
while( $row = mysql_fetch_array($job_cat_result) )
{
echo "<td width='5%'>
      <input type='checkbox' name='".$row['job_cat_no']."' value='".$row['job_cat_no']."'>".$row['job_cat']."
      </td>
  </tr>";
}
echo "</tbody></table>";

This cycles through all of the entries in the job_cat table and uses them for 3 things. 1. it gives the tick boxes the name of each job catagory number (job_cat_no) so I can refer to them on the next page. 2. It sets the value of each tick box to the corrosponding job_cat_no for each entry (so that I can add them to the DB on the next page), and 3. It labels each tick box with the names of each job_cat entry.
Now I come to the code that adds the selected tick boxes to the DB. I have my Insert statement done:

$insert_into_job_cat_eng = "INSERT INTO " . $Table3 . " VALUES('" . $_SESSION['eng_no'] . "','" . $_POST['job_cat'] . "')";

All I need to do is find some way of telling PHP to cycle through each of the values submitted from the previous form and in each while statement, run the Insert statement above. I just don't know what to pass to the while statement to make it work.

Edit Just had another thought, how would I stop the ones that ween't selected from being entered into the DB as if they were?

My brain hurts.

Sorry, a tad long winded there but I hope it made sense.🙂

[Pig]

Let me explain the scenario, then you can adjust your coding. To pass multiple checkboxes for the same item, you name the checkboxes with the same name, followed by square brackets. On the process page, every checked box will be part a member of an array. eg:
<input type=checkbox name='foo[]' value='$row[id]'>

I'm not exactly sure your scenario, but I think this example is pretty close. I don't know if you want to handle multiple engineers at once, so I'll pretend like you do; it'll be easy to simplify it if you don't.

Let's say that there are 10 categories, and each engineer could be part of any of them. Your input page would have each engineer and 10 checkboxes. You would name each checkbox after the category ID, and have the value be the engineer's ID. So if every engy put a check in the first box (let's say category ID '1'), then you would have an array named '1', with every engy's ID in there. You could then loop through that array and insert into the table with the 2 id's.

If you are handling only one engy at a time, then just reverse it. The boxes have the engy's ID as their name, and the cat_id as the value. Either way works.

Hope that made sense.

[Calahan]

Hi Pig (doesn't sound like a nice opening line🙂),

I really think I'm just being dumb with this question and I imagine that you're probably cursing the lack of intellegence on my part by now but if I could just pick your brains 1 more time....
I have written out below exactly how the "Add engineer" pages work so that you can get a better idea of where it is I'm going wrong. The resulting problem is at the end.

On Page 1

4 input boxes where the user inputs the engineer username, fullname, password and site..

On Page 2

Error checking to ensure that all info entered didn't already exist and all required fields have been filled in.

PHP code then runs to insert the new engineer into the Database.

$insert_engineer = "INSERT INTO " . engineers. " VALUES ('" . $_POST['username'] . "','','" . $_POST['name'] . "','" . md5($_POST['password']) . "','" . $_POST['site'] . "')";

mysql_query($insert_engineer) OR die(mysql_error());

Now run a query to get the auto allocated engineer number from the engineers table (added to in the last statement) and assign it to the variable

$_SESSION['eng_no'] -

$get_eng_no = "SELECT eng_no FROM " . engineers . " WHERE username='" . $_POST['username'] . "'";

$get_eng_no_result = mysql_query($get_eng_no) OR die(mysql_error());

while( $row = mysql_fetch_array($get_eng_no_result) )
{
$_SESSION['eng_no'] = $row['eng_no'];
}

Now show the user a selection of check boxes with each of the job catagories from the job_cat table.

$job_cat_query = "SELECT * FROM " . job_cat . "";

$job_cat_result = mysql_query($job_cat_query) OR die(mysql_error());

while( $row = mysql_fetch_array($job_cat_result) )
{
echo "<td width='5%'>
      <input type='checkbox' name='job_cat[]' value='".$row['job_cat_no']."'>".$row['job_cat']."
      </td>
  </tr>";
}
echo "</tbody></table>";

On Page 3

Assign a query to insert the new engineer into the eng_job_cat table along with his/her selected job catagories-

$insert_into_eng_job_cat = "INSERT INTO " . eng_job_cat . " VALUES('" . $_SESSION['eng_no'] . "','" . $_POST['job_cat'] . "')";

Now run a while loop to insert the information into the eng_job_cat table as many times as there is supplied information -

while( $row = mysql_fetch_array($_POST['job_cat']) )
{
mysql_query($insert_into_eng_job_cat) OR die(mysql_error());
}

Here I get an error message. Now I understand the meaning of the error message:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
as the information I'm asking it to execute with didn't come from a MySQL query but my question is what do I need to supply to the while loop to make it run through each of the submitted check boxes? Or do I need something other than a while loop?
Secondly, if a check box isn't selected, does that mean that it is just not added to the array?
I know that job_cat is now an array containing (I hope) the selected check boxes and their corrosponding job catagory values as using print $_POST['job_cat']; in the code prints out the word "Array", its just that I don't know how to extract them.

Thanks for your time on this.

[Pig]

Before I answer your question, I want to give you a tip. There is a function called mysql_insert_id(). It will give you the last inserted ID. Example:
$result = mysql_query("INSERT INTO table (a,b) VALUES (1,2)");
$insert_id = mysql_insert_id();

This would replace the need for this part:

$get_eng_no = "SELECT eng_no FROM " . engineers . " WHERE username='" . $_POST['username'] . "'";

$get_eng_no_result = mysql_query($get_eng_no) OR die(mysql_error());

while( $row = mysql_fetch_array($get_eng_no_result) )
{
$_SESSION['eng_no'] = $row['eng_no'];
}

Anyway, you are using mysql_fetch_array() to pull info out of an array. That function is only for retrieving data from a mysql query. You need to use one of the loops (for, while, foreach). In this case, foreach would be the best.

foreach($_POST['job_cat'] AS $key)
{
     mysql_query("INSERT INTO eng_job_cat (cat_id, eng_id) VALUES ('$key', '$insert_id')");

Not sure on your column names, so you'll have to fix those.

[Calahan]

I just fixed it 5 minutes after writing the post and just came back to give you the answer that you just gave me!

Oh well, hopefully all of my babbling will help someone else out too.

Thanks for the mysql_insert_id() tip. It's sure to come in handy later too.

[Pig]

I'm glad you got it worked out. 🙂