Query Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Query Problem

Post 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 :)
Last edited by John Cartwright on Mon Jul 26, 2004 1:15 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you want to echo out all catagories? remove the group by then.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I want to display the catagory

then all the rules that are in that catagory
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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>";
   }
?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

wow i'm a noob ty
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'];
      }   
    } 

?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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 :)
Post Reply