Hello All,
I have my script, it works great! but I would like it to be ran on a the MySQL server to lessen the bandwidth as I am running it from the web browser right now.
Would this be hard to convert over to a sql script, if not could someone show me how to do this as I don't have a clue! I want to convert this because this script is processing over 2.3 million records each month which is split over 6 times so I download a one of the files to the database now it needs processing then the next day I download another file and then it needs processing etc etc, the way it is now I am using the webserver, database server and php to do the job that the database server only could do Also one more question, is there a way to be able to add and take away from the where clause I will need to be adding to
my PHP script is:
<head>
<style type="text/css">
body {background-color:black;color:yellow;}
</style>
</head>
<body>
<?php
ini_set('display_errors', 'ON');
ini_set('error_reporting', E_ALL | E_STRICT );
include '/home/dev/www/lib/db_config_cr-dev.php';
include '/home/dev/www/lib/db_conn-select.php';
// Counts record in table
$query_s = "SELECT COUNT(*) FROM irs_rawdata";
$result_s = mysql_query($query_s) OR die("Sorry, unable to count the records in the table!");
$result_c = mysql_result($result_s, 0);
$record_count = $result_c;
echo "There is ". $record_count ." records in the irs_rawdata table<br /><br />";
// checks to see if sesult_c is greater then zero
// if so then it runs script
// otherwise it says there is no more records to process
for($i=1; $i <= $result_c; $i++)
{
//used to change $i to a meanful var name
$record_id = $i;
// selects and limits it to the first row only and retrives the EIN then return a 1 or 0
$query_s = "SELECT EIN FROM irs_rawdata LIMIT 0,1";
$result_s = mysql_query($query_s) OR die("Sorry, unable to to select EIN for the first record! <br /> ". mysql_error());
$record = mysql_fetch_assoc($result_s);
$count = mysql_num_rows($result_s);
if($count)
{
// changes long var to short var
$fin = $record['EIN'];
// gets record if there is one in block returns 1 or 0
echo "We are now processing record ". $record_id. " with Fed ID #". $fin ."!<br />";
$query_s = "SELECT fin FROM block WHERE fin = '$fin'";
$result_s = mysql_query($query_s) OR die("Sorry, unable to select record: " . mysql_error());
$record = mysql_fetch_assoc($result_s);
$count = mysql_num_rows($result_s);
if ($count == 0)
{
echo "There was no record found in the \"block\" table!<br />";
echo "We are now checking if record ". $record_id ." matchs the required criteria!<br />";
$query_is = "INSERT INTO query SELECT * FROM irs_rawdata
WHERE EIN = '$fin' AND
(
PNO LIKE '%blind%' OR
PNO LIKE '%deaf%' OR
PNO LIKE '%braille%' OR
PNO LIKE '%sight%' OR
PNO LIKE '%vision%' OR
PNO LIKE '%sign%' OR
PNO LIKE '%ear%' OR
PNO LIKE '%eye%' OR
PNO LIKE '%dog%' OR
Activity_Code LIKE '%031%' OR
NTEE_Code LIKE '%G41%' OR
NTEE_Code LIKE '%G42%' OR
NTEE_Code LIKE '%H41%' OR
NTEE_Code LIKE '%H42%' OR
NTEE_Code LIKE '%P86%' OR
NTEE_Code LIKE '%P87%'
)";
$result_is = mysql_query($query_is);
$count = mysql_affected_rows();
if($count)
{
echo "We have successfully added the record to the \"query\" table, it match the criteria!<br />";
echo "We are now deleting record ". $record_id . " from \"irs_rawdata\" table!<br />";
$query_d = "DELETE FROM irs_rawdata WHERE EIN = '$fin'";
$result_d = mysql_query($query_d);
$count = mysql_affected_rows();
if($count)
{
echo "We have successfully deleted the record from the \"irs_rawdata\" table!<br /><br />";
}
}
else
{
echo "We was unable to match the record ". $record_id ." to the criteria!<br />";
echo "We are now adding record ". $record_id . " to \"block\" table!<br />";
$query_i = "INSERT INTO block (fin, who_blocked) VALUES ('$fin', 'S-123456789')";
$result_i = mysql_query($query_i) OR die("Sorry was unable to insert ".$fin." into the database table allowed! <br />" . mysql_error());
$count = mysql_affected_rows();
if($count)
{
echo "We have successfully added the record to the \"block\" table!<br />";
echo "We are now deleting record ". $record_id . " from \"irs_rawdata\" table!<br />";
$query_d = "DELETE FROM irs_rawdata WHERE EIN ='$fin'";
$result_d = mysql_query($query_d) OR die("Was unable to delete ".$fin." from the db table irs_rawdata! <br />" . mysql_error());
$count = mysql_affected_rows();
if($count)
{
echo "We have successfully deleted the record from the \"irs_rawdata\" table!<br /><br />";
}
}
else
{
echo "We was unable to successfully added the record to the \"block\" table!<br />";
}
}
}
else
{
echo "There was one record found in the \"block\" table!<br />";
echo "We are now deleting record ". $record_id . " from \"irs_rawdata\" table!<br />";
$query_d = "DELETE FROM irs_rawdata WHERE EIN = '$fin'";
$result_d = mysql_query($query_d) OR die("Was unable delete ".$fin." from the db table irs_rawdata! <br />" . mysql_error());
$count = mysql_affected_rows();
if($count)
{
echo "We have successfully deleted the record from the \"irs_rawdata\" table!<br /><br />";
}
}
}
}
echo "The computer has finished processing the records in irs_rawdata!";
include_once ('/home/dev/www/lib/db_close.php');
?>
Thanks for you help!
Christopher
