db input

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

db input

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

fscanf() would seem useful.
wildwobby
Forum Commoner
Posts: 66
Joined: Sat Jul 01, 2006 8:35 pm

Post by wildwobby »

or maybe explode?
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Experiment with format strings.

You do it one line at a time.
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

fgets(). fscanf() will automatically stop at the first carriage return.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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);
yshaf13
Forum Commoner
Posts: 72
Joined: Mon Apr 03, 2006 7:59 pm

re

Post by yshaf13 »

thats works great! thanx!
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

Post 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
Post Reply