Retrieving values from SQL Table

[mb326]

Hi, I'm trying to retrieve a column of values from an SQL table and insert them into my HTML form as a drop-down menu using php. However, when I display the form in my browser, all I get is a drop-down showing $row["Hall"]. I've tried switching around the quotes and inserting single quotes instead, but this has not resolved the issue. Here's my code:

 

 

// run query to find list of halls

$query = mysqli_query($database, "SELECT * FROM Residences");

 

// print out the list of halls

$halls = mysqli_fetch_row($query);

foreach($halls as $row) {

  echo "<option> = $row["Hall"] $row["Hall"] </option>";

}

 

I was told by a group-mate of mine that this is much better than hardcoring the values into a form as it makes it easier for a user to extend and add new values through another form.

[AndyB]

echo "<option> = $row["Hall"] $row["Hall"] </option>";

 

Try this instead:

echo "<option value = '". $row['Hall']. "'>". $row['Hall']. "</option>\n";

[mb326]

That didn't work, sorry. Throws up an unexpected '"' in response

[AndyB]

Please post a few lines of the HTML code generated, including your select/option code.  If you're seeing '"' then $row['Hall'] likely has no value at the point where the code executes

[mb326]

Do you mean the rest of the php file I have developed which contains this class or the HTML output of the webpage I see when I click the form button to submit? If its the latter, it seems pointless as the page I direct to only says 'You have successfully added a record', even though it doesnt!.

[AndyB]

Actually I meant the html page that's generated and includes the select/options produced by your php code.

[mb326]

Okay, sorry for the confusion.

 

This part of the code generates the whole drop-down box, incuiding the part I've been finding problematic:

 

<p> Hall: <select name="halls">

 

<option value= "Brendon"> Brendon </option>

<option value= "Quarry"> Quarry </option>

<option value= "Mendip"> Mendip </option>

<option value= "Conygre"> Conygre </option>

<option value= "Quantock"> Quantock </option>

<option value= "Polden"> Polden </option>

<option value= "Derhill"> Derhill </option>

<option value= "Cotswold"> Cotswold </option>

<option value= "Wolfson"> Wolfson </option>

<option value= "Norwood"> Norwood </option>

<option value= "John Wood"> John Wood </option>

<option value= "Pultney Court"> Pultney Court </option>

<option value= "Clevelands & Canal Wharf"> Clevelands & Canal Wharf </option>

 

// run query to find list of halls

$query = mysqli_query($database, "SELECT * FROM Residences");

 

// print out the list of halls

$halls = mysqli_fetch_row($query);

foreach($halls as $row) {

  echo "<option value = $row["Hall"]> $row["Hall"] </option>";

}

 

<!--End of menu selection-->

</select> </p>

[atl_andy]

An easier way to make drop downs with PHP and MySQL is as follows (I have an application with a form that has 6 drop downs that are populated from mysql tables)

 

First, make a function that you can call in your app:

 

function dropDown ($table, $name) {
   $result = mysql_query("SELECT DISTINCT $name FROM $table ORDER BY $name");
   $first = "";  // Only necessary if you want a blank as the first option

   $dropdown = "<select tabindex=\"1\" name=\"$name\">";
   $dropdown .= "<option>$first</option>";
   while($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
       $dropdown .= "<option>$row[$name]</option>";
   }
   echo "</select>";
   return $dropdown;
}

 

Now, when you need to call the function later, all you need is the table name and query parameter.  Like so...

 

// Halls Drop Down
$table = halls;
$name = halls;

echo '<p>'.dropDown($name, $table).'</p>';

 

I have it set up so that the function is in an include file, so my code is cleaner.  This way, your dropdown is only 3 lines of code, which is nice when you get multiple dropdowns on a form.

[mb326]

OK, I've tried implementing the first part of your code in the class I'm using to retrieve the form results and the second part of your code in my form. However, in the line:

 

 echo '<p>'.dropDown($name, $table).'</p>';

 

