Help noob with multiple while

[kletig]

Hello

I have some PHP code that will extract data from multiple tables and present it with mpdf as a PDF. Everything is working well except i only get the last row instead of all rows with one of the while statements.

I realize that it's not optimal to run multiple while statements within eachother, but since i am a noob this is the only way i can get the job done. I'm sure there are plenty of room for improvements as for both performance and security, however i'm still learning.

It's the "getpos" that wont return all rows.

This is the code:

<?php
session_start();
if($_SESSION['loggedin'] != "ja") {
header('Location: ' . BASE_URL . '/login.php');
exit();
}
else {
require('settings.php');
require('connect.php');
require('mpdf/mpdf.php');

$kundnr = mysql_real_escape_string($_GET['kundnr']);
$certnr = mysql_real_escape_string($_GET['certnr']);

$getcert = mysql_query("SELECT * FROM certificate WHERE certifikatnr='$certnr' AND kundnr='$kundnr'");
while ($cert = mysql_fetch_assoc($getcert)){

    $getcustomer = mysql_query("SELECT * FROM customer WHERE kundnr='$kundnr'");
    while ($customer = mysql_fetch_assoc($getcustomer)){

        $getpos = mysql_query("SELECT * FROM position WHERE certifikatnr='$certnr'");

        while ($position = mysql_fetch_assoc($getpos)){

            $specidt = $position['specid'];
            $tillv_inkopnr = $position['tillv_inkopnr'];
            $artikelnr = $position['artikelnr'];

            $getspec = mysql_query("SELECT * FROM spec WHERE specid='$specidt'");
            while ($spec = mysql_fetch_assoc($getspec)){

                $getpurorder = mysql_query("SELECT * FROM pur_order WHERE tillv_inkopsnr='$tillv_inkopnr'");
                while ($purorder = mysql_fetch_assoc($getpurorder)){

                    $charge1 = $purorder['charge_nr1'];
                    $charge2 = $purorder['charge_nr2'];
                    $charge3 = $purorder['charge_nr3'];

                    $getitem = mysql_query("SELECT * FROM item WHERE artikelnr='$artikelnr'");
                    while ($item = mysql_fetch_assoc($getitem)){

                        $getcharge = mysql_query("SELECT * FROM charge WHERE chargenr='$charge1'");
                        while ($charge = mysql_fetch_assoc($getcharge)){

$header = "<div class='topcontainer'><div class='topleft'><img src='http://www.url.com/img/logo-pdf.png'></div><div class='topright'><h2>INTYG / CERTIFICATE</h2><table width='100%'><tr><td class='kant'>Datum / Date<br /><strong>" . $cert['datum'] . "</strong></td><td class='kant'>Nr / No<br /><strong>" . $cert['certifikatnr'] . "</strong></td><td class='kant'>Sida / Page<br /><strong>{PAGENO}</strong></td></tr></table></div></div><div class='topcontainer'><div class='topleft'><table width='100%'><tr><td class='kant'>Kund / Customer<br /><strong>" . $customer['kundnamn'] . "<br />" . utf8_encode($customer['gatuadress']) . "<br />". utf8_encode($customer['postadress']) . "<br />" . $customer['postnr'] . " - " . utf8_encode($customer['ort']) . "<br />" . $customer['land'] . "</strong></td></tr></table></div><div class='topright'><table width='100%'><tr><td colspan='2' class='kant'>Kund ordernr / Customer order no<br /><strong>" . $cert['kundorder'] . "</strong></td></tr><tr><td class='kant'>Vår order / Our order<br /><strong>" . $cert['var_order'] . "</strong></td><td class='kant'>Utfärdat av / Approved by<br /><strong>Company</strong></td></tr><tr><td class='kant'>Kundkrav / Customer demands<br /><strong>ISO 3506</strong></td><td class='kant'>Levererad / Supplied<br /><strong>ISO 3506</strong></td></tr></table></div></div>";

$html = "<div class='topcontainer'><table width='100%'><tr><td class='kant'>Pos<br /><strong>" . $position['pos'] . "</strong></td><td class='kant'>Vårt artikelnr / Our item nr<br /><strong>" . $position['artikelnr'] . "</strong></td><td class='kant'>Beskrivning / Description<br /><strong>" . $item['benamning'] . "</strong></td><td class='kant'>EN 10204<br /><strong>" . $position['specid'] . "</strong></td><td class='kant'>TO nr / Pro Lot no<br /><strong>" . $position['tillv_inkopnr'] . "</strong></td></tr></table></div><div class='topcontainer2'><strong>Kemisk sammansättning enl stålleverantören / Chemical composition according to steel supplier</strong><br /><br />Charge nr / Cast no <strong>" . $charge['chargenr'] . "</strong><br /><br /><table width='100%'><tr><td></td><td>C%</td><td>Si%</td><td>Mn%</td><td>P%</td><td>S%</td><td>Cr%</td><td>Ni%</td><td>Mo%</td><td>Cu%</td><td>N%</td><td>Övr / Other</td></tr><tr><td></td><td><strong>" . $charge['c'] . "</strong></td><td><strong>" . $charge['si'] . "</strong></td><td><strong>" . $charge['mn'] . "</strong></td><td><strong>" . $charge['p'] . "</strong></td><td><strong>" . $charge['s'] . "</strong></td><td><strong>" . $charge['cr'] . "</strong></td><td><strong>" . $charge['ni'] . "</strong></td><td><strong>" . $charge['mo'] . "</strong></td><td><strong>" . $charge['cu'] . "</strong></td><td><strong>" . $charge['n'] . "</strong></td><td><strong>" . $charge['ovrigt'] . "</strong></td></tr><tr><td>Spec. Min</td><td>" . $spec['c_min'] . "</td><td>" . $spec['si_min'] . "</td><td>" . $spec['mn_min'] . "</td><td>" . $spec['p_min'] . "</td><td>" . $spec['s_min'] . "</td><td>" . $spec['cr_min'] . "</td><td>" . $spec['ni_min'] . "</td><td>" . $spec['mo_min'] . "</td><td>" . $spec['cu_min'] . "</td><td>" . $spec['n_min'] . "</td><td></td></tr><tr><td>Spec. Max</td><td>" . $spec['c_max'] . "</td><td>" . $spec['si_max'] . "</td><td>" . $spec['mn_max'] . "</td><td>" . $spec['p_max'] . "</td><td>" . $spec['s_max'] . "</td><td>" . $spec['cr_max'] . "</td><td>" . $spec['ni_max'] . "</td><td>" . $spec['mo_max'] . "</td><td>" . $spec['cu_max'] . "</td><td>" . $spec['n_max'] . "</td><td></td></tr></table></div><div class='topcontainer2'><strong>Mekaniska egenskaper enligt ISO 3506 / Mechanical properties according to ISO 3506</strong><br /><br /><table width='100%'><tr><td></td><td>Rm N/mm&#178;</td><td>Rp 0.2 n/mm&#178;</td><td>Brottöjning / Elongation</td><td>Provbelastn mutter / Proof load nuts N/mm&#178;</td><td>MBmin</td><td>Hårdhet / Hardness</td><td>Antal prov / No of samples</td></tr><tr><td>Min</td><td><strong>" . $purorder['rm_min'] . "</strong></td><td><strong>" . $purorder['rp_min'] . "</strong></td><td><strong>" . $purorder['brottojn_min'] . " x d</strong></td><td><strong>" . $purorder['provbelastn_n'] . "</strong></td><td><strong>" . $purorder['provbelastn_mb'] . "</strong></td><td><strong>" . $purorder['hardhet_min'] . "</strong></td><td><strong>" . $purorder['antal_prov'] . "</strong></td></tr><tr><td>Max</td><td><strong>" . $purorder['rm_max'] . "</strong></td><td><strong>" . $purorder['rp_max'] . "</strong></td><td><strong>" . $purorder['brottojn_max'] . " x d</strong></td><td><strong>???</strong></td><td><strong>???</strong></td><td><strong>" . $purorder['hardhet_max'] . "</strong></td><td><strong>???</strong></td></tr><tr><td>Spec Min</td><td><strong>" . $spec['rm'] . "</strong></td><td><strong>" . $spec['rp'] . "</strong></td><td><strong>" . $spec['elongation'] . " x d</strong></td><td><strong>" . $spec['provbelastn_n'] . "</strong></td><td><strong>" . $spec['provbelastn_mb'] . "</strong></td><td><strong>" . $spec['hardhet'] . "</strong></td><td><strong>" . $spec['antal_prov'] . "</strong></td></tr><tr><td>Spec Max</td><td><strong>???</strong></td><td><strong>???</strong></td><td><strong>???</strong></td><td><strong>???</strong></td><td><strong>???</strong></td><td><strong>" . $spec['hardhet_max'] . "</strong></td><td><strong>???</strong></td></tr></table></div><div class='topcontainer2'>" . $position['anmarkning'] . "</div>";

$footer = '
<table width="100%" style="border-top: 1px solid #000000; vertical-align: bottom;">
<tr><td><strong>Company</strong></td></tr>
</table>
';

$mpdf=new mPDF('utf-8','A4');
$mpdf=new mPDF();

$stylesheet = file_get_contents('css/pdfstyle.css');
$mpdf->WriteHTML($stylesheet,1);
$mpdf->SetHTMLHeader($header);
$mpdf->SetHTMLFooter($footer);
$mpdf->WriteHTML($html);
$mpdf->Output();
exit;
                        }
                    }
                }
            }
        }
    }
}
}
?>

