How to import a memo type field to mySQL

[kuau]

Holy crap. I can't even follow that logically nevermind write code to execute it. I thought this was going to be easier. I'm going to try phpMyAdmin again once I have the data ready. God help me if that doesn't work!

[djr33]

Imagine this setup: 1,2,3;4,5,6;3,9,0;#,#,#;....

So you have two levels of repetition: groups of 3 numbers [which is itself a group].

//In order to separate it you use:
$string = '1,2,3;4,5,6;3,9,0;#,#,#;....';
$split = explode(';',$string); //split the entries apart in the data
foreach($split as $key=>$value) { //go through each entry then
$split[$key] = explode(',',$value); //split up the entry into its parts
}
//now $split is a two-level array-- an array of entries, each of which is an array of items

//now, to USE this you do:
foreach($split as $entry) {
echo $entry[0],$entry[1],$entry[2]; //prints out '123' or '456', '390', ###, ....
} 

So it's just two layers of arrays: one to hold each entry and one FOR each entry that splits up the data into its parts.


(Note: this is the reason that databases are nice-- without them everything would be like this when organizing data.)



Also, be aware that whenever you are doing this level of string parsing, it is CRUCIAL that the data have no errors or inconsistencies. Batch processing will do very unpredictable things when there's even one error in a particularly wrong place. Error-checking is much harder to program, so it's better to make good input than fool-proof code. (Of course it's the opposite if you have users of your website inputting text-- then you must verify. But here you're the only one using it so it should be ok.)



I hope that clarifies somewhat. If you can get phpmysqladmin to work, great. If not, this is probably your only other option ('this' in the general sense-- using PHP to parse it. You might find another way to split the data).

[kuau]

Hey Daniel... After I manipulated the data a bit, I tried the import again and it worked!! Which is a huge relief because from Access I could find no way to specify a record separator and there didn't seem to be one (maybe a CR or LF).

Anyway, the data is all safe and sound on the server and I can go to bed. Thanks so much for your help. I actually think your for each code may work for doing something I thought was impossible with the data, now that it is accessible. But that is not the priority at the moment. Later...

[brudawson]

This script will export all the data in your table and also append the date and time of the export to the filename it is generating.

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$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."");
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;
?>