ir noob need some help with import a text file into sql with some special needs

[ssjrocks]

hey guys, i need some help with importing a logfile into sql

im sure there are tons easier ways to do every part of the script so i was hoping for a nudge in the right direction

1st is an example of the file im importing

1841526 19:41:01 22-12-09 POCSAG-4 NUMERIC  512  TONE ONLY
1841527 19:41:01 22-12-09 POCSAG-4 NUMERIC  512  TONE ONLY
0370377 19:41:05 22-12-09 POCSAG-3  ALPHA  1200  19:40 P101686..ADA2
0547216 19:41:11 22-12-09 POCSAG-1  ALPHA  1200  19:40 P90393..MSG1.MON.F2
0411456 19:41:16 22-12-09 POCSAG-3  ALPHA  1200  J230059,4.2,:HH130B 117,62 COLUMBIA CR 

MODBURY_NORTH,PETE"?)'agi
1517188 19:41:16 22-12-09 POCSAG-1 NUMERIC 1200  2578
1601773 19:41:16 22-12-09 POCSAG-4 NUMERIC 1200  TONE ONLY
0866741 19:41:16 22-12-09 POCSAG-3 NUMERIC 1200  TONE ONLY
0404958 19:41:16 22-12-09 POCSAG-2 NUMERIC 1200  TONE ONLY
0465590 19:41:16 22-12-09 POCSAG-2 NUMERIC 1200  2709
2058471 19:41:17 22-12-09 POCSAG-4  ALPHA  1200  v?v???ff.&?"?
                                                 &6O?/?"G?&?BN??j?Nv??/?7O?.??/?J???~nNv>?
                                                 >?N?2?n?"G?
0511406 19:41:18 22-12-09 POCSAG-3  ALPHA  1200  2212-19:40 CONTACT STATE GOVT SECURITY 07-3224-6666 RE 

ALARM
0847399 19:41:18 22-12-09 POCSAG-3  ALPHA  1200  19:40 P253441.SVRS2

as you can see thers alot of special chars coming through that i have to deal with hence the screwing around with mysql_real_escape_string and what not.

and here is my script

<?php
$rimp = "0";
$dup = "0";
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "<br />";
echo "Type: " . $_FILES["file"]["type"] . "<br />";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";
$rndmna = rand(1, 256);
if (file_exists("upload/" . $_FILES["file"]["name"]))
{
	echo $_FILES["file"]["name"] . " already exists. <BR>";
	move_uploaded_file($_FILES["file"]["tmp_name"],
	"upload/" . $rndmna . $_FILES["file"]["name"]);
	echo "Renamed and Stored in: " . "upload/" . $rndmna . $_FILES["file"]["name"];

}
else
{
	move_uploaded_file($_FILES["file"]["tmp_name"],
	"upload/" . $_FILES["file"]["name"]);
	echo "Stored in: " . "upload/" . $_FILES["file"]["name"];
}
}
echo "<BR><br>Processing.";


flush();







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

mysql_select_db("spl1nt", $con);
$spc = " ";
$tcnt = "";
$filename = "upload/" . $_FILES["file"]["name"];
$fp = @fopen($filename, 'r');
if ($fp) {
$array = explode("\n", fread($fp, filesize($filename)));
}

