[SOLVED] Getting single field from table in a field

[nvee]

Hey Guys

 

Ill get right into it. I have a report.php page with 2 form fields, both being dropdowns from a table in the database called course_student. The one represents the actual course and the other one the year (so the user can then see which students registered for what courses in what year).

 

The actual table consists of 3 fields namely sno, cid and year (student number, course id and year) - When a student registers for a course a new entry is made into this table with the student number, the course ID and then the year of registration.

 

What I want to do now is to have a dropdown of the courses to select, which works 100%, and my logic told me I could use the same code to generate the year, but now it makes a year entry for each course in the database. so meaning the dropdown has 4 "2009"s, 1 for each registered student.

 

What needs to happen is that it must only show a single year e.g. 2009 once, and next year 2010 should a new registration for a course take place per student. I could make the dropdown list static by manually entering the years, but that would then mean the user can search for student/course registrations in  2011, 2012+ and would have no results. I only want results to show of actual years where there are entries.

 

Here is my code for the form:

 

// This part is to display the courses in the database, no problem here!
<select name="course" id="course">
                            <?php
							$dbx = mysql_connect("localhost","root","");	
							if(!$dbx) die("The connection to the database could not be made!");
							$db = mysql_select_db("$dbname");
							if(!$db) die("Cannot connect to database to obtain course values");
							$result = mysql_query("SELECT * FROM course");
								while ($row = mysql_fetch_array($result)) {
								echo "<option value='" . $row["cid"] . "'>" . $row["cname"] . "</option>";
								}
								?>
                          </select>
                                </td>
                              </tr>
                              <tr>
                                <td>Year:</td>
                                <td>
// This is the code which should generate the years (obviously view all years will show all registrations per course without having the year condition.
                                <select name="year" id="year">
                            <?php
							$dbx = mysql_connect("localhost","root","");	
							if(!$dbx) die("The connection to the database could not be made!");
							$db = mysql_select_db("$dbname");
							if(!$db) die("Cannot connect to database to obtain course values");
							$result = mysql_query("SELECT year FROM course_student");
								while ($row = mysql_fetch_array($result)) {
								echo "<option value='" . $row["year"] . "'>" . $row["year"] . "</option>";
								}
								echo "<option value='all'>View all years</option";
								?>
                          </select>

[DavidAM]

 SELECT DISTINCT year FROM course_student

 

Make note of the fact that you could end up with gaps if there are no students registered for any course for a particular year (highly unlikely with this data, I think, but something to keep in mind if you try this approach with other data).

 

by the way, you do not need to connect to the server and database for every query.  You can connect once at the beginning of the script and use the same connection throughout.

[nvee]

Thank you for your feedback!

 

This will definately help A LOT

 

I was under the impression you have to start a new database connection with every new <?php ?>, thought it automatically closes the database. When in a page is the database then closed?

[DavidAM]

Not until it is done processing the entire file.