while loop help!!

[dubc07]

Currently i have two tables on the DB

 

One which name is service and the other which is subcategory

 

I'm wanting to pull the service name and list it's subcategory's under it

 

my code is here

 

<?php
			  
			  $query  = "select  * from servicelink ";
$result = mysql_query($query) or die('Error, query failed');
//run the while loop that grabs all the news scripts
  

while($r=mysql_fetch_array($result)) 
   { 
     
  $cat=$r["type"];
  $magsset=$r["servicename"];
  
  
  $query2  = "select  * from subcategory WHERE cat=$cat";
$result2 = mysql_query($query2) or die('Error, query failed');
//run the while loop that grabs all the news scripts
  

while($s=mysql_fetch_array($result2)) 
   { 
     
  
  $magsset=$r["servicename"];
  $info=$r["subs"];
  $fuls="$info<br /> <br />";
  
  //$people = array($pics);
  //$trycount= count($people);
			  $apples='<table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                      <td height="46">'.$magsset.'</td>
                    </tr>
                    <tr>
                      <td height="47">'.$fuls.'</td>
                    </tr>
                  </table>';
			  echo $apples;
			  }
			  }
			  ?>

 

This will pull from both but when it does it, it supplies the servicename twice Like this

 

Product1

sub1

 

Product1

sub2

 

Product1

sub3

 

I was just wondering if someone could help me make it just pull the service 1 and list all the subcats with out supplying the service on each one Like this

 

Product1

sub1

sub2

sub3

 

Thanks for all your help.

 

 

[JonnoTheDev]

Of course. You should just use 1 query to get all categories / subcategories using a join. Then create a multidimensional array to place all subcats under the parent category, then loop over that array to create the output. Using a query nested in a loop is not efficient.

[madspof]

Try this should work but might now lol you were echoing in the wrong place

 

<?php
              
              $query  = "select  * from servicelink ";
$result = mysql_query($query) or die('Error, query failed');
//run the while loop that grabs all the news scripts
  

while($r=mysql_fetch_array($result)) 
   { 
     
     $cat=$r["type"];
     $magsset=$r["servicename"];
     echo "<table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                      <td height="46">'.$magsset.'</td>
                    </tr>";
     
     $query2  = "select  * from subcategory WHERE cat=$cat";
$result2 = mysql_query($query2) or die('Error, query failed');
//run the while loop that grabs all the news scripts
  

while($s=mysql_fetch_array($result2)) 
   { 
     
     $info=$s["subs"];
     $fuls="$info<br /> <br />";
     
     //$people = array($pics);
     //$trycount= count($people);
              $apples='<tr>
                      <td height="47">'.$fuls.'</td>
                    </tr>
                  </table>';
              echo $apples;
              }
              }
              ?>

[JonnoTheDev]

Like I said this is inefficient!

You should construct an array as below using just a single query. If there are 10 categories then your code is running 10 queries plus the main query (11 queries).

Array
(
    [0] => Array
        (
            [maincat] => category 1
            [subcats] => Array
                (
                    [0] => sub cat 1
                    [1] => sub cat 2
                    [2] => sub cat 3
                )

        )

    [1] => Array
        (
            [maincat] => category 2
            [subcats] => Array
                (
                    [0] => sub cat 1
                    [1] => sub cat 2
                    [2] => sub cat 3
                )

        )

    [2] => Array
        (
            [maincat] => category 3
            [subcats] => Array
                (
                    [0] => sub cat 1
                )

        )

)

[dubc07]

How could i put the query into an array?

I'm pretty new too all this, So sorry for the dumb questions!

[Ken2k7]

I think you can do something like -

SELECT sl.servicename, sc.subs FROM servicelink sl INNER JOIN subcategory sc ON sc.cat = sl.type;

[dubc07]

The above array would work but it only limits 3 subs after main service.

There has to be some loop feature that can pull data as it is in the database

 

There are an unlimited amount of subs on the table

[JonnoTheDev]

The above array would work but it only limits 3 subs after main service

eh?

 

Doesn't limit anything. It is an example!