Populate dropdown with data retrieved from database.

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

37 Replies - 77971 Views - Last Post: 16 May 2011 - 05:50 AM Rate Topic: -----

#1 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 08:14 AM

Hi All - Dream in Code Noobo here.

I am looking for a little guidance here. I have created a data summary page for my data with an "update" url. This URL passes the "Project_id" to another form where I have managed to populate all the relevant fields BUT some of these field are to be drop down fields. this form will be used to update date that has been already input previously.

As I say I can populate the fields but I need them to be drop downs with the "selected" data as the primary option in the "Select"
Ok so my existing code: this code sends the Project id which i use to populate the form..


 <!-- link to update_record.php and send value of project_id -->
<td><a href="update_record.php?Project_id=<?php echo $rows['Project_id']; ?>">update</a></td>


The form populates fine and here is the code I have.. I have removed most of the additional definitions.. $xyz = $_POST['xyz'] just to ease input here;


<?php
// connect to Dbase - username pass database name initial table
<?php include("db_connect.php"); ?>

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

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

$rows=mysql_fetch_array($result);
?>
<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><input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>">

// loads more fields with similar entries...... approx 5 will be dropdowns

<table>
<td>Project ID: </td><td><?php echo $rows['Project_id']; ?></td>
<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">


</td></tr>
</table></form>



this then gets passed to "update_record_ac.php" where it updates the MYSQL data. seems simple enough but I have hit a hump I cant get past.. any guidance greatly received.. This is an internal DB for about 4 people and I'm not worried about sql injections etc and yup i'm mixing Html and PHP.. I have figured that its something to do with how I am presenting the data but i'm stumped..

// I think this is where i'm messing up....
 <input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>"> 



Thanks for reading and again if you are able to assist..

Balgrath

Is This A Good Question/Topic? 0
  • +

Replies To: Populate dropdown with data retrieved from database.

#2 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 08:39 AM

If you're wanting make PHP make a <select></select> element then you'd just have to use a while loop.

<?php
//get results from database
?>
<select name="whatever">
<?php
while($row = mysql_fetch_assoc($results))
{
	?>
	<!-- Separated HTML and PHP -->
	<option value="<?php echo $row['whateveragain']?>"><?php echo $row['whateveragain']?></option>
	<?php
}
?>
</select>



Demonstrating how to select options as default
<?php
//get results from database
?>
<select name="whatever">
<?php
while($row = mysql_fetch_assoc($results))
{
	?>
	<!-- Separated HTML and PHP -->
	<option value="<?php echo $row['whateveragain']?>;?>"
	   <?php
		if($row['whateveragain'] == 'somevalue')
			echo ("selected=\"selected\"");
>
	   <?php echo $row['whateveragain']?>
	</option>
	<?php
}
?>
</select>



Also you should ALWAYS sanitize data input. Never place data straight into a database from a submitted form or whatever the case may be. If a user just happens to enter
Address: St. John's, Antigua
then you're going to have some serious problems.

This post has been edited by codeprada: 03 May 2011 - 08:46 AM

Was This Post Helpful? 2
  • +
  • -

#3 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 02:24 PM

Hi codeprada
Thanks for the response.. and appreciate I am not cleaning the data properly but that will follow. In essence yes this is what I am trying to achieve " <select></select> " BUT it the way that I am pulling the data and what I am trying to do is the issue.
My current code, albeit dirty works to retrieve the data I require. It places that data into simple input boxes (as you know)
The tough bit is retrieving this data into a select box where this result is the selected option and the other "dropdown options are in the dropdown box also.

In my 'addrecord.php' I use the following code to build my dropdowns

 from addrecord.php 

<tr><td>Financial Year: xxxx/xxxx</td><td>
<!-- pulls the data from the table FinancialYear 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. Contact Admin");
			if (!mysql_select_db($database))
				 die("Can't select database. Contact Admin");
		$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
		<?=$options?>
		</SELECT> 
	</td></tr>



Could I manipulate this code to achieve my gaol?? Your code above does allow me to build the dropbox but not populate the entries.. I have amended the code error on line 14 of the second code set. I think that I am unsure what the ' somevalue' should be. I think this is a static value but i'll not always know the value that goes here.

