Page 1 of 1

Query Problem

Posted: Mon Jul 26, 2004 12:19 pm
by John Cartwright
K I have 2 tables

clanrules_rules

contains ruleid, catagoryid, rule

clanrules_catagory

contains catagoryid, catagory

and I have this query

Code: Select all

<?php
	$result = mysql_query("SELECT  * FROM clanrules_rules a 
						   LEFT JOIN clanrules_catagory b 
						   ON a.catagoryid = b.catagoryid  
						   GROUP BY b.catagoryid") or die(mysql_error()); 
?>
and do loop through the catagories i do:

Code: Select all

<?php
	while ($row = mysql_fetch_array($result))
	{
	echo $row["catagoryid"];
	echo $row["catagory"]."<br><br>";
	echo $row["rule"]."<br><br>";
	}
?>
but this will only echo out the first rule contained withen each catagory.. what's the proper way to do this?

THANkS :)

Posted: Mon Jul 26, 2004 12:27 pm
by feyd
you want to echo out all catagories? remove the group by then.

Posted: Mon Jul 26, 2004 12:30 pm
by John Cartwright
I want to display the catagory

then all the rules that are in that catagory

Posted: Mon Jul 26, 2004 12:35 pm
by feyd
something like?

Code: Select all

<?php
   $oldcat = '';
   while ($row = mysql_fetch_assoc($result))
   {
   if($row["catagoryid"] != $oldcat)
     echo "<hr />" . ($oldcat = $row['catagoryid']) . "<br />";
   echo $row["catagory"]."<br><br>";
   echo $row["rule"]."<br><br>";
   }
?>

Posted: Mon Jul 26, 2004 12:36 pm
by John Cartwright
wow i'm a noob ty

Posted: Mon Jul 26, 2004 1:17 pm
by John Cartwright

Code: Select all

<?php

	$result = mysql_query("SELECT  * FROM clanrules_rules a 
						   LEFT JOIN clanrules_catagory b 
						   ON a.catagoryid = b.catagoryid  ") or die(mysql_error()); 
	$oldcat = ""; 
	$i=0;

	$template = new template();

	while ($row = mysql_fetch_assoc($result)) 
	{ 
	$i++;
 	if($row["catagory"] != $oldcat) 
		{	
			$template->maketable($oldcat = $row['catagory']);		
		}
		
		echo "<tr>
				<td>". 
					$i.": ".$row["rule"]."<br><br>
				</td>
			  </tr>"; 

		
    }  

	// $template->closetable("");


?>
I duno what's wrong with me but I can't seem to figure out anything today
.... I can't figure out an efficient way to get the function closetable() to get but in the last spot of its catagory and then space the tables

Posted: Mon Jul 26, 2004 1:54 pm
by feyd
maybe more like:

Code: Select all

<?php

   $result = mysql_query("SELECT  * FROM clanrules_rules a
                     LEFT JOIN clanrules_catagory b
                     ON a.catagoryid = b.catagoryid  ") or die(mysql_error());
   $oldcat = "";
   $i=0;

   $template = new template();

   while ($row = mysql_fetch_assoc($result))
   {
   $i++;
   if($row["catagory"] != $oldcat)
      {   
         $template->maketable($row['catagory']);
      }
      
      echo "<tr>
            <td>".
               $i.": ".$row["rule"]."<br><br>
            </td>
           </tr>";

   if($row["catagory"] != $oldcat)
      {   
      $template->closetable("");
      $oldcat = $row['catagory'];
      }   
    } 

?>

Posted: Mon Jul 26, 2004 2:09 pm
by John Cartwright
this is not a very good way of doing it but it works...

Code: Select all

<?php
	$result = mysql_query("SELECT  * FROM clanrules_rules a 
						   LEFT JOIN clanrules_catagory b 
						   ON a.catagoryid = b.catagoryid  ") or die(mysql_error()); 
	$oldcat = ""; 
	$i = 0;
	
	$template = new template();

	while ($row = mysql_fetch_assoc($result)) 
	{ 
		$numrows = (mysql_num_rows(mysql_query("SELECT  * FROM clanrules_rules WHERE catagoryid = '".$row["catagoryid"]."'")));    
		$i++;
		
		if($row["catagory"] != $oldcat) 
		{	
			$template->maketable($oldcat = $row['catagory']);		
			$i2 = 0;
		}
			
		$i2++;
			
		echo "<tr>
				<td style='padding-left: 5px'>". 
					$i.". ".$row["rule"]."
				</td>
			  </tr> 
			  <tr>
			  	<td>
					&nbsp;
				</td>
			  </tr>";	
		
		if ($i2 == $numrows)
		{
			$template->closetable("");
			echo "<br>";
		}
		
    }  

?>
btw feyd your made my tables appear all distorted

ty alot tho !! as usual :)