foreach ($array as $k => $v) {

$tcnt ++;
$tccnt = count($array);
$fields = preg_split('//', $array[$k]);


$fcnt = count($fields);


// wow @ below.... gotta be an easier way

if(isset($fields[0])){$fieldx[0] = $fields[0] . $fields[1] . $fields[2] . $fields[3] . $fields[4] . $fields[5] . $fields[6] . $fields[7];}

if(isset($fields[9])){$fieldx[1] = $fields[9] . $fields[10] . $fields[11] . $fields[12] . $fields[13] . $fields[14] . $fields[15] . $fields[16];}
if(isset($fields[17])){$fieldx[2] = $fields[18] . $fields[19] . $fields[20] . $fields[21] . $fields[22] . $fields[23] . $fields[24] . $fields[25];}
if(isset($fields[27])){$fieldx[3] = $fields[27] . $fields[28] . $fields[29] . $fields[30] . $fields[31] . $fields[32] . $fields[33] . $fields[34];}
if(isset($fields[36])){$fieldx[4] = $fields[36] . $fields[37] . $fields[38] . $fields[39] . $fields[40] . $fields[41] . $fields[42] . $fields[43];}
if(isset($fields[43])){$fieldx[5] = $fields[43] . $fields[44] . $fields[45] . $fields[46] . $fields[47];}
if(isset($fields[49])){$fieldx[6] = $fields[49] ;}


//$fields[17]


if($fields[0] >= 0){




	$h=0;
	$tst1 = '';
	foreach ($fields as $d) {




		if ($h > '49'){


			$tst1 = $tst1 . $d;


		}

		$h++;
	}

	//		$qry11 = "SELECT * FROM data WHERE Address='$fieldx[0]' and Date ='$fieldx[2]' and Msg='$tst1'";
	//		echo "<BR>pp" . $fieldx[0] . "pp";

				echo "<BR>";

		//echo "SELECT * FROM data WHERE Address='$fieldx[0]' and Date ='$fieldx[2]' and Msg='$tst1'";//	        echo "pp" . $fieldx[2] . "pp";
	echo $checkstr;
	echo "<BR>" . $check . "<BR>" . mysql_num_rows($check);
	$checkstr = "SELECT * FROM data WHERE Address='$fieldx[0]' and Date ='$fieldx[2]' and Msg='$tst1'";

	$check = mysql_query($checkstr,$con);
if($fieldx[0] != "" || $fieldx[2] != "" || $tst1 != ""){
	if(mysql_num_rows($check) >= 1)
	{

		$dup ++;

	}
	else;
	{
		if(mysql_num_rows($check) == 0)
		{
			if($tst1 == "- Smash -"
){break;}
			$tst1 = mysql_real_escape_string($tst1);
			$sqlqu="INSERT INTO Data (Address, Time
			, Date, p1234, Type, Baud, Msg)
			VALUES ('$fieldx[0]', '$fieldx[1]', '$fieldx[2]', '$fieldx[3]', '$fieldx[4]', '$fieldx[5]', '$tst1')";
			$rimp ++;





			if (!mysql_query($sqlqu,$con))
			{
				die('Error: ' . mysql_error());
			}
		}
	}

	echo".";
}


}
}
echo "<BR><BR>";

echo $rimp . " records imported";
echo "<BR>" . $dup . " Duplicates Ignored";
mysql_close($con);
?>

it looks ugly as but im fairly new to this

i keep getting

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2\htdocs\doimp2.php on line 111

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2\htdocs\doimp2.php on line 116

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\apache2\htdocs\doimp2.php on line 124

and when i got parts of it to work without errors it would execute both parts of my if loop to check for dups tally up the $rimp and $dup variables then insert it into the database anyway

any help given would be much apreciated.

 

 

 

 

[gerkintrigg]

have you looked into explode() or preg_replace()?

Though you might be better off with some kind of preg_match function and then loop through the matches.

 

Try a for each loop.

[ssjrocks]

i had very limited sucess with preg split so for compatibility i decided to explode the file on new line and preg split on all then crazy foreach loops

 

[ChemicalBliss]

Looks like a fixed-width file.

 

If indeed each field has a specific amount of characters (with space as padding, as is in your example):

 

This class can convert the fixed-width files into CSV format, which can be read easily by fgetcsv();

 

Fixed-Width Converter Class: (With Example);

http://www.phpro.org/classes/Convert-Fixed-Width-File-To-CSV.html

 

fgetcsv() (also with examples);

http://php.net/fgetcsv

 

---

 

Unless you want to write your own custom code, which may get buggy with different versions.

 

if you do though, i would use something like substr, with a loop, instead of explode.

 

-CB-