Populating dropdown menu from db

[wafflestomper]

Okay, I am trying to populate a drop down menu with items from a table in my db. 

I have a table called "teacher" which has the following info:

 

 

I use the following code to grab the info from the teacher table.

 

<?php
$conn = db_connect();
$query = "select teacher_name from teacher";
$result = $conn->query($query);
$row = $result->fetch_assoc();
?>	

 

I then populate the form using the following:

<form>
Teacher: 
<select name="teacher_menu">
	<option value="1"<?php echo ($_SESSION['teacher'] ==1 ? 'selected="selected"' : '') ?>><?php echo $row['teacher_name'];?></option>
	<option value="2"<?php echo ($_SESSION['teacher'] ==2 ? 'selected="selected"' : '') ?>><?php echo $row['teacher_name'];?></option>
	<option value="3"<?php echo ($_SESSION['teacher'] ==3 ? 'selected="selected"' : '') ?>><?php echo $row['teacher_name'];?></option>
	<option value="4"<?php echo ($_SESSION['teacher'] ==4 ? 'selected="selected"' : '') ?>><?php echo $row['teacher_name'];?></option>
	<option value="5"<?php echo ($_SESSION['teacher'] ==5 ? 'selected="selected"' : '') ?>><?php echo $row['teacher_name'];?></option>
</select>
</form>

 

The problem is each of the selections shows "Mr. Zollinhofer", but I want each teacher's name to show.  I know I am missing something simple, I just don't know what.

 

I guess one of my main questions is how to get the information I grabbed from the query and stored in $result to be displayed on a page...  If it indeed did grab all the teacher's names, how do I get to them?

 

If there is a better way to go about doing this, please feel free to suggest (I'm new to all this).  I feel like using a while statement may be easier, but I don't understand how to use it.  If you'd like to expand on that idea, feel free to as well! 

 

Thanks so much for your help.

[Garath531]

See if this works for you.

 

<?php
//Connects to database
$user = "mysql_username";
$password "mysql_password";
$host = "host";
$con = mysql_connect($host, $user, $password) or die(mysql_error());
mysql_select_db("db_name", $con) or die(mysql_error());
//Gets the information needed
$sql = "SELECT * FROM teacher";
$query = mysql_query($sql) or die(mysql_error());
//Displays the information in a drop down menu
echo "<form>
<select name='teacher_menu'>";
while($result = mysql_fetch_assoc($query) {
echo "<option value='".$result['teacher_id']."'>".$result['teacher_name']."</option>";
}
echo "</select>
</form>";
?>

I haven't tested this.

 

 

[Guest prozente]

Just as I was about to post you posted Garath531. You'll want to do something along the lines of

 

<?php
$link = mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());

mysql_select_db('database') or die('Could not select database');

$query = "select `teacher_id`,`teacher_name` from `teacher`";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>
<form>
Teacher:
<select name="teacher_menu">
<?php
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo '<option value="'.$row['teacher_id'].'"'.
        (isset($_SESSION['teacher']) && $_SESSION['teacher'] == $row['teacher_id'] ? 'selected="selected"' : '').'>'.
        $row['teacher_name'].'</option>';
}
?>
</select>
</form>

 

EDITED

 

[wafflestomper]

Thanks to both of you, that really helped.  If you could explain a couple of things to me so that I can be more knowledgeable in the future...

 

Lets say I have another drop down menu that selects schools this time...  would I need to do this part again?:

 

$link = mysql_connect('localhost', 'wepcorg_brian', 'yabanc1') or die('Could not connect: ' . mysql_error());

mysql_select_db('wepcorg_practice') or die('Could not select database');

 

or could I start right in with the new $query?

 

I understood what most of the code did, but I was a little confused about this part.  Could you talk me through what it is doing (if you don't mind, that is...):

 

	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo '<option value="'.$row['teacher_id'].'"'.
        (isset($_SESSION['teacher']) && $_SESSION['teacher'] == $row['teacher_id'] ? 'selected="selected"' : '').'>'.
        $row['teacher_name'].'</option>';
}

 

The main things I need some explanation about:

1.  what while is doing.

2.  what the "$row = mysql_fetch_array($result, MYSQL_ASSOC)" is doing

    *I think this is grabbing the $result (the teacher's names and ids) as well as the titles of the table they came from (using the MYSQL_ASSOC) and putting them into the $row var.  Is that right?

 

Thanks again for any help/explanations.  It has already been extremely helpful.

Also, just for posting code, how come some code comes up colored, and others just plain black?

[Guest prozente]

Lets say I have another drop down menu that selects schools this time...  would I need to do this part again?:

 

$link = mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());
mysql_select_db('database') or die('Could not select database');

You only have to connect once then you can run multiple queries. If you need to access another database tho you will need to use mysql_select_db to change to that database.

 

