How do I get different user selected SQL tables displayed as an html table using PHP?

Question

I am creating a page that prompts for server login info, and redirects after submitting correct information. Once on the next page, there is a select form with different table names representing tables inside of the database. Once one is selected, and 'select table' is clicked, it is to display the entire table from the database as a table in html by using PHP. I'm not sure how to figure out the number of columns in a sql table to be able to display it using a loop and html table tags.

<?
session_start();
if(!isset($_SESSION['session']))
{
    $_SESSION['session'] = 0;
}
if(isset($_POST['host']))
{
    $_SESSION['host'] = $_POST['host'];
    $_SESSION['dbname'] = $_POST['dbname'];
    $_SESSION['username'] = $_POST['username'];
    $_SESSION['pw'] = $_POST['pw'];
}

?>
<!DOCTYPE html>
<html>

<body>
    <?          
    if(isset($_POST['logout']))
    {
        $_SESSION['session'] = 0;
    }

    if(isset($_POST['submit']))
    {
        try
        {
            $db = new PDO("mysql:host=".$_POST['host'].";dbname=".$_POST['dbname'], $_POST['username'], $_POST['pw']);
        }
        catch(Exception $error)
        {
            die("Connection to user database failed: " . $error->getMessage());
        }

        try
        {
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO:: ERRMODE_EXCEPTION);
            $query = "SHOW TABLES";
            $results = $db->query($query)->fetchall();
            $_SESSION['session']=1;
        }
        catch(Exception $error)
        {
            echo "Problem with ~query, go back to home screen";
            $_SESSION['session']=0;
        }
    }       

    if($_SESSION['session'] == 0)
    {?>
    <form action="<?$_SERVER['PHP_SELF']?>" method="post" name='initialentry'>
        <table border='0' style='text-align: center'>
            <tr>
                <td style='text-align: right;'>Enter host name:</td>
                <td style='text-align: left;'>
                    <input type='text' name='host' value='localhost'>
                </td>
            </tr>
            <tr>
                <td style='text-align: right;'>Enter database name:</td>
                <td style='text-align: left;'>
                    <input type='text' name='dbname' value='zxyx999'>
                </td>
            </tr>
            <tr>
                <td style='text-align: right;'>Enter user name:</td>
                <td style='text-align: left;'>
                    <input type='text' name='username' value='zxyx999'>
                </td>
            </tr>
            <tr>
                <td style='text-align: right;'>Enter password:</td>
                <td style='text-align: left;'>
                    <input type='password' name='pw' width='15' value='12345'>
                </td>
            </tr>
            <tr>
                <td style='text-align: right;'>
                    <input type="reset" value="Reset">
                </td>
                <td style='text-align: left;'>
                    <input name='submit' type="submit" value="Submit">
                </td>
            </tr>
        </table>
    </form>
    <?}

    if($_SESSION['session']==1)
    {
        ?><form action='<?$_SERVER['PHP_SELF']?>' method='post' name='tableList'>
        <select name='selected'><?
            foreach($results as $row)
            {
                echo "<option value=".$row[0].">" . $row[0] . "</option>";
            }?>
        </select>
        <input type="submit" name="selectTable" value="Select Table">
        <input type="submit" name="logout" value="logout">
        </form><?
    }

    if(isset($_POST['selectTable']))
    {
        try
        {
            $db = new PDO("mysql:host=".$_SESSION['host'].";dbname=".$_SESSION['dbname'], $_SESSION['username'], $_SESSION['pw']);
        }
        catch(Exception $error)
        {
            die("Connection to user database failed: " . $error->getMessage());
        }

        try
        {
            $statement = $db->prepare("SELECT * FROM ".$_POST['selected']);
            $statement->execute();
        }
        catch(Exception $error)
        {
            echo "Problem with ~query";
        }

        echo "</br>";
        echo "<table border= '1'>";

        while($row = $statement->fetch())
        {
            echo $row['book_id'] . $row['title'];
        }

        echo "</table>";
    }

    ?>
</body>
</html>

