Write a HTML table from MySQL tables

Question

I have a piece of code that generates some tables:

bp_rows: where I take a snapshot of some "headers" for each row of the table.
bp_flussi: where I store a date and an amount only if the amount is not equal to 0.

Flussi can be positive or negative. When this is done, I use different code to populate a table with one row for each line in bp_rows. Then each row will split in five sub rows with details coming from bp_flussi and bp_rows (a column for each month) and the fifth row is for a total. In the end of the table I have a grand total.

This code is taking too much time to load. Actually, the output is loaded into a div on the page using jQuery's .load() and only when the browser notifies me that the script is taking too long, it will output the table (if I hit to continue running the script) and this will happen immediately.

My question: Will there be a difference in terms of performance if I use .ajax() method instead of .load()? How can I debug the PHP page that will build the table to see what is taking so much time?

The table is loaded with this code:

$('.container').load('plan/visualizza.php?id_bp='+id_bp+'&dett='+dett+'&veicolo='+veicolo);

The PHP code:

<?php
require '../../session_handler.inc.php';
session_start();
$actusr = $_SESSION['id'];
$id_bp = $_GET['id_bp'];
$dett = $_GET['dett'];
$veicolo = $_GET['veicolo'];
if($veicolo != '0'){
    $veicolo_clause = " AND titolare='$veicolo'";
}else{
    $veicolo_clause = "";
}
require_once '../../global_functions.php';
require '../Classes/financial_class.php';
$f = new Financial;
$irr_mensile = array();
//$annoMin = $pdo->query("SELECT MIN(anno) AS anno FROM bp_flussi WHERE id_bp = '$id_bp'")->fetchColumn();
//$annoMax = $pdo->query("SELECT MAX(anno) AS anno FROM bp_flussi WHERE id_bp = '$id_bp'")->fetchColumn();
$annoMin = '2008';
$annoMax = '2016';
$vuoto = 0.00;
$creds = $pdo->query("SELECT * FROM bp_rows WHERE id_bp = '$id_bp'$veicolo_clause")->fetchAll();
$data_rif = $pdo->query("SELECT data_rifer FROM bp_head WHERE id = '$id_bp'")->fetchColumn();
?>
<script type="text/javascript">
$(document).ready(function(){
    resizeDiv();
    $("#spnmsg").fadeTo(200,0.1,function(){
    $(this).removeClass().addClass('spn_normal').html("Visualizzazione del business plan '<b><?php echo get_name('bp_head',$id_bp,'nome',$pdo);?></b>'.").fadeTo(900,1);
    });
    //$('#businessplan').DataTable();
});
</script>

<div class="titolo" id="title">
    <h1>JENIUS</h1>
    <h2>Business Plan '<?php echo get_name('bp_head',$id_bp,'nome',$pdo);?>' con data di riferimento <?php echo mysql2table($data_rif); ?> </h2>
</div>

