Need help populating SELECT multiple form field and inserting into db

[grumpy]

Hello- RE: my code below

I have an update form where I'm trying to populate a SELECT multiple field with a list of 48 categories, from tbl work_cat. And show, as SELECTED, the one or many choices that the user had previously selected from the 48 categories which are stored in tbl cat_relations as $relation_cat. Then allow the user to update their selections and update the database. But I can't get my form to work. First problem, I can't get the SELECT field on the form to show the categories, and then I don't know where to go from there. Below are my form page and my processing page. Any help would be appreciated.

Thanks.

 

 

 

//my form

<?

$sql = "SELECT * FROM cat_relations where relation_usr_id = '37' ";

// execute SQL query and get result

$result = mysql_query($sql);

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

$c = $row["relation_cat"] ;

}

 

 

$sql = "SELECT * FROM work_cat ORDER BY cat_name ASC";

$result = mysql_query($sql);

// put data into drop-down list box

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

 

 

$i = $row["ID"];

$i = "<OPTION value=\"$i\"";

 

 

if($c == $i) {

 

$d = "selected";

}

 

else {

$d = " ";

}

 

$i= "$i</OPTION>";

 

}

 

 

?>

<table width="60%" align="center">

<td>

<form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST>

<p>Select work categories. Hold down the Ctrl key to make multiple selections.<br>

<SELECT name="co_workcat[]" size="4" multiple>

<?php

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

$i = $row["ID"];

$strCatId = $row['ID'];

echo '<OPTION value="' . $strCatId . ' $d">' . $i . '</OPTION>';

}

?>

</select>

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

</form>

 

// my processing page

<?

$cats = $_POST['co_workcat'];

foreach($cats as $cat) {

$cat_list1 .= $cat . ' ';

}

;

 

 

$query = mysql_query("SELECT ID FROM work_cat where cat_name = '$cat_list1'");

$result = $query;

// put data into drop-down list box

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

echo ($row["ID"]);

$cat_list1 = $row["ID"];

};

 

foreach ($cats as $strKey => $strValue)

{

$sql = "UPDATE cat_relations SET relation_usr_id='$ID',relation_cat='$strValue'";

if (@mysql_query($sql)) {

echo "<script languag='javascript'>location.href='index.php';</script>";

 

} else {

echo("<p>Error adding cats: " .

mysql_error() . "</p>");

}

}

 

[rajivgonsalves]

do you just want the checkbox checked with the user data ?

[grumpy]

Hi, again- You had helped me yesterday with the initial input. Its a SELECT field, a drop-down box. Their previous selections would show as selected (highlighted) in the drop-down list. They then could change their previous selections if they wanted to and the table would be updated.

[rajivgonsalves]

Hi try this code

$c = array();
$sql = "SELECT * FROM cat_relations where relation_usr_id = '37' ";
// execute SQL query and get result
$result = mysql_query($sql); 
while ($row = mysql_fetch_array($result)) { 
$c[$row["relation_cat"]] = " selected " ;
}


?> 
<table width="60%" align="center">
<td>
   <form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST> 
<p>Select work categories. Hold down the Ctrl key to make multiple selections.

<SELECT name="co_workcat[]" size="4" multiple> 
<?php
while ($row = mysql_fetch_array($result)) { 
$i = $row["ID"]; 
$strCatId = $row['ID']; 
echo '<OPTION value="' . $strCatId . $c[$strCatId].' ">' . $i . '</OPTION>'; 
} 

[grumpy]

Thanks for your answer. I replaced my code with yours, but I'm just getting an empty SELECT box.

[rajivgonsalves]

mistake in code try this

 

$c = array();
$sql = "SELECT * FROM cat_relations where relation_usr_id = '37' ";
// execute SQL query and get result
$result = mysql_query($sql); 
while ($row = mysql_fetch_array($result)) { 
$c[$row["relation_cat"]] = " selected " ;
}


?> 
<table width="60%" align="center">
<td>
  <form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST> 
<p>Select work categories. Hold down the Ctrl key to make multiple selections.

<SELECT name="co_workcat[]" size="4" multiple> 
<?php
while ($row = mysql_fetch_array($result)) { 
$i = $row["ID"]; 
$strCatId = $row['ID']; 
echo '<OPTION value="' . $strCatId .' "'. $c[$strCatId]. '>' . $i . '</OPTION>'; 
} 

[grumpy]

Still getting a blank box. I shoud be seeing all 48 categories in the drop-down box with the users 4 previous choices highlighted.

[rajivgonsalves]

can you provide the url to your site ?

[rajivgonsalves]

