CSV Export: Multiple Database Rows to Single Row

Question

Hi,

The problem: I Have an Export from a Database, however it needs to extract data from two tables. I Achieve this with an inner join.

artikel is the table with the basic information. It contains the id, article number, name, type and package form.

article_content is the table which contains the text which is part of the article, however, there are multiple languages. Every Article has a row for each language.

The Export Code

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=artikeldatenbank-' . date("dmyhi") . '.csv');

$output = fopen('php://output', 'w');

$stmt = $db->prepare('SELECT artikel.id, artikel.artikel_nummer, artikel.artikel_name, artikel.artikel_packung, artikel.artikel_cat, article_content.article_text, article_content.article_recuse, article_content.article_spec FROM artikel INNER JOIN article_content ON article_content.article_id = artikel.artikel_nummer');
$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc())
fputcsv($output, $row);

What I want to Achieve

I need every row from the article_content table by it's article in a single line, instead of multiple lines. Sorry for the links, but imgur doesn't let me upload these. (Don't know why)

What happens now (image): http://prntscr.com/ek86bn

What I Want (edited image): http://prntscr.com/ek87ct

What is the best way to achieve this? Is this possible on the way I do it now?

Ahmad Rezk · Answer 1 · 2017-03-16T07:08:20.577

1

Skip the VIEW solution, Solve it by code, my suggestion is

$result = $stmt->get_result();

$artikeID = '';
$newRow = [];
while ($row = $result->fetch_assoc())
{
    if($artikeID != $row['id'])
    {
        if(!empty($newRow))
        {
            fputcsv($output, $newRow);
            $newRow = [];
        }
        $artikeID = $row['id'];
        $newRow = $row;
    }
    else 
    {
        $newRow['title-'.$row['id']] = $row['artikel_name'];
        $newRow['content-'.$row['id']] = $row['article_text'];
    }
}

edited Mar 16 '17 at 07:08

answered Mar 15 '17 at 12:25

Ahmad Rezk

198
1
13

@Ahmed This is partly a solution. Now it's 1 line, but only one row of all the language content is entered: prntscr.com/ek8eq5. The English and French translations in this case, are gone. Additionaly are articles without any language_content gone too – Synthiatic Mar 15 '17 at 13:15
You are right, try to make a VIEW in your DB to contain each language content [Check this question](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Ahmad Rezk Mar 15 '17 at 13:29
Don't know how to correctly implement that in the Query. could you write me an example? – Synthiatic Mar 15 '17 at 13:38

CSV Export: Multiple Database Rows to Single Row

1 Answers1