The
  $Project_id=$_GET['Project_id']; 
provides the relevant row details from the mysql DB. As I say I can currently retreive the correct info just not build the required dropbox with the correct retreived data and the remainder of the dropdown fields. I must also apologies as I should have added that the initial Select options come from a different table with in the same DB.

Am currently greatful for the time and code above but if you are able to spare some more time it would be happily received.

cheers
Balgrath
Was This Post Helpful? 0
  • +
  • -

#4 RPGonzo   User is offline

  • // Note to self: hmphh .... I forgot
  • member icon

Reputation: 151
  • View blog
  • Posts: 954
  • Joined: 16-March 09

Re: Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 02:39 PM

Curious if your code is not working as expected or??

You have the majority of it correct, but missed closing your static first option in your select.

<SELECT NAME="FinancialYear">
		<OPTION VALUE=0>Choose</OPTION>
		<?=$options?>
		</SELECT> 


Was This Post Helpful? 1
  • +
  • -

#5 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 02:58 PM

Hi and thanks for the reply...
sorry if it seems misleading. Currently the form I am populating works. it populates the fields with the correct data pulled from the database. As the form is to update the data there are about 5 or 6 fields that I wish to restrict the users input to.. this is where the select or dropdown comes into play.

What I am trying to do is two fold i guess, populate the dropdown with the data contained in the FinancialYear table and then using the store data in table Main set the focus on the stored value.

so the drop box say has 6 entries (could change so i want the dropbox to change dynamically) pulled from the Financialyear table;
1: year1
2: year2
3: year3

in the form the focus should then be on the correct entry as stored in table Main.

I use a table that selects * from table main and builds an update link using the Project_id field. this passes the correct id to my update_record form where I use $_GET to pull the data based on the Project_id.

I am stuck in building and populating the dropdown..... codeprada has aided and I can now build the dropdown but am unable to populate dropdown with the options from the FinancialYear table and select the data store in the table Main.

I want the user to be able if necessary to update the data from dropdown and the post this back into the database.

any thoughts.. I know I am over looking something

(again srory for the dirty code... it will be tidied later :o( )
Was This Post Helpful? 0
  • +
  • -

#6 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 03:15 PM

for completeness i have attached the files it might just show you what I currently have and possibly give you a better understanding....

There are a few fields I wish to introduce dropdowns for but I can rework these later One would be a great start. FinancialYear..

hope this aids you aid me. :o)
Thanks






Please scan for viruses - I also do and so should you.

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#7 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Populate dropdown with data retrieved from database.

Posted 03 May 2011 - 05:43 PM

Oh so what I'm getting from your explanation is that you want to read the database and only offer the user 5 or 6 options they can choose from...while still leaving the one that you retrieved from the database selected by default.

I got lost in your code but here's an example
//get results from database
$selected = $id == $row['id'] ? "selected=\"selected\"" : "";
$option .= "<option value=\"{$row['id']}\" $selected>{$row['id']}</option>";



I think this is what you wanted...
Was This Post Helpful? 1
  • +
  • -

#8 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 01:05 AM

The more I talk the more i seem to loose people. I think this is true of my typing too. :withstupid:
Thanks for taking the time to try and understand my issue. again yes this is what I am trying to achieve..