The main things I need some explanation about:

1.  what while is doing.

 

While is used to go through each row returned, everything inside the brackets of while is executed for each row that is returned.

 

2.  what the "$row = mysql_fetch_array($result, MYSQL_ASSOC)" is doing

    *I think this is grabbing the $result (the teacher's names and ids) as well as the titles of the table they came from (using the MYSQL_ASSOC) and putting them into the $row var.  Is that right?

You are correct, it's returning an array as $row, MYSQL_ASSOC tells it the format to return the array in. MYSQL_ASSOC tells it to use the column names for the array keys.

 

Also, just for posting code, how come some code comes up colored, and others just plain black?

 

The php tags trigger the colors.

 

for example with php tags

 

<?php echo (isset($error) ? 'ERROR: '.$error : ''); ?>

 

without

echo (isset($error) ? 'ERROR: '.$error : ''); 

[wafflestomper]

Great!  Thanks for the help.

[wafflestomper]

Alright, trying another one, but running into a little problem.

 

Here is my code for the next drop down menu:

 

	<?php
//Creates drop down for grade level selection
	$query = "select grade.grade_id, grade.grade_name, students.grade_id from grade, students";
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	?>
	<form>
	Grade:
	<select name="grade_menu">

	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
	  echo '<option value="'.$row['grade.grade_id'].'"'.'>'.
	        $row['grade_name'].'</option>';
	}
	?>
	</select>
	<br />

 

Here are the tables:

Grade Table

 

Students Table

 

The drop down menu populates correctly, except it shows each grade level 4 times.  1st, 2nd, 3rd... then back to 1st, 2nd, 3rd... and again 2 more times.  I changed the query to LIMIT 12, and that fixes the problem, but I'm sure that isn't the right way of going about it.  Help?

[Guest prozente]

sidenote: in the code you posted you're missing an opening <?php tag

 

Change the query to "select grade_id, grade_name from grade"

 

and

 

$row['grade.grade_id']

 

to

 

$row['grade_id']

[wafflestomper]

In trying to make the problem simpler, I left out one main thing.  If a person has already chosen their grade and that information is stored in the table "students" how do I show that in the query?  I need to get the value of grade_id from the students table.  I want the drop down to reflect that there is already a selection made by the user.  This worked as listed above, but I don't know how to get the query set up correctly (at least I think it is the query...)

 

<?php	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo '<option value="'.$row['grade_id'].'"'.
        (isset($row['students.grade_id']) && $row['students.grade_id'] == $row['grade.grade_id'] ? 'selected="selected"' : '').'>'.
        $row['grade_name'].'</option>';
}?>

[karyoker]

This code is for a phpbb2 forum and is a partial of an input form. If the user is logged in it automatically inserts their info in the input txt boxes. The same can be used for drop down using print loop as above..

Which they can edit or accept for input...

This is what shows up on the actual source code. Note the value="<? echo $userdata[user_email]; ?>"

 

Just another little twist...

<?php

        $userdata = session_pagestart($user_ip, PAGE_HELP);
// this page must be defined in includes/page_header.php, viewonline.php, admin/index.php and includes/constants.php.
init_userprefs($userdata);

   include_once "config2.php";

      	mysql_connect(DB_HOST, DB_UNAME, DB_PASS);
mysql_select_db(DB_DBNAME);

      $query  = "SELECT username, user_level, user_email FROM phpbb_users";
                       $result = mysql_query($query);


?> 

Then email for ex.

<tr>
				<td class="row1"><span class="genmed"><strong>E-mail address</strong> *</span></td>
				<td class="row2"><input type="text" class="post" style="width:200px" name="email" size="25" maxlength="255" value="<? echo $userdata[user_email]; ?>" /></td>
			</tr>
                <tr>

[wafflestomper]

I feel like it is a bit different with a drop down as there are 12 options, any of which may be the selected one.  I have the text fields working the correct way. 

 

It also looks as though the info is being pulled from one table vs two tables.  I'm sure it isn't hard to do, I just don't know how to do it. 

 

Thanks for the help, but being as new at this as I am, I need a little bit more specific/relevant help (specific to a drop down).  Not trying to be a pain, I'm just a moron for the first few weeks of learning... 

[Guest prozente]

select students.student_id, students.grade_id, grade.grade_name from students, grade where students.grade_id = grade.grade_id

 

This should help you

 

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

 

If you want to also return students without a grade match you would use like in the link above

 

select students.student_id, students.grade_id, grade.grade_name from students left join grade on students.grade_id = grade.grade_id

[wafflestomper]

By using the query you just set up, would it still grab the entire grade table?  I need all that information to populate the grade drop down table.  If I am understanding the code correctly, it will only grab the grade that is listed for the student that is listed in the students table.

[Guest prozente]

