CODEWITHSUNDEEP
phpmysqlcodeigniter
Roy Goring
Roy Goring

Reputation: 57

Codeigniter - How to display database results vertically as columns instead of rows

I have a database table that has 21 columns and I want to display all of them, so I decided to try and put the table vertically.

The first column would be the 'Headers' followed by each column displaying a row of data from the database.

|--------|------|------|------|
| Title1 | row1 | row2 | row3 |
|--------|------|------|------|
| Title2 | row1 | row2 | row3 |
|--------|------|------|------|

etc

In the view

echo '<table>';
echo '<h2>Schemes</h2>';
foreach ($scheme->result() as $schemerow)
{
echo '<tr><td class="app"><strong>Award&nbsp;1</td><td class="app">'.$schemerow->Description.'</td></tr>';
echo '<tr><td class="app"><strong>Award&nbsp;2</td><td class="app">'.$schemerow->Description.'</td></tr>';
echo '<tr><td class="app"><strong>Location</td><td class="app">'.$schemerow->LocationName.'</td></tr>';
echo '<tr><td class="app"><strong>Day</td><td class="app">'.$schemerow->DayName.'</td></tr>';

etc

This gives me one long table with each row below the previous.

Screenshot of result

But I can't work out how to get the second row to the right of the first one etc?

Upvotes: 1

Views: 1021

Answers (2)

Roy Goring
Roy Goring

Reputation: 57

Thanx to Paul Spiegel who gave me a way to do it.

It did need a lot of tidying up :)

View

echo '<table>';
echo '<h2>Schemes</h2>';
$transposedArray = array();
foreach ($mscheme->result_array() as $mschemerow)
{
    foreach ($mschemerow as $columnName => $value)
    {
        $transposedArray[$columnName][] = $value;
    }
$value =    form_open(base_url().'index.php/swim/mscheme_edit/'.$mschemerow['MainSchemeID']).form_submit('submit', 'Edit').form_close();
$transposedArray['Edit'][] = $value;
}
foreach ($transposedArray as $header => $values)
{
    if($header == 'LocationName' || $header == 'Season' || $header == 'Award 1' || $header == 'Award 2' || $header == 'DayName' || $header == 'Week 1' || $header == 'Week 2' || $header == 'Week 3' || $header == 'Week 4' || $header == 'Week 5' || $header == 'Week 6' || $header == 'Week 7' || $header == 'Week 8' || $header == 'Week 9' || $header == 'Week 10' || $header == 'Week 11' || $header == 'Week 12' || $header == 'Week 13' || $header == 'Week 14' || $header == 'Week 15' || $header == 'Edit')
    {
        switch($header)
        {
            case 'LocationName':
                $header = 'Location';
                break;
                case 'Season':
                $header = 'Term';
                break;
            case 'DayName':
                $header = 'Day';
                break;
            }
    echo '<tr>';
    echo "<td class='app'><b>{$header}</b></td>";
    foreach ($values as $value)
    {
        echo "<td class='app'>{$value}</td>";
    }
    echo '</tr>';
    }
}
echo '</table>';

Model

public function CRUD_read_mschemes()
    {
        return $this->db->select('*, p.Description AS Award 1, q.Description AS Award 2, a.MSchemeShort AS Week 1, b.MSchemeShort AS Week 2, c.MSchemeShort AS Week 3, d.MSchemeShort AS Week 4, e.MSchemeShort AS Week 5, f.MSchemeShort AS Week 6, g.MSchemeShort AS Week 7, h.MSchemeShort AS Week 8, i.MSchemeShort AS Week 9, j.MSchemeShort AS Week 10, k.MSchemeShort AS Week 11, l.MSchemeShort AS Week 12, m.MSchemeShort AS Week 13, n.MSchemeShort AS Week 14, o.MSchemeShort AS Week 15')
            ->from('schemesmain')
            ->join('schemesmaindetail AS a', 'MWeek1 = a.MSchemeID')
            ->join('schemesmaindetail AS b', 'MWeek2 = b.MSchemeID')
            ->join('schemesmaindetail AS c', 'MWeek3 = c.MSchemeID')
            ->join('schemesmaindetail AS d', 'MWeek4 = d.MSchemeID')
            ->join('schemesmaindetail AS e', 'MWeek5 = e.MSchemeID')
            ->join('schemesmaindetail AS f', 'MWeek6 = f.MSchemeID')
            ->join('schemesmaindetail AS g', 'MWeek7 = g.MSchemeID')
            ->join('schemesmaindetail AS h', 'MWeek8 = h.MSchemeID')
            ->join('schemesmaindetail AS i', 'MWeek9 = i.MSchemeID')
            ->join('schemesmaindetail AS j', 'MWeek10 = j.MSchemeID')
            ->join('schemesmaindetail AS k', 'MWeek11 = k.MSchemeID')
            ->join('schemesmaindetail AS l', 'MWeek12 = l.MSchemeID')
            ->join('schemesmaindetail AS m', 'MWeek13 = m.MSchemeID')
            ->join('schemesmaindetail AS n', 'MWeek14 = n.MSchemeID')
            ->join('schemesmaindetail AS o', 'MWeek15 = o.MSchemeID')
            ->join('location', 'LocationsID = MSchemeLocationID')
            ->join('termseason', 'SeasonsID = MSchemeSeasonID')
            ->join('awards AS p', 'MSchemeAwardsID1 = p.AwardID')
            ->join('awards AS q', 'MSchemeAwardsID2 = q.AwardID')
            ->join('days', 'DaysID = MSchemeDayID')
        ->get();
    }

Result Result

Upvotes: 0

Paul Spiegel
Paul Spiegel

Reputation: 31772

I'm not fimilar with codeigniter and don't know if the following can be achieved with the result() function. But fetching the result into an array with result_array(), you should be able to transponse that array with:

$transposedArray = array();
foreach ($scheme->result_array() as $schemerow)
{
    foreach ($schemerow as $columnName => $value)
    {
        $transposedArray[$columnName][] = $value;
    }
}

Then output it with:

foreach ($transposedArray as $header => $values)
{
    echo '<tr>';
    echo "<td class='header'>{$header}</td>";
    foreach ($values as $value)
    {
        echo "<td class='value'>{$value}</td>";
    }
    echo '</tr>';
}

or

// column1 in a row
echo '<tr>';
echo "<td class='header'>Readable Column Name1</td>";
foreach ($transposedArray['columnName1'] as $value)
{
    echo "<td class='value'>{$value}</td>";
}
echo '</tr>';

// column2 in a row
echo '<tr>';
echo "<td class='header'>Readable Column Name2</td>";
foreach ($transposedArray['columnName1'] as $value)
{
    echo "<td class='value'>{$value}</td>";
}
echo '</tr>';

// column3 in a row
...

Upvotes: 1

Related Questions