<div class="pagina "id="contenuto">
<table class="report" id="businessplan">
    <caption class="report">Crediti inseriti nella sofferenza</caption>
    <thead>
    <tr>
        <th class="report">ID</th>
        <th class="report">Lotto</th>
        <th class="report">Sofferenza</th>
        <th class="report">Data acquisto</th>
        <th class="report">Cedente</th>
        <th class="report">Valore ammesso</th>
        <th class="report">Prezzo acquisto</th>
        <th class="report">Originator</th>
        <th class="report">Stato</th>
        <th class="report">Data chiusura</th>
        <th class="report">Motivo chiusura</th>
        <th class="report">Data ultimo incasso</th>
        <th class="report destra">% inc. prev.</th>
        <th class="report">IRR Storico</th>
        <th class="report">IRR Mensile</th>
        <th class="report">IRR Annuale</th>
        <th class="report destra">NPV</th>
        <th class="report">Tipo flusso</th>
        <?php       
        for($anno = $annoMin; $anno<=$annoMax; $anno++){
        for($mese = 1; $mese <= 12; $mese++){
            $mese = sprintf("%02s", $mese);
            echo '<th class="report">'.$mese.'/'.$anno.'</th>';
        }
        }
        ?>
        <th class="report">Totale</th>
    </tr>
    </thead>
    <tbody>
    <?php
    //inizializzo l'array che contiene i valori dei totali di riga per mese per fare il totale di tabella
    $flusso_complessivo = array();
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
        for($mese = 1; $mese <= 12; $mese++){
            $flusso_complessivo[] = $vuoto;
        }
    }
    $k = 0;
    foreach($creds as $cred){    
    ?>
    <!-- dati generali dei singoli crediti -->
    <tr>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo $cred['id_cre']; ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo $cred['serie']; ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo soff2name($cred['cod_soff'],$pdo); ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo mysql2table($cred['data_acq']); ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo prot2name($cred['cedente'],$pdo); ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo number_format($cred['ammesso'],"2",",","."); ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo number_format($cred['prezzo_acq'],"2",",","."); ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo prot2name($cred['originator'],$pdo); ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo $cred['stato']; ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo ($cred['data_chius'] <> '0000-00-00') ? mysql2table($cred['data_chius']) : ''; ?></td>
        <td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo chius2name($cred['caus_chius'],$pdo); ?></td>
        <?php
        $error = '';
        $credito = $cred['id_cre'];
        $last_inc = $pdo->query("SELECT max(data_inc) FROM incassi_row WHERE id_cre = '$credito'")->fetchColumn();
        echo '<td class="report quarta" ';
        echo ($dett == 1) ? 'rowspan="5"' : '';
        echo '>';
        echo ($last_inc <> '') ? mysql2table($last_inc).'</td>' : '</td>';
        $perc_worst = $pdo->query("SELECT perc_worst FROM azioni_row JOIN azioni_head ON azioni_row.id_az = azioni_head.id_az WHERE id_cred = '$credito'")->fetchColumn();
        echo '<td class="report quarta destra" ';
        echo ($dett == 1) ? 'rowspan="5"' : '';
        echo '>'.$perc_worst.'%</td>';
        ?>
        <td class="report quarta" id="" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>IRR Storico</td>
        <td class="report quarta" id="irr_mese_<?php echo $k; ?>" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>IRR Mensile</td>
        <td class="report quarta" id="irr_anno_<?php echo $k; ?>" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>IRR Annuale</td>
        <td class="report quarta destra" id="" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>NPV</td>
<!-- dettagli acquisto -->
    <?php
    $k = $k + 1;
    echo ($dett == 1) ? '<td class="report">Acquisto</td>' : '';

    $acquisti = array();
    $acquisto_tot = 0;
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
    for($mese = 1; $mese <= 12; $mese++){
        $mese = sprintf("%02s", $mese);
        $inizio = $anno.'-'.$mese.'-01';
        $fine = $anno.'-'.$mese.'-31';
        $prezzo_acq = $pdo->query("SELECT prezzo_acq FROM crediti WHERE id_cre='$credito' AND data_acq BETWEEN '$inizio' AND '$fine'")->fetchColumn();
        echo($dett == 1) ? '<td class="report">-'.num2cur($prezzo_acq).'</td>' : '';
        array_push($acquisti,$prezzo_acq);
        $acquisto_tot = $acquisto_tot + $prezzo_acq;
    }
    }
    echo ($dett == 1) ? '<td class="report"><b>-'.num2cur($acquisto_tot).'</b></td></tr>' : '';
    ?>
