[SOLVED] Filtering content from a file

[sasa]

now you have value1:value2:value3:value4

for inserting in db we need

INSERT ... VALUES ('value1', 'value2', 'value3', 'value4')

1st part is constant

for 2nd part you need to change : to ', ' and add (' to front and ') to end of string

construct sql string and insert in db

do this for each line

[monkeypaw201]

now you have value1:value2:value3:value4

for inserting in db we need

INSERT ... VALUES ('value1', 'value2', 'value3', 'value4')

1st part is constant

for 2nd part you need to change : to ', ' and add (' to front and ') to end of string

construct sql string and insert in db

do this for each line

 

So, it would be a find and replace? and then append?

[discomatt]

A real finished query is the final query you want this script to perform, with real sample data...

 

INSERT INTO `clients` (`col`, `col2`, `col3`) VALUES ('A', 'B', 'C'), ('D', 'E', 'F')

 

ect.

[monkeypaw201]

A real finished query is the final query you want this script to perform, with real sample data...

 

INSERT INTO `clients` (`col`, `col2`, `col3`) VALUES ('A', 'B', 'C'), ('D', 'E', 'F')

 

ect.

 

Here you go:

 

INSERT INTO `clients` (`id`, `callsign`, `cid`, `realname`, `clienttype`, `frequency`, `latitude`, `longitude`, `altitude`, `groundspeed`, `planned_aircraft`, `planned_tascruise`, `planned_depairport`, `planned_altitude`, `planned_destairport`, `server`, `protrevision`, `rating`, `transponder`, `facilitytype`, `visualrange`, `planned_revision`, `planned_flighttype`, `planned_deptime`, `planned_actdeptime`, `planned_hrsenroute`, `planned_minenroute`, `planned_hrsfuel`, `planned_minfuel`, `planned_altairport`, `planned_remarks`, `planned_route`, `planned_depairport_lat`, `planned_depairport_lon`, `planned_destairport_lat`, `planned_destairport_lon`, `atis_message`, `time_last_atis_recieved`, `time_logon`, `heading`, `QNH_iHg`, `QNH_Mb`) VALUES (NULL, '11991', '929251', 'Dmitry Belov UEEE', 'PILOT', '118.75', '41.26159', '69.28548', '1431', '10', 'B/AN12/F', '330', 'UTTT', '17700', 'UTDD', 'EUROPE-CW', '100', '1', '1200', 'NONE', '20', '1', 'I', '0', '0', '0', '0', '0', '0', 'UTTT', 'hi', 'UTTT UA G3 FC A103 SX UTDD', '41.257306', '069.281667', '38.543333', '068.825000', 'KBOS ATIS', '20080811174338', '20080811174335', '89', '29.74', '1006.97');

[discomatt]

If you give me some REAL data and a REAL finished database query, I could probably give you a more detailed example.

 

I need the course data to go along with it.

 

It's hard to add x and y when you only know one of them

[monkeypaw201]

11991:929251:Dmitry Belov UEEE:PILOT:118.75:39.40068:68.87036:17518:278:B/AN12/F:330:UTTT:17700:UTDD:EUROPE-CW:100:1:1200:NONE:20:2:I:0:1758:0:0:0:0:UTTT::UTTT UA G3 FC A103 SX UTDD:41.257306:069.281667:38.543333:068.825000:KBOS ATIS:20080811174338:20080811174335:184:29.74:1007.01:

[sasa]

$sql = "INSERT INTO ... VALUES (NULL, '". str_replace(':', "', '",$line)."');";
mysql_query($sql) or die(mysql_error());

[monkeypaw201]

$sql = "INSERT INTO ... VALUES (NULL, '". str_replace(':', "', '",$line)."');";
mysql_query($sql) or die(mysql_error());

 

What about the loop part 

[sasa]

put this in foreach loop

[monkeypaw201]

put this in foreach loop

 

I assume the $line gets replaced with the $lines?

[sasa]

1st i have $file

2nd i get right part of it  $part

3rd explode $part in lines and get array $lines

4th foreach($lines as $line){

naw in $line i get just one line of file with data and insert it in db

}

[monkeypaw201]

