...

View Full Version : Grouping?



SDP2006
04-28-2004, 03:31 AM
I have 2 tables in my MySQL database.
- categories
- sections

Categories acts like the forums here - Server-side help, client side help, etc.
Sections acts like the sections in the forums - PHP (with Server Side) , JavaScript (with Client Side), etc.

Both tables have an id that relates them together. For instance a category name 'General' has the id of 1. Each section in that goes with 'General' also has an id of 1, so that they correlate.

My question is, how would I display them together? For instance, how would I get all general sections to show up under the general forum like how PHP,Perl/CGI,etc. show up under Server-Side help here?

I hope I am clear by what I am asking.

Thanks.

sad69
04-28-2004, 09:15 PM
$result_cats = mysql_query("SELECT * FROM tbl_categories", $db);
while($row_cats = mysql_fetch_array($result_cats)) {
echo $result_cats["name"]."<br>";

$result_secs = mysql_query("SELECT * FROM tbl_sections WHERE id=".$result_cats["id"], $db);
while($row_secs = mysql_fetch_array($result_secs)) {
echo " ".$result_secs["name"]."<br>";
}
}


So name is the column that holds the name of the category/section (change that accordingly) and id is the id of the category/section (change that accordingly).

That's a pretty basic example, but that's the idea. You can work with that to put your data into tables or however you want to display it.

Hope that helps,
Sadiq.

SDP2006
04-28-2004, 09:45 PM
Thanks - I'll have to test that probably sometime this weekend since I wil be unable to do so until then. I'll let ya know how it turns out!

Thanks

SDP2006
05-01-2004, 03:26 PM
Okay, it seems to be working *somewhat*. I turns out like this -
Serverside Programming
Client Side Programming
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY `id` ASC' at line 1as you can see its turning out my sections, but there is something wrong with my category query. I usually can debug my own sql errors, but this one is puzzling me :confused:

function main_display(){

$this->connect();

$sections_query = "SELECT id,name,cat_id FROM sections"; /* ie: server side programming, client side, etc. */
$result = mysql_query($sections_query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){ echo $row['name']."<br>"; }

$category_query = "SELECT id,name,cat_id FROM categories WHERE cat_id=".$row['cat_id']." ORDER BY id ASC"; /* ie: php, javascript, html, css, dhtml, etc. */
$result = mysql_query($category_query) or die(mysql_error());
while($row = mysql_fetch_assoc($category_query)){ echo $row['name']."<br>"; }
}
Thanks to all. ;)

trib4lmaniac
05-01-2004, 05:49 PM
while($row = mysql_fetch_assoc($category_query))

Shouldn't that be

while($row = mysql_fetch_assoc($result)) :confused:

raf
05-01-2004, 06:29 PM
the resourceID is indeed wrong.

but the reason for the error is that $row['cat_id'] inside $category_query is empty. (as you would have seen if you printed the executed sql.)

SDP2006
05-02-2004, 02:21 AM
Alright, im making progress :)


function main_display(){

$this->connect();

$sections_query = "SELECT id,name,cat_id FROM sections"; /* ie: server side programming, client side, etc. */
$result = mysql_query($sections_query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){ echo $row['name']."<br>"; $cat_id = $row['cat_id']; /*easier in the string just to set it to a variable*/}

$category_query = "SELECT id,name,cat_id FROM categories WHERE cat_id=".$cat_id." ORDER BY id ASC"; /* ie: php, javascript, html, css, dhtml, etc. */
$result = mysql_query($category_query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){ echo " ".$row['name']."<br>"; }
} returns
Serverside Programming
Client Side Programming
(X)HTML
CSS Yet, there are two 'sub-sections' to Serverside and they are not showing up. Any reasons why?

raf
05-02-2004, 09:08 AM
Probably because there is an error inside your code or because your db isn't filled in correctly.

Could it be that you missed a closing curly bracket ?

Also, you have column id and cat_id inside the categories table --> what's the point of that? Shouldn't it be a section id? And shouldn't the cat_id inside the sections table be dropped?
In a normalised db, you'd have a design like
sections table
section_id (PK)
section_name

categories table
cat_id
cat_name
section_id (FK)

You should also do this with an outer join so save on these querys inside the loop. Assuming that you have the above design then your code is:


function main_display(){

$this->connect();

$query = "SELECT section.section_id, sections.section_name, categories.cat_id, categories.cat_name
FROM sections LEFT JOIN categories ON sections.section_id=categories.section_id
ORDER BY sections.section_name ASC, categories.cat_name ASC";
$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result) >=1){
$previous = '';
while($row = mysql_fetch_assoc($result)){
if ($row['section_name'] != $previous){
echo $row['section_name'] . '<br />';
$previous = $row['section_name'];
}
echo " ".$row['cat_name']. '<br />';
}
}else{
echo 'No sections found';
}
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum