Use dropdown box to filter data on same page

WAMFT1 · February 7, 2014

Can anyone help me please?

I am trying to populate my page with data that is filtered from the dropdown box. It can either be "onchange" or clicking a button, not too fussed on this.

I have tried so many ways but getting more and more lost an confused and ended up with nothing at all. I have stripped the coding back to where it last worked so any help is appreciated.

<body>
<table width="680" border="0" align="center" cellpadding="4" cellspacing="0">
  <tr> <td> </td>
  </tr>
  <tr><td class="text-heading"><p>Financial Services Guides and Adviser Profiles</p><p class="Text_Heading_Black1">PAGE STILL IN DEVELOPMENT</p>
      <form id="form1" name="form1" method="post" action="">
      <span class="text-questions">Adviser: </span>
        <select name="advselect" id="advselect">
          <option value=" " selected="selected"> </option>
  <?php
require ('../edb.php');

if (!$con) {
    die("MySQL could not connect!");
}

$result= mysql_query("SELECT id, LastName, FirstName, UserType FROM `eusers` WHERE UserType='ADV' or UserType='STF' ORDER by LastName ASC");

while ($row = mysql_fetch_array($result)){
    echo '<option value="'. $row['id'] .'">'. $row['LastName'] .', '. $row['FirstName'] .'</option>';
}

?>
        </select>
        <input type="submit" name="advselect" id="advselect" value="Filter / View" />
      </form></td>
  </tr>
  <tr><td><p class="Text_SubHeading">Current Version    
    </p>
      <table width="650" border="0" align="center" cellpadding="4" cellspacing="0"> 
        <tr class="text-sectionheading">
          <td width="100">Date </td>
      <td width="150">Adviser</td>
      <td width="200">Document</td>
      <td width="100">Version</td>
      <td width="50">Current</td>
      <td width="50">View</td>
      </tr>
    
    <?php
			include("../edb.php");
			
				
			$data=mysql_query("SELECT * FROM `adocs_fsg_profile` INNER JOIN `eusers` ON eusers.id = adocs_fsg_profile.AdviserCode WHERE Current='Y' and ORDER BY LastName ASC, FirstName ASC, DocName ASC, DateUploaded DESC");

			
			while($test = mysql_fetch_array($data))
			{
				$id = $test['id'];	
				echo"<tr>";
				echo"<td class='text-questions'>".$test['DateUploaded']."</td>";
				echo"<td class='text-questions'>".$test['LastName'].", ".$test['FirstName']."</td>";	
				echo"<td class='text-questions'>".$test['DocName']."</td>";	
				echo"<td class='text-questions'>".$test['Version']."</td>";	
				echo"<td class='text-questions'>".$test['Current']."</td>";	
				echo"<td><a class='".$test['cssclass']."'href =".$test['URL'].">".$test['ImageType']."</a>";
				echo "</tr>";
			}
			mysql_close($conn);
			?>
  </table>
      <p class="Text_SubHeading">Previous Versions </p>
      <table width="650" border="0" align="center" cellpadding="4" cellspacing="0"> 
        <tr class="text-sectionheading">
          <td width="100">Date </td>
      <td width="150">Adviser</td>
      <td width="200">Document</td>
      <td width="100">Version</td>
      <td width="50">Current</td>
      <td width="50">View</td>
      </tr>
    
    <?php
			include("../edb.php");
			
				
			$data=mysql_query("SELECT * FROM `adocs_fsg_profile` INNER JOIN `eusers` ON eusers.id = adocs_fsg_profile.AdviserCode WHERE Current='N' ORDER BY LastName ASC, FirstName ASC, DocName ASC, DateUploaded DESC");

			
			while($test = mysql_fetch_array($data))
			{
				$id = $test['id'];	
				echo"<tr>";
				echo"<td class='text-questions'>".$test['DateUploaded']."</td>";
				echo"<td class='text-questions'>".$test['LastName'].", ".$test['FirstName']."</td>";	
				echo"<td class='text-questions'>".$test['DocName']."</td>";	
				echo"<td class='text-questions'>".$test['Version']."</td>";	
				echo"<td class='text-questions'>".$test['Current']."</td>";	
				echo"<td><a class='".$test['cssclass']."'href =".$test['URL'].">".$test['ImageType']."</a>";
				echo "</tr>";
			}
			mysql_close($conn);
			?>
  </table></td>
  
  </tr>
</table>
</body>

Edited February 7, 2014 by WAMFT1

sKunKbad · February 7, 2014

If after posting your value is available using $_POST['advselect'] then you will validate that value and use it in your query.

mac_gyver · February 7, 2014

i have some suggestions concerning your code -

1) you need to separate your 'business logic' (the php logic that is determining what to do on the page and producing the content that will be displayed) from your 'presentation logic' (minimal php code and the html/javascript/css making up the output you are sending to the browser.) basically, a majority of the php code will be first on the page, following at the end by essentially a html template that is just echoing php variables or at most looping over arrays of data producing the html output from that data.

this business logic would be where you test for and validate the filter values that are received by the page, that are then used to determine what to query for.

2) you need to make one database connection. you are currently including/requiring the connection code multiple times and since you are testing if the connection worked in you main code, i can only assume you are not selecting a database, resulting in queries that are failing with errors. you are also closing the connection at several points. this opening/closing of the database connection takes a significant amount of time, to the point that you will probably notice a difference in the page generation time if you just open one connection at the start and close that one connection at the end (or just let php close it automatically when the script ends.)

3) you need to ALWAYS have error checking logic for your sql queries. the suggestion in a previous thread was not just for debugging when things don't work. when a query error does occur, you need to display that when developing code and log it when on a live server.

