Create dropdown from one table and set focus based on entry in another ..

[balgrath]

Hi all,

firstly apologies as this is a cross post from another forum and we have hit a block.. I am hoping that opening this up to another set of gurus we can get a resolution.

 

What I am trying to achieve is this...

I have 2 tables Main and FinancialYear. Main holds all data which I use a form to post the data to it..(all works fine). I use this code to create a drop down in the insert.php form. again this works.

<tr><td>Financial Year: xxxx/xxxx</td><td>
<!-- pulls the data from the table variable to populate the dropdown menu -->
   <?php
      $database = 'Projects_Main';
      $fintable = 'FinancialYear';
         if (!mysql_connect($db_host, $db_user, $db_pwd))
               die("Can't connect to database 'cos somethin' is wrong");
         if (!mysql_select_db($database))
             die("Can't select database");
      $result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear");
      $options="";
         while ($row=mysql_fetch_array($result)) 
            {
               //$id=$row["FinancialYear_id"];
               $thing=$row["FinancialYear"];
               $options.="<OPTION VALUE=\"$thing\">".$thing.'</option>';
            }
   ?>
      <SELECT NAME="FinancialYear">
      <OPTION VALUE=0>Choose</OPTION>
      <?=$options?>
      </SELECT> 
   </td></tr>

 

What I have done is built another form which list all records in the database and creates an update url for every record that passes the field Project_id where i use $_get to retrieve the Project_id to retrieve the relevant data into the update.php form.

 

I am able to populate the form with all the correct information BUT I am looking to introduce some dropdowns to aid updating the data and provide consistency to the data. .

 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db('Projects_Main')or die("cannot select DB");

// get value of id that sent from address bar
$Project_id=$_GET['Project_id'];
//define vars
$FinancialYear=$_POST['FinancialYear'];
// other vars defined here also.. about 30

// Retrieve data from database 
$sql="SELECT * FROM Main WHERE Project_id='$Project_id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
//update_record_ac.php posts the data to the dbase.
<form name="form1" method="post" action="update_record_ac.php">
<center>
<table>
<tr><td><b>Store Details<b></td></tr>
<tr><td>Financial Year:</td><td>
// takes the data from $rows and present to form
<input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>">

// this is where I need to create the drop down.. see my other comments in the post.....

</td></tr>

 

the financialYear table consists if the following;

financialyear_id - pri, auto inc. ---- data format is 2010/2011, 2011/2012....

financialyear

 

the main table contains 30 fields .. won't list em all...

Project_id - pri, auto inc

financialyear

 

I need the drop down to pull the data from the financialyear table and then to present or focus on the currently stored data...

so if the store value in the table Main is 2010/2011 if Ii was to select the update url in the list_record.php it will pull all the relevant data into update_record.php form. the financialyear field in the form should be a dropdown with all the financial years listed but the 2010/2011 is selected or focused. I still need to be able to change the entry and post this back to the table Main.....

 

So the dropdown contains the list of years from the financialyear table but when the record is pulled from table main the year that is stored in table Main should be highlighted in the dropdown and I should be able to select a new record and post back to the table Main..

 

 

any thoughts...

 

please don't slate for the cross post, I haven't sanatised the data at any stage. I know i'm open to injection attacks. and yes my code is a little dirty... all these will be rectified as i finalise the process and ensure the consept works.

 

Thanks for taking the time to read and hopefully you are able to understand the requirement and are able to assist.

 

thanks

Balgrath

[balgrath]

Hi All,

I'm looking to give this a BUMP and see if anyone has any ideas with regards this post.  if anything needs clarification please let me know. Thanks

Balgrath

[balgrath]

Please note this code is very "Dirty".  In that I mean it works but isn't easy to read etc.. (so I have been told)  I know what it does and how to update it and have the life commented out of it...  here is the code with out the comments..

