• We've upgraded our forums. Please post any issues/requests in this thread.

Populating drop down menu

Joined
Jan 9, 2010
Messages
481 (0.17/day)
Likes
117
Location
Kansas
System Name Late 2013 rMBP 13'' w/ 250 GB SSD
Display(s) Dell P2416D @ 2560x1440 & Dell U2211H @ 1920x1080
Audio Device(s) NuForce uDAC-2 w/ Klipsch Promedia 2.1 & Sennheiser HD595
Mouse Logitech G400 @ 1600 DPI
Keyboard Razr Black Widow
Software OS X
#26
Wasley, I went ahead and coded what you are trying to accomplish after I saw your screenshot of the MySQL database you are working with.

Here is my version quick and dirty, nothing fancy... just JavaScript, HTML, and PHP.

Working version of what I think you want here - http://bookstur.com/mcases/index.php

index.php below
Code:
<?php include 'dbinfo-mcases.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mcases</title>
<script type="text/javascript">
function submit() {
document.forms["select"].submit();
}
<?php if (isset($_GET['caseReference'])){ // this gets the item only if user is changing selection in the listbox
$caseReference = $_GET['caseReference']; ?>
function setFocus()
{
document.getElementById("caseReference").focus();
document.getElementById('caseReference').value = "<?php echo $_GET['caseReference'];?>";
}
<?php } ?>
</script>
</head>
<body onload="setFocus();">
<h1 style="font-family:verdana;">
Mcases
</h1>
<p style="font-family:arial">
<form name="select" id="select" action="" method="get">
<select name="caseReference" id="caseReference" onchange="submit();" tabindex="1">
<?php 
$result = mysqli_query($dbc, "SELECT distinct caseReference from mcases ");
$i = 0;
while($row=mysqli_fetch_array($result)) {
$caseArray[$i] = $row['caseReference'];
$i++;
}
sort($caseArray);
foreach ($caseArray as $option) {
echo "<option value='" . $option . "'>" . $option . "</option>";
}
?> 
</select> 
</form>
<br />	
<table border="1" cellpadding="10">
  <tr>
	<td>Case ID</td>
	<td>Exhibit ID</td>
	<td>Date / Time</td>
	<td>File Size</td>
	<td>Camera Make</td>
	<td>Camera Model</td>
	<td>Height</td>
	<td>Width</td>
	<td>Longitude</td>
	<td>Latitude</td>
	<td>Map</td>
  </tr>			
<?php
if (!empty($_GET['caseReference'])) { // if user is selecting a caseReference from select listbox
$caseResult = mysqli_query($dbc, "SELECT * FROM `mcases` WHERE caseReference='".$_GET['caseReference']."' ");
	while($caseReference = mysqli_fetch_array($caseResult)) {
		echo '<tr><td>';
		echo $caseReference['caseReference'];
		echo '</td><td>';
		echo $caseReference['exhibitReference'];
		echo '</td><td>';
		echo $caseReference['dateTimeCreated'];
		echo '</td><td>';
		echo $caseReference['fileSize'];
		echo '</td><td>';
		echo $caseReference['cameraMake'];
		echo '</td><td>';
		echo $caseReference['cameraModel'];
		echo '</td><td>';
		echo $caseReference['height'];
		echo '</td><td>';
		echo $caseReference['width'];
		echo '</td><td>';
		echo $caseReference['longitude'];
		echo '</td><td>';
		echo $caseReference['latitude'];
		echo '</td><td>';
		echo '<a href="https://maps.google.co.uk/maps?q='.$caseReference['longitude'].',-2.102723">View on map</a>';
	} // end of while
} elseif (!empty($caseArray)) { // if db is populated and page is being shown for first time without any interaction from the select listbox, no GET method submitted
$caseResult = mysqli_query($dbc, "SELECT * from `mcases` WHERE caseReference='".$caseArray[0]."' ");
	while($caseReference = mysqli_fetch_array($caseResult)) {
		echo '<tr><td>';
		echo $caseReference['caseReference'];
		echo '</td><td>';
		echo $caseReference['exhibitReference'];
		echo '</td><td>';
		echo $caseReference['dateTimeCreated'];
		echo '</td><td>';
		echo $caseReference['fileSize'];
		echo '</td><td>';
		echo $caseReference['cameraMake'];
		echo '</td><td>';
		echo $caseReference['cameraModel'];
		echo '</td><td>';
		echo $caseReference['height'];
		echo '</td><td>';
		echo $caseReference['width'];
		echo '</td><td>';
		echo $caseReference['longitude'];
		echo '</td><td>';
		echo $caseReference['latitude'];
		echo '</td><td>';
		echo '<a href="https://maps.google.co.uk/maps?q='.$caseReference['longitude'].',-2.102723">View on map</a>';
	} // end of while?>
</table>
<?php } // end of else ?>
</p>
</body>
</html>
dbinfo-mcases.php below
Code:
<?php
/*Define constant to connect to database */
DEFINE('DATABASE_USER', 'mcases');
DEFINE('DATABASE_PASSWORD', '123'); // no this isn't the password i used and mine is behind root tree :~P
DEFINE('DATABASE_HOST', 'localhost');
DEFINE('DATABASE_NAME', 'mcases');

