Page 1 of 1

db input

Posted: Tue Feb 06, 2007 6:02 pm
by yshaf13
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?

Posted: Tue Feb 06, 2007 6:14 pm
by feyd
fscanf() would seem useful.

Posted: Tue Feb 06, 2007 6:49 pm
by wildwobby
or maybe explode?

Posted: Wed Feb 07, 2007 1:15 am
by yshaf13
what would the format parameter of fscanf be? and if i used explode i understand how to explode one line, but multiple lines?

Posted: Wed Feb 07, 2007 1:20 am
by feyd
Experiment with format strings.

You do it one line at a time.

re

Posted: Wed Feb 07, 2007 1:25 am
by yshaf13
i'm sorry i don't understand, if i'm loading a file with 50 rows in it how do i access one row at a time?

Posted: Wed Feb 07, 2007 1:27 am
by feyd
fgets(). fscanf() will automatically stop at the first carriage return.

Posted: Wed Feb 07, 2007 3:54 am
by Ollie Saunders
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);

re

Posted: Wed Feb 07, 2007 6:16 am
by yshaf13
thats works great! thanx!

Posted: Wed Feb 07, 2007 8:18 am
by Kadanis
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

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;
?>
this echos out

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');
but obviously can be amended to write to the db