combining two lists

[corruption]

I thought that i could do this task with a join, but i'm not sure now.

I have database that contains a list of pledge classes and a table of people.

I want the output to come out in a format that shows the pledgeclass and then those that are in that pledgeclass and then the next pledgeclass and those people in that one and so on.

here's what i have right now.

<td valign="top" class="content_table">
<table  class="news_table"  bgcolor="#FFFFFF">
<?
//echo "<tr><td colspan='5' class='newstitle'> Alumni of Iota Nu </td></tr>";
//script to display the list of people

$sql = "SELECT * FROM class, alumni";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0)
	{
	while ($row = mysql_fetch_array($result)) {
    	$pledgeclass = $row['class'];
    	echo "<tr><td='menuh_class'>";
   	echo $pledgeclass;
   	echo "</td></tr>";
   	echo "<tr><td>";
   	$sql = "SELECT * FROM alumni WHERE pledgeclass = '$pledgeclass'";
   	$result = mysql_query($sql) or die(mysql_error());
   	if(mysql_num_rows($result) > 0){
	   	while($row = mysql_fetch_array($result))
	   	echo "<a>";
	   	echo $row['fname']." ".$row['lname']." ".$row['iotanu'];
   	echo "</a></td></tr>";

}
}
}

All that this returns is the first pledgeclass.

[Installer]

You are using both "$result" and "$row" in both of your loops. You can't use the same variable to store two different values at the same time. Also you have no braces around the second "while" loop, so that only the "echo "<a>";" statement gets executed in the loop (better formatting would make this obvious). Fix these problems and you'll be a lot closer to making it work.

[corruption]

thanks for the help, I've got the script doing what I want, except it does it until the page times out.

I can't figure out what's wrong.

<?
//echo "<tr><td colspan='5' class='newstitle'> Alumni of Iota Nu </td></tr>";
//script to display the list of people

$sql = "SELECT * FROM class, alumni";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) > 0)
	{
	while ($row = mysql_fetch_array($result)) 
		{
    		$pledgeclass = $row['class'];
       	echo "<tr><td class=\"menuh_class\">";
		echo $pledgeclass;
		echo "</td></tr>";
    		$sql2 = "SELECT * FROM alumni WHERE pledgeclass = '$pledgeclass'";
   		$result2 = mysql_query($sql2) or die(mysql_error());
   		if(mysql_num_rows($result2) > 0)
   	{
	   	while($row2 = mysql_fetch_array($result2))
	   	{
	   	echo "<tr><td>";
	   	echo "<a>";
	   	echo $row2['fname']." ".$row2['lname']." IN".$row2['iotanu'];
   		echo "</a></td></tr>";
	}
	}
}

}

[bpat1434]

How many records are you calling back? Too many could cause the query to be really huge, and thus time-out your page.

And your first query doesn't require the alumni table to be queried. You're not retrieving data from it (I would assume). That could also help increase the returned size.

Biggest thing is the number of rows that are being called. I would paginate it (50) or so.

~Brett

[Weedpacket]

Originally posted by bpat1434
And your first query doesn't require the alumni table to be queried. You're not retrieving data from it (I would assume). That could also help increase the returned size.

Hugely: if there are C records in the classes table, and A records in the alumni table, that combined query would return C&times;A results: every record in the classes table paired off with every record in the alumni table! It's fairly certain that that is not what you want 🙂