Here is what my finished piece comes out to (with everyone's comments)

 

<?php

$con = mysql_connect("localhost","user","pass");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("databsae", $con);

$file = 'source.txt';
$start = '!CLIENTS:';
$end = ';';

# Put file to an array
$lines = file( $file );

# Set up placeholders
$loop = TRUE;
$capture = FALSE;
$filtered = array();

# Loop through lines, until we've found $end
for( $i = 0, $count = count($lines); $i < $count && $loop === TRUE; $i++ ) {

# Check to see if capturing has been turned on
if ( $capture === TRUE ) {
	# Check to see if this is the endline
	if ( strpos($lines[$i], $end) === 0 )
		# End the loop
		$loop = FALSE;
	else
		# Explode and add to filtered results
		$filtered[] = explode( ':', trim($lines[$i] ) );
}
# Check to see if this is the starting line
elseif ( strpos($lines[$i], $start) === 0 )
	# Turn on capturing
	$capture = TRUE;

} 

# Output results, use foreach loop(s) to build your query
#print_r( $filtered );

#Start loop(s) for queries
foreach( $filtered as $filter ) {

$sql = "INSERT INTO `clients` (`id`, `callsign`, `cid`, `realname`, `clienttype`, `frequency`, `latitude`, `longitude`, `altitude`, `groundspeed`, `planned_aircraft`, `planned_tascruise`, `planned_depairport`, `planned_altitude`, `planned_destairport`, `server`, `protrevision`, `rating`, `transponder`, `facilitytype`, `visualrange`, `planned_revision`, `planned_flighttype`, `planned_deptime`, `planned_actdeptime`, `planned_hrsenroute`, `planned_minenroute`, `planned_hrsfuel`, `planned_minfuel`, `planned_altairport`, `planned_remarks`, `planned_route`, `planned_depairport_lat`, `planned_depairport_lon`, `planned_destairport_lat`, `planned_destairport_lon`, `atis_message`, `time_last_atis_recieved`, `time_logon`, `heading`, `QNH_iHg`, `QNH_Mb`) VALUES (NULL, '". str_replace(':', "', '",$lines)."');";
mysql_query($sql) or die(mysql_error());

}

?>

 

outputs:

Column count doesn't match value count at row 1

 

EDIT: It may be throwing errors because there are some NULL values? that are just skipped (ie value:value::value)

[discomatt]

Not a bad start! This one uses looping to make sure there's no bad quote or anything in your data that might mess stuff up!

 

It also uses the implode() to convert our arrays into usable strings.

 

<?php

$file = 'contents.txt';
$start = '!SOMETHING:';
$end = '!STATS:';

# Put file to an array
$lines = file( $file );

# Set up placeholders
$loop = TRUE;
$capture = FALSE;
$filtered = array();

# Loop through lines, until we've found $end
for( $i = 0, $count = count($lines); $i < $count && $loop === TRUE; $i++ ) {

# Check to see if capturing has been turned on
if ( $capture === TRUE ) {
	# Check to see if this is the endline
	if ( strpos($lines[$i], $end) === 0 )
		# End the loop
		$loop = FALSE;
	else
		# Explode and add to filtered results
		$filtered[] = explode( ':', trim($lines[$i] ) );
}
# Check to see if this is the starting line
elseif ( strpos($lines[$i], $start) === 0 )
	# Turn on capturing
	$capture = TRUE;

} 

$columns = array('id', 'callsign', 'cid', 'realname', 'clienttype', 'frequency', 'latitude', 'longitude', 'altitude', 'groundspeed', 'planned_aircraft', 'planned_tascruise', 'planned_depairport', 'planned_altitude', 'planned_destairport', 'server', 'protrevision', 'rating', 'transponder', 'facilitytype', 'visualrange', 'planned_revision', 'planned_flighttype', 'planned_deptime', 'planned_actdeptime', 'planned_hrsenroute', 'planned_minenroute', 'planned_hrsfuel', 'planned_minfuel', 'planned_altairport', 'planned_remarks', 'planned_route', 'planned_depairport_lat', 'planned_depairport_lon', 'planned_destairport_lat', 'planned_destairport_lon', 'atis_message', 'time_last_atis_recieved', 'time_logon', 'heading', 'QNH_iHg', 'QNH_Mb');

$values = array();

foreach( $filtered as $filter ) {
# Sanitize individual rows
foreach ( $filter as $key => $val )
	$filter[$key] = mysql_real_escape_string( $val );
# Format and implode
$values[] = "('" . implode( "', '", $filter ) . "')";
}

$q = "INSERT INTO `clients` (`" . implode( '`, `', $columns ) . "`) VALUES " . implode( ', ', $values );

echo $q;

?>

[monkeypaw201]

WOW...

 

A LOT of errors...

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'apache'@'localhost' (using password: NO) in update.php on line 51

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in update.php on line 51

 

Over and over and over... probably a good hundred pages worth...

 

EDIT:

 

oops ... forgot to change the user / pass

 

 

anyways... i assume, now i just pass $q through as an insert string?

[sasa]

try

<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }
mysql_select_db("databsae", $con);
$file = 'source.txt';
$start = '!CLIENTS:';
$end = ';';
# Put file to an array
$lines = file( $file );
# Set up placeholders
$loop = TRUE;
$capture = FALSE;
$filtered = array();
# Loop through lines, until we've found $end
for( $i = 0, $count = count($lines); $i < $count && $loop === TRUE; $i++ ) {
# Check to see if capturing has been turned on
if ( $capture === TRUE ) {
	# Check to see if this is the endline
	if ( strpos($lines[$i], $end) === 0 )
		# End the loop
		$loop = FALSE;
	else
		# Explode and add to filtered results
		// you don't need to do explode
		$filtered[] = trim($lines[$i]);
}
# Check to see if this is the starting line
elseif ( strpos($lines[$i], $start) === 0 )
	# Turn on capturing
	$capture = TRUE;

} 