ok try this

 

 

 

$c = array();
$sql = "SELECT * FROM cat_relations where relation_usr_id = '37' ";
// execute SQL query and get result
$result = mysql_query($sql); 
while ($row = mysql_fetch_array($result)) { 
$c[$row["relation_cat"]] = " selected " ;
}

print_r($c);


?> 
<table width="60%" align="center">
<td>
   <form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST> 
<p>Select work categories. Hold down the Ctrl key to make multiple selections.

<SELECT name="co_workcat[]" size="4" multiple> 
<?php
while ($row = mysql_fetch_array($result)) { 
$i = $row["ID"]; 
$strCatId = $row['ID']; 
echo '<OPTION value="' . $strCatId .' "'. $c[$strCatId]. '>' . $i . '</OPTION>'; 
} 

[rajivgonsalves]

and post the output which you get

[grumpy]

The 48 categories to populate the Select drop-down box should be getting pulled from the table: work_cat by cat_name and ID. I tried the following but it doesn't work:

$sql = "SELECT * FROM work_cat ORDER BY cat_name ASC";

// execute SQL query and get result

$result = mysql_query($sql);

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

$i = $row["cat_name"];

};

 

My test page is here:

http://navigator.websitewelcome.com/~freeesti/booking/admin/updatecats-test5.php

[rajivgonsalves]

saw the page but cannot undertstand your query the code I provided should work

[grumpy]

Here is my code cobined with your code.

 

<?

// populate the drop-down box with the 48 possible selections

$sql = "SELECT * FROM work_cat ORDER BY cat_name ASC";

// execute SQL query and get result

$result = mysql_query($sql);

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

$i = $row["cat_name"];

};

 

 

$c = array();

$sql = "SELECT * FROM cat_relations where relation_usr_id = '37' ";

// execute SQL query and get result

$result = mysql_query($sql);

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

$c[$row["relation_cat"]] = " selected " ;

}

 

 

?>

<table width="60%" align="center">

<td>

  <form name=addcats action=do_addcats.php?ID=<?php echo $ID; ?> method=POST>

<p>Select work categories. Hold down the Ctrl key to make multiple selections.

 

<SELECT name="co_workcat[]" size="4" multiple>

<?php

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

//$i = $row["ID"];

$strCatId = $row['ID'];

echo '<OPTION value="' . $strCatId .' "'. $c[$strCatId]. '>' . $i . '</OPTION>';

}

 

?>

 

</form>

 

 

</body>

</html>

[grumpy]

I've worked on my code. Now its showing the drop-down box properly and the user's selections are highligted. But now when it submits, it is not updating the table cat_relations properly. Its putting in only one of the category id's. I'm not seeing any errors and I'm getting the "GOOD" message. Can anyone tell me what I'm still doing wrong? My updates code is below. Thanks.

 

//my form

<?php

include ('../connect.php');

 

$ID = '37';

$c = array();

$sql = "SELECT * FROM cat_relations where relation_usr_id = '$ID' ";

$result = mysql_query($sql);

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

$c[$row["relation_cat"]] = " selected " ;

}

$sql2 = "SELECT * FROM work_cat ORDER BY cat_name ASC";

// execute SQL query and get result

$result = mysql_query($sql2);

 

?>

<table width="60%" align="center">

<td>

<form name=addcats action=do_updatecats.php?ID=<?php echo $ID; ?> method=POST>

<p>Select work categories. Hold down the Ctrl key to make multiple selections.

<p>

<SELECT name="co_workcat[]" size="4" multiple>

<?php

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

$i = $row["cat_name"];

$strCatId = $row['ID'];

 

echo '<OPTION value="' . $strCatId .' "'. $c[$strCatId]. '>' . $i . '</OPTION>';

}

 

?>

</select>

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

</form>

 

// my processing page - do_updatecats.php

 

<?php

include ('../connect.php');

$ID = $_GET['ID'];

 

 

$cats = $_POST['co_workcat'];

foreach($cats as $cat) {

$cat_list1 .= $cat . ' ';

}

;

 

 

$query = mysql_query("SELECT ID FROM work_cat where cat_name = '$cat_list1'");

$result = $query;

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

echo ($row["ID"]);

$cat_list1 = $row["ID"];

};

 

foreach ($cats as $strKey => $strValue)

{

$sql = "UPDATE cat_relations SET relation_usr_id='$ID',relation_cat='$strValue'";

if (@mysql_query($sql)) {

echo ("GOOD");

} else {

echo("<p>Error adding cats: " .

mysql_error() . "</p>");

}

}

 

?>