from the file in the attachment of which there are 3:
List_records.php - this initially pulls all data for all 31 fields and presents them in a neat table with a Update url(which is comprised of the Project_id which it sends to the address bar
Update_record.php - then pulls the Project_id and uses the Project_id to retrieve the individual record for update.
Update.php - then updates this to the database

This all works.. no issues.

I have another table "FinancialYear" with other data (id and a name). What is needed is the FinancialYear field in the update_record.php to build the dropdown from this date but the focus to be on the already entered data. So like i was saying in the earlier post..

"financialyear" table:

id* name
1 year1
2 year2
3 year3
4 year4
5 year5
*numbers may not be sequencial



"Main" table
.... 30 fields... one of which is FinancialYear with say year2 as its entered data.


so now when I open the update_record.php the form should build all records and the financialyear field should be a dropdown with the year2 preselected. I can then use the dropdown (populated from the financialyear table to change the record.

sorry if im not getting my issue across and thank you for your patience.


I think your initial code might be what I need but am unable to manipulate it to achieve what I need. I can get the dropbow to build but am unable to populate it let alone select the record.. :stupid:
Was This Post Helpful? 0
  • +
  • -

#9 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 04:33 AM

Post the section of code where you're trying to populate the dropdown.
Was This Post Helpful? 1
  • +
  • -

#10 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 04:52 AM

View Postcodeprada, on 04 May 2011 - 04:33 AM, said:

Post the section of code where you're trying to populate the dropdown.


This is the section of code I need to populate.. Currently this just pulls the record from the table "Main" and give me the correct record in the input box. I can manually change and successfully "update" the data but this is where i'd like the dropdown.

 
// get value of id that sent from address bar
$Project_id=$_GET['Project_id'];
//set vars...
$FinancialYear=$_POST['FinancialYear'];
// load more of these vars.....

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

$rows=mysql_fetch_array($result);
?>
<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><input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>">

// ......... other fields of similar nature... 

<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">

</form>
</center>

<?

// close connection 
mysql_close();

?>




The data for the FincancialYear dropdown are stored in table FinancialYear. So the dropdown should check the stored value in table "Main" then build the dropdown and focus on the same entry in the dropdown. This when changed will then update the record in table "Main"

I can see what I want just having difficulty tell you.. thanks again for your patience. I'm a PHP\mysql total noobo but am learning by the minute..
cheers
Balgrath
Was This Post Helpful? 0
  • +
  • -

#11 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6259
  • View blog
  • Posts: 24,028
  • Joined: 23-August 08

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 05:40 AM

$rows=mysql_fetch_array($result);

You're only fetching the first row.

We have a tutorial on this subject, as well as a tutorial on MySQL and PHP for beginners.
Was This Post Helpful? 0
  • +
  • -

#12 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 05:58 AM

I am retrieving
$sql="SELECT * FROM Main WHERE Project_id='$Project_id'";


this gives me the all the data to fill the remainder of the form fields based on the Project_id see this section of code..
$Project_id=$_GET['Project_id']; 
where it pulls the Project_id from the address bar as it was posted by list_records.php refer to the zip file.

As i say the form as it stands returns all the data relevant to the Project_id into its rightful locations. Thats not the issue.

I am looking for some of the fields to be dropdowns where the dropdown data is pulled from table "FinancialYear" but the entry in table "Main" is in focus on drop down.. and if I change the financial year from say 2010 to 2011 when I post this data the table "Main" is updated..

help that makes it clearer.
Balgrath
Was This Post Helpful? 0
  • +
  • -

#13 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 06:03 AM

This is how I have built my drop down for my frm_data_input.php
<?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>



I am looking for something like this with the focus on the data that is on the table "Main"

This post has been edited by Balgrath: 04 May 2011 - 06:07 AM

Was This Post Helpful? 0
  • +
  • -

#14 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 06:51 AM

View PostBalgrath, on 04 May 2011 - 09:03 AM, said:

I am looking for something like this with the focus on the data that is on the table "Main"


I'm getting it piece by piece...Correct me if I'm wrong.

When you retrieve a row from Main you will get the Financial Year ($rows['FinancialYear']). You would like this value to be used to retrieve the corresponding row in the FinancialYear table. You then would like to take that row returned from FinancialYear to be used to populate a dropdown list. The value retrieved should be selected by default as well.

That's a summary of what I got from your explanations so far.
Was This Post Helpful? 1
  • +
  • -

#15 Balgrath   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 03-May 11

Re: Populate dropdown with data retrieved from database.

Posted 04 May 2011 - 06:58 AM

not quite.. I think if i put it this way.

The last bit of code i uploaded is how I input the data into table "Main". it looks at the table Main and then builds the dropdown. in essence this is the dropdown I would like in my new form but as this form is updating the data already entered I need to see the existing data hence the "focus" part of the dropdown request..

is that confusing you and others more???
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3