0

Hi All,

I need to create a dropdown box for a user to select a row from a table and populate the fields in a form for the user to update that row.

What will be the best way to do this?

Thank you in advance

Max

2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by trilithon
0

As soon as the dropdown value/option is changed you will make an ajax request to fetch the data from the database and populate the form. you can then cache the data in JS itself. also you can simply load all the data from the DB when the page is loaded itself.

0

Can you help me with some sample code of the dropdown box and the ajax script

Thank you

Max

0

not really. I dont have the time to write up sample code for you, untill you pay me heh :D

but its pretty easy

0

I have figured out how to query the supplier table for a single row and then to populate the values to a new form. The form also displays correctly in the suppliers page, but it does not update the table when submitting the update button.

There is also a problem with the ajax script in IE7, it works fine in Firefox.

Please check what's wrong with the code below:

FILE:suppliers.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" href="css/style.css" type="text/css" media="all" />
<script type="text/javascript">
function showSupplier(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getsupplier.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>


 /* Rest of page script goes here */


<form>
<fieldset>
<br />
<h4>Update Supplier Information</h4>
<br />
<label>Select Supplier:&nbsp;*&nbsp;</label>	
	<?php 
  
	$result = mysql_query("SELECT * FROM supplier") 
	or die("Invalid Query: " .mysql_query()); 
  
	echo '<select name="select_supplier" onchange="showSupplier(this.value)">'; 
	echo '<option value="">Select a Supplier:</option>'; 
  
	while ($row = mysql_fetch_assoc($result)) 
	{ 
	$va = $row['s_code']; 
	echo "<option value='$va'>$va</option>"; 
	} 
	echo '</select>'; 
	?>



<br />
<br />
<div id="txtHint">


</div>
<br />
<br />
</fieldset>
</form>


 /* Rest of page script goes here */

FILE:getsupplier.php

<?php
include("include/session.php");
$q=$_GET["q"];


$sql="SELECT * FROM supplier WHERE s_code = '".$q."'";

$result = mysql_query($sql);
   /* Error occurred, return given name by default */
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info";
      return;
   }
   if($num_rows == 0){
      echo "Database table empty";
      return;
   }
   /* Display table contents */
   for($i=0; $i<$num_rows; $i++){
      $uname = mysql_result($result,$i,"name");
      $us_code = mysql_result($result,$i,"s_code");
      $ut_name = mysql_result($result,$i,"t_name");
      $ureg_num = mysql_result($result,$i,"reg_num");
      $uaddress_l1 = mysql_result($result,$i,"address_l1");
      $uaddress_l2 = mysql_result($result,$i,"address_l2");
      $uaddress_l3 = mysql_result($result,$i,"address_l3");
      $uaddress_l4 = mysql_result($result,$i,"address_l4");
      $up_code = mysql_result($result,$i,"p_code");
      $uregion = mysql_result($result,$i,"region");
      $userv_type = mysql_result($result,$i,"serv_type");
      $ucap_amount = mysql_result($result,$i,"cap_amount");
      $uvat = mysql_result($result,$i,"vat");
      $uvat_detail = mysql_result($result,$i,"vat_detail");
      $ubee = mysql_result($result,$i,"bee");
      $ubee_detail = mysql_result($result,$i,"bee_detail");
      $ucontact = mysql_result($result,$i,"contact");
      $utel = mysql_result($result,$i,"tel");
      $umobile = mysql_result($result,$i,"mobile");
      $ufax = mysql_result($result,$i,"fax");
      $uemail = mysql_result($result,$i,"email");
      $uwww = mysql_result($result,$i,"www");
      $uorder = mysql_result($result,$i,"order");
      $upay_terms = mysql_result($result,$i,"pay_terms");
      $udisc = mysql_result($result,$i,"disc");
      $udisc_terms = mysql_result($result,$i,"disc_terms");
      $ubank = mysql_result($result,$i,"bank");
      $ubank_acc_type = mysql_result($result,$i,"bank_acc_type");
      $ubank_branch_name = mysql_result($result,$i,"bank_branch_name");
      $ubank_branch_code = mysql_result($result,$i,"bank_branch_code");
      $ubank_acc_num = mysql_result($result,$i,"bank_acc_num");
      $ubank_statement_ref = mysql_result($result,$i,"bank_statement_ref");
      $ucontract_start = mysql_result($result,$i,"contract_start");
      $ucontract_review = mysql_result($result,$i,"contract_review");
      $ucontract_end = mysql_result($result,$i,"contract_end");
      $unotice_period = mysql_result($result,$i,"notice_period");
      $utotal_value = mysql_result($result,$i,"total_value");
      $umonth_value = mysql_result($result,$i,"month_value");
      $ucontract_note = mysql_result($result,$i,"contract_note");
      $ulow_resp = mysql_result($result,$i,"low_resp");
      $ulow_day_hour = mysql_result($result,$i,"low_day_hour");
      $unorm_resp = mysql_result($result,$i,"norm_resp");
      $unorm_day_hour = mysql_result($result,$i,"norm_day_hour");
      $uhigh_resp = mysql_result($result,$i,"high_resp");
      $uhigh_day_hour = mysql_result($result,$i,"high_day_hour");
      $uemerg_resp = mysql_result($result,$i,"emerg_resp");
      $uemerg_day_hour = mysql_result($result,$i,"emerg_day_hour");
      $ugen_notes = mysql_result($result,$i,"gen_notes");

echo "
<form name='update_supplier' action='upd_supl.php' method='post'>
<fieldset>
<br />
<h3>Add new supplier</h3>
<fieldset>
<br />
<h4>Genaral Information</h4>
<br />
<table style='font-size:12px' align='center' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>Supplier Name:</td><td><input type='text' name='p_name' size='50' value='$uname' /></td><td>&nbsp;</td><td align='right'>Supplier Code:</td><td><input type='text' name='s_code' size='3' value='$us_code' /></td>
</tr>
<tr>
<td>Trade Name:</td><td><input type='text' name='t_name' size='50' value='$ut_name' /></td><td>&nbsp;</td><td align='right'>Company Registration Number:</td><td><input type='text' name='reg_num' size='20' value='$ureg_num' /></td>
</tr>
<tr>
<td valign='top'>Address:</td><td><input type='text' name='address_l1' size='50' value='$uaddress_l1' /><br /><input type='text' name='address_l2' size='50' value='$uaddress_l2' /><br /><input type='text' name='address_l3' size='50' value='$uaddress_l3' /><br /><input type='text' name='address_l4' size='50' value='$uaddress_l4' /></td><td>&nbsp;</td><td align='right'></td><td></td>
</tr>
<tr>
<td>Postal Code:</td><td><input type='text' name='p_code' size='4' value='$up_code' /></td><td>&nbsp;</td><td align='right'></td><td></td>
</tr>
<tr>
<td>Region:</td><td><input type='text' name='region' size='50' value='$uregion' /></td><td>&nbsp;</td><td align='right'></td><td></td>
</tr>
<td>Service Type:</td><td><input type='text' name='serv_type' size='50' value='$userv_type' /></td><td>&nbsp;</td><td align='right'>Capability Amount:</td><td><input type='text' name='cap_amount' size='10' value='$u'cap_amount /></td>
</tr>
</table>
<table style='font-size:12px' align='center' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
";
	if($uvat=="1") {
		echo "<td>VAT Regitered:</td><td><input type='checkbox' name='vat' value='1' checked='checked' /></td>\n";
	} else {
		echo "<td>VAT Regitered:</td><td><input type='checkbox' name='vat' value='1' /></td>\n";    
	}


echo "<td>&nbsp;</td><td align='right'>VAT Number:</td><td><input type='text' name='vat_detail' size='20' value='$uvat_detail'/></td>
</tr>
<tr>
";
	if($ubee=="1") {
		echo "<td>BEE Regitered:</td><td><input type='checkbox' name='bee' value='1' checked='checked' /></td>\n";
	} else {
		echo "<td>BEE Regitered:</td><td><input type='checkbox' name='bee' value='1' /></td>\n";    
	}


echo "<td>&nbsp;</td><td align='right'>Detail:</td><td><input type='text' name='bee_detail' size='50' value='$ubee_detail' /></td>
</tr>
</table>
</fieldset>


<fieldset>
<br />
<h4>Contact Details</h4>
<br />
<table style='font-size:12px' align='center' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>Contact Name:</td><td><input type='text' name='contact' size='50' value='$ucontact'/></td><td>&nbsp;</td><td align='right'>Tel:</td><td><input type='text' name='tel' size='20' value='$utel'/></td>
</tr>
<tr>
<td>Fax:</td><td><input type='text' name='fax' size='20' value='$ufax'/></td><td>&nbsp;</td><td align='right'>Mobile:</td><td><input type='text' name='mobile' size='20' value='$umobile'/></td>
</tr>
<tr>
<td>Email:</td><td><input type='text' name='email' size='50' value='$uemail'/></td><td>&nbsp;</td><td align='right'>Website:</td><td><input type='text' name='www' size='50' value='$uwww'/></td>
</tr>
</table>
</fieldset>


<fieldset>
<br />
<h4>Payment Details</h4>
<br />
<table style='font-size:12px' align='center' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
";
	if($uorder=="1") {
		echo "<td>Order Required:</td><td><input type='checkbox' name='order' value='1' checked='checked' /></td>\n";
	} else {
		echo "<td>Order Required:</td><td><input type='checkbox' name='order' value='1' /></td>\n";    
	}


echo "<td>&nbsp;</td><td align='right'></td><td></td>
</tr>
<tr>
<td>Payment Terms:</td><td><input type='text' name='pay_terms' size='3' value='$upay_terms' /> Days</td><td>&nbsp;</td><td align='right'></td><td></td>
</tr>
<tr>
<td>Discount</td><td><input type='text' name='disc' size='3' value='$udisc' /> %</td><td>&nbsp;</td><td align='right'>Dicount Terms</td><td><input type='disc_terms' name='disc_terms' size='3' value='$udisc_terms' /> Days</td>
</tr>
</table>
</fieldset>


<fieldset>
<br />
<h4>Banking Details</h4>
<br />
<table style='font-size:12px' align='center' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>Bank:</td><td><input type='text' name='bank' size='50' value='$ubank' /></td><td>&nbsp;</td><td align='right'>Account Type:</td><td><input type='text' name='bank_acc_type' size='50' value='$ubank_acc_type' /></td>
</tr>
<tr>
<td>Branch Name:</td><td><input type='text' name='bank_branch_name' size='50' value='$ubank_branch_name' /></td><td>&nbsp;</td><td align='right'>Branch Code:</td><td><input type='text' name='bank_branch_code' size='11' value='$ubank_branch_code' /></td>
</tr>
<tr>
<td>Account Number:</td><td><input type='text' name='bank_acc_num' size='25' value='$ubank_acc_num' /></td><td>&nbsp;</td><td align='right'>Statement Reference:</td><td><input type='text' name='bank_statement_ref' size='50' value='$ubank_statement_ref' /></td>
</tr>
</table>
</fieldset>


<fieldset>
<br />
<h4>Contract Details</h4>
<br />
<table style='font-size:12px' align='center' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>Commencement Date:</td><td><input type='text' name='contract_start' size='11' value='$ucotract_start' />
<!-- calendar attaches to existing form element -->
	<script language='JavaScript'>
	new tcal ({
		// form name
		'formname': 'update_supplier',
		// input name
		'controlname': 'contract_start'
	});

	</script>
	</td><td>&nbsp;</td><td align='right'>Review Date:</td><td><input type='text' name='contract_review' size='11' value='$ucotract_review' />
	<!-- calendar attaches to existing form element -->
	<script language='JavaScript'>
	new tcal ({
		// form name
		'formname': 'update_supplier',
		// input name
		'controlname': 'contract_review'
	});

	</script>
</td>
</tr>
<tr>
<td>Termination Date:</td><td><input type='text' name='contract_end' size='11' value='$ucotract_end' />
	<!-- calendar attaches to existing form element -->
	<script language='JavaScript'>
	new tcal ({
		// form name
		'formname': 'update_supplier',
		// input name
		'controlname': 'contract_end'
	});

	</script>
</td><td>&nbsp;</td><td align='right'>Notice Period:</td><td><input type='text' name='notice_period' size='50' value='$unotice_period' /></td>
</tr>
<tr>
<td>Total Value:</td><td><input type='text' name='total_value' size='50' value='$utotal_value' /></td><td>&nbsp;</td><td align='right'>Monthly Value:</td><td><input type='text' name='month_value' size='50' value='$umonth_value' /></td>
</tr>
</table>
<table style='font-size:12px' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>Notes:</td><td align='right'><textarea name='contract_note' rows='5' cols='85' value='$ucotract_note'></textarea>
</td>
</tr>
</table>
</fieldset>

	
<fieldset>
<br />
<h4>Response Times</h4>
<br />
<table style='font-size:12px' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>Low Priority:</td><td><input type='text' name='low_resp' size='3' value='$ulow_resp' /></td>
<td>
<select name='low_day_hour'>
<option value='$ulow_day_hour'>$ulow_day_hour</option>
<option value='hours'>Hours</option>
<option value='days'>Days</option>
</select>
</td>
<td>Normal Priority:</td><td><input type='text' name='norm_resp' size='3'  value='$unorm_resp'/></td>
<td>
<select name='norm_day_hour'>
<option value='$unorm_day_hour'>$unorm_day_hour</option>
<option value='hours'>Hours</option>
<option value='days'>Days</option>
</select>
</td>
</tr>
<tr>
<td>High Priority:</td><td><input type='text' name='high_resp' size='3'  value='$uhigh_resp'/></td>
<td>
<select name='high_day_hour'>
<option value='$uhigh_day_hour'>$uhigh_day_hour</option>
<option value='hours'>Hours</option>
<option value='days'>Days</option>
</select>
</td>
<td>Emergency Priority:</td><td><input type='text' name='emerg_resp' size='3'  value='$uemerg_resp'/></td>
<td>
<select name='emerg_day_hour'>
<option value='$uemerg_day_hour'>$uemerg_day_hour</option>
<option value='hours'>Hours</option>
<option value='days'>Days</option>
</select>
</td>
</tr>
</table>
</fieldset>


<fieldset>
<table style='font-size:12px' border='0' cellspacing='0' cellpadding='5' width='800px'>
<tr>
<td>General Notes:</td><td align='right'><textarea name='gen_notes' rows='5' cols='85' value='$gen_notes'></textarea>
</td>
</tr>
</table>
</fieldset>

<br />
<input type='Submit' value='update Supplier'>


</fieldset>
</form>
";     
   }
?>

FILE:upd_supl.php

<?php
include("include/session.php");

$p_1 = $_POST['s_code'];
$p_2 = $_POST['name'];
$p_3 = $_POST['t_name'];
$p_4 = $_POST['reg_num'];
$p_5 = $_POST['address_l1'];
$p_6 = $_POST['address_l2'];
$p_7 = $_POST['address_l3'];
$p_8 = $_POST['address_l4'];
$p_9 = $_POST['p_code'];
$p_10 = $_POST['region'];
$p_11 = $_POST['serv_type'];
$p_12 = $_POST['cap_amount'];
$p_13 = $_POST['vat'];
$p_14 = $_POST['vat_detail'];
$p_15 = $_POST['bee'];
$p_16 = $_POST['bee_detail'];
$p_17 = $_POST['contact'];
$p_18 = $_POST['tel'];
$p_19 = $_POST['mobile'];
$p_20 = $_POST['fax'];
$p_21 = $_POST['email'];
$p_22 = $_POST['www'];
$p_23 = $_POST['order'];
$p_24 = $_POST['pay_terms'];
$p_25 = $_POST['disc'];
$p_26 = $_POST['disc_terms'];
$p_27 = $_POST['bank'];
$p_28 = $_POST['bank_acc_type'];
$p_29 = $_POST['bank_branch_name'];
$p_30 = $_POST['bank_branch_code'];
$p_31 = $_POST['bank_acc_num'];
$p_32 = $_POST['bank_statement_ref'];
$p_33 = $_POST['contract_start'];
$p_34 = $_POST['contract_review'];
$p_35 = $_POST['contract_end'];
$p_36 = $_POST['notice_period'];
$p_37 = $_POST['total_value'];
$p_38 = $_POST['month_value'];
$p_39 = $_POST['contract_note'];
$p_40 = $_POST['low_resp'];
$p_41 = $_POST['low_day_hour'];
$p_42 = $_POST['norm_resp'];
$p_43 = $_POST['norm_day_hour'];
$p_44 = $_POST['high_resp'];
$p_45 = $_POST['high_day_hour'];
$p_46 = $_POST['emerg_resp'];
$p_47 = $_POST['emerg_day_hour'];
$p_48 = $_POST['gen_notes'];



$query="UPDATE `cmms`.`supplier` SET 
`s_code` = '$p_1', 
`name` = '$p_2',
`t_name` = '$p_3',
`reg_num` = '$p_4',
`address_l1` = '$p_5',
`address_l2` = '$p_6',
`address_l3` = '$p_7',
`address_l14` = '$p_8',
`p_code` = '$p_9',
`region` = '$p_10',
`serv_type` = '$p_11',
`cap_amount` = '$p_12',
`vat` = '$p_13',
`vat_detail` = '$p_14',
`bee` = '$p_15',
`bee_detail` = '$p_16',
`contact` = '$p_17',
`tel` = '$p_18',
`mobile` = '$p_19',
`fax` = '$p_20',
`email` = '$p_21',
`www` = '$p_22',
`order` = '$p_23',
`pay_terms` = '$p_24',
`disc` = '$p_25',
`disc_terms` = '$p_26',
`bank` = '$p_27',
`bank_acc_type` = '$p_28',
`bank_branch_name` = '$p_29',
`bank_branch_code` = '$p_30',
`bank_acc_num` = '$p_31',
`bank_statement_ref` = '$p_32',
`contract_start` = '$p_33',
`contract_review` = '$p_34',
`contract_end` = '$p_35',
`notice_period` = '$p_36',
`total_value` = '$p_37',
`month_value` = '$p_38',
`contract_note` = '$p_39',
`low_resp` = '$p_40',
`low_day_hour` = '$p_41',
`norm_resp` = '$p_42',
`norm_day_hour` = '$p_43',
`high_resp` = '$p_44',
`high_day_hour` = '$p_45',
`emerg_resp` = '$p_46',
`emerg_day_hour` = '$p_47',
`gen_notes` = '$p_48' WHERE `s_code`='$p_1';";

mysql_query($query);

mysql_close();

?>

Thank you

Max

Edited by trilithon: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.