<?PHP
// Read in the form variables - magic quotes is on
$state = $_POST['state'];
$numsplit = $_POST['numsplit'];
$fname = $_POST['fname'];
// check to see if there are form variables
if(!$state || !$numsplit) {
echo "<center>Please use your back button and make sure you have choosen a state and entered the number of splits.</center>";
} else {
// define the server, user and pass to connect to the db
$server = '';
$user = '';
$pass = '';
// create a db server connection
$link = mssql_connect($server, $user, $pass);
// selct the db
mssql_select_db('', $link);
// create the templist and populate it with data
$sql1 = "
create table templist (
number BIGINT NULL
)
insert into templist (number)
select distinct number -- this makes sure the exported list is deduped
from list
join areacodes ar on ar.areacode = LEFT(.number, 3)
where state = '$state'
";
// run the above query
mssql_query($sql1,$link);
// delete any removes from the templist
$sqlrem = "delete from templist
where number in (select number from removes)";
// run the above query
mssql_query($sqlrem,$link);
// count the total numbers in the templist
$sqlnum = "select count(number) as num from templist";
//run the above query
$resnum = mssql_query($sqlnum,$link);;
// check to see if the list count is smaller than the numsplits
// if it is smaller then set the numsplit to the actual list count value
while ($rownum = mssql_fetch_array($resnum)) {
if ($rownum['num'] < $numsplit) { $numsplit = $rownum['num']; }
}
// perform the list splitting
$sql = "
-- declare some variables
declare @tlist int,
@listc int,
@lcount int,
@tablename varchar(200),
@state varchar(100)
-- set some variables
set @lcount = 0
set @state = '$state'
-- set the number of splits
SELECT @listc = dbo.RoundUp((count(*)/$numsplit))
from templist
-- set the value for the rowcount
SELECT @tlist = count(*)/@listc
FROM templist
-- set the rowcount to be used in the initial iteration
set rowcount @tlist
-- start the list splitting loop
while @lcount < @listc
-- begin the looping
begin
-- set the loop counter
set @lcount = @lcount + 1
-- set the tablename to be used in the execute statements
select @tablename = 'list_' + @state + '_' + convert(char(12), @lcount, 14)
-- create the individual tables
execute ('create table ' + @tablename + '
(
number BIGINT NULL
)')
-- this is really not necessary in this context
set nocount off
-- perform the insert
execute('insert into ' + @tablename + ' (number)
select number
from templist')
-- this is not necessary in this context
set nocount on
-- delete data from the templist
-- this will only delete up to the current iteration of the rowcount
delete from templist
--end the looping
end
-- get rid of the templist
drop table templist
";
// run the above query
$res = mssql_query($sql,$link);
// count the number of splits created
$sql2 = "
Select Count(*) As TableCount
From Information_Schema.Tables
where Table_Name like '%$state%'
";
// run the above query
$res = mssql_query($sql2,$link);
// declare and set the total of lists to be used in the next script
while ($row = mssql_fetch_array($res)) {
$gocount = $row['TableCount'];
}
mssql_free_result($res); // clean up some memory
mssql_free_result($resnum); // clean up some memory
mssql_close($link); // close the db connection
// print some information to the screen
echo "<center>The $state List Data Segments have been created.<br /><br />The list will now be generated.</center>";
// pass some data to a form and auto submit the form
// this starts the next part of the process
print "<body onload='submitForm()'>
<form name='form' method='post' action='listbystate.php'>
<input type='hidden' name='count' value='1'>
<input type='hidden' name='state' value='$state'>
<input type='hidden' name='gocount' value='$gocount'>
<input type='hidden' name='fname' value='$fname'>
</form>
<SCRIPT LANGUAGE='JavaScript'>
function submitForm(){
document.form.submit();
}
</SCRIPT>";
}// end if !numsplit
?>
