db input
Moderator: General Moderators
db input
hi, could someone help me with a code to insert a file like this:
date > time > to > secs > cost > service
20/12/2006 23:29:52 00000000 24 $0.099 Cellcom
21/12/2006 00:35:48 12343435 12 $0.099 Cellcom
21/12/2006 00:48:00 45645674 36 $0.099 Cellcom
21/12/2006 01:57:08 45665434 60 $0.099 Cellcom
into a my sql database? how do i split up the fields and lines?
date > time > to > secs > cost > service
20/12/2006 23:29:52 00000000 24 $0.099 Cellcom
21/12/2006 00:35:48 12343435 12 $0.099 Cellcom
21/12/2006 00:48:00 45645674 36 $0.099 Cellcom
21/12/2006 01:57:08 45665434 60 $0.099 Cellcom
into a my sql database? how do i split up the fields and lines?
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
scanf is probably faster performance but i'm not familar with it:
Code: Select all
$str = '20/12/2006 23:29:52 00000000 24 $0.099 Cellcom
21/12/2006 00:35:48 12343435 12 $0.099 Cellcom
21/12/2006 00:48:00 45645674 36 $0.099 Cellcom
21/12/2006 01:57:08 45665434 60 $0.099 Cellcom';
$matches = array();
$str = preg_split("~[\n\r]+~", $str);
foreach ($str as $i => $line) {
$matches[$i] = preg_split('~\s+~', $line);
}
echo '<pre>';
print_r($matches);playing around with the fscanf that feyd suggested and i came up with this. not sure if there is any performance benefits using the different methods
this echos out
but obviously can be amended to write to the db
Code: Select all
<?php
//var for holding column names
$columns = '';
//var for holding sql statment
$sqlString = '';
//handle for the file
$handle = fopen("data.txt", "r");
//monitor line number
$lineNo = 1;
//initial pattern (for field names / first row)
$pattern = "%s\t>\t%s\t>\t%s\t>\t%s\t>\t%s\t>\t%s\n";
//iterate over the file line by line
while ($data = fscanf($handle, $pattern)) {
//assign line data to variables based on pattern
list ($date, $time, $to, $secs, $cost, $service) = $data;
//if line 1 get the field names, increment counter and reset pattern for data rows
if ($lineNo == 1){
$columns = "$date, $time, $to, $secs, $cost, $service";
$pattern = "%s\t%s\t%s\t%s\t%s\t%s\n";
$lineNo++;
} else {
//build a query string,
//replace with actually running the query, remember to escape the data.
$sqlString .= "INSERT INTO table ($columns) VALUES ('$date', '$time', '$to', '$secs', '$cost','$service');\n";
}
}
fclose($handle);
echo $sqlString;
?>Code: Select all
INSERT INTO table (date, time, to, secs, cost, service) VALUES ('20/12/2006', '23:29:52', '00000000', '24', '$0.099','Cellcom');
INSERT INTO table (date, time, to, secs, cost, service) VALUES ('21/12/2006', '00:35:48', '12343435', '12', '$0.099','Cellcom');
INSERT INTO table (date, time, to, secs, cost, service) VALUES ('21/12/2006', '00:48:00', '45645674', '36', '$0.099','Cellcom');
INSERT INTO table (date, time, to, secs, cost, service) VALUES ('21/12/2006', '01:57:08', '45665434', '60', '$0.099','Cellcom');