Putting an array in to a CSV using fputcsv

[Kristoff1875]

Hi guys,

 

I've got quite a few fields in my tables that i've serialised to keep the number of fields down. For everything else that works perfect as it stores the data and when needed I can use the following as an example:

$dateofbirth = unserialize($row['dateofbirth']);
$dobday = $dateofbirth[0];
$dobmonth = $dateofbirth[1];
$dobyear = $dateofbirth[2];

 

 

Date of birth is stored as dd,mm,yyyy and for everything else I can call it fine. My issue is now that i'm trying to use fputcsv to create a csv file using the following:

$result = mysqli_query($con, 'SELECT u.user_id, b.dateofbirth FROM Users u INNER JOIN Basic b USING (user_id) ORDER BY user_id DESC');


$fp = fopen('latest.csv', 'w');
 
fputcsv($fp, array('User ID', 'DOB' ));

 

 

 

The CSV generates, but for the date of birth column in the csv it outputs as "a:3:{i:0;s:2:"03";i:1;s:2:"02";i:2;s:4:"1986";}" because it's obviously still serialised. What is my best and or easiest way of handling these fields?

 

Many thanks in advance.

Edited January 21, 2015 by Kristoff1875

[Barand]

You'll have to unserialize() then join() to get a string value.

 

Why are a you using a relational database to store data in a manner that prevents you from ever using it as a relation database? I recommend you read up data normalization and relational database design.

[Kristoff1875]

Hi Barand, Hope you're well.

 

The relation is because there are 5 or 6 different tables for different things, but all have the UserID field so they can be joined.

Edited January 21, 2015 by Kristoff1875

[Barand]

As a space-saver it isn't much of an idea either

 

A DATETIME field in a table occupies 8 bytes whereas

$serialized_date = 'a:3:{i:0;s:2:"03";i:1;s:2:"02";i:2;s:4:"1986";}';
echo strlen($serialized_date);       #--->  47

39 wasted bytes!

[Kristoff1875]

For these fields I can't use a datetime field, I can't remember why, but it won't work... I set these up a few months ago and it was either give the month/day etc a different column or make it an array. I tried datetime and it was conflicting with what I was doing if I remember correctly.

[Barand]

... it was either give the month/day etc a different column or make it an array.

 

Nonsense!. You can access any bits of a datetime field that you need. EG

mysql> SELECT dob FROM date_sample;
+---------------------+
| dob                 |
+---------------------+
| 2014-09-11 20:57:22 |
+---------------------+

mysql> SELECT YEAR(dob) as year FROM date_sample;
+------+
| year |
+------+
| 2014 |
+------+

mysql> SELECT MONTH(dob) as month FROM date_sample;
+-------+
| month |
+-------+
|     9 |
+-------+

mysql> SELECT DAY(dob) as day FROM date_sample;
+------+
| day  |
+------+
|   11 |
+------+

mysql> SELECT DATE_FORMAT(dob, '%b %d') as birthday FROM date_sample;
+----------+
| birthday |
+----------+
| Sep 11   |
+----------+

[maxxd]

I'd be interested to know why it won't work. What you describe with the 5 or 6 table set-up all joined on UserID is exactly how a relational database should work - hence the 'relational' in the name. If a DATETIME field is conflicting with what you're doing, then honestly you're handling that data incorrectly. Serialized data crammed into a VARCHAR or TEXT field may seem like it works when it's set up, but it will almost always come back to bite you in the butt later on.

[Kristoff1875]

It wasn't the relation that wasn't working, if I remember correctly it was to do with extracting the data to create a PDF the other side. As I say, I can't remember 100%, although reading Barand's last post, it makes me think it was possible, I just didn't know how!

Edited January 21, 2015 by Kristoff1875

[ginerjm]

May I just ad my two cents here?

 

BoneHead Move. You have data. You are using a database. Use It Properly. A csv string/file inside a db table? Now I've heard of every stupid thing that a newbie can possibly do wrong. In my previous incarnation a programmer working for me would never be allowed to do this (let alone think of doing it!) or he wouldn't work for me very much longer.

 

You are getting good advice from people who know what they are talking about. You came here just for that, didn't you? LISTEN TO THEM! You're driving your car down a one-way street in the wrong direction. Turn around now and stop debating them.

[Kristoff1875]

The CSV is being generated by the data from the database, not the other way.

[maxxd]

OK - so you're not storing serialized data in a table field, but serializing it before you try to convert it to CSV? 'Cause now I'm a bit confused, I think...

[ginerjm]

How did the csv'ed data get INTO that database? It needs to stop. Then you won't have this problem.