[SOLVED] Going from drop-down menu to selectable menu...

[ShootingBlanks]

I had a full php site/database working as I'd liked, and then I found out that I had to make a change.  But it's a change that I have no clue how to do. 

 

I have a "categories" table with 2 columns - "cat_id" (primary) and "cat_name".  I have a "documents" table with columns "doc_id" (primary), "cat_id", and "doc_name".

 

In order to insert a new document into the database where the user can select a category to associate with the document, I have a drop-down menu being pre-populated with categories from the "categories" table using the following code:

 

(above the DOCTYPE):

mysql_select_db($database_SalesRepository, $SalesRepository);
$query_getCategories = "SELECT * FROM categories ORDER BY cat_name";
$getCategories = mysql_query($query_getCategories, $SalesRepository) or die(mysql_error());

 

(in the form/body of the page):

<select name="cat_id" id="cat_id">
  <?php
    while($row = mysql_fetch_assoc($getCategories)){
      echo '<option value="'.$row['cat_id'].'">'.$row['cat_name'].'</option>';
    }
  ?>
</select>

 

(and then a basic INSERT query to put the data into the database)

 

All of this works fine.  HOWEVER, I now found out that some documents need to have MULTIPLE categories.  So, I need to change that drop-down menu to a multiple-select box...

 

I assume I'd need a new "cross-referencing" type of table in my database, so I created one called "doc_cat_lookup" with 2 columns "doc_id" and "cat_id".  But, after that I'm lost.  And I'm not sure that THAT was even the right thing to do...

 

I'm very new to PHP, so even creating what I already have has been a chore.  I hope altering my project this way won't be a total mess!  Thanks for any help that can be offered!!!

 

 

 

 

[ShootingBlanks]

I don't know if this is necessary to help troubleshoot, but the INSERT query I'm using is this:

 

INSERT INTO documents (cat_id, doc_name) VALUES (%s, %s)",
                       GetSQLValueString($_POST['cat_id'], "int"),
                       GetSQLValueString($_POST['doc_name'], "text"));

 

[ShootingBlanks]

Any help?...or am I out of luck here?  ???

 

 

[chronister]

I would simply add another dropdown for the 2nd cat. Then in the database, create a table called cat_doc or something like that. Create the fields cat_id, doc_id

 

This table would be used to link the cats with the docs. This way it is easily searchable, and you can manage the category/doc combos independent of each other.

 

If you don't need to search and stuff, then just add a 2nd drop down box and in the query insert both id's using a separator of your choice.

 

 

Nate

 

 

[ShootingBlanks]

If you don't need to search and stuff, then just add a 2nd drop down box and in the query insert both id's using a separator of your choice.

 

But there may not be a 2nd category.  And there may be 10 categories....in which case I'd need 10 more drop-down menus that may or may not be used.

 

That's why I think I'd have to opt for the multiple-select box.

 

 

[chronister]

gotcha, yeah that would probably be the best.

 

 

 

Then you are going to have to turn the field into an array so multiple items can be selected. Take a look at the following script. Something I played with for a few. I don't generally use multiple select fields so I had to see how this would work.

 

<?php
print_r($_POST['select']);
?>
<form id="form1" name="form1" method="post" action="<?=$_SERVER['PHP_SELF'] ?>">
  
  <select name="select[]" size="10" multiple="multiple" id="select">
  <?php
  $x=0;
  while($x < 50)
  {
	echo '<option value="'.$x.'">'.$x.'</option>';
               $x++;				
  }
  ?>
  </select>
  <input type="submit" name="submit" value="Submit"  />
</form>

 

 

So you will have to make it an array by adding [] to the name of your field, then you will have to loop through the array and deal with the results as you see fit.

 

Hope this helps

 

[ShootingBlanks]

Okay - that helped.  Baby steps here!  Heheh...

 

...now I have a multiple select field that works.  The problem now is capturing the data and getting it input into the correct database table(s), based on my original post.  Here is the code I used to get the multiple-select box populated, so you can see the variables and stuff that I used:

 

<tr valign="baseline">
      <td nowrap align="right" valign="top">Categories:</td>
      <td><select name="categories[]" size="11" multiple="multiple" id="categories">
	<?php
	// build multiple choice list with contents of categories table
	while($row = mysql_fetch_assoc($getCategories)) {
	?>
		<option value="<?php echo $row['cat_id']; ?>">
		<?php echo $row['cat_name']; ?>
		</option>
	<?php } ?>
	</select>
      </td>
    </tr>

 

Where to go from here?... ???

 

 

[chronister]

You have to determine if you wanna store each cat_id in a seperate table to link the 2 tables together as I described earlier, or if you want to store them in the existing field and use a seperator. If you want to use a seperator you  would do this... say you wanna use a % as your seperator (I chose that because it is strange and will probably not appear in the results to cause problems, you can use whatever you want as that seperator)

 

So now categories is going to be an array. So you need to loop through and get each individual var.

<?php
$categories=$_POST['categories'];
foreach($categories as $k=>$v)
{
   $cats.=$v.'%'; // I think this is right... sorry can't test it right now.
}
?>

 

This should give you 4%6%8%10%11%15. Then when you display it on screen or need to use it you would run it through explode and create an array with it and loop through that array in the display script.

 

If you wanna use a separate table to link them together, you would grab the doc_id first then loop through the results like above but instead of assigning it to a variable, you would run the query there to insert the doc_id and cat_id into the table. To get the doc_id of a record inserted in the db you would use $new_id=mysql_insert_id(); right after the query to insert the doc_name in the DB.

 

I hope this helps as I am short on time and can't go into details. Post back if you need clarification and I will try to help.

 

Nate

[ShootingBlanks]

Cool - got it working.  Thanks for the help!!!