[RESOLVED] Re-Populating drop-down box from db

grumpy

Hello-
I have a form for my users to update their information. When they originally submitted their info, they could select one or multiple categories from a Select field drop-down box. These multiple categories are stored as a space-separated array string in the db. What I'm trying to do is allow them to update their categories by again using a Select drop-down box. But I want the Select drop down-box on the update form to show what their present categories are before they update(that is that their current options are highlited). I can get the Select to display one category properly (if they originally selected only one category,) but I can't get it to populate properly if they selected more than one category originally. How can I get the form field to show more than one category selected?
Below is my code snippet. Any help would be appreciated. thnk you.

// start populate work cats drop-down box
$sql = "SELECT * FROM contractors where usr_id = '24' ";
// execute SQL query and get result
 $result = mysql_query($sql); 
// put data into drop-down list box
 while ($row = mysql_fetch_array($result)) { 
$c = $row["co_workcat1"] ;  

}
 // create SQL statement 
$sql = "SELECT * FROM work_cat ORDER BY cat_name ASC";
// execute SQL query and get result
 $result = mysql_query($sql); 
// put data into drop-down list box
 while ($row = mysql_fetch_array($result)) { 

$i = $row["cat_name"]; 
$option_block_cat_name .= "<OPTION value=\"$i\""; 


if($c == $i) { 
$option_block_cat_name .= " selected>"; 
} else { 
$option_block_cat_name .= ">"; 
} 

$option_block_cat_name .= "$i</OPTION>"; 
}
 // end populate work cats drop-down box


// form field to show current categories and allow changes
<SELECT name=categories size=4 multiple> 
$option_block_cat_name .
</SELECT>

paulnaj

Hi,

First thing to fix is that $c has not been defined in this code. It should be an array, but as it stands, it will only contain a single value at a time.

PS. Other things may be wrong but fix this first.

grumpy

I don't think I understand what you mean. How do I fix $c? $c is space-delimited string from the table contractors. Do I use explode?

paulnaj

Ah Ok, I see.

Saw the statement ...

while ($row = mysql_fetch_array($result)) {
	$c = $row["co_workcat1"] ;
}

and assumed you were returning more than one row.

As it stands, you first retrieve one contactor and get its category name: contractors.co_workcat1

Then you cycle through a list of categories and and pick out the one whose name is the same as contractors.co_workcat1.

Surely only one category can be selected since this contractor only has one category.

Paul.

grumpy

Thanks for your answer. $row["co_workcat1"] is one row which can contain multiple category names separated by a space. I guess what I'm trying to do is split each category out of the row and then make it SELECTED in the select drop-down box. My code works if $row["co_workcat1"] only contains one category, but fails if more than one.

paulnaj

Got it.

OK. This is not the 'done' thing. You should really create a third table to store the one-to-many relationship.

Having said that, if you are sure that your string is well-formed, you could do ...

//replace $c = $row["co_workcat1"];
$c = explode(' ', $row["co_workcat1"]);

//...
//replace if($c == $i) {
if(in_array($i, $c)) {

Should do it.

EDIT : if(in_array($i, $c) needed an extra ')'

grumpy

I tried thr third-table thing but it's apparently past my capabilities. Worked on it for days. 🙁 The way I'm trying to do it now is a method I've used in the past and worked well but it was used where the user could not select multiple choices, only one.

paulnaj

It's not too hard, once you've got the principles and it really is the right way to do it.

If you want some help to get you going, paste a copy of your CREATE statements for these two tables.

But anyway, is it working as expected now?

grumpy

I'm trying to do the third intermediate table you recommended, putting each selection form the the form's SELECT field into its own row. But I can't get my data into the table. I just keep getting the word 'array' instead of the data. I tried implode and explode. (This is why I gave up last time, couldn't figure it out then either). Here is my code. Whats am I doing wrong? Any help would be appreciated again.

//My form

$sql = "SELECT * FROM work_cat ORDER BY cat_name ASC";
// execute SQL query and get result
 $result = mysql_query($sql); 
// put data into drop-down list box
 while ($row = mysql_fetch_array($result)) { 
$i = $row["cat_name"]; 
$option_block_cat_name .= "<OPTION value=\"$i\">$i</OPTION>"; 
}
?>
	 <form name="addcats" action="do_addcats.php" method="POST">
    <p>Select a first primary category<br>
    <SELECT name="co_workcat" size="4" multiple> 

<?php 

echo ("$option_block_cat_name ."); 

?>
<p><input type=submit name=submit value=SUBMIT>     

  </form>

// My processing page

$ID = 4;
$_POST['co_workcat'] = explode(",", $_POST['co_workcat'] );
for($x = 0;$x < count ($_POST['co_workcat']);$x++){
$sql = "INSERT INTO cat_relations SET 
relation_user_id='$ID',

	relation_cat='$co_workcat'

";
if (@mysql_query($sql)) {
echo ("Good");

  } else {
    echo("<p>Error adding cats: " .
         mysql_error() . "</p>");
  }


}

?>

grumpy

Thanks figured it out. closing thread.