Populate drop down list with values from SQL database
7 replies, posted
Basically I have a php form with a drop down box. I want the box to display degree names from a degree table containing details of different degree courses. How would I go about populating the list box with values from the table?
Any help appreciated :). Thanks guys.
What's the database structure?
[code]
DROP TABLE IF EXISTS student CASCADE;
CREATE TABLE student(
StudentId VARCHAR(8) NOT NULL,
FirstName VARCHAR(20) NOT NULL,
MiddleNames VARCHAR(20),
LastName VARCHAR(20) NOT NULL,
CurrentYear TINYINT(1) NOT NULL,
DOB DATE NOT NULL,
EmailAddress VARCHAR(320) NOT NULL,
PRIMARY KEY (StudentId)
);
DROP TABLE IF EXISTS researchdegree CASCADE;
CREATE TABLE researchdegree(
DegreeId VARCHAR(8) NOT NULL,
DegreeName VARCHAR(20) NOT NULL,
LengthYears TINYINT(1) NOT NULL,
PRIMARY KEY (DegreeId)
);
DROP TABLE IF EXISTS application CASCADE;
CREATE TABLE application(
AppId INT(7) NOT NULL auto_increment,
AppResult VARCHAR(9),
StudentId VARCHAR(8) NOT NULL REFERENCES student,
DegreeId VARCHAR(8) NOT NULL REFERENCES researchdegree,
PRIMARY KEY (AppId)
);
DROP TABLE IF EXISTS interview CASCADE ;
CREATE TABLE interview(
InterviewId INT(7) NOT NULL auto_increment,
IDate DATE NOT NULL,
ITime VARCHAR(5) NOT NULL,
Interviewer VARCHAR(50) NOT NULL,
AppId VARCHAR(8) NOT NULL REFERENCES application,
PRIMARY KEY (InterviewId)
);
DROP TABLE IF EXISTS reference CASCADE;
CREATE TABLE reference(
RefId INT(7) NOT NULL auto_increment,
SourceName VARCHAR(50) NOT NULL,
SourceEmail VARCHAR(320) NOT NULL,
RefContent VARCHAR(1000) NOT NULL,
AppId VARCHAR(8) NOT NULL REFERENCES application,
PRIMARY KEY (RefId)
);
DROP TABLE IF EXISTS viva CASCADE;
CREATE TABLE viva(
VivaId INT(7) NOT NULL auto_increment,
VDate DATE NOT NULL,
VTime VARCHAR(5) NOT NULL,
Supervisor VARCHAR(50) NOT NULL,
AppId VARCHAR(8) NOT NULL REFERENCES application,
PRIMARY KEY (VivaId)
);
DROP TABLE IF EXISTS examiner CASCADE;
CREATE TABLE examiner(
ExaminerId INT(7) NOT NULL auto_increment,
Phone VARCHAR(11),
Email VARCHAR(320),
PRIMARY KEY (ExaminerId)
);
[/code]
[editline]02:16PM[/editline]
The relevant table is research degree, I want to populate the list with the values from DegreeName
This should do what you need. I used the DegreeId as the value that will be submitted to whatever script this posts to. I'll make an example one and link it here shortly.
Edit: Here you go, example [url]http://snakes-servers.com/testing/dropdown.php[/url]
[code]<?php
//Database connect start here
$mysql_host = "IP HERE"; //database host here
$mysql_username = "USERNAME"; //database username
$mysql_password = "PASSWORD"; //database password
$link = mysql_connect("$mysql_host", "$mysql_username", "$mysql_password");
mysql_select_db("DATABASENAME",$link); //switch to the right database
//Database connect end here
//Querying the database
$query = mysql_query("SELECT DegreeId, DegreeName FROM researchdegree",$link);
?>
<html>
<head>
</head>
<body>
<!-- Start the dropdown -->
<select>
<?php
//loop through the query result and make options!
while($result = mysql_fetch_object($query)){ ?>
<option value="<?php print $result->DegreeId; ?>"><?php print $result->DegreeName; ?></option>
<?php } //end the loop ?>
<!-- end our select box -->
</select>
</body>
</html>
[/code]
Thanks for that, I've actually just got it working around 5 mins ago. My solution was pretty similar to what you posted there.
Here's my code:
[code]
<?php
//Connection data
$username="root";
$password="qwert";
$database="data";
//Open connection
mysql_connect(localhost,$username,$password);
//Select database
@mysql_select_db($database) or die( "Unable to select database");
//SQL Query
$query="SELECT DegreeId,DegreeName FROM researchdegree";
//Do Query
$result = mysql_query ($query);
echo "<select name='element_2' id='element_2'>";
while($row=mysql_fetch_array($result))
{
echo "<option value='$row[DegreeId]'>'$row[DegreeName]'</option>";
}
echo "</select>";
mysql_close();
?>
[/code]
Thanks for the help! :)
Nice. Well if you need anything else, just give me a prod. I'm just learning myself so it's nice to be able to help out.
Thanks for the offer there snake, you don't happen to know how to set up a html form that displays a single record from the sql database, has next and previous buttons and allows the user to edit the record and then update the database?
Sorry didn't keep up with the thread. Still need help? I can do the editing and saving. Might have to look something up for the next/prev buttons, but it shouldn't be a problem.
[editline]07:34AM[/editline]
Found your other thread, I'll post there.
Sorry, you need to Log In to post a reply to this thread.