You need to be more clear on what you are trying to do as a dropdown can only have one value selected and what it appears you are trying to do is get all of the students information. Are you trying to create a dropdown for each student or just one dropdown for one student?

[wafflestomper]

Sure.

I want  a student to log in.

He can then go to his "accounts" page where he can set values up for his account:

1. Name

2. grade

3. teacher

4. school

 

I want 2-4 to be drop downs so we don't have to worry about misspellings. 

 

I want the grade drop down to list grades 1-12.  If they have already set that value, I want that value to be shown as selected in the drop down.

 

Same for teacher and school.

 

I have it working, but I doubt my code is written in the best manner, and was hoping for some tips to see what would be a better way of going about it.

 

you can see what I'm talking about at:

http://wepc.org/zollinhofer/member.php

username: casey

password: 1

 

click on update account to see the drop downs currently.

 

Here is the code I used.  Again, I'm new to all this and I'm sure there is a better way. 

<?php

db connect and that sort of stuff up here...

?>
<form>
First name: 
<input type="text" name="fname" value="<?php echo $_SESSION['fname']; ?>"/>
<br>
Last name: 
<input type="text" name="lname" value="<?php echo $_SESSION['lname']; ?>"/>
<br>

<?php
//creates drop down for school

	$query = "select school_id, school_name from school order by school_name asc";
	$query2 = "select school_id from students where username = '$username'";
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
	$s_school_id = mysql_fetch_array($result2, MYSQL_ASSOC);
	$_SESSION['s_school_id'] = $s_school_id['school_id'];
	?>
	<form>
	School:
	<select name="school_menu">
	<?php
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
	  echo '<option value="'.$row['school_id'].'"'.
	        ($_SESSION['s_school_id'] == $row['school_id'] ? 'selected="selected"' : '').'>'.
	        $row['school_name'].'</option>';

	}
		?>
	</select>
	<br />

<?php
//Creates drop down for grade level selectio
	$query = "select grade_id, grade_name from grade";
	$query2 = "select grade_id from students where username = '$username'";
	$result = mysql_query($query) or die('Query failed: ' . mysql_error());
	$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
	$s_grade_id = mysql_fetch_array($result2, MYSQL_ASSOC);
	$_SESSION['s_grade_id'] = $s_grade_id['grade_id'];
	?>
	<form>
	Grade:
	<select name="grade_menu">
	<?php
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
	  echo '<option value="'.$row['grade_id'].'"'.
	        ($_SESSION['s_grade_id'] == $row['grade_id'] ? 'selected="selected"' : '').'>'.
	        $row['grade_name'].'</option>';

	}
	?>
	</select>
	<br />
	<?php

//Creates drop down for teacher selection
$query = "select `teacher_id`,`teacher_name` from `teacher`";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
?>
Teacher:
<select name="teacher_menu">
<?php
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  echo '<option value="'.$row['teacher_id'].'"'.
        (isset($_SESSION['teacher_id']) && $_SESSION['teacher_id'] == $row['teacher_id'] ? 'selected="selected"' : '').'>'.
        $row['teacher_name'].'</option>';
}
?>
</select>
</form>	


<?
// give menu of options
display_user_menu();

do_html_footer();
?>

 

 

[Guest prozente]

Try to combine code with similar functions

 

You have two queries

 

select grade_id from students where username =

select school_id from students where username =

 

You can combined these into a single one and you can initiate your session variables at the same time.

 

So instead  you'd use

select grade_id,school_id from students where username =

 

The only other thing is if you don't have magic quotes on then ensure you run any user input through mysql_escape_string before passing it through the query

 

[wafflestomper]

Sounds good.  Can you show me what that looks like exactly? (the mysql_escape_string)

[Guest prozente]

For instance this line of code

 

$query = "select grade_id, school_id from students where username = '$username'";

 

should be

 

$query = "select grade_id, school_id from students where username = '".mysql_escape_string($username)."'";

 

http://us.php.net/manual/en/function.mysql-escape-string.php

[wafflestomper]

I have magic_quotes_gpc on but the other magic_quotes options are off.  Do I still need to worry about using the escape_string?

[Guest prozente]

It's still good to check if it is on or not in case the web application is moved to a different host and magic quotes is off. By that time you may not be thinking of magic quotes, one way you could approach this is

 

$query = "select grade_id, school_id from students where username = '".(get_magic_quotes_gpc() ? $username : mysql_escape_string($username))."'";

 

It will check to see if magic quotes is on, if it is the username would be returned, if magic quotes is off it will return the result after passing it through the mysql_escape_string function

[wafflestomper]

So every time I have a variable in a query, I need to magic_quote it?  That seems like a lot of magic quotes...

[Guest prozente]

Only times you have untrusted user input. You need to make sure the data is escaped properly

[wafflestomper]

Can't I just trust everyone?