-1

I know how to export a MySQL TABLE as Excel. But my problem is that I have a complex scenario whereby I am able to get data from multiple MySQL tables and arrange them onto a HTML table output. My question is: how can I get this data output as Excel?

Here is my code

<?php 
include(".../dbconnect.txt");

$result = mysql_query("select * from main_table where jobid='".$_GET['jobid']."'") or die(mysql_error());
?>

<table width='1450'>
<tr>
<td>S/N</td>
<td>Job Title</td>
<td>Applicant</td>
<td>Date</td>
<td>Expe</td>
<td>Course</td>
<td>Email</td>
<td>Quali</td>
<td>Age</td>
<td>Gender</td>
</tr>
<?php
$cnt =1;
while($row = mysql_fetch_array($result)){
$count = $cnt; $cnt++;
?>
<tr>
<td>
<?php echo $count; ?>
</td>
<td>
<?php
$re = mysql_query("select * from table1 where id='".$_GET['jobid']."'") or die(mysql_error());
$rw = mysql_fetch_array($re);
echo $rw['title'];
?>
</td>
<td> 
<?php
$re1 = mysql_query("select * from table2 where email='".$row['email']."'") or die(mysql_error());
$rw1 = mysql_fetch_array($re1);

echo ucwords($rw1['fname']); 
echo "&nbsp;";
echo ucwords($rw1['lname']);
?>
</td>
<td><?php echo $row['date']; ?></td>
<td>
<?php 
$re2 = mysql_query("select * from table3 where email='".$row['email']."'") or     die(mysql_error());
$rw2 = mysql_fetch_array($re2);
echo $rw2['years'];
?>
</td>
<td>
<?php 
$re3 = mysql_query("select * from table4 where email='".$row['email']."'") or die(mysql_error());
$rw3 = mysql_fetch_array($re3);
echo ucwords($rw3['course']);
?>
</td>
<td>
<?php echo $row['email']; ?>
</td>
<td> 
<?php 
echo $rw3['quali'];
?>
</td>
<td>
<?php echo $rw1['age']; ?>
</td>
<td>
<?php echo $rw1['gender']; ?>
</td>
</tr>
<?php } ?>
</table>

The HTML result is something like this:

S/N Job Title   Applicant   Date    Expe    Course  Email   Quali   Age    Gender
1    Program Officer - Clinical Services     Username Surname   Wed 31, Jul, 2013    1       Agricultural Engineering    useremail@yahoo.com     BEng    24  Male
2    Program Officer - Clinical Services     Username Surname   Wed 31, Jul, 2013    3       Political Science   useremail@gmail.com     BSc     33  Male
3    Program Officer - Clinical Services     Username Surname   Wed 31, Jul, 2013    1   Microbiology    useremail@yahoo.com     HND     25  Female

How to output this table in Excel instead?

brasofilo
  • 25,496
  • 15
  • 91
  • 179
Sam Adah
  • 3
  • 5
  • 2
    Use one of the many libraries like [PHPExcel](https://phpexcel.codeplex.com/) that can write Excel files – Mark Baker Aug 18 '13 at 17:46
  • Immediately following your initial query, loop through the results and do all the other sub queries and store everything in an array. Now the array can be passed to either your html template (you will need to adjust it to use the array) or to your excel generator. You will also end up with code that is much easier to read. – Cerad Aug 18 '13 at 21:09

1 Answers1

0

This is an example of how to convert all your queries into a nice array that can be passed to either your html or your excel. I got bored after awhile so I only entered a few queries. But you should be able to from here.

$rows = mysql_query("select * from main_table where jobid='".$_GET['jobid']."'") or die(mysql_error());
$items = array();

while($row = mysql_fetch_array($result))
{
    $item = array();

    // Master stuff
    $item['date'] = $row['date'];

    // Title
    $re = mysql_query("select * from table1 where id='".$_GET['jobid']."'") or die(mysql_error());
    $rw = mysql_fetch_array($re);
    $item['title'] = $rw['title'];

    // Names
    $re1 = mysql_query("select * from table2 where email='".$row['email']."'") or die(mysql_error());
    $rw1 = mysql_fetch_array($re1);

    $item['name'] = ucwords($rw1['fname']) . ' ' . ucwords($rw1['lname']);

    // ... rest of queries

    // Add to list
    $items[] = $item;
}

I will suggest becoming a bit familiar with the sql left join operation. You can dramatically reduce the number of queries. Possible even down to one.

Cerad
  • 48,157
  • 8
  • 90
  • 92