I get an 'unexpected '>' ' error, after fiddling around with the single and double quotes, I still can't resolve the problem.

 

I've tried calling it with function using

 

 $um->dropDown($name, $table) 

 

but that still doesn't solve it.

[atl_andy]

Does the error tell you what line has the unexpected '>'?  Can you post your code?

[mb326]

Sure thing, here's my code and the error is thrown on line 54.

If you think it helps, I can upload my interpretation of the dropDown() function, though it's not much different from the one you showed me.

 

<?php

/**
* addStudent.php
* Written by: Matthew Bewers
* Last Updated: April 09, 2008
*/

$rootPath = './';
include($rootPath.'include/session.php');
include($rootPath.'include/template.php');
include('StudentContactEcho.class.php');
$template = new Template('Add a Student', $rootPath);
global $database;

$um = new StudentContact();

echo $template->header();

$content = "<div class =\"section\"><H1>Add A Student</H1>\n";
$content .= ' <form action="sendStudent.php" method="post">


<p>BUCS Username: 	<input type="text" name="BUCS_username" size="5"/> </p>
<p>First Name:		<input type="text" name="firstname" size="20"/> </p>

<!-Should be a drop down-->
<p>Surname:			<input type="text" name="surname" size="10"/> </p>
<p>	Hall:			<select name="halls">

				<option value= "Brendon"> Brendon </option>
				<option value= "Quarry"> Quarry </option>
				<option value= "Mendip"> Mendip </option>
				<option value= "Conygre"> Conygre </option>
				<option value= "Quantock"> Quantock </option>
				<option value= "Polden"> Polden </option>
				<option value= "Derhill"> Derhill </option>
				<option value= "Cotswold"> Cotswold </option>
				<option value= "Wolfson"> Wolfson </option>
				<option value= "Norwood"> Norwood </option>
				<option value= "John Wood"> John Wood </option>
				<option value= "Pultney Court"> Pultney Court </option>
				<option value= "Clevelands & Canal Wharf"> Clevelands & Canal Wharf </option>

// run query to find list of halls
$query = mysqli_query($database, "SELECT * FROM Residences"); 

// print out the list of halls
$halls = mysqli_fetch_row($query);
foreach($halls as $row) {
 echo '<p>'.dropDown($name, $table).'</p>';
}

// echo "<option> = $row["Hall"] $row["Hall"] </option>";

<!--End of menu selection-->
</select> </p>

<p>Room No:			<input type="text" name="roomNo" size="4"/> </p>
<p>Mobile:			<input type="text" name="mobileNo" size="11"/> </p>

<!-How to verify with earlier mobile?-->
<p>Confirm Mobile:	<input type="text" name="mobile2" size="11"/> </p>

<p><input type="submit" value="Add New Student" /></p>
</form>';

//--Display the form--
$content .="</div>";
echo $content;

echo $template->footer();

?>

[atl_andy]

If you use the function to make your drop downs, the only information you need here is the name of the table and the search parameter.  $table and $name

 

Also, you're using the mysqli extension, I use mysql.  You'll need to adjust for that in your code.

 

This:

// run query to find list of halls
$query = mysqli_query($database, "SELECT * FROM Residences"); 

// print out the list of halls
$halls = mysqli_fetch_row($query);
foreach($halls as $row) {
 echo '<p>'.dropDown($name, $table).'</p>';
}

// echo "<option> = $row["Hall"] $row["Hall"] </option>";

<!--End of menu selection-->
</select> </p>

 

Only needs to be this:

$name = Hall;            // These values depend on how your table is structured
$table = Residences;   // Same here....You want them to match the table name and column header of your table

echo '<p>'.dropDown($name, $table).'</p>';

 

 

All the other stuff is included in the dropDown function.  Just be sure to change it to match mysqli if you need to.

 

Also, I noticed a typo in the function

 

Change:

function dropDown ($table, $name) {

 

to:

function dropDown ($table, $name, $first) {

 

To account for the first blank option.