[paul_oxy]

ok my friend i will take this code and do it at home because i'm in job right now and i will replay you in the night dont care my friend i wil give you the solution ;)

SNIP
SNIP

[kletig]

Wow! Thanks paul_oxy!

Thats... awesome =)

[veedeoo]

Hi,

This is just a humble suggestion.. I didn't really read your codes in its entirety, but I could see lots of improvements in the nested while loops area. You can try improving it by using mysql join. By using join, you might be able to get rid some of the while loops.

In my previous expermental application, I used singleton database connector class as shown ,,, Here. This can help a lot on the database function of your script, and this can serve as the CRUD on your application.

There is nothing wrong with nested while loops if there is no other way of avoiding it. So, don't force it, if it cannot be done with effeciency.

[kletig]

I have created a demo-version of this code where i only have a single while statement that goes through mpdf and it still only shows the last row.

If i use my code to do a simple echo of the values i get all rows, so i guess the syntax is correct.

Is it even possible to get the contents from a while statement in mpdf by setting it as the "html" variable?

$getpos = mysql_query("SELECT * FROM position WHERE certifikatnr='$certnr'");
while ($position = mysql_fetch_assoc($getpos)){

$html = "Position: " . $position['pos'] . "<pagebreak />";

}

[kletig]

Finally this is solved!

I ditched the first two while statements and fetched those values by using GET and then did some INNER JOIN action between a couple of other tables... works like charm now! Thanks veedeoo who got me in the right direction with JOIN.