Jump to content

PHP dropdown menu to fetch columnar data


Go to solution Solved by Barand,

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 post
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 post
Share on other sites

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.

Link to post
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.

  • Like 1
Link to post
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 post
Share on other sites

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?

Link to post
Share on other sites

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?

Link to post
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 post
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.

Edited by ginerjm
Link to post
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 post
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 post
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>';

Edited by ginerjm
Link to post
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);
?>
Edited by mattix
Link to post
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";
Edited by Barand
Link to post
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);

?>
Edited by mattix
Link to post
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>";


}
Edited by Barand
Link to post
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 post
Share on other sites

 

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

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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