how to show the option based on the value in the table?

[tayys]

hi,

 

Instead giving its default on "Choose Process", I would like to display its process based on the value in database. How can I do it?

 

<?php 
require_once '../../connectDB.php';
require_once '../include/functions.php';

$sql = " SELECT *
	 FROM tb_item
	 ORDER BY itm_id asc";
$result = mysql_query($sql) or die(mysql_error());
?>
<form>
<h1>Items</h1>
<table width="75%" border="1" align="left" cellpadding="5" cellspacing="1">
	<tr> 
   		<th width="100" align="left">Item ID</th>
   		<th width="100" align="left">Parts</th>
   		<th width="100" align="left">Sub-Process</th>
   		<th width="100" align="left">Confirmation</th>
   	</tr> 	
   	<tr>	
   	<?php 
   	while ($row = mysql_fetch_array($result)) 
	{
	   	extract($row);
	?>
		<td><?php echo $itm_id; ?></td>
	    <td><?php echo $itm_parts; ?></td>
	    <td> 
			<select name='cboSub' id='cbosub'>
				<option value='' selected>---- Choose Process ----</option>	
			    <?php createComboBox(); ?>			  
			</select>
	 	</td>
		<td>
			<a href='processItem.php?itm_id=<?php echo $itm_id; ?>'>
				<img src='../images/modify.png' width='20' height='20' alt='confirm'/>
			</a>
		</td>
	</tr>
	<?php 
	}; 
	?>				

</table>
</form>
<?php 
/**
* Create combo box for sub-process
*/
function createComboBox() 
{
$sql = " SELECT *
 		 FROM tb_sub_process
    		 ORDER BY sub_id asc";

$result = mysql_query($sql) or die(mysql_error());

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

	echo "<option value='$sub_id' $select>$sub_name</option>";
}
}
?>

 

I've attached an example of what basically the output will looks like.

 

Any help are appreciated.

 

Thanks in advance.

 

 

Regards,

Juz

 

[attachment deleted by admin]

[Buddski]

Is the process value stored in the tb_item table?

[tayys]

yup...it's in one of the table

[jcbones]

Throw us a bone here, where is it located, and what is the column name. 

[tayys]

What I am trying to do is, initially it is set default - "Choose Process".

After selecting and saved, it will display the option been selected on the next login.

 

jcbones,

the sub_id is stored in tb_item.

 

[tayys]

here's my tables:

 

*****tb_item*****

itm_id | itm_qty | itm_cost | sub_id

 

*****tb_process*****

pro_id | pro_name

 

*****tb_sub_process*****

sub_id | sub_name | pro_name

[jcbones]

Try this, and let us know:

<?php 
require_once '../../connectDB.php';
require_once '../include/functions.php';

$sql = " SELECT *
	 FROM tb_item
	 ORDER BY itm_id asc";
$result = mysql_query($sql) or die(mysql_error());
?>
<form>
<h1>Items</h1>
<table width="75%" border="1" align="left" cellpadding="5" cellspacing="1">
	<tr> 
   		<th width="100" align="left">Item ID</th>
   		<th width="100" align="left">Parts</th>
   		<th width="100" align="left">Sub-Process</th>
   		<th width="100" align="left">Confirmation</th>
   	</tr> 	
   	<tr>	
   	<?php 
   	while ($row = mysql_fetch_array($result)) 
	{
	   	extract($row);
	?>
		<td><?php echo $itm_id; ?></td>
	    <td><?php echo $itm_parts; ?></td>
	    <td> 
			<select name='cboSub' id='cbosub'>
				<option value='' selected>---- Choose Process ----</option>	
			    <?php createComboBox($sub_id); ?>			  
			</select>
	 	</td>
		<td>
			<a href='processItem.php?itm_id=<?php echo $itm_id; ?>'>
				<img src='../images/modify.png' width='20' height='20' alt='confirm'/>
			</a>
		</td>
	</tr>
	<?php 
	}; 
	?>				

</table>
</form>
<?php 
/**
* Create combo box for sub-process
*/
function createComboBox($id) 
{
$sql = " SELECT *
 		 FROM tb_sub_process
    		 ORDER BY sub_id asc";

$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($result)) 
{						
	extract($row);
	$select = ($id == $sub_id) ? 'selected="selected"' : NULL;
	echo "<option value='$sub_id' $select>$sub_name</option>";
}
}
?>

[tayys]

thanx a lot jcbones...it works...

 

I'm facing another problem. it wouldn't update into the database when i click on the confirmation link.

 

it might be $_POST['cboSub'], cause i m getting a null value. what can i do to fix it right?

 

here's my code on processItem.php:

<?php
$itm_id = isset($_GET['itm_id']) ? $_GET['itm_id'] : '';

$sub_id=$_POST['sub_id'];
echo $sub_id;
$sql = "UPDATE tb_item 
        SET sub_id = '$sub_id'
        WHERE itm_id = '$itm_id'";
           
$result = mysql_query($sql) or die('Cannot update sub-process ID into tb_item. ' . mysql_error());
?>

[jcbones]

Does the page source reveal that the sub_id is set in the select box?

 

[tayys]

Sorry my mistake. It should be

 

$sub_id=$_POST['cboSub'];

 

But it still doesn't pass the selected value from the earlier page so that It can be updated into database based on the itm_id.

[jcbones]

What 'earlier page'?

[tayys]

ermm...maybe i m not clear here.

 

here goes again.

 

1) the user will choose an option.

2) update to database by clicking a confirmation link next to it.

3) continue with other items with step 1&2.

 

codes in list.php:

<?php 
require_once '../../connectDB.php';
require_once '../include/functions.php';

