Jump to content

[SOLVED] Filtering content from a file

monkeypaw201

By monkeypaw201
in PHP Coding Help

 Share

Recommended Posts

sasa Newbie

sasa

Posted
Posted

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

Link to comment
Share on other sites
monkeypaw201 Regular Member

monkeypaw201

Posted
  • Author
Posted

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?

Link to comment
Share on other sites
monkeypaw201 Regular Member

monkeypaw201

Posted
  • Author
Posted

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');
Link to comment
Share on other sites
monkeypaw201 Regular Member

monkeypaw201

Posted
  • Author
Posted

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)

Link to comment
Share on other sites
discomatt Member

discomatt

Posted
Posted

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;

?>

Link to comment
Share on other sites
monkeypaw201 Regular Member

monkeypaw201

Posted
  • Author
Posted

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 :D

 

 

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

Link to comment
Share on other sites
sasa Newbie

sasa

Posted
Posted

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());
}
?>

Link to comment
Share on other sites
monkeypaw201 Regular Member

monkeypaw201

Posted
  • Author
Posted

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

 

Link to comment
Share on other sites
discomatt Member

discomatt

Posted
Posted

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');

Link to comment
Share on other sites
monkeypaw201 Regular Member

monkeypaw201

Posted
  • Author
Posted

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

Link to comment
Share on other sites
discomatt Member

discomatt

Posted
Posted

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;

?>

Link to comment
Share on other sites
discomatt Member

discomatt

Posted
Posted

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 ;)

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.