populate dropdown menu on SQL columns

Question

i have been trying to populate a dropdown menu with the columns of a SQL table, but i am stuck. Or all the fields in the dropdown menu are : Array, or the dropdown menu is populated with empty values.

am i missing something?

i tried this code.

SHOW COLUMNS, DESCRIBE 'TABLE' AND SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE = ikbb;

    <?php
    $conn = new mysqli('localhost', 'heijsdb_user', 'maus', 'heijsdb') 
    or die ('Cannot connect to db');
    $result = $conn->query("SHOW COLUMNS FROM ikbb FROM heijsdb");
    echo "selecteer een input om aan te passen";
    echo "<html>";
    echo "<body>";
    echo "<select name='id'>";
    while ($row = $result->fetch_assoc()) {

                $rows[0] = $row;
                  echo '<option value="'.$row.'">'.$row.'</option>';

}

    echo "</select>";
    echo "</body>";
    echo "</html>";
?>

result result 2

Possible duplicate of [Get all columns from all MySQL tables](https://stackoverflow.com/questions/5648420/get-all-columns-from-all-mysql-tables) — Dan Hastings, Jul 04 '17 at 10:31
how is this a duplicate since i want to populate a dropdown menu — Maurice de Vries, Jul 04 '17 at 10:32
`echo '';` should be something like `echo '';` since $row is an array — ADyson, Jul 04 '17 at 10:34
what does `SHOW COLUMNS FROM ikbb FROM heijsdb` actually return? — ADyson, Jul 04 '17 at 10:37
BTW `echo "selecteer een input om aan te passen"; echo "";` this makes an invalid HTML document. `` should enclose everything else on the page (unless you have a !DOCTYPE directive above that, which you should really). the echo'd text should be inside the body tag — ADyson, Jul 04 '17 at 10:39
it's results in showing every column in the table, and the file is a PHP while, so what is the problem? — Maurice de Vries, Jul 04 '17 at 10:42
why is everyone on this site so salty. This site is here to learn right? — Maurice de Vries, Jul 04 '17 at 10:44
Honestly, Maurice, have you tried very hard to debug and self-solve this? I don't see any error checking beyond the connection and you haven't provided any database data in the question. I am being fair here. — mickmackusa, Jul 04 '17 at 10:47
I've been stuck at this for 4 hours now.. i can include the database data, but this should work for every database anyway right? It would just be great if someone had an answer for this one. — Maurice de Vries, Jul 04 '17 at 10:51
@MauricedeVries Could you please show us what this script returns? — DamiToma, Jul 04 '17 at 10:59

score 1 · Accepted Answer · answered Jul 04 '17 at 11:17

You're trying to populate a dropdown with an array, and this is why you see Array in your select input. Furthermore, you shouldn't add the html tag later than echo something.

I would recommend this query for getting the columns of a table :

SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='your_database' AND `TABLE_NAME`='your_table'

To populate a dropdown with these names, simply use this script :

<?php
$conn = new mysqli("server", "user", "password", "database");
$result = $conn->query("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='your_database' AND `TABLE_NAME`='your_table'");
echo '<html>';
echo '<body>';
echo '<select name="id">';
while($row = $result->fetch_assoc()) {
    echo '<option value="'.$row['COLUMN_NAME'].'">'.$row['COLUMN_NAME'].'</option>';
}
echo '</select>';
echo '</body>';
echo '</html>';
?>

populate dropdown menu on SQL columns

1 Answers1