2
\$\begingroup\$

How can I do the following with less nested loops. How can I output the Table names above their corresponding tables without having to loop.

Functions for returning the DB Info:

// Return all the Table names in an array
public function getDbTables() {          
    $result = $this->query("SHOW TABLES");
    while ($row = $result->fetchAll(PDO::FETCH_COLUMN)) {
        return $row;
    }       
}

// Return ALL column names for each Table available
public function getColumnNames($table){
    $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table";
    try {
        $stmt = $this->dbh->prepare($sql);
        $stmt->bindValue(':table', $table, PDO::PARAM_STR);
        $stmt->execute();
        $output = array();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $output[] = $row['COLUMN_NAME'];                
        }
        return $output; 
    }

    catch(PDOException $pe) {
        trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
    }
}

// Populate HTML Table with the DB Table Data
public function populateTable($table) {          
    $sql = "SELECT * FROM $table";
    try {
        $stmt = $this->dbh->prepare($sql);
        $stmt->execute();
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            return $row;              
        }

    }
        catch(PDOException $pe) {
        trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
    }
}

HTML Output:

<?php print_r($phpCore->getDbTables()); ?>
<div>
<form>
<table>
<?php
foreach($phpCore->getDbTables() as $tablename) {
    echo '<tr>';
    foreach($phpCore->getColumnNames($tablename) as $fieldnames) {
        echo '<td>'.$fieldnames.'</td>';    
    }
    echo '</tr>';

    echo '<tr>';
    foreach($phpCore->populateTable($tablename) as $tabledata) {
        echo '<td><input type="text" value="'.$tabledata.'"></input></td>';
    }   
    echo '</tr>';
}
?>
</table>
</form>
</div>

Visual Output: enter image description here

\$\endgroup\$
  • \$\begingroup\$ Why do you want to avoid using a loop? Sometimes the most trivial solution is the best and the most understood throughout the developer communities. \$\endgroup\$ – AnotherGuy Jul 21 '15 at 19:09
  • \$\begingroup\$ Very true, I find loops to become too overwhelming in code sometimes, if there is a way to do it with less loops then I would do that. They are also causing some issues with the table names being printed. \$\endgroup\$ – CodeX Jul 21 '15 at 19:11
  • \$\begingroup\$ Working on an alternative answer. The solution is the exact same as yours, but with some extra information. \$\endgroup\$ – AnotherGuy Jul 21 '15 at 19:27
1
\$\begingroup\$

A simple solution could be to add another <tr> element into the first foreach loop. You already have the table name, so outputting it to the screen shouldn't be too much trouble.

/*
 * Your original structure with a line added.
 */
foreach($phpCore->getDbTables() as $tablename) {

    /*
     * Display the current table name.
     */
    echo '<tr><b>Table: ' . ucfirst($tablename) . '</b></tr>'; 

    echo '<tr>';
    foreach($phpCore->getColumnNames($tablename) as $fieldnames) {
        echo '<td>'.$fieldnames.'</td>';    
    }
    echo '</tr>';

    echo '<tr>';
    foreach($phpCore->populateTable($tablename) as $tabledata) {
        echo '<td><input type="text" value="'.$tabledata.'"></input></td>';
    }   
    echo '</tr>';
}

I feel like the answer was a little easy, so I will add some more. Mixing PHP and HTML with interpolating strings with variables and tags can be hard to read and course some bugs later. You should consider the following.

Extra

I would urge you to separate display logic and domain logic. The domain logic should only be responsible of fetching the required data, where the display logic should be responsible for the actual rendering of the data. An example could be the following:

The domain logic

Then the appropriate data has been fetched from the data storage source you could return an array or even an object with the data. If using an array it could look like the following:

$tables = [
    'users' => [
        'columns' => ['id', 'username'],
        'data'    => ['1',  'admin']
    ],
    'logins' => [
        'columns' => ['id', 'user_id', 'timestamp'],
        'data'    => ['1',  '1',       '2015-7-21 21:25:00']
    ]
];

The display logic would then receive this array structure and decide (independently of the domain) how it should be rendered.

<table>
    <?php foreach($tables as $name => $values): ?>

    <tr>
       <td>
           <b><?= ucfirst(htmlspecialchars($name, ENT_QUOTES, 'UTF-8')); ?></b>
       </td>
    </tr>

    <tr>
        <?php foreach($values['data'] as $data): ?>
        <td>
            <input type="text" value="<?= htmlspecialchars($data, ENT_QUOTES, 'UTF-8'); ?>"></input>
        </td>
        <?php endforeach; ?>
    </tr>

    <tr>
        <?php foreach($values['columns'] as $column): ?>
        <td>
            <?= ucfirst(htmlspecialchars($column, ENT_QUOTES, 'UTF-8')); ?>
        </td>
        <?php endforeach; ?>
    </tr>

    <?php endforeach; ?>
</table>

Now you have separated display and domain logic effortlessly from each other. You can change your display to easily include the table name without changing the functions/methods used to fetch data. The same goes for the domain part. You can add new information into the domain without breaking the display layer. The new information will only be displayed when you write the necessary HTML structure.

Disclaimer: personal stuff incoming! Using the alternative PHP syntax inside HTML helps me change mental state so that I am less likely to write domain actions inside the display layer. This is of course subjective.

I would also remind you of output escaping. Here I have used the htmlspecialchars() function, but there are more to it than that. If you want you should read up on it as some of nastiest security attacks are performed with lack of output escaping.

Hope this can help, happy coding!

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Thanks so much for this, I like your approach, it makes things a lot cleaner and easier to manage. I like to do all my security stuff after I've coded the "guts" unless its a large project then I do it all in line. \$\endgroup\$ – CodeX Jul 21 '15 at 19:54
  • 1
    \$\begingroup\$ @CodeX - Just remember to do the security. I do the same, but add TODO comments (my IDE sees these - PHPStorm) along the way, so that I can be (almost) sure I do not miss something. Glad I could help :) \$\endgroup\$ – AnotherGuy Jul 21 '15 at 19:57
1
\$\begingroup\$

Ok, so I'm happy with the foreach loops now.

To get the Table names where I wanted them I simply had to encapsulate the PHP in HTML tags:

<?php print_r($phpCore->getDbTables()); ?>
<div>
<form>
<table>
<?php
foreach($phpCore->getDbTables() as $tablename) {
    echo '<tr><td>'.$tablename.'</td></tr>';
    echo '<tr>';
    foreach($phpCore->getColumnNames($tablename) as $fieldnames) {
        echo '<td>'.$fieldnames.'</td>';    
    }
    echo '</tr>';

    echo '<tr>';
    foreach($phpCore->populateTable($tablename) as $tabledata) {
        echo '<td><input type="text" value="'.$tabledata.'"></input></td>';
    }   
    echo '</tr>';

}
?>
</table>
</form>
</div>
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.