Export MySQL to CSV Problem

it is not exporting all the data

Page 1 of 1

4 Replies - 3207 Views - Last Post: 12 July 2010 - 06:57 AM Rate Topic: -----

#1 itdemo  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 16-May 08

Export MySQL to CSV Problem

Posted 12 July 2010 - 03:50 AM

Hi everyone!

Been a while since I have posted here. I have landed myself in a summer job and things were going great for the first week. Now I have run into a problem :(

I was using a script to export data from a MySQL table but for some reason it is not exporting all the data. Is there a limit to how many rows MySQL will select? Or is my problem in the query? I have looked over this many times but my PHP is not the best so... Hopefully one of the bright minds here can point me in the right direction. :D

Here's the PHP script

function exportMysqlToCsv($filename = 'export.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";
    $sql_query = "SELECT *
					FROM clients c
					 INNER JOIN domains d
					 ON c.id = d.client_id 
					 INNER JOIN invoice_items ii
					 ON ii.domain_id = d.client_id AND 
					  ii.domain_id = c.id";
 
    // Gets the data from the database
    $result = mysql_query($sql_query);
    $fields_cnt = mysql_num_fields($result);
 
 
    $schema_insert = '';
 
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
 
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
 
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
 
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed . 
					str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
 
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
 
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
 
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose ;)/>
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
 
}



Thanks in advanced!
Gavin

Is This A Good Question/Topic? 0
  • +

Replies To: Export MySQL to CSV Problem

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3030
  • View blog
  • Posts: 10,553
  • Joined: 08-August 08

Re: Export MySQL to CSV Problem

Posted 12 July 2010 - 05:39 AM

What have you tried?
Have you verified that your query produces the results you expect, perhaps by running it manually in phpmyadmin?
Was This Post Helpful? 1
  • +
  • -

#3 itdemo  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 16-May 08

Re: Export MySQL to CSV Problem

Posted 12 July 2010 - 06:45 AM

Yep I ran the query in phpmyadmin and I get the same result as I do in the .csv file.
It only shows the first 200 or so entries, even when I manually set the limit to the correct amount of entries (402).

Oh... Just now I realize not all the tables have the same amount of records. The one with the least of them has 282 which must be where I am getting my entries.
Hum... I'm gonna' ask the boss why that is... He probably only needs the 282 records anyway.

I'll get back to you after.
Thanks!
Gavin
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Export MySQL to CSV Problem

Posted 12 July 2010 - 06:50 AM

Since you're doing an INNER JOIN it's only going to pull records that exist in all 3 tables you're querying. You could try using a LEFT JOIN which will get you all the matched records from the 3 tables along with all the records in the first table that aren't matched.
Was This Post Helpful? 1
  • +
  • -

#5 itdemo  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 16-May 08

Re: Export MySQL to CSV Problem

Posted 12 July 2010 - 06:57 AM

View PostPsychoCoder, on 12 July 2010 - 01:50 PM, said:

Since you're doing an INNER JOIN it's only going to pull records that exist in all 3 tables you're querying. You could try using a LEFT JOIN which will get you all the matched records from the 3 tables along with all the records in the first table that aren't matched.


You lifesaver!
Thanks man worked like a charm ;)

Gavin

Oh BTW: How do I mark this topic as solved? :)

This post has been edited by itdemo: 12 July 2010 - 06:58 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1