// Make the connection:
$dbc = @mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD,
 DATABASE_NAME);

if (!$dbc) {
 trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
}

?>


This isn't near elegant, but it works. I think you should look into database design and normalization also. Although you are new to PHP you should also learn about sanitizing input, escaping, and anything/everything security. And this script is by no means secure, there is no escaping at all. Go ahead, inject some JavaScript in the GET method and see what happens :D

I'm not really sure what you're trying to accomplish, but it's always great to learn and I thought I'd help you since it appears you can learn quickly :toast:

/edit

Version #2 - Does the exact same thing, without the messy JavaScript in the header. It's still messy, but this is using the method I suggested earlier in my previous posts. All the other code below this remains the same.
Code:
<?php include 'dbinfo-mcases.php'; 
if (isset($_GET['caseReference'])){ // this gets the item only if user is changing selection in the listbox
$caseReference = $_GET['caseReference']; 
}?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mcases</title>
</head>
<body>
<h1 style="font-family:verdana;">
Mcases
</h1>
<p style="font-family:arial">
<form name="select" id="select" action="" method="get">
<select name="caseReference" id="caseReference" onchange="if (this.value) window.location.href=this.value" tabindex="1">
<?php 
$result = mysqli_query($dbc, "SELECT distinct caseReference from mcases ");
$i = 0;
while($row=mysqli_fetch_array($result)) {
$caseArray[$i] = $row['caseReference'];
$i++;
}
sort($caseArray);
foreach ($caseArray as $option) { ?>
<option value="<?php echo $_SERVER['PHP_SELF'].'?caseReference='.$option; ?>" <?php if (isset($_GET['caseReference']) && $option == $_GET['caseReference']){ echo 'selected="selected"'; } ?>><?php echo $option ?></option>
<?php
}
?> 
</select>
Coding... millions of ways to accomplish the same thing and billions of reasons to argue at which is more efficient and easier to read... Bah I realized I don't need the foreach loop I should have just sorted using SQL... oh well, I'm not changing it again lol
 
Last edited:
Joined
Jun 30, 2008
Messages
1,135 (0.33/day)
Likes
67
Location
England
System Name Wasleys PC
Processor Intel i5 2400 3.10GHz
Motherboard Asus P8z68-V
Cooling AC Freezer Pro
Memory Kingston 4GB (2x2GB) DDR3 Hyperx Memory
Video Card(s) HIS ATi 6850 1GB DDR3
Storage Seagate ST3500320AS 500GB Hard Drive SATA II 7200rpm *32MB Cache*
Case Antec 900 with mods
Audio Device(s) On Board
Power Supply OCZ Stealth Xtream 500W
Software Windows 7
#27
Ygapm