[RESOLVED] Eliminating multiple do-while loops

swatisonee

Hi,

I've been using the foll type of coding since i started php a while ago but i know multiple do-while loops can be eliminated to make the code more efficient. i've tried various combinations but i cannot seem to make it work. Would anyone be able to have a look and tell me how i can change it ? Thanks !

<?

session_cache_limiter('public');
include ("../include/session.php");
include ("../include/ticker.php");

 if(!isset($_SESSION['userid'])){
 echo "<center><font face='Verdana' size='2' color=red>Sorry, Please login and use this page </font></center>";
 exit;}
$userid = $_SESSION['userid'];

if(isset($_POST) && !empty($_POST["customer"]))

{

?>
<b><p><font face="Tahoma" size="3" color="#E9CF61">These are the office locations  </b></font><p>
<table border="1" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<tr bgcolor="#E9CF61">
<td><b><font size=2 face=Tahoma color=red>S.No.</b></td>
<td><b><font size=2 face=Tahoma color=red>Company</b></td>
<td><b><font size=2 face=Tahoma color=red>Street</b></td>
<td><b><font size=2 face=Tahoma color=red>City </b></td>
<td><b><font size=2 face=Tahoma color=red>Postcode</b></td>
<td><b><font size=2 face=Tahoma color=red>Phone</b></td>
<td><b><font size=2 face=Tahoma color=red>Fax</b></td>
<td><b><font size=2 face=Tahoma color=red>Email</b></td>

<tr>

<?php

$customer = $_POST["customer"];
$i=0;

$sqla = "SELECT * FROM `Customers` WHERE  `Company` LIKE '%$customer%' ORDER BY `Company` asc";

$resulta = mysql_query($sqla) or die (mysql_error ());

if ($myrowa = mysql_fetch_array($resulta))
{

do {

$cid = $myrowa["CID"];
$company = $myrowa["Company"];

$resultc = mysql_query("SELECT * FROM `Custaddress` WHERE CID = $cid ");
if ($myrowc=mysql_fetch_array($resultc))
{

do {

$cityid= $myrowc['CITYID'];
$sql3 =" SELECT  * FROM Cities WHERE CITYID = $cityid  ";
$result3 = mysql_query( $sql3 ) or die (mysql_error());
$myrow3 = mysql_fetch_array($result3);
$city = $myrow3['City'];
$st = $myrow3['State'];
$i= $i+1;
    printf("<tr><td><font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
    <font size=2 face=Tahoma color=blue>%s<td>
     </tr>",
 $i,$company,
 $myrowc["Street"],
 $city." , ".$st,
 $myrowc["Pcode"],
 $myrowc["Areacode"]." - ".$myrowc["Phone"],
 $myrowc["Areacode"]." - ".$myrowc["Fax"],
 $myrowc["Email"] );

  } while ($myrowc = mysql_fetch_array($resultc));

}
}while ($myrowa = mysql_fetch_array($resulta));

}
?>

</table>

Roger_Ramjet

You do that by writing better queries. In this case you need to join the customers table to the address table and that to the cities table

$sql = "SELECT * FROM customers c INNER JOIN custaddress a USING(cid) INNER JOIN cities t USING(cityid) WHERE c.company LIKE '%$customer%' ORDER BY company asc";

swatisonee

Thank you for replying. Using that query only the 1st matching record is returned and not the whole list.

Roger_Ramjet

Are you itterating through the results or just fetching the first one? You need

while ($row=mysql_fetch_array($result)) {
echo
echo

}

swatisonee

Aargh ! Sorry that was loose of me. Yes it works now and thank you. I do have trouble using joins and i guess when i try to eliminate it , i end up with the many loops. I hope i can improve.

Although i am marking this thread as solved, heres' why i have trouble with joins. If i want to get the value of $cgid and then list out all cos with their addresses i cannot do it. cgid is a value that occurs only in the customers table ( structure - cid, cgid and company)

$sqla = "SELECT * FROM `Customers` WHERE  `Company` LIKE '%$customer%' ORDER BY `Company` asc";
$resulta = mysql_query($sqla) or die (mysql_error ());
if ($myrowa = mysql_fetch_array($resulta));
$cgid = $myrowa["CGID"];

$resultc = mysql_query("SELECT * FROM Custaddress INNER JOIN Customers ON Custaddress.CID WHERE (Custaddress.CID = $cid  || Customers.CGID = $cgid) ORDER BY `Company` asc ");

By doing this i had hoped to get not only all companies that match '%$customer% but others in the same group. Anyway i dont get a result with the above query so its also wrong.

Example: '%$customer% = nation . This will yield nation, national, international . But if nation and subcontinent are 2 companies of the same group say 1 , then when i run $sql, i not only want nation, national, international to get listed but also subcontinent. And thats where the join gives me problems.

Any ideas ? Thanks.

Thanks again !
Swati