Query output in tables question

ChrisF79

Hey everyone,

I'm new to PHP and have been reading a lot about it. I'm stuck however on this little output problem and was hoping someone could point me in the right direction.

I am doing a tutorial site (not for PHP obviously) and want to make an output similar to the one on http://www.htmlcenter.com/tutorials/index.cfm/main/ . I need the category name from one table to be put at the top, with a few tutorial titles from a different table underneath just like they did. Again, it will be in table format which is what confuses me. Can anybody tell me how to go about doing this? I think I could do it with about 30 queries, but there has to be a much easier and better way. Any suggestions would be greatly appreciated.

Thanks in advance,

Chris

leatherback

Hi,

This is largely dependent on the way you have setup your database, I assume you have made sure you can actually do it.

OK.. Here we go.

You have headers of each section on the page ( here HTML, dHTML, CSS etcetc) and then you have subsequent sub_sections listed underneath.

Make sure you have a section_id, or section colum in the database, and a sub_section_id, or sub_section in the table.

Then, all you do is:

$query ="select header from table";
  $headers = mysql_query($query);
  while ($row =@ mysql_fetch_array($headers)) 
   {
     $thissection=$row[section]
      $query2 = "select * from table where ( section = $thissection)";
     $result = mysql_query($query2);
     while ($row2 =@ mysql_fetch_array($result))

 {
 echo $thissection; 
 echo $row[sub_section]
 }
   }

Hope this makes sense

kevinsequeira

30 queries !! you could probably do it in 2 i guess....

STEP 1 :
a. have two table called category and titles.
b. category table has category_id, category_name
c. titles table has title_id, title_name, category_id

so basically the titles table has category_id which it references from the category table;

eg
category table
1|html
2|java
3|php

title table
1|creating tables|1
2|creating forms|1
3|awt|2
4|applets|2
5|running php scripts with cron|3
6|form validation|3

STEP 2: connect to db and display the way it is at the url you gave<BR>

<?php
	$host = "your_host";
	$user = "your_username";
	$pwd = "your_password";
	$database = "your_database";
	mysql_connect($host,$user,$pwd);
	mysql_select_db($database);
	$query = "select * from category order by category_name";
	$result = mysql_query($query);	
?>
<table border="1">
<?
	$count = 1;
		while($row = mysql_fetch_array($result)){
		if($count%3 ==1)
			print("<tr>");	

		print("<td>");
?>
<b><?php print($row['category_name']); ?></b><br>
<?
     		$title_query = "select * from title where category_id='".$row['category_id']."'";
     		$title_result = mysql_query($title_query);
     		while($title_row = mysql_fetch_array($title_result)){
     			echo $title_row['title_name']."<BR>";
     		}

	print("</td>");
	if($count%3 == 0)
		print("</tr>");				
	$count++;
}
?>
</table>
<?php mysql_close(); ?>

hope this helps
reg
kevin

ChrisF79

Wow, thank you so much for your reply. That all makes a lot of sense now that I see your methodology. I'll give it a try later today and let you know how it goes. Thanks again!