PHP, MySQL csv export over multiple tables

PHP
1

Hi

I have created PHP reports that display on screen. The information for these reports come from 2 tables thus I have used an INNER JOIN in the mySql query to gather the information for the reports.

I now have to create a csv export of these exact reports - i.e. gather the info over 2 tables and output the data to the csv file to be exported.

The usual way of exporting it won’t work as that exports only the contents of ONE specific table…



$host = 'localhost';
$user = 'mysqluser';
$pass = 'mysqluser';
$db = 'Resources';
$table = 'user_time';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
       $csv_output .= $row['Field']."; ";
       $i++;
    }
}
$csv_output .= "\
";

$values = mysql_query("SELECT * FROM ".$table." WHERE week_ending='$week_ending'");
while ($rowr = mysql_fetch_row($values)) {
    for ($j=0;$j<$i;$j++) {
        $csv_output .= $rowr[$j].", ";
    }
    $csv_output .= "\
";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

The query that I’m running for the report looks like this…


$query="select max(pd.pd_id), hs.*, pd.*
					from health_screening hs
						inner join personal_details pd on pd.user_id = hs.user_id
					where
						hs_id = (select hs_id from health_screening where user_id = '$query_user_id' order by hs_id limit 1)
						or hs_id = (select hs_id from health_screening where user_id = '$query_user_id' order by hs_id desc limit 1)
						or hs_id = (select hs_id from health_screening where user_id = '$query_user_id' order by hs_id desc limit 1 offset 1)
					group by hs.hs_id
						order by hs_id";

I have tried using the above query to replace the query in the export code but the file it produces is empty.

Please could anyone tell me how I can get the report query to run and export the fields into a CSV file?

Thanks

2

Does the query return any data if you run it from phpMyAdmin? If not then it’s the problem right there.

3

The query alone returns all the correct data from phpMyAdmin.
The problem is only with the extract to the csv file…