# Output results, use foreach loop(s) to build your query
#print_r( $filtered );

#Start loop(s) for queries
foreach( $filtered as $filter ) {
// change $line to $filter and remove one extra VALUES ('
$sql = "INSERT INTO `clients` (`id`, `callsign`, `cid`, `realname`, `clienttype`, `frequency`, `latitude`, `longitude`, `altitude`, `groundspeed`, `planned_aircraft`, `planned_tascruise`, `planned_depairport`, `planned_altitude`, `planned_destairport`, `server`, `protrevision`, `rating`, `transponder`, `facilitytype`, `visualrange`, `planned_revision`, `planned_flighttype`, `planned_deptime`, `planned_actdeptime`, `planned_hrsenroute`, `planned_minenroute`, `planned_hrsfuel`, `planned_minfuel`, `planned_altairport`, `planned_remarks`, `planned_route`, `planned_depairport_lat`, `planned_depairport_lon`, `planned_destairport_lat`, `planned_destairport_lon`, `atis_message`, `time_last_atis_recieved`, `time_logon`, `heading`, `QNH_iHg`, `QNH_Mb`) VALUES (NULL, '". str_replace(':', "', '",$filter)."');";
mysql_query($sql) or die(mysql_error());
}
?>

[discomatt]

anyways... i assume, now i just pass $q through as an insert string?

 

You got'r. should work fine.

 

And yours and sasa's requires multiple queries. Mine will do it all in one.

[monkeypaw201]

anyways... i assume, now i just pass $q through as an insert string?

 

You got'r. should work fine.

 

And yours and sasa's requires multiple queries. Mine will do it all in one.

 

Thanks discomatt, 1 more question though... something seems to be getting messed up..it looks like something got dropped... all the rows got shifted over 1... the `id` field is an auto-incrementing reference in MySQL

 

[discomatt]

OOPS! Remove that from the $columns array

 

$columns = array('callsign', 'cid', 'realname', 'clienttype', 'frequency', 'latitude', 'longitude', 'altitude', 'groundspeed', 'planned_aircraft', 'planned_tascruise', 'planned_depairport', 'planned_altitude', 'planned_destairport', 'server', 'protrevision', 'rating', 'transponder', 'facilitytype', 'visualrange', 'planned_revision', 'planned_flighttype', 'planned_deptime', 'planned_actdeptime', 'planned_hrsenroute', 'planned_minenroute', 'planned_hrsfuel', 'planned_minfuel', 'planned_altairport', 'planned_remarks', 'planned_route', 'planned_depairport_lat', 'planned_depairport_lon', 'planned_destairport_lat', 'planned_destairport_lon', 'atis_message', 'time_last_atis_recieved', 'time_logon', 'heading', 'QNH_iHg', 'QNH_Mb');

[monkeypaw201]

OOPS! Remove that from the $columns array

 

$columns = array('callsign', 'cid', 'realname', 'clienttype', 'frequency', 'latitude', 'longitude', 'altitude', 'groundspeed', 'planned_aircraft', 'planned_tascruise', 'planned_depairport', 'planned_altitude', 'planned_destairport', 'server', 'protrevision', 'rating', 'transponder', 'facilitytype', 'visualrange', 'planned_revision', 'planned_flighttype', 'planned_deptime', 'planned_actdeptime', 'planned_hrsenroute', 'planned_minenroute', 'planned_hrsfuel', 'planned_minfuel', 'planned_altairport', 'planned_remarks', 'planned_route', 'planned_depairport_lat', 'planned_depairport_lon', 'planned_destairport_lat', 'planned_destairport_lon', 'atis_message', 'time_last_atis_recieved', 'time_logon', 'heading', 'QNH_iHg', 'QNH_Mb');

 

Output:

 

Column count doesn't match value count at row 1

[discomatt]

Hm, then there's an issue with your data... try this...

 

 

<?php

$file = 'contents.txt';
$start = '!SOMETHING:';
$end = '!STATS:';

# Put file to an array
$lines = file( $file );

# Set up placeholders
$loop = TRUE;
$capture = FALSE;
$filtered = array();

