I'm using php with mysql.
I have a large database of mysql
containing 365 tables, each day contain a single table and that contains thousand of records for each client in single table. My problem is that when I'm going to generate the report for multiple clients it doesn't show any thing.
On other hand when I check mysql
log it shows queries running on back end, when queries a completed at back end still nothing comes to browser, browser still show process running.
My current code looks like this:
//$ClientList Contains 100 clientIds
//$TableList contains 30 table list
$TotalCount = count($ClientList);
$CountTables = count($TableList);
for($i=0; $i<$TotalCount; $i++) {
for($j=0; $j<$CountTables; $j++) {
$sql = "INSERT INTO TABLEA SELECT * FROM ".$TableList[$j]." WHERE clientid = '".$ClientList[$i]."'";
$rs = mysql_query($sql);
}
}
for($i=0; $i<$TotalCount; $i++) {
$sql = "SELECT * FROM TABLEA";
//STORE IN ARRAY
$rs = mysql_query($sql);
while($ds=mysql_fetch_assoc($rs)) {
$aRRAY[$i] = $ds;
}
}
for($i=0; $i<count($aRRAY); $i++) {
}
But nothing is coming to browser I have also added the settimeout
to 0; increased session time, but no results. Any solution to this issuee?
Do not store one million records in a php array:
//$ClientList Contains 100 clientIds
//$TableList contains 30 table list
$TotalCount = count($ClientList);
$CountTables = count($TableList);
for($i=0; $i<$TotalCount; $i++) {
for($j=0; $j<$CountTables; $j++) {
$sql = "INSERT INTO TABLEA SELECT * FROM ".$TableList[$j]." WHERE clientid = '".$ClientList[$i]."'";
$rs = mysql_query($sql);
}
}
//OUTPUT
$sql = "SELECT * FROM TABLEA";
//
$rs = mysql_query($sql);
while($ds=mysql_fetch_assoc($rs)) {
my_output($ds);
}
Also if you are trusting the database to put all your records into TABLEA, why put them into separate tables at all?
//$ClientList Contains 100 clientIds
//$TableList contains 30 table list
$TotalCount = count($ClientList);
$CountTables = count($TableList);
for($i=0; $i<$TotalCount; $i++) {
for($j=0; $j<$CountTables; $j++) {
//OUTPUT
$sql = "SELECT * FROM ".$TableList[$j]." WHERE clientid = '".$ClientList[$i]."'";
//
$rs = mysql_query($sql);
while($ds=mysql_fetch_assoc($rs)) {
my_output($ds);
}
}
}