Jump to content

PHP dropdown menu to fetch columnar data


mattix

Recommended Posts

I am seeking a PHP code to fetch and retrieve data from a single column from a mysql table and display in a tabular form on the page. So I need a dropdown option list for the column names so every time a different column is selected, the relevant data is displayed on the page. Can someone provide me a code snippet or help me out with this, please? thanks.  

Link to comment
Share on other sites

If you want to change the data when the dropdown changes, and without reloading the page, then the search terms you want include AJAX. If you don't mind refreshing the page by submitting the form then it's just normal database access.

 

Why am I not just giving you code? Because the subject has been done to death. And you'll learn better if you aren't copying and pasting code someone made for you.

Link to comment
Share on other sites

I am so happy that someone has steered you in the correct direction here, as they should have.

 

If you want someone to write code for you ('provide me a code snippet') when you haven't yet shown us what you are trying, you probably want to pay someone to do that for you. See the related forum on this site.

 

OTOH - if you want to learn, then this is the place. Show us what you have learned by reading and experimenting. Hopefully not something that you simply grabbed and pasted and don't have a clue about. I personally will give it my best shot, although there are many better than I on this forum who will also jump in.

 

It's a learning experience, this coding thing.

Link to comment
Share on other sites

Thanks for your advice. yes I tried before posting here, but without success. I am not php expert, just know some basics. What I found on internet and tried to adapt to my needs (closest) was this one : http://www.itgeared.com/articles/1403-ajax-php-sql-database-data-tutorial/  though I seek a table result, and instead of selecting field values in the select box, field names to show all field values. 

Link to comment
Share on other sites

<table class="tb_output">
 <tr>
  <th>Events</th> 
     </tr>
 
 <?php
 
  include ("config.php");
 
  $sql = "SELECT Events order by Date DESC";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {
 
   while($row = $result->fetch_assoc()) 
   {
        echo "<tr><td>" . $row["Events"] . "</td></tr>";
 
 }
  
      echo "</table>";
 
} else { echo "0 results"; }
$conn->close();
      
    ?>   
       
</table>

I tried to do something like this. but this is not what I want. I need a select box implementation to choose the field names, instead of fixed field names in the code. 

Link to comment
Share on other sites

Not bad. You do however have an extra end table tag. Edit: No - what you have is an echo in the middle of your table output - not neat.

 

Nor does this code have anything to do in any way with this dropdown you are mentioning. This is result code.

Link to comment
Share on other sites

I found a code that's similar to what i want. And with a little modification, I am got half of what I wanted. but I am stuck with the results section. How can I display the contents of any selected field from the drop down list?  Here is the code:

 

<?php 


$host = 'localhost'; 
$port = '3306'; 
$server = $host . ':' . $port; 
$user = 'root'; 
$password = ''; 
$link = count($t_tmp = explode(':', $server)) > 1 ? mysqli_connect($t_tmp[0], $user, $password, '', $t_tmp[1]) : mysqli_connect($server, $user, $password); 
if (!$link) { 
    die('Error: Could not connect: ' . mysqli_error($link)); 
} 
$database = 'mydb'; 
mysqli_select_db($link, $database); 
$query = 'select * from mytable'; 
$result = mysqli_query($link, $query); 
if (!$result) { 
    $message = 'ERROR:' . mysqli_error($link); 
    return $message; 
} else { 
    $i = 0; 
     echo '<select name="mySelect" id="mySelect">'; 
    while ($i < mysqli_field_count($link)) { 
        $meta =  
        mysqli_fetch_field_direct($result, $i); 
        echo '<option>' . $meta->name . '</option>'; 
        $i = $i + 1; 
    } 
echo '</select>';
} 
mysqli_close($link); 
?>
Link to comment
Share on other sites

Use mysqli's fetch_assoc() function.

 

Then if the column you want is called 'columnA' then

while ( $row = $result->fetch_assoc() ) {
    echo $row['columnA'] . '<br>';                  // outputs the column you want
}
Link to comment
Share on other sites

And if you want a dropdown to show each of the column names to be then picked from:

// TO SELECT THE COLUMN NAMES YOU ONLY NEED QUERY 1 ROW
$query = 'select * from mytable limit 1';
$result = mysqli_query($link, $query);
if (!$result)
{
$message = 'ERROR:' . mysqli_error($link);
return $message;
}
// get the result row
$row = $result->fetch_assoc();
// begin dropdown of column names here
echo '<select name="mySelect" id="mySelect">';
echo '<option>Choose a column name</option>';
// pull each column name now
foreach ($row as $k=>$v)
echo '<option>' . $k . '</option>';
// finish the dropdown
echo '</select>';

Link to comment
Share on other sites

Thanks for your interest, ginerjm. I am still struggling with the second part. I can't get the results displayed on the page. 

Here is the final code:

 

<table class="tbresult">
<?php 
include ("config.php");
$query = 'select * from myTable'; 
$result = mysqli_query($link, $query); 
if (!$result) { 
    $message = 'ERROR:' . mysqli_error($link); 
    return $message; 
} else { 
    $i = 0; 
    echo '<form name="select" action="" method="GET">';
     echo '<select name="mySelect" id="mySelect"  onchange="this.form.submit()">'; 
    while ($i < mysqli_field_count($link)) { 
        $meta =  
        mysqli_fetch_field_direct($result, $i); 
        echo '<option>' . $meta->name . '</option>'; 
        $i = $i + 1; 
    } 
echo '</select>';
echo '</form>';
} 
if(isset($_GET['mySelect'])) {
$sql = "SELECT 'mySelect' FROM myTable";

  if ($result->num_rows > 0) {

  while($row = $result->fetch_assoc()) 
   {
        echo "<tr><td>" . $row["mySelect"] . "</td></tr>";
   }
  
      echo "</table>";

} else { echo "0 results"; }

}
mysqli_close($link);
?>
Link to comment
Share on other sites