$sql = " SELECT *
	 FROM tb_item
	 ORDER BY itm_id asc";
$result = mysql_query($sql) or die(mysql_error());
?>
<form>
<h1>Items</h1>
<table width="75%" border="1" align="left" cellpadding="5" cellspacing="1">
	<tr> 
   		<th width="100" align="left">Item ID</th>
   		<th width="100" align="left">Parts</th>
   		<th width="100" align="left">Sub-Process</th>
   		<th width="100" align="left">Confirmation</th>
   	</tr> 	
   	<tr>	
   	<?php 
   	while ($row = mysql_fetch_array($result)) 
	{
	   	extract($row);
	?>
		<td><?php echo $itm_id; ?></td>
	    <td><?php echo $itm_parts; ?></td>
	    <td> 
			<select name='cboSub' id='cbosub'>
				<option value='' selected>---- Choose Process ----</option>	
			    <?php createComboBox($sub_id); ?>			  
			</select>
	 	</td>
		<td>
			<a href='processItem.php?itm_id=<?php echo $itm_id; ?>'>
				<img src='../images/modify.png' width='20' height='20' alt='confirm'/>
			</a>
		</td>
	</tr>
	<?php 
	}; 
	?>				

</table>
</form>
<?php 
/**
* Create combo box for sub-process
*/
function createComboBox($id) 
{
$sql = " SELECT *
 		 FROM tb_sub_process
    		 ORDER BY sub_id asc";

$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($result)) 
{						
	extract($row);
	$select = ($id == $sub_id) ? 'selected="selected"' : NULL;
	echo "<option value='$sub_id' $select>$sub_name</option>";
}
}
?>

 

Codes in processItem.php:

<?php
$itm_id = isset($_GET['itm_id']) ? $_GET['itm_id'] : '';

$sub_id=$_POST['cboSub'];
$sql = "UPDATE tb_item 
        SET sub_id = '$sub_id'
        WHERE itm_id = '$itm_id'";
           
$result = mysql_query($sql) or die('Cannot update sub-process ID into tb_item. ' . mysql_error());
?>

 

how can I link these two sets of code together correctly?

 

 

[jcbones]

See if this works.  Un-tested:

<?php 
require_once '../../connectDB.php';
require_once '../include/functions.php';
if(isset($_POST['change'])) { 
$itm_keys = array_keys($_POST['change']);
foreach($itm_keys as $id) {
	if(!empty($_POST['cboSub'][$id])) {
		$sub_id = (int) $_POST['cboSub'][$id];
			$sql = "UPDATE tb_item SET sub_id = '$sub_id' WHERE itm_id = '$id'";
			mysql_query($sql) or trigger_error($sql . ' has an error: ' . mysql_error());
	}
}
}
$sql = " SELECT *
	 FROM tb_item
	 ORDER BY itm_id asc";
$result = mysql_query($sql) or die(mysql_error());
?>
<form>
<h1>Items</h1>
<table width="75%" border="1" align="left" cellpadding="5" cellspacing="1">
	<tr> 
   		<th width="100" align="left">Item ID</th>
   		<th width="100" align="left">Parts</th>
   		<th width="100" align="left">Sub-Process</th>
   		<th width="100" align="left">Confirmation</th>
   	</tr> 	
   	<tr>	
   	<?php 
   	while ($row = mysql_fetch_array($result)) 
	{
	   	extract($row);
	?>
		<td><?php echo $itm_id; ?></td>
	    <td><?php echo $itm_parts; ?></td>
	    <td> 
			<select name='cboSub[<?php echo $itm_id; ?>]' id='cbosub'>
				<option value='' selected>---- Choose Process ----</option>	
			    <?php createComboBox($sub_id); ?>			  
			</select>
	 	</td>
		<td>
			<input type="submit" name="change[<?php echo $itm_id; ?>]" value="Modify" />
			</a>
		</td>
	</tr>
	<?php 
	}; 
	?>				

</table>
</form>
<?php 
/**
* Create combo box for sub-process
*/
function createComboBox($id) 
{
$sql = " SELECT *
 		 FROM tb_sub_process
    		 ORDER BY sub_id asc";

$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($result)) 
{						
	extract($row);
	$select = ($id == $sub_id) ? 'selected="selected"' : NULL;
	echo "<option value='$sub_id' $select>$sub_name</option>";
}
}
?>

[tayys]

Thanks for the response. I'll give it a try and let you know how it turns out!

 

[tayys]

I tried the codes but it is shows the error message as in the attachment.

 

I never encounter this seen this error message before. Any idea what's the problem is?

 

Thanx.

 

[attachment deleted by admin]

[jcbones]

Sorry, I didn't even pay attention to the form element.

 

Change:

<form>

To:

<form action="" method="post">

[tayys]

jcbones,

 

thanks dude.

 

it's working perfectly now.

 

despite, there is a statement i can't get myself clear.

 

Can u please briefly explain these?

$itm_keys = array_keys($_POST['change']);

[jcbones]

array_keys just returns the keys of the array that is passed to it.  This is done so that we can find what items were sent in the post field.

[tayys]

noted.

 

thank you very much for the guides and suggestions. I learnt a lot from you.

 

*I would like to lift another issue regarding 2 chained combo boxes in jquery. r u able to give me some advises on that?

 

 

[jcbones]

Just start another thread, so that anyone looking at this one will not confuse the two different issues.

[tayys]

sure. I've created under the forum of javascript helps sometime last week.

 

Here's the link to the new thread:

http://www.phpfreaks.com/forums/index.php?topic=346236.0

 

any help is appreciated.