help with getting similar entries under single entry

emrys404

Okay, this code is supposed to reprsent dynamic pages and organize them according to what menu box they are supposed to be under. currently it organizes the info like this:

--projects---
new

--projects---
old

--projects--
etc.

where i want it to be formatted like this:
--projects--
new
old
etc.

I know this is because of the inner join, but this piece of code is going to be present on multiple pages (by using it in an include(); ) so i want it to be as efficient as possible because many people will be viewing this at once...
Any suggestions how to do this effieciently and effectivley?

current code:

<?php

$link = mysql_connect("localhost", "username", "pass") or die("Could not connect to database");
mysql_select_db("ma", $link) or die("Could not select database");

$result = mysql_query("SELECT hall.*, room.* FROM hall INNER JOIN room WHERE hall.hallname = room.hallname", $link) or die (mysql_error());
$i = mysql_num_rows($result);

while($i >0)
{
  --$i;

	list($hid, $hallname, $rid, $hallroomname, $roomname, $date) = mysql_fetch_row($result);

	?>
			<!-----template hall---------->	
				<table border="0" cellspacing="0" cellpadding="0" width="150">
				<tr>
					<td bgcolor="#000000" >
					<table width="150" border="0" cellspacing="1" cellpadding="0">
					<tr>
						<td valign="top" bgcolor="#123456"><IMG SRC="/mypage/images/topleft02.gif" WIDTH="15" HEIGHT="15" ALIGN="absmiddle"></IMG> &nbsp;<font color="#FFFF00"><b><?php echo $hallname; ?></b></font></td>
					</tr>
					<tr>
						<td bgcolor="#47515f"><font face="verdana,arial,helvetica" size="1" class="sf">
					<strong><big>·</big></strong> <a href="/mypage/room.php?rid=<?php echo $rid; ?>"><?php echo $roomname; ?></a><br>
					</font>
						</td>
					</tr>
					</td>
					</table>

				</tr>
				</table>	

			<!------/template hall---------->
<br>
<?php
}// end template loop

mysql_free_result($result);
mysql_close();
?>

oh yes, the table "hall" has columns hid and hallname. table "room" has columns rid, hallname, roomname, date, author and content. hall.hallname and room.hallname will the the same where it should be under the hall menubox type thing...

I"m sorry if this is all very confusing. its very late 🙂
Any and all help is appreciated. Thanks
-emrys

tuf-web_co_uk

what exactly are u trying to do?!

only show 1 result even if there are several the same?

emrys404

As i said, i'm trying to organize some dynamic pages (ie. a user can use a form and create category then populate it with material) i used two tables to do this, one table is the category (hall) and the other is the content (room). The code i posted organizes all the dynamic pages by groups. it puts all the rooms with the same hall under a html box with the hall's name. Thats what i want it to do anyway..

does this make any more sense? probably not, heh.

Thakns in advance
-emrys

emrys404

I know the above posts are a bit difficult to understand so i'll try to reword the problem as best i can...

I have two tables, one that remembers the name of the category and another table that contains the name of the sub category and other pertinent data (ie, like what category its in, author, date, content, etc). When i go to pull it out of the db with the code above I get this:

--category---
sub-category

--category--
sub-category

where i want it to be formatted like this:
--category--
sub-category
sub-category
sub-category

Hope that clears things up. Let me know if it doesnt.
any and all help is appreciated.
Thanks
-emrys

tuf-web_co_uk

instead of using the catergory and sub-category in the same while loop put the category before the while loop

eg.


// load the distinct category from the database
$sql = "SELECT DISTINCT cat.....";

while () {

echo "category";

$sql = "SELECT sub-cat.... where cat=$row[cat]";
// select the subcategory for that category

while () {

echo "sub-cat";

}

}

emrys404

Thanks! that was exactly what i needed. Thanks alot. Now i need advise on how to make this more efficient. This particular code is going to be in a tool bar that gets displayed on most pages of the site so i need advise on how to make it more efficient. It makes two calls to the database everytime its displayed and i'm worried that might be too much..

<?php

$link = mysql_connect("localhost", "username", "password") or die("Could not connect to database");
mysql_select_db("ma", $link) or die("Could not select database");

//$result = mysql_query("SELECT hall.*, room.* FROM hall INNER JOIN room WHERE hall.hallname = room.hallname", $link) or die (mysql_error());
$resulthall = mysql_query("SELECT DISTINCT hallname FROM hall ORDER BY hid DESC", $link) or die (mysql_error());
$i = mysql_num_rows($resulthall);

while($i >0)
{
  --$i;
  list($hallname) = mysql_fetch_row($resulthall);

	?>
			<!-----template hall---------->	
				<table border="0" cellspacing="0" cellpadding="0" width="150">
				<tr>
					<td bgcolor="#000000" >
					<table width="150" border="0" cellspacing="1" cellpadding="0">
					<tr>
						<td valign="top" bgcolor="#123456"><IMG SRC="/mypage/images/topleft02.gif" WIDTH="15" HEIGHT="15" ALIGN="absmiddle"></IMG> &nbsp;<font color="#FFFF00"><b><?php echo $hallname; ?></b></font></td>
					</tr>
					<tr>
						<td bgcolor="#47515f"><font face="verdana,arial,helvetica" size="1" class="sf">
				<?php
					$resultroom = mysql_query("SELECT * FROM room WHERE hallname = '$hallname' ORDER BY rid DESC", $link) or die (mysql_error());
					$a = mysql_num_rows($resultroom);

					while($a >0)
					{
					 --$a;
					 list($rid, $hallnameroom, $roomname, $date) = mysql_fetch_row($resultroom);
				?>
					<strong><big>·</big></strong> <a href="/mypage/room.php?rid=<?php echo $rid; ?>"><?php echo $roomname; ?></a><br> 
				<?php
					}
				?>
					</font>
						</td>
					</tr>
					</td>
					</table>

				</tr>
				</table>	

			<!------/template hall---------->
<br>
<?php
}// end template loop

if($resultroom) mysql_free_result($resultroom);
mysql_free_result($resulthall);
mysql_close();
?>

Thanks again!

tuf-web_co_uk

running more than 1 sql call on 1 page is not "in-efficient", its more natural than anything

maybe at later dates when the number of sql calls on 1 page increases alot then you can worry about in-efficiency, and slowness.

i'm sure the code you have is probably the most efficient it will be

emrys404

I'm not just concerned about a single call to the db. Its a page that is pretty sql intensive and that particular code is included on almost every page of the site... so its not just a couple of calls. Anyway thanks for your help, i guess i'll get concerned about it only if it starts to bog down. 🙂 Thanks again
-Emrys