Exporting from MySQL to CSV using PHP

[Django x2]

Ok, so I've got this working, kind of.

On the website, enquiries that are made are stored in a MySQL database.

This is:

• id
• name
• telephone
• email
• address

I've also set up a button that when clicked, allows admins to download a CSV file of this database containing the above information. The problem is, the address only shows the first line.

The submission form has a textarea for the address and people would generally enter the address as:

123 Fake Street
MyTown
PO35 8CO

That appears in phpMyAdmin fine too.

Basically, how can I get the whole output of the address cell into CSV format?

Ta

 

[dim_pwn]

Not an expert but perhaps when exporting you need to remove the line breaks. Use replace() on the char(13)'s.

You could also have another field in the table, which has a on-update trigger to copy the address and modify then put into the field. Then extract this new field out instead of the textarea?

 

[Django x2]

Ok, rather then, is there a way that on submitting the form, the carriage returns can be stripped out?

 

[Redrum]

CSV files usually use a new line character to say when you are moving on to the next record, so if you put data into a CSV that has a line break, then it can break because the line break says that the current record has ended and you're on to the next one.

But, you mention it only shows the first line but you didn't say anything about the following lines also being broken, so it makes me wonder if you actually have a problem. If it was breaking, then you would see the second line of the address in the first column of the next line, so the problems would be worse than just the address not showing. Maybe you just need to click on the cell to show the other lines, or make the row taller in your speadsheet program?

But if it is breaking, all you need to do is put some encapsulation. The usual character to use for that would be a quote. So for example, instead of:

5,bob,07788123456,[email protected],123 Fake Street
MyTown
PO35 8CO

You would output:

"5","bob","07788123456","[email protected]","123 Fake Street
MyTown
PO35 8CO"

And then your spreadsheet or anything else reading the file will be able to recognise that the line breaks in the address are not moving on to the next record because they are inside the quotes.

An alterative is that when you are generating the file, instead of one column for address, you have have a number of them, e.g. add1, add2 etc. Then on each record, you take the address and explode it like so:

$address_array = explode("\n",$row['address']);
for($i=0;$i<sizeof($address_array);$i++)
{
	${'add'.($i+1)} = $address_array[$i];
}

The above code would split the address up into 3 variables, $add1, $add2 and $add3. The for loop isn't really needed because you could output it directly from $address_array.

The only problem you have with putting each line in its own column is that you've let people enter their addresses in a text area, so you don't know how many lines to allow for. Someone could have put an address with 20 lines for all you know unless you first go through every record counting them. So just putting encapsulation on is possibly the better solution.

Or as dim_pwn said, you could replace the new lines, so you could do something like

$address = str_replace("\n",", ");

This would put it all on one line separated by commas, but depending on what someone is doing with the CSV, changing the address like that might not be convenient and it could be confusing if the person that wrote the address put their own commas in on some lines.

 

[Django x2]

Thanks, that makes sense.