<!-- dettagli incassi -->
    <?php
    echo ($dett == 1) ? '<tr><td class="report">Incasso</td>' : '';
    $incasso_tot = 0;
    $incassi = array();
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
    for($mese = 1; $mese <= 12; $mese++){
        $mese = sprintf("%02s", $mese);
        $periodo = $mese.'/'.$anno;
        try{
        $sql = "SELECT incasso FROM bp_flussi WHERE id_cre='$credito' AND id_bp='$id_bp' AND periodo = '$periodo'";
        $z = $pdo->prepare($sql);
        $z->execute();
        }
        catch(PDOException $e){
        $importo = $e->getMessage();
        }
        $incasso = $z->fetchColumn();
        echo ($dett == 1) ? '<td class="report incasso _'.$credito.'">'.num2cur($incasso).'</td>' : '';
        $incasso_tot = $incasso_tot + $incasso;
        array_push($incassi,$incasso);
    }
    }
    echo ($dett == 1) ? '<td class="report"><b>'.num2cur($incasso_tot).'</b></td></tr>' : '';
    ?>

<!-- dettagli spese -->

    <?php
    echo ($dett == 1) ? '<tr><td class="report">Spesa</td>' : '';
    $spesa_tot = 0;
    $spese = array();
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
    for($mese = 1; $mese <= 12; $mese++){
            $mese = sprintf("%02s", $mese);
        $periodo = $mese.'/'.$anno;
        try{
        $sql = "SELECT spesa FROM bp_flussi WHERE id_cre='$credito' AND id_bp='$id_bp' AND periodo = '$periodo'";
        $z = $pdo->prepare($sql);
        $z->execute();
        }
        catch(PDOException $e){
        $importo = $e->getMessage();
        }
        $spesa = $z->fetchColumn();    
        echo ($dett == 1) ? '<td class="report spesa_'.$credito.'">-'.num2cur($spesa).'</td>' : '';
        $spesa_tot = $spesa_tot + $spesa;
        $spese[] = $spesa;
    }
    }
    echo ($dett == 1) ? '<td class="report"><b>-'.num2cur($spesa_tot).'</b></td></tr>' : '';
    ?>
<!-- dettagli vendita -->

    <?php
    echo ($dett == 1) ? '<tr><td class="report">Vendita</td>' : '';
    $vendite = array();
    $vendita_tot = 0;
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
    for($mese = 1; $mese <= 12; $mese++){
        $mese = sprintf("%02s", $mese);
        $inizio = $anno.'-'.$mese.'-01';
        $fine = $anno.'-'.$mese.'-31';      
        try{
        $sql = "SELECT prezzo_chius FROM crediti WHERE id_cre='$credito' AND data_chius BETWEEN '$inizio' AND '$fine'";
        $z = $pdo->prepare($sql);
        $z->execute();
        }
        catch(PDOException $e){
        $prezzo_ven = $e->getMessage();
        }
        $prezzo_ven = $z->fetchColumn();
        echo ($dett == 1) ? '<td class="report">'.num2cur($prezzo_ven).'</td>' : '';
        array_push($vendite, $prezzo_ven);
        $vendita_tot = $vendita_tot + $prezzo_ven;
    }
    }
    echo ($dett == 1) ? '<td class="report"><b>'.num2cur($vendita_tot).'</b></td></tr><tr class="quarta">' : '';
    ?>
    <!-- righe totale -->
        <td class="report quarta">Totale</td>
    <?php
    $i = 0;
    $importo_tot = 0;
    $irr_array = array();
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
    for($mese = 1; $mese <= 12; $mese++){
        $mese = sprintf("%02s", $mese);
        $importo = $incassi[$i] + $vendite[$i] - $spese[$i] - $acquisti[$i];
        echo ($importo >= 0) ? '<td class="report quarta success">'.num2cur($importo).'</td>' :
        '<td class="report quarta err">'.num2cur($importo).'</td>';
        $flusso_complessivo[$i] = $flusso_complessivo[$i] + $importo;
        array_push($irr_array, $importo);
        $i = $i + 1;
        $importo_tot = $importo_tot + $importo;
    }
    }
    echo '<td class="report quarta"><b>'.num2cur($importo_tot).'</b></td>';

    ?>
    </tr>
    <?php
    foreach($irr_array as $value){
        if($value == 0){
            array_shift($irr_array);
        }else{
            break;
        }
    }
    for($i = count($irr_array)-1; $i>=0; $i-- ){
        if($irr_array[$i] == '0'){
            array_pop($irr_array);
        }else{
            break;
        }
    }
    if(count($irr_array)>0){
        $irr_singolo = $f->IRR($irr_array);
        if(is_null($irr_singolo)){$irr_singolo = '0.00';}
    }else{
        $irr_singolo = '0.00';
    }
    array_push($irr_mensile, $irr_singolo);
} //fine del foreach credito
?>
    <tr>
    <td class="report" colspan="18">Totale</td>
    <?php
    $i = 0;
    $totalissimo = 0;
    for($anno = $annoMin; $anno<=$annoMax; $anno++){
        for($mese = 1; $mese <= 12; $mese++){
            $valore = $flusso_complessivo[$i];
            echo ($valore >= 0) ? '<td class="report success"><b>'.num2cur($valore).'</b></td>' : '<td class="report err"><b>'.num2cur($valore).'</b></td>';
            $totalissimo += $valore; 
            $i = $i + 1;
        }
    }
    ?>
    <td class="report"><b><?php echo num2cur($totalissimo); ?></b></td>
    </tr>
    </tbody>