Possible dubplicate of: http://stackoverflow.com/questions/658395/find-the-number-of-columns-in-a-table — icecub, Oct 29 '15 at 00:30

Shadow · Accepted Answer · 2015-10-29T01:03:47.483

2

You can use columncount property of the PDOStatement object after execute() has been called to determine the number of columns in the resultset.

Update: let's see some code

...
$c=$statement->columnCount()-1; //column indexes in resultset are 0 based
while($row = $statement->fetch())
    {
        echo '<tr>'; //start row
        for($i=0; $i<=$c; $i++)
            {
                echo '<td>'.$row[$i] . '</td>'; //print cell
            }
        echo '</tr>'; //close the row
    }
...

edited Oct 29 '15 at 01:03

answered Oct 29 '15 at 00:34

Shadow

33,525
10
51
64

Honestly, your answer is the most sensible here so far. Please include some example with explanation and I'll upvote. – icecub Oct 29 '15 at 00:38
Thank you, this was what i was looking for. Now the only thing I have left to do is to get the column names to display from the database. Here's the code I have to create an html table of the table, all it's missing is the column header names: > `echo ""; > echo ""; > while($row = $statement->fetch()) { echo ""; for($i=0; $i < $statement->columnCount(); $i++) > { echo ""; } echo ""; }`
" . $row[$i] . "
– Zac Hyder Oct 29 '15 at 01:02
fetch() will retrieve the column names as well at the default settings of PDO. – Shadow Oct 29 '15 at 01:07

score 0 · Answer 2 · answered Oct 29 '15 at 00:28

0

To select the number of the columns of a table you can use this query.

SELECT COUNT(*) AS total_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'selected_table' AND table_schema = 'your_database';

You can find more info about INFORMATION_SCHEMA here.

answered Oct 29 '15 at 00:28

Kostas Mitsarakis

4,772
3
23
37

Is this correctly formatted? `$columns= $db->prepare("SELECT COUNT(*) AS total_columns FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name =" . $_POST['selected'] . " AND table_schema =" . $_SESSION['host']); $columns->execute(); echo var_dump($columns);` Because I get this output when I run it: `object(PDOStatement)#3 (1) { ["queryString"]=> string(115) "SELECT COUNT(*) AS total_columns FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name =book AND table_schema =localhost" }` – Zac Hyder Oct 29 '15 at 00:39
No, try this: $columns= $db->prepare("SELECT COUNT(*) AS total_columns FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '".$_POST['selected']."' AND table_schema = '".$_SESSION['host']."';"); – Kostas Mitsarakis Oct 29 '15 at 00:43
If you cannot do it this way try what @Shadow proposed. – Kostas Mitsarakis Oct 29 '15 at 00:46

score 0 · Answer 3 · edited May 23 '17 at 12:26

you'd want to use the INFORMATION_SCHEMA.COLUMNS table as indicated in this answer

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

you first create a loop where you make the <tr><th>[CONTENT]</th>...</tr> row of the table then you run your existing loop to generate the <tr><td>[CONTENT]</td>...</tr> rows.

your first loop would more or less be the same as your second with the difference being that you are using a different sql result, using <th> instead of <td> and a loop inside your second loop. this will...

display the entire table from the database as a table in html

and if you need a row/column count you can stick some sort of iterator in each loop

first loop example

echo "<tr>";
$countColumn = 0;
while($row = $statement->fetch())
{
    echo "<th>".$row['COLUMN_NAME']."</th>";
    $countColumn += 1;
}
echo "</tr>";

second loop example

$countRow = 0;
while($row = $statement->fetch())
{
    echo "<tr>";
    foreach($row as $data)
    {
        echo "<td>".$data."</td>";
        $countRow += 1;
    }
    echo "</tr>";
}

NOTE: Browsers are retarded children, unless in your

echo "<table border= '1'>";

while($row = $statement->fetch())
{
    echo $row['book_id'] . $row['title'];
}

echo "</table>";

part your $row['book_id'] . $row['title'] has your <tr><td> tags don't let them fill in the blanks in. especially if this is going to be a non-intranet page

How do I get different user selected SQL tables displayed as an html table using PHP?

3 Answers3