0
I am having frequent problems with PHP, perhaps because I am creating in a "simple" way, or it would be my hosting that is basic?
Use of Locaweb’s Hosting I plan.
I am creating a system that will make two SELECT in SQL Server, picking all ZIP codes of two different months, to then compare them.
In drives where return less results the program works correctly. But in drives that return is much larger, the page hangs with error 503.
Index.php
<?
function in_array_r($needle, $haystack, $strict = false) {
foreach ($haystack as $item) {
if (($strict ? $item === $needle : $item == $needle) || (is_array($item) && in_array_r($needle, $item, $strict))) {
return true;
}
}
return false;
}
function cmp($a, $b) {
$cep = preg_replace("/[^0-9\s]/", "", $a['cep']);
$cepCompara = preg_replace("/[^0-9\s]/", "", $b['cep']);
return $a['cep'] > $b['cep'];
}
$mes = $_GET['mes'];
$mesAnt = $mes-1;
$unidade = $_GET['unidade'];
$dbhost = "HOST HEHE";
$db = "BANCO";
$user = "USER";
$password = "PASS";
mssql_connect($dbhost,$user,$password) or die("Não foi possível a conexão com o servidor!");
mssql_select_db("$db") or die("Não foi possível selecionar o banco de dados!");
$sql = "SELECT CtrNro, CtrDstNroCep, CtrDat, CtrQtdPeso, CtrValFreteBase
FROM rlt005
WHERE CtrUnnCodDestino = $unidade
AND DATEPART(MONTH, CtrDat) = $mesAnt";
$consulta = mssql_query($sql);
$numRegistros = mssql_num_rows($consulta);
$mesUm = array();
if ($numRegistros!=0) {
while ($cadaLinha = mssql_fetch_array($consulta)) {
$dados = array(
"operacional" => $cadaLinha['CtrNro'],
"cep" => $cadaLinha['CtrDstNroCep'],
"data" => substr($cadaLinha['CtrDat'],0,3),
"peso" => $cadaLinha['CtrQtdPeso'],
"frete" => $cadaLinha['CtrValFreteBase'],
);
array_push($mesUm, $dados);
}
}
$sql = "SELECT CtrNro, CtrDstNroCep, CtrDat, CtrQtdPeso, CtrValFreteBase
FROM rlt005
WHERE CtrUnnCodDestino = $unidade
AND DATEPART(MONTH, CtrDat) = $mes";
$consulta = mssql_query($sql);
$numRegistros = mssql_num_rows($consulta);
$mesDois = array();
if ($numRegistros!=0) {
while ($cadaLinha = mssql_fetch_array($consulta)) {
$dados = array(
"operacional" => $cadaLinha['CtrNro'],
"cep" => $cadaLinha['CtrDstNroCep'],
"data" => substr($cadaLinha['CtrDat'],0,3),
"peso" => $cadaLinha['CtrQtdPeso'],
"frete" => $cadaLinha['CtrValFreteBase'],
);
array_push($mesDois, $dados);
}
}
$novo = array();
$perda = array();
$totalNovo=0;
$totalPerda=0;
$tam = sizeof($mesDois);
for($i=0; $i < $tam; $i++){
if (!in_array_r($mesDois[$i]['cep'], $mesUm)) {
if(!in_array_r($mesDois[$i]['cep'], $novo)){
$totalNovo++;
}
array_push($novo, $mesDois[$i]);
}
}
$tam = sizeof($mesUm);
for($i=0; $i < $tam; $i++){
if (!in_array_r($mesUm[$i]['cep'], $mesDois)) {
if(!in_array_r($mesUm[$i]['cep'], $perda)){
$totalPerda++;
}
array_push($perda, $mesUm[$i]);
}
}
?>
<header>
<section class="wrap-center clearfix">
<div class="content-head">
<h1>Unidade <?php echo $unidade; ?></h1>
<p>Perdas e Ganhos do mês <?php echo $mes; ?> em relação ao mês <?php echo $mesAnt; ?></p>
</div>
</section>
</header>
<div class="container">
<section class="card">
<h3><i class="fa fa-long-arrow-down" aria-hidden="true"></i> <?php echo $totalPerda; ?></h3>
<div class="card-block">
<table class="table">
<thead>
<tr>
<th style="padding: 2px 4px 2px 17px !important;">●</th>
<th>Operacional</th>
<th>CEP</th>
<th>Peso (Kg)</th>
<th>Frete (R$)</th>
</tr>
</thead>
<tbody>
<?php
$tam = sizeof($perda);
for($i=0; $i < $tam; $i++){
echo '<tr>
<td style="padding:2px 0px 2px 17px !important;"><a href="#" onclick="popUP('.preg_replace("/^(\d{5})(\d{3})$/", "\\1-\\2", $novo[$i]['cep']).')"><i class="fa fa-long-arrow-down" aria-hidden="true"></i></a></td>
<td>'.$perda[$i]['operacional'].'</td>
<td>'.preg_replace("/^(\d{5})(\d{3})$/", "\\1-\\2", $perda[$i]['cep']).'</td>
<td>'.$perda[$i]['peso'].'</td>
<td>'.str_replace(".", ",", $perda[$i]['frete']).'</td>
</tr>';
}
?>
</tbody>
</table>
</div>
</section>
<section class="card">
<h3><i class="fa fa-long-arrow-up" aria-hidden="true"></i> <?php echo $totalNovo; ?></h3>
<div class="card-block">
<table class="table">
<thead>
<tr>
<th style="padding: 2px 4px 2px 17px !important;">●</th>
<th>Operacional</th>
<th>CEP</th>
<th>Peso (Kg)</th>
<th>Frete (R$)</th>
</tr>
</thead>
<tbody>
<?php
$tam = sizeof($novo);
for($i=0; $i < $tam; $i++){
echo '<tr>
<td style="padding:2px 0px 2px 17px !important;"><a href="#" onclick="popUP('.preg_replace("/^(\d{5})(\d{3})$/", "\\1-\\2", $novo[$i]['cep']).')"><i class="fa fa-long-arrow-up" aria-hidden="true"></i></a></td>
<td>'.$novo[$i]['operacional'].'</td>
<td>'.preg_replace("/^(\d{5})(\d{3})$/", "\\1-\\2", $novo[$i]['cep']).'</td>
<td>'.$novo[$i]['peso'].'</td>
<td>'.str_replace(".", ",", $novo[$i]['frete']).'</td>
</tr>';
}
?>
</tbody>
</table>
</div>
</section>
</div>
To be honest, your code is with several "recursions", it is quite likely that it is the same problem. But I couldn’t quite grasp the goal to try to simplify
– Guilherme Nascimento
Mas em unidades que o retorno é bem maior,.. Bigger? Thousand records? 10,000 records? The normal is to create a pagination to avoid a very large volume of data on a single page.– Daniel Omine
@Danielomine In smaller units the return is about 2 thousand records, and in larger about 20 thousand...
– Pedro Daher
@Guilhermenascimento, the goal is to take all the zip codes of month 6 and all of month 5 (for example), and compare to see if we’ve lost any.
– Pedro Daher
Create a pagination. Never return such a large amount. Even the smallest that is 2 thousand is already too much. Pagination is 50 per page, 100 per page. Much less than that, for example, 20, 30
– Daniel Omine
@Pedrodaher: Avoid "AND DATEPART(MONTH, Ctrdat) = $mesAnt" constructions, as they are non-SARGable. Better to use something like "AND Ctrdat between $homeMesAnt and $finalMesAnt" // Vide https://en.wikipedia.org/wiki/Sargable
– José Diz