4) your filtering determines that the page will display. your html forms should use method='get' as you are determining what will be gotten and output on the page. this will also simplify the logic of persisting those filters between page requests as they will already be present in the url/$_GET array.

5) lastly, the mysql (no i on the end) functions are depreciated. all new code needs to be written using the mysqli or pdo database libraries so that you don't need to rewrite your code in the near future.

edit: 6) i just noticed you are referencing both a $con and a $conn database connection variable.

Edited February 7, 2014 by mac_gyver

mac_gyver · February 7, 2014

example (untested) showing some of the suggestions -

<?php
require ('../edb.php');

if (!$con) {
    die("MySQL could not connect!");
}

// any input filtering validation would go here...


// get data for Adviser drop-down
$result= mysql_query("SELECT id, LastName, FirstName, UserType FROM `eusers` WHERE UserType='ADV' or UserType='STF' ORDER by LastName ASC");
$adviser_data = array();
while ($row = mysql_fetch_assoc($result)){
    $adviser_data[] = $row;
}

// get data for main content (if current can have more values then y or n, this query needs a where clause added back in to just return y or n values)
$data=mysql_query("SELECT * FROM `adocs_fsg_profile` INNER JOIN `eusers` ON eusers.id = adocs_fsg_profile.AdviserCode ORDER BY LastName ASC, FirstName ASC, DocName ASC, DateUploaded DESC");
$main_data['y'] = array();
$main_data['n'] = array();
while($row = mysql_fetch_assoc($data)){
    $main_data[strtolower($row['current'])][] = $row; // this will make arrays of arrays with y or n as the main index
}

// end of the business logic
// you could close the database connection here or let php close it when the script ends

// the logic and mark-up needed to produce the page follows -

// template/function to produce the output for the main content on this page
function main_output($title, $data){
    ?><p class="Text_SubHeading"><?php echo $title; ?> </p>
    <table width="650" border="0" align="center" cellpadding="4" cellspacing="0">
    <tr class="text-sectionheading">
    <td width="100">Date </td>
    <td width="150">Adviser</td>
    <td width="200">Document</td>
    <td width="100">Version</td>
    <td width="50">Current</td>
    <td width="50">View</td>
    </tr>
    <?php
    foreach($data as $test)
    {
        $id = $test['id'];
        echo"<tr>";
        echo"<td class='text-questions'>".$test['DateUploaded']."</td>";
        echo"<td class='text-questions'>".$test['LastName'].", ".$test['FirstName']."</td>";
        echo"<td class='text-questions'>".$test['DocName']."</td>";
        echo"<td class='text-questions'>".$test['Version']."</td>";
        echo"<td class='text-questions'>".$test['Current']."</td>";
        echo"<td><a class='".$test['cssclass']."'href =".$test['URL'].">".$test['ImageType']."</a>";
        echo "</tr>";
    }
    ?>
    </table>
    <?php
}
// the html document -
?>
<!DOCTYPE HTML>
<html>
<head>
    <meta charset="utf-8" />
    <title>your title</title>
</head>
<body>
<table width="680" border="0" align="center" cellpadding="4" cellspacing="0">
<tr> <td> </td>
</tr>
<tr><td class="text-heading"><p>Financial Services Guides and Adviser Profiles</p><p class="Text_Heading_Black1">PAGE STILL IN DEVELOPMENT</p>
<form id="form1" name="form1" method="get" action="">
<span class="text-questions">Adviser: </span>
<select name="advselect" id="advselect">
<option value=" " selected="selected"> </option>
<?php
foreach($adviser_data as $row){
    // if you need to dynamically select an option based on an existing choice, you would have logic here to do so
    echo '<option value="'. $row['id'] .'">'. $row['LastName'] .', '. $row['FirstName'] .'</option>';
}
?>
</select>
<input type="submit" name="advselect" id="advselect" value="Filter / View" />
</form></td>
</tr>
<tr><td>
<?php
main_output('Current Version',$main_data['y']); // you might want to add some logic so that if there is no corresponding data, you don't display an empty table, but display a meaningful message
main_output('Previous Versions',$main_data['n']);
?>
</td>
</tr>
</table>
</body>
</html>

Edited February 7, 2014 by mac_gyver

WAMFT1 · February 21, 2014

Hi mac_gyver

I have tried the code that you suggested but I cannot get any data to display when I select the dropdown box and click filter. I am not familiar with the format that you suggested above as I am only relatively new to php coding and cannot work out why no data is displaying. Can you please offer any more assistance?

mac_gyver · February 21, 2014

the example i posted was just that -

example (untested) showing some of the suggestions -

did you add the logic needed to do what sKunKbad mentioned in post #2?

WAMFT1 · February 26, 2014

I understand that it is untested but I am relatively new to this coding and the format you suggested above is totally foreign to me. I am not even sure where the "logic" in post 2 sits within your suggestions in post 4. I am totally confused.

mac_gyver · February 26, 2014

the example code i posted contains a comment in it where you would put the code needed to take the submitted drop-down value, validate it and produce a conditional statement that you can add to the mysql query to serve as a filter -

// any input filtering validation would go here...

so far you have not even shown your attempt at producing that logic, let along putting it into any code.

the code i posted functions and retrieves all (since it doesn't have any filtering logic in it) the data from your tables (tested).

i'm sorry but the point of programming help is we don't post copy/paste solutions and it appears that would be the only way this thread is going to move forward.

Sign In

Use dropdown box to filter data on same page

Recommended Posts

WAMFT1

Link to comment

Share on other sites

sKunKbad

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

WAMFT1

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

WAMFT1

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information