Another way to loop this?

[ale1981]

I have been trying to work this out all afternoon and there is probably an easy solution, but I can not think of it!

 

My current code loops through a result of order number's. Now if there is just one manifest returned for that order number, this is fine, BUT, if there is more than one i obviously get the 2 tables instead of just the one with 2 rows. I know its because the while is looping through each order number and therefore showing the code below for each manifest. How can i re-arrange this so that just one table is shown ?

 

	while($man_inf = mssql_fetch_array($result)) {
		$man_date =	convert_date($man_inf['PDATE']);
		$sql2 = 'SELECT pid, title, driver, man_date, size FROM manifestPDF WHERE (man_date = ' .$man_date. ') AND (driver = "' .trim($man_inf['UPSZONE']). '")';
		$result2 = mysql_query($sql2);
		$num_PDFs = mysql_num_rows($result2);
		echo '<p>The following ' .$num_PDFs. ' PDF(s) have been found: </p>';
?>
		<table class="sep" width="250">
		<tr>
			<td class="title">Driver</td>
			<td class="title" colspan="2">Manifest Date</td>
		</tr>
<?
		if ($num_PDFs > 0) {
			while($PDF_inf = mysql_fetch_array($result2)) {
				$man_date = date('d/m/Y', $PDF_inf['man_date']);
?>
				<tr>
					<td><?=$PDF_inf['driver'];?></td>
					<td><?=$man_date?></td>
					<td><a href="view_manifest.php?id=<?=$PDF_inf['pid']?>">View</a></td>
				</tr>
<?
			}
		echo '</table>
		<p><a href="javascript: history.go(-1)">SEARCH AGAIN</a></p>';
		} else {
			echo '<p>No Manifests found with that criteria.</p>
			<p><a href="javascript: history.go(-1)">SEARCH AGAIN</a></p>';
		}
	}

 

Thanks in advance and apologise if its a simple solution, my brain is dead today.

 

[Wuhtzu]

Put the table outside the first while() ?

 

<table class="sep" width="250">
    <tr>
        <td class="title">Driver</td>
        <td class="title" colspan="2">Manifest Date</td>
    </tr>

<?php

while($man_inf = mssql_fetch_array($result)) {
    
    	$sql2 = 'SELECT pid, title, driver, man_date, size FROM manifestPDF WHERE (man_date = ' .$man_date. ') AND (driver = "' .trim($man_inf['UPSZONE']). '")';

        if ($num_PDFs > 0) {	

            while($PDF_inf = mysql_fetch_array($result2)) {	

                $man_date = date('d/m/Y', $PDF_inf['man_date']);
?>
                    <tr>
                        <td><?=$PDF_inf['driver'];?></td>
                        <td><?=$man_date?></td>
                        <td><a href="view_manifest.php?id=<?=$PDF_inf['pid']?>">View</a></td>
                    </tr>
<?php
            }
        }
        else {
                    <tr>
                        <td><p>No Manifests found with that criteria.</p><p><a href="javascript: history.go(-1)">SEARCH AGAIN</a></p></td>
                    </tr>
        }
}

?>

</table>  

[ale1981]

Thanks for your reply Wuhtzu,

 

I knew it would be fairly simple, but I think i thought of that, and then failed on how to get the num of PDFs to display correctly?

 

[Wuhtzu]

Just add a new row for that information:

 

My code again with the new row:

<table class="sep" width="250">
    <tr>
        <td class="title">Driver</td>
        <td class="title" colspan="2">Manifest Date</td>
    </tr>

<?php

while($man_inf = mssql_fetch_array($result)) {
    
    	$sql2 = 'SELECT pid, title, driver, man_date, size FROM manifestPDF WHERE (man_date = ' .$man_date. ') AND (driver = "' .trim($man_inf['UPSZONE']). '")';
        $num_PDFs = mysql_num_rows($result2);
?>
        <tr>
            <td>The following <?php echo $num_PDFs; ?> PDF(s) have been found:</td>
        </tr>

<?php
        if ($num_PDFs > 0) {	

            while($PDF_inf = mysql_fetch_array($result2)) {	

                $man_date = date('d/m/Y', $PDF_inf['man_date']);
?>
                    <tr>
                        <td><?=$PDF_inf['driver'];?></td>
                        <td><?=$man_date?></td>
                        <td><a href="view_manifest.php?id=<?=$PDF_inf['pid']?>">View</a></td>
                    </tr>
<?php
            }
        }
        else {
                    <tr>
                        <td><p>No Manifests found with that criteria.</p><p><a href="javascript: history.go(-1)">SEARCH AGAIN</a></p></td>
                    </tr>
        }
}

?>

</table> 

[ale1981]

That still repeats, like;

 

Driver Manifest Date 
The following 1 PDF(s) have been found: 
PW 22/10/2007 View 
The following 1 PDF(s) have been found: 
PW 19/10/2007 View 

 

I would like it to just show;

 

The following 2 PDF(s) have been found:

Driver Manifest Date 
PW 22/10/2007 View 
PW 19/10/2007 View

 

 

[Wuhtzu]

Ahh yes...

 

I believe your sql1 and sql2 (the two queries) can be made into one advanced SQL statement which will be easier to handle because you don't have to while so many times... But I'm not a SQL expert so I wont be able to help you but I know we got some SQL gurus here maybe they will pop by.

 

Ty posting sql1 and sql2 (the two queries) and ask for help to join them

[ale1981]

Unfortunately not!

 

One query is done on a MSSQL db and the other a MYSQL. The inventory system we use at work uses MSSQL, but i prefer to use MySQL  where i can and i am in the process of doing our intranet.

[sasa]

remove man_date condition from WHERE part in $sql2

[ale1981]

remove man_date condition from WHERE part in $sql2

 

Hi sasa,

 

Can you explain a bit further what exactly this will achieve?

 

Thanks

[rajivgonsalves]

You could try this out

 

<table class="sep" width="250">
    <tr>
        <td class="title">Driver</td>
        <td class="title" colspan="2">Manifest Date</td>
    </tr>
<?php
        $num_main_PDFs = mssql_num_rows($result);

?>
<tr>
            <td>The following <?php echo $num_main_PDFs; ?> PDF(s) have been found:</td>
        </tr>


<?php

while($man_inf = mssql_fetch_array($result)) {
    
    	$sql2 = 'SELECT pid, title, driver, man_date, size FROM manifestPDF WHERE (man_date = ' .$man_date. ') AND (driver = "' .trim($man_inf['UPSZONE']). '")';
        $num_PDFs = mysql_num_rows($result2);

        if ($num_PDFs > 0) {	

            while($PDF_inf = mysql_fetch_array($result2)) {	

                $man_date = date('d/m/Y', $PDF_inf['man_date']);
?>
                    <tr>
                        <td><?=$PDF_inf['driver'];?></td>
                        <td><?=$man_date?></td>
                        <td><a href="view_manifest.php?id=<?=$PDF_inf['pid']?>">View</a></td>
                    </tr>
<?php
            }
        }
        else {
                    <tr>
                        <td><p>No Manifests found with that criteria.</p><p><a href="javascript: history.go(-1)">SEARCH AGAIN</a></p></td>
                    </tr>
        }
}

?>

</table>