PHP dropdown menu to fetch columnar data

[mattix]

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.  

[requinix]

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.

[mattix]

Thanks, requinix for your suggestion and help. yes, Ajax would be nice to have! I've done some search but couldn't find anything similar, the one I found doesn't work, it has some errors. 

[requinix]

Then... fix the errors? You can't just grab stuff from the internet and expect it (a) to work or (b) to do what you want. You have to understand what it is and how it works, then adapt it for your own use.

[ginerjm]

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.

[mattix]

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. 

[requinix]

That tutorial is more than 6 years old and does a number of things poorly. This one looks better, though it does depend on Bootstrap and jQuery which I'm not thrilled about.

[mattix]

Thanks, requinix. This is definitely better. It is in the same format though where I am stuck.  thanks, I will check it in more detail. 

[ginerjm]

What does "table result" mean to you? Are we talking about output on a client screen? Like an HTML table that your php code could easily generate by scrolling thru some query results?

[mattix]

yes, I mean the result is displayed in a table with <tr> and <td>s on the html page with a  single column and multiple rows. 

Edited April 30, 2018 by mattix

[ginerjm]

So we understand your goal at least. So - what's the hangup? Do you have a set of data yet? Can you write a php set of code to perform a query and process a set of query results with a while/fetch loop?

[mattix]

<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. 

[ginerjm]

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.

Edited April 30, 2018 by ginerjm

[mattix]

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); 
?>

[Barand]

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
}

[mattix]

Thanks, Barand. I will try this. 

[ginerjm]

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>';

Edited May 6, 2018 by ginerjm

[mattix]

Thanks a lot, Ginerjm. 

[ginerjm]

So - it's been 2 days. What have you written to achieve your goal? Just curious.

[mattix]

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);
?>

Edited May 9, 2018 by mattix

[Barand]

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";

Edited May 9, 2018 by Barand

[mattix]

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);

?>

Edited May 9, 2018 by mattix

[Barand]

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>";


}

Edited May 9, 2018 by Barand

[mattix]

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);


?>

[Barand]

 

$myselect = $_GET['mySelect'];  // needs to be after the above check

 

 

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

 

Variable names do not match - lowercase vs uppercase "s"