# Loop through lines, until we've found $end
for( $i = 0, $count = count($lines); $i < $count && $loop === TRUE; $i++ ) {

# Check to see if capturing has been turned on
if ( $capture === TRUE ) {
	# Check to see if this is the endline
	if ( strpos($lines[$i], $end) === 0 )
		# End the loop
		$loop = FALSE;
	else
		# Explode and add to filtered results
		$filtered[] = explode( ':', trim($lines[$i] ) );
}
# Check to see if this is the starting line
elseif ( strpos($lines[$i], $start) === 0 )
	# Turn on capturing
	$capture = TRUE;

} 

$columns = array('callsign', 'cid', 'realname', 'clienttype', 'frequency', 'latitude', 'longitude', 'altitude', 'groundspeed', 'planned_aircraft', 'planned_tascruise', 'planned_depairport', 'planned_altitude', 'planned_destairport', 'server', 'protrevision', 'rating', 'transponder', 'facilitytype', 'visualrange', 'planned_revision', 'planned_flighttype', 'planned_deptime', 'planned_actdeptime', 'planned_hrsenroute', 'planned_minenroute', 'planned_hrsfuel', 'planned_minfuel', 'planned_altairport', 'planned_remarks', 'planned_route', 'planned_depairport_lat', 'planned_depairport_lon', 'planned_destairport_lat', 'planned_destairport_lon', 'atis_message', 'time_last_atis_recieved', 'time_logon', 'heading', 'QNH_iHg', 'QNH_Mb');

$values = array();

foreach( $filtered as $filter ) {

if ( count($filter) != count($columns) ) {
	die( '
		Column counts did not match. MySQL expects ' .count($columns). ' columns and 
		your data is giving it ' .count($filter). ' columns.
	');
}

# Sanitize individual rows
foreach ( $filter as $key => $val )
	$filter[$key] = mysql_real_escape_string( $val );
# Format and implode
$values[] = "('" . implode( "', '", $filter ) . "')";
}

$q = "INSERT INTO `clients` (`" . implode( '`, `', $columns ) . "`) VALUES " . implode( ', ', $values );

echo $q;

?>

[monkeypaw201]

output: Column counts did not match. MySQL expects 41 columns and your data is giving it 42 columns.

 

EDIT: I just noticed... the end of the line ends in : is that the problem? if so, i can just add a placeholder column in MySQL..

[discomatt]

Yeah, check your data over... I think you're missing a column, or there's a ':' where you don't want it.

 

Something between

 

INSERT INTO `clients` (`id`, `callsign`, `cid`, `realname`, `clienttype`, `frequency`, `latitude`, `longitude`, `altitude`, `groundspeed`, `planned_aircraft`, `planned_tascruise`, `planned_depairport`, `planned_altitude`, `planned_destairport`, `server`, `protrevision`, `rating`, `transponder`, `facilitytype`, `visualrange`, `planned_revision`, `planned_flighttype`, `planned_deptime`, `planned_actdeptime`, `planned_hrsenroute`, `planned_minenroute`, `planned_hrsfuel`, `planned_minfuel`, `planned_altairport`, `planned_remarks`, `planned_route`, `planned_depairport_lat`, `planned_depairport_lon`, `planned_destairport_lat`, `planned_destairport_lon`, `atis_message`, `time_last_atis_recieved`, `time_logon`, `heading`, `QNH_iHg`, `QNH_Mb`) VALUES (NULL, '11991', '929251', 'Dmitry Belov UEEE', 'PILOT', '118.75', '41.26159', '69.28548', '1431', '10', 'B/AN12/F', '330', 'UTTT', '17700', 'UTDD', 'EUROPE-CW', '100', '1', '1200', 'NONE', '20', '1', 'I', '0', '0', '0', '0', '0', '0', 'UTTT', 'hi', 'UTTT UA G3 FC A103 SX UTDD', '41.257306', '069.281667', '38.543333', '068.825000', 'KBOS ATIS', '20080811174338', '20080811174335', '89', '29.74', '1006.97');

 

and

 

11991:929251:Dmitry Belov UEEE:PILOT:118.75:39.40068:68.87036:17518:278:B/AN12/F:330:UTTT:17700:UTDD:EUROPE-CW:100:1:1200:NONE:20:2:I:0:1758:0:0:0:0:UTTT::UTTT UA G3 FC A103 SX UTDD:41.257306:069.281667:38.543333:068.825000:KBOS ATIS:20080811174338:20080811174335:184:29.74:1007.01:

 

Doesn't match up

[monkeypaw201]

Ok, well I added a placeholder column and it seems to be running smoothly now..

 

Thanks a lot to everyone!

 

--scampers off to donate--