Efficient way to query SQL and format to CSV

Question

I have a MySQL database that I am keeping temperature readings from several different sensors. I initially thought of using three different tables to store my data:

mysql> select * from sensor_info;
+----+------------------+------+--------+
| id | address          | name | active |
+----+------------------+------+--------+
|  1 | 28D684CD02000057 | NULL |      1 |
|  2 | 28099B49030000D8 | NULL |      1 |
|  3 | 28339ACD0200004B | NULL |      1 |
+----+------------------+------+--------+

mysql> select * from data_period limit 4;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2012-06-30 09:35:02 |
|  2 | 2012-06-30 09:36:22 |
|  3 | 2012-06-30 09:37:46 |
|  4 | 2012-06-30 09:40:36 |
+----+---------------------+

mysql> select * from data_points limit 4;
+----+-------------+-----------+-------+
| id | data_period | sensor_id | data  |
+----+-------------+-----------+-------+
|  1 |           1 |         1 | 77.90 |
|  2 |           1 |         2 | 77.34 |
|  3 |           1 |         3 | 77.56 |
|  4 |           2 |         1 | 78.01 |
+----+-------------+-----------+-------+

What I'm trying to do is to take my stored data and put it into a CSV file so I can display it using dygraphs Javascript library. I need to get my data into a format like this:

date,temp1,temp2,temp3
2012-06-30 09:35:02,77.90,77.34,77.56
2012-06-30 09:36:22,78.01,77.36,77.59
....

Every way I start to do this (using PHP), I seem to make this overly complicated and have to put queries inside loops inside loops. Am I making this harder on myself than I need to?

Will most of the work be done using the queries or using PHP? Down the road, I will also want to add code that will place NULL in the CSV if a temperature reading is missing from a particular timestamp.

I'm not looking for a very specific answer, I just want to know what direction I should go. I don't even know how to start to format my data from the database or if I should try looking at a different format to store my info in the database.

http://stackoverflow.com/questions/11198008/zend-db-whats-the-right-strategy-to-export-large-amounts-of-data-to-csv-chunk/11198091#11198091 — , Jun 30 '12 at 23:11

score 2 · Accepted Answer · edited Jul 01 '12 at 01:14

I'd run a single select query to join the lot together. You can use an outer join where there might not be data.

SELECT data_period.ts AS date, dp1.data AS temp1, dp2.data AS temp2, dp3.data AS temp3
FROM data_period
LEFT OUTER JOIN data_points AS dp1 ON dp1.data_period=data_period.id AND dp1.sensor_id=1
LEFT OUTER JOIN data_points AS dp2 ON dp2.data_period=data_period.id AND dp2.sensor_id=2
LEFT OUTER JOIN data_points AS dp3 ON dp3.data_period=data_period.id AND dp3.sensor_id=3

(See: SQL Fiddle Example )

That should give you a single set of results that you can just loop through.

If you really want MySQL to do most of the work, you can change the first line to (I think) SELECT data_period.ts +','+ IFNULL(dp1.data,'NULL') +','+ IFNULL(dp2.data,'NULL') +','+ IFNULL(dp3.data,'NULL')

To Synthesize the comment and the answer and to get this out to a file:

SELECT data_period.ts AS date, dp1.data AS temp1, dp2.data AS temp2, dp3.data AS temp3
INTO OUTFILE '/home/user/output.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM data_period LEFT OUTER JOIN data_points AS dp1 ON dp1.data_period=data_period.id      
AND dp1.sensor_id=1 LEFT OUTER JOIN data_points AS dp2 ON  
dp2.data_period=data_period.id   AND dp2.sensor_id=2 LEFT OUTER JOIN data_points AS dp3 
ON dp3.data_period=data_period.id AND dp3.sensor_id=3;

score 0 · Answer 2 · answered Jun 30 '12 at 23:43

To do this you can use two SQL statements giving an outer and an inner loop. The first statement

select  ts from data_points as point 
inner join data_period on data_period = data_period.id 
group by ts 
order by ts

will get a list of the dates that you have data for.

The second one on the inner loop will get a list of data values ordered by the sensor id - if you have missing values in the data_points then this list will be wrong and have gaps; so if you do have gaps you may need to do an extra step to get the list of sensors and then populate this list from the data coming back from the query.

The following example illustrates how you could proceed with this.

// get the outer list
$sql = "select  ts from data_points as point 
        inner join data_period on data_period=data_period.id 
        group by ts 
        order by ts";

$result = mysql_query($sql);

$result || die(mysql_error());

while($row = mysql_fetch_row($result))
{
    // get the date for the second query
    $date = $row[0];

    $sql = "select  data from data_points as point
            inner join data_period on data_period=data_period.id
            inner join sensor_info on sensor_id = sensor_info .id
            where ts = '$date'
            order by sensor_id";

    $result_1 = mysql_query($sql);

    // now create an array from the values - we could use mysql_fetch_array to do this
    // but this way allows us the possibility of extending the processing to 
    // take into account missing values.
    $vals = array();
    while($row_1 = mysql_fetch_row($result_1)) {
        $vals[]=$row_1[0];
    }
    print "$date,".join(",",$vals)."\n";
}

This is exactly what I was hoping to accomplish. It is the SQL queries that I always get stuck on. Inner joins/Outer joins/ect are a concept that I have a hard time becoming good at. — Tim M, Jul 01 '12 at 00:43
for a good explanation of joins see http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — Richard Harrison, Jul 01 '12 at 06:44

score 0 · Answer 3 · answered Jun 30 '12 at 23:45

I'd recommend just doing the csv formatting in php after the fact.

Here is a handy function I found online and really love using. And if you wrap your SQL calls in a class then you can just have this method available after you query. :)

Note I modified this to me MSSQL but its easy to replace it with just mysql.

  public final function SQL2CSV() {
        // set initial .csv file contents
        $CSV = array();
        $rowid = 0;
        //This would be your query.
        $res = $this->fetchQuery();

        // get column captions and enclose them in doublequotes (") if $print_captions is not set to false
            for ($i = 0; $i < mssql_num_fields($res); $i++) {
                $fld = mssql_fetch_field($res, $i);
                $colnames[] = $fld->name;
            }
            // insert column captions at the beginning of .csv file
            $CSV[$rowid] = implode(",", $colnames);


        // iterate through each row
        // replace single double-quotes with double double-quotes
        // and add values to .csv file contents
        if (mssql_num_rows($res) > 0) {
            while ($row = mssql_fetch_array($res, MSSQL_NUM)) {
                $rowid++;
                for ($i = 0; $i < sizeof($row); $i++)
                    //$row[$i] = '"'.str_replace('"', '""', $row[$i]).'"';
                    $row = str_replace (',', '', $row);

                $CSV[$rowid] = "\n".implode(",", $row);

            }
        }


        RETURN $CSV;
    }

Efficient way to query SQL and format to CSV

3 Answers3