<?php

		if (!mysql_connect($host, $username, $password))
			   die("Can't connect to database 'cos somethin' is wrong");
		if (!mysql_select_db($db_name))
			 die("Can't select database");
	$result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear");
	$options="";
		while ($row=mysql_fetch_array($result)) 
			{
				$id=$row["FinancialYear_id"];
				$thing=$row["FinancialYear"];
				$options.="<OPTION VALUE=\"$thing\">".$thing.'</option>';
			}
?>
	<SELECT NAME="FinancialYear">
	<OPTION VALUE="<?php echo $rows['FinancialYear']; ?>"><?php echo $rows['FinancialYear']; ?></OPTION>
	<? echo $options?>
	</SELECT>

 

basically what this code does is builds a drop down form another table called FinanciaYear. you can see in the...

 $result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear");

 

It builds the dropdown in the format I want how i want...

 $options="";
		while ($row=mysql_fetch_array($result)) 
			{
				$id=$row["FinancialYear_id"];
				$thing=$row["FinancialYear"];
				$options.="<OPTION VALUE=\"$thing\">".$thing.'</option>';
			}
?>
	<SELECT NAME="FinancialYear">
	<OPTION VALUE="<?php echo $rows['FinancialYear']; ?>"><?php echo $rows['FinancialYear']; ?></OPTION>
	<? echo $options?>
	</SELECT> 

 

it also in the last section sets the value in the dropdown box to be the  stored value in the other table Main..

 

<SELECT NAME="FinancialYear">
	<OPTION VALUE="<?php echo $rows['FinancialYear']; ?>"><?php echo $rows['FinancialYear']; ?></OPTION>
	<? echo $options?>
	</SELECT>

 

Again this code is super all over the place but it does work.  I would suggest you play with it and tidy it up if you are going to use it.

 

this code below may give you a better understanding of how I have used the code.

<?php
$host="localhost"; // Host name 
$username="THE USER"; // Mysql username 
$password="THE PASSWORD"; // Mysql password 
$db_name="Projects_Main"; // Database name 
$tbl_name="Main"; // Table name
$fintable = 'FinancialYear';// Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db('Projects_Main')or die("cannot select DB");

// get value of id that sent from address bar
$Project_id=$_GET['Project_id']; //get the Project_id from the address bar

//define fields
$FinancialYear=$_POST['FinancialYear']; 

// Retrieve data from database 
$sql="SELECT * FROM Main WHERE Project_id='$Project_id'";
$result=mysql_query($sql);
//build array
$rows=mysql_fetch_array($result);
?>
<form name="form1" method="post" action="update_record_ac.php">
<center>
<table>
<tr><td><b> Details<b></td></tr>
<tr><td>Financial Year:</td><td>
<?php

		if (!mysql_connect($host, $username, $password))
			   die("Can't connect to database 'cos somethin' is wrong");
		if (!mysql_select_db($db_name))
			 die("Can't select database");
	$result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear");
	$options="";
		while ($row=mysql_fetch_array($result)) 
			{
				$id=$row["FinancialYear_id"];
				$thing=$row["FinancialYear"];
				$options.="<OPTION VALUE=\"$thing\">".$thing.'</option>';
			}
?>
	<SELECT NAME="FinancialYear">
	<OPTION VALUE="<?php echo $rows['FinancialYear']; ?>"><?php echo $rows['FinancialYear']; ?></OPTION>
	<? echo $options?>
	</SELECT>
</td></tr>

//there is more code after this but is basically a tonnes of other fields...



// this just shows the Project_id
<td>Project ID: </td><td><?php echo $rows['Project_id']; ?></td>
//submit
<input name="Project_id" type="hidden" id="Project_id" value="<?php echo $rows['Project_id']; ?>"> <td> &nbsp </td><td> &nbsp </td>
<input type="submit" name="Submit" value="Update"> 

 

 

Hopefully this might help someone.. remember this is very badly coded as i'm not a programmer  :-\ only a NOBO hence its super messy.. did i mention its messy?? 

 

Balgrath