Search tables from SQL and show their data on page

Question

I've been trying to find an easy way for this. A search (dropdown menu) of all tables in mysql, and show their content when I click the table I want to show on the page. Instead of showing just every table on the page I thought it can be easier? Any help would be appreciated!
My code so far:

<?php
$host    = "localhost";
$user    = "heijsdb_user";
$pass    = "maus";
$db_name = "heijsdb";

//create connection
$connection = mysqli_connect($host, $user, $pass, $db_name);

//test if connection failed
if(mysqli_connect_errno()){
    die("connection failed: "
        . mysqli_connect_error()
        . " (" . mysqli_connect_errno()
        . ")");
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
echo "borsten HFP controle";
$result = mysqli_query($connection,"SELECT * FROM borstenHFPcontrole");
$all_property = array();  //declare an array for saving property

//showing property
echo '<table class="data-table w3-table-all" border="2px">
        <tr class="data-heading">';  //initialize table tag
while ($property = mysqli_fetch_field($result)) {
    echo '<td>' . $property->name . '</td>';  //get field name for header
    array_push($all_property, $property->name);  //save those to array
}
echo '</tr>'; //end tr tag

//showing all data
while ($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    foreach ($all_property as $item) {
        echo '<td>' . $row[$item] . '</td>'; //get items using property value
    }
    echo '</tr>';
}
echo "</table>";
////////////////////////////////////////////////////////////////////////////////////////////////////////////

I think you mean `array_push($all_property, $property); //save those to array` instead of `array_push($all_property, $property->name); //save those to array` in the first while, right? — tiomno, Jun 07 '17 at 09:01
Oh, right! Sorry Muas. Yes, I've tested your solution and it works okay. So, what you need is to populate a dropdown menu with the table names and then when the user selects a table shows the content of that table in the page? — tiomno, Jun 07 '17 at 10:33
There are two ways to do this: refreshing the page when the user selects a table in the dropdown menu or making an AJAX request to the server to grab the info from the table and populate the table. Which solution would you rather implement? — tiomno, Jun 07 '17 at 11:54
@timo rather go with the refreshing page, thinking you are referencing to a from with POST? No experience whatsoever with AJAX — maus, Jun 07 '17 at 12:08
That's correct a form with POST would be a perfect solution. You will need some interactions in JS to make this work on the `change` event of the dropdown menu. I'll prepare a simple solution for you with vanilla JS and you can go from there. — tiomno, Jun 07 '17 at 22:27

score 1 · Accepted Answer · answered Jun 07 '17 at 23:24

This is pretty much the idea, you can play from here and adapt it to your solution. Sorry I used my way, I prefer PHP template style when embedding in HTML. ;)

$host    = "localhost";
$user    = "heijsdb_user";
$pass    = "maus";
$db_name = "heijsdb";

//create connection
$connection = mysqli_connect($host, $user, $pass, $db_name);

//test if connection failed
if(mysqli_connect_errno()){
    die("connection failed: "
        . mysqli_connect_error()
        . " (" . mysqli_connect_errno()
        . ")");
}

//check if the form was submitted
$table = filter_input(INPUT_POST, 'table', FILTER_SANITIZE_STRING);

?>
<html>
<head>
    <title>showing table content on user action</title>
</head>
<body>
    <div>

        <form id="form-menu" method="post">
            <label for="select-menu">Choose a table</label>
            <select id="select-menu" name="table">
                <option></option>
                <?php
                $result = mysqli_query($connection,"SELECT table_name FROM information_schema.tables where table_schema='test'"); // <-- the table_schema field here is your database name, change 'test' for yours
                while ($row = mysqli_fetch_array($result)) : $selected = $row['table_name'] == $table ? 'selected' : ''; ?>
                    <option value="<?php echo $row['table_name'] ; ?>" <?php echo $selected; ?>><?php echo $row['table_name'] ; ?></option>
                <?php endwhile; ?>
            </select>
        </form>

        <hr>

        <div>
            <?php if (empty($table)) : ?>
                <h3>Please select a table to show its content</h3>
            <?php else : ?>
                <h3>Content for the table `<?php echo $table; ?>`</h3>
                <?php
                $result = mysqli_query($connection,"SELECT * FROM `{$table}`");
                $all_property = [];  //declare an array for saving property
                ?>
                <!-- showing property -->
                <table class="data-table w3-table-all" border="2px">
                    <tr class="data-heading"> <!-- initialize table tag -->
                        <?php while ($property = mysqli_fetch_field($result)) : ?>
                        <td><?php echo $property->name; ?></td> <!-- get field name for header -->
                        <?php $all_property[] = $property->name; //save those to array ?>
                        <?php endwhile; ?>
                    </tr> <!-- end tr tag -->

                    <!-- showing all data -->
                    <?php while ($row = mysqli_fetch_array($result)) : ?>
                    <tr>
                        <?php foreach ($all_property as $item) : ?>
                            <td><?php echo $row[$item]; ?></td> <!-- get items using property value -->
                        <?php endforeach; ?>
                    </tr>
                    <?php endwhile; ?>
                </table>
            <?php endif; ?>
        </div>

    </div>

    <script>
        document.getElementById('select-menu').addEventListener('change', function() {
            document.getElementById('form-menu').submit();
        });
    </script>
</body>
</html>

Handy links:
- Get table names using SELECT statement in MySQL
- Examples of how to do query, style, dom, ajax, event etc like jQuery with plain javascript.

Hope this helps :)

Search tables from SQL and show their data on page

1 Answers1