Your query selects the string literal value 'myselect', so no matter what is in the table, that is all you get.

 

Example

mysql> select * from customer;
+-------------+-------+-----------+---------+
| customer_id | fname | lname     | phone   |
+-------------+-------+-----------+---------+
|           1 | John  | Smith     | 5551224 |
|           2 | Peter | Smidt     | 5552345 |
|           3 | Paul  | Spock     | 5555287 |
|           4 | Mary  | Spielberg | 5556974 |
|           5 | Adam  | Smethers  | 5552211 |
|           6 | John  | Smith     | 5554321 |
|           7 | John  | Spall     | 5556789 |
|           8 | Eve   | Smirnov   | 5555455 |
|           9 | John  | Smith     | 5557501 |
+-------------+-------+-----------+---------+
9 rows in set (0.02 sec)

mysql> select 'mySelect' from customer;
+----------+
| mySelect |
+----------+
| mySelect |
| mySelect |
| mySelect |
| mySelect |
| mySelect |
| mySelect |
| mySelect |
| mySelect |
| mySelect |
+----------+
9 rows in set (0.00 sec)

You need to store the value that is in $_GET['mySelect'] and select that

$myselect = $_GET['mySelect'];
$sql = "SELECT `$myselect` as mySelect FROM myTable";
Link to comment
Share on other sites

Thanks, Barand. I did what you suggested. but I got this error (many of the same notice):

Notice: Undefined index: mySelect in C:\wamp64\www\example.php on line 38  

 

Line 38 :         echo "<tr><td>" . $row["mySelect"] . "</td></tr>";

 

here is the code after the changes:

 

<table class="tbresult">
<?php 
include ("confige.php");
$query = 'select * from employees'; 
$result = mysqli_query($link, $query); 
if (!$result) { 
    $message = 'ERROR:' . mysqli_error($link); 
    return $message; 
} else { 
    $i = 0; 
    echo '<form name="selectSomething" action="" method="GET">';
     echo '<select name="mySelect" id="mySelect"  onchange="this.form.submit()">'; 
    while ($i < mysqli_field_count($link)) { 
        $meta =  
        mysqli_fetch_field_direct($result, $i); 
        echo '<option>' . $meta->name . '</option>'; 
        $i = $i + 1; 
    } 
echo '</select>';
echo '</form>';
} 

$myselect = $_GET['mySelect'];
if(isset($_GET['mySelect'])) {

$sql = "SELECT `$mySelect` as mySelect FROM employees";

  if ($result->num_rows > 0) {

   // output data of each row
   while($row = $result->fetch_assoc()) 
   {
        echo "<tr><td>" . $row["mySelect"] . "</td></tr>";

   }
  
      echo "</table>";


} else { echo "0 results"; }

}
mysqli_close($link);

?>
Link to comment
Share on other sites

You must have grabbed the code just before I edited or else $_GET['mySelect '] isn't set.


if(isset($_GET['mySelect'])) {
$myselect = $_GET['mySelect'];  // needs to be after the above check

$sql = "SELECT `$mySelect` as mySelect FROM employees";  // add column alias

  if ($result->num_rows > 0) {

   // output data of each row
   while($row = $result->fetch_assoc()) 
   {
        echo "<tr><td>" . $row["mySelect"] . "</td></tr>";

   }
  
      echo "</table>";


}
Link to comment
Share on other sites

ok, I tried this too. but still the same.

Notice: Undefined variable: mySelect in C:\wamp64\www\example.php on line 29
Line 29 : $sql = "SELECT `$mySelect` as mySelect FROM employees";  // add column alias
 
Notice: Undefined index: mySelect in C:\wamp64\www\example.php on line 36

Line 36:         echo "<tr><td>" . $row["mySelect"] . "</td></tr>";

Code after changes:

 

<table class="tbresult">


<?php 


include ("confige.php");


$query = 'select * from employees'; 
$result = mysqli_query($link, $query); 
if (!$result) { 
    $message = 'ERROR:' . mysqli_error($link); 
    return $message; 
} else { 
    $i = 0; 
    echo '<form name="selectSomething" action="" method="GET">';
     echo '<select name="mySelect" id="mySelect"  onchange="this.form.submit()">'; 
    while ($i < mysqli_field_count($link)) { 
        $meta =  
        mysqli_fetch_field_direct($result, $i); 
        echo '<option>' . $meta->name . '</option>'; 
        $i = $i + 1; 
    } 
echo '</select>';
echo '</form>';
} 


if(isset($_GET['mySelect'])) {
$myselect = $_GET['mySelect'];  // needs to be after the above check


$sql = "SELECT `$mySelect` as mySelect FROM employees";  // add column alias


  if ($result->num_rows > 0) {


   // output data of each row
   while($row = $result->fetch_assoc()) 
   {
        echo "<tr><td>" . $row["mySelect"] . "</td></tr>";


   }
  
      echo "</table>";




}


} else { echo "0 results"; }




mysqli_close($link);


?>
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.