PHP - I cannot correctly display data in xls file (exported from mysql))

Question

I'm using php. I want to export some data from mysql database to xls file.

The table Answers i have in mysql database:

Table Answer:

Id    Registration_id    Question_id    Answer
1     Reg01                 1            John
2     Reg01                 2            Smith
3     Reg01                 3            [email protected]
4     Reg02                 1            Rachel
5     Reg02                 2            Smith
6     Reg02                 3            [email protected]

The result i obtain from the code below is not the result i expected. The headers are displayed correctly but the answers are displayed in the same line...

Name SecondName Email John Smith [email protected] Rachel Smith [email protected]

I want this result:

Name   SecondName  Email
John    Smith      [email protected]  
Rachel  Smith      [email protected]

Can you tell me how can I do this?

Thanks.

Here is the code:

$conn = mysql_connect("localhost","form","pass");
$db = mysql_select_db("form",$conn);


$query = "SELECT DISTINCT wp_events_question.question as qst, wp_events_answer.answer as ans
FROM wp_events_question, wp_events_answer 
WHERE wp_events_question.id = wp_events_answer.question_id";

$query2 = "SELECT wp_events_question.question as qst, wp_events_answer.answer as ans
FROM wp_events_question, wp_events_answer
WHERE wp_events_question.id = wp_events_answer.question_id and wp_events_detail.id = wp_events_attendee.event_id AND wp_events_attendee.id = wp_events_answer.attendee_id";

$result = mysql_query($query) or die(mysql_error()); //Headers  

 $result2 = mysql_query($query2) or die(mysql_error()); //Answers   

    //Headers
            $tbl= " <table border='1'>";

            $tbl= $tbl . "<tr height='50px'>";

            while($row = mysql_fetch_array($result)) 
            {       
                $tbl= $tbl . "<td WIDTH='50px' align='center'>".$row['qst']."</td>";                
            }   



   //Answers    
                $tbl = $tbl . "</tr>";  
                $tbl = $tbl . "<tr>";
            while($row2 = mysql_fetch_array($result2)) 
            {

                $tbl= $tbl . "<td WIDTH='50px' align='center'>".$row2['ans']."</td>";

            }
            $tbl = $tbl . "</tr>";
            $tbl = $tbl . "</table>";


            header("Cache-Control: no-stor,no-cache,must-revalidate");
            header("Cache-Control: post-check=0,pre-check=0", false);
            header("Cache-control: private");
            header("Content-Type: application/force-download");
            header("Content-Disposition: inline; attachment;filename=Reservations.xls");
            header("Content-Transfer-Encoding: binary");
            header("Pragma: no-cache");
            header("Expires: 0");

            print $tbl;

You do know you can use $tbl .= instead of $tbl= $tbl . ? For the rest, I have no Excel so I can't test this, but I assume generating a proper xls(x) file with one of the many libraries, or failing that, writing an .csv file is more easily understood by Excel then HTML. — Wrikken
– Wrikken, Commented Jan 11, 2012 at 21:03
As so often, HTML masquerading as XLS, why not just name it Reservations.html ?? Excel can still import HTML files: it isn't taken in by the con. — Mark Baker
– Mark Baker, Commented Jan 11, 2012 at 21:42

Mark Baker · Accepted Answer · 2012-01-11 21:37:02Z

1

The code snippet bears no resemblence to your example data.

You're writing each result to a new TD, but all in the same TR, that's why they're all in the same row.

Change:

        $tbl = $tbl . "<tr>"; 
        while($row2 = mysql_fetch_array($result2))  
        { 

            $tbl= $tbl . "<td WIDTH='50px' align='center'>".$row2['ans']."</td>"; 

        } 
        $tbl = $tbl . "</tr>";

to

        while($row2 = mysql_fetch_array($result2))  
        { 
            $tbl = $tbl . "<tr>"; 
            $tbl = $tbl . "<td WIDTH='50px' align='center'>".$row2['ans']."</td>"; 
            $tbl = $tbl . "</tr>"; 
        }

But I've no idea why you're executing the same SQL query twice... Just run one loop against one query with a $firstRow = true; before the loop, and an if ($firstRow) { // display your headers; $firstRow = false; } as the first action in the loop

edited Jan 11, 2012 at 21:37

answered Jan 11, 2012 at 21:31

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP - I cannot correctly display data in xls file (exported from mysql))

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related