current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

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

share|improve this question
    
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. – AnotherGuy Jul 21 '15 at 19:09
    
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. – CodeX Jul 21 '15 at 19:11
    
Working on an alternative answer. The solution is the exact same as yours, but with some extra information. – AnotherGuy Jul 21 '15 at 19:27
up vote 1 down vote accepted

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!

share|improve this answer
    
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. – CodeX Jul 21 '15 at 19:54
1  
@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 :) – AnotherGuy Jul 21 '15 at 19:57
up vote 1 down vote

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>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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