</table>
<script type="text/javascript">

    var irrs = <?php echo json_encode($irr_mensile); ?>;
    var num_creds = irrs.length;
    for(i=0; i<=num_creds; i++){
    sing_irr_mese = irrs[i];
    if(sing_irr_mese != '0.00'){ sing_irr_anno = 1 + sing_irr_mese; }else{ sing_irr_anno = '0.00';}
    sing_irr_anno = Math.pow(sing_irr_anno,12);
    if(sing_irr_anno != '0.00'){ sing_irr_anno = sing_irr_anno -1; }else{ sing_irr_anno = '0.00';}
    sing_irr_mese= sing_irr_mese * 100;
    sing_irr_anno = sing_irr_anno * 100;
    $('#irr_mese_'+i).number(sing_irr_mese, 2,',','.').append('%');
    $('#irr_anno_'+i).number(sing_irr_anno, 2,',','.').append('%');

    }
</script>

</div>

I believe what you're looking for is a "profiler." That is easy and this SO question may help you get started. Once you know why your code is slow, Code Reviewers would happily show you further improvements! Just as long as you've done all you can to figure it out yourself. — Alex L, Jun 16, 2015 at 22:13
Like @AlexL said, "Will there be a difference in terms of performance if I use .ajax() method instead of .load()?" and "How can I debug the PHP page that will build the table to see what is taking so much time?" are 2 separate questions that belong on stackoverflow. Now if you wanted input about your code then you should rephrase your question. As an aside you should look into Model View Controller style of programming. — Mike, Jul 3, 2015 at 1:45
I am sure that difference between .load() and .ajax() is not actually noticeable as time taken for server request and response will be simply be much more competitively. — Raj Kamal, Jan 8, 2016 at 11:45

Sᴀᴍ Onᴇᴌᴀ · Accepted Answer · 2019-10-29 16:37:56Z

As others pointed out in comments, the question about the difference between .ajax() and .load() should be and does exist on SO (and there are related posts like the difference between the two.

The PHP code has multiple places with nested for loops that have SQL queries in them. It would be advantageous to extract the values from the WHERE conditions and run one query for each list, then iterate over the results to find the relevant data.

I also see places where query string values are placed directly into the queries without being sanitized.

$veicolo = $_GET['veicolo'];
$veicolo_clause = " AND titolare='$veicolo'";

This could lead to SQL injection attacks. It would be best to bind parameters and sanitize the values.

Stack Exchange Network

Write a HTML table from MySQL tables

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
javascript
jquery
php5
pdo
or ask your own question.

Hot Network Questions

Write a HTML table from MySQL tables

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged javascriptjqueryphp5pdo or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
javascript
jquery
php5
pdo
or ask your own question.