SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP, MySQL csv export over multiple tables

    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...

    PHP Code:

    $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 .= "\n";

    $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 .= "\n";
    }

    $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...
    PHP Code:
    $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. #2
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does the query return any data if you run it from phpMyAdmin? If not then it's the problem right there.

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2008
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query alone returns all the correct data from phpMyAdmin.
    The problem is only with the extract to the csv file...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •