1. Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

Populating drop down menu

Discussion in 'Programming & Webmastering' started by Wozzer, Aug 14, 2012.

  1. Ross211

    Ross211

    Joined:
    Jan 9, 2010
    Messages:
    473 (0.26/day)
    Thanks Received:
    115
    Location:
    Kansas
    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: Aug 15, 2012
    Wozzer says thanks.
  2. Wozzer

    Joined:
    Jun 30, 2008
    Messages:
    1,133 (0.48/day)
    Thanks Received:
    68
    Location:
    England
    Ygapm
     

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page