[SOLVED] PHP & MySQL

[aramikgh]

Any one know how to create a php file that gets data from the database and lists them using categories, sub-categories, and sub-sub-categories?  3 levels...?

 

what are you recommendations on how I should do this?

 

this is how i think would work best but dont know what to do after...

structure will look like this: (i think)

 

Category DB

 

Category_Name  |  Category_ID

 

Air                        0

 

Bin                        1

 

Swords                  2

 

 

 

Catalog DB

 

Catalog_Name  |  Catalog_ID

 

Catalog A            1

 

Catalog B            2

 

Catable C            3

 

 

 

Content DB

 

Cat_ID  |  Category_ID  |  pagenum

 

1            1                    02

 

1            0                    04

 

1            2                    06

 

 

Basically i need it to list a category name and then under it it lists which catalog its from then the page num

for ex: 

AIR

  Catalog A

    02,04,03,05,06

Bin

  Catalog A

    08,09,10,11

 

and so forth....

thanks for your help.

[Barand]

query

SELECT a.category_name, b.Catalog_Name, GROUP_CONCAT(c.pagenum ORDER BY pagenum) as pages
FROM content c
INNER JOIN catalog b ON c.cat_id = b.Catalog_ID
INNER JOIN category a.Category_ID = c.Category_ID
GROUP BY a.category_name, b.Catalog_Name

 

loop through results

 

previous_cat = ''
while read next row
   if category != previous_cat
       output category name
       set previous_cat = category name
   end if

   output catalog name, pages
end while

[aramikgh]

Ok this is the code I got...

 

but its not working, Im sure it something below that i wrote is wrong...

thanks for all your help...

(my tables are actually called a,b, & c)

<?php 

include 'config.php';
include 'opendb.php';

$query = "SELECT a.category_name, b.Catalog_Name, GROUP_CONCAT(c.pagenum ORDER BY pagenum) as pages
FROM content c
INNER JOIN catalog b ON c.cat_id = b.Catalog_ID
INNER JOIN category a.Category_ID = c.Category_ID
GROUP BY a.category_name, b.Catalog_Name";


$result = mysql_query($query);


while($row = mysql_fetch_array($result))
{
    if (category != previous_cat) {
        echo "{$row['a.catagory_name']}";
      $previous_cat  =  $row['a.catagory_name'];
  }
    echo "{$row['b.Catalog_Name']}";
}



?>

[bluejay002]

if that is what you need, try this (though i havent tested, am gettin lazy):

 

<?php
include 'config.php';
include 'opendb.php';

// the query
$query = "SELECT DISTINCT a.Category_Name as category, b.Catalog_Name as catalog
FROM Content c
INNER JOIN Catalog b ON c.Cat_Id = b.Catalog_ID
INNER JOIN Category a.Category_ID = c.Category_ID
ORDER BY a.Category_Name, b.Catalog_Name";

$result = mysql_query($query);
$category = null;
$previous_cat = null;

while($row = mysql_fetch_array($result)) {
    $category = $row['category'];

    if ($category != $previous_cat) {
        echo "{$category}";
        $previous_cat  =  $row['category'];
    }
    echo "{$row['catalog]}";
}
?>

 

did it work?

[aramikgh]

Actually im getting the same error as before...

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/elemenu9/public_html/rexdist/test.php on line 16

 

but your coding looks much better than mine...

 

 

 

[aramikgh]

here is how my database tables look:

 

table: a

category_name Category_ID

Air 0

Bin 1

 

table: b

Catalog_Name Catalog_ID

Catalog A 0

Catalog B 1

 

table: c

 

cat_id Category_ID pagenum

0 0 1

0 1 2

 

(just incase)...

thanks again..

 

 

[bluejay002]

here is how my database tables look:

 

table: a

category_name Category_ID

Air 0

Bin 1

 

table: b

Catalog_Name Catalog_ID

Catalog A 0

Catalog B 1

 

table: c

 

cat_id Category_ID pagenum

0 0 1

0 1 2

 

(just incase)...

thanks again..

 

 

 

okay... here is the query:

 

$query = "SELECT DISTINCT a.category_name as category, b.Catalog_Name as catalog
FROM c
INNER JOIN b ON c.cat_id = b.Catalog_ID
INNER JOIN a on a.Category_ID = c.Category_ID
ORDER BY a.category_name, b.Catalog_Name";

 

the error is expected because you need to insert the mysql connection and database selection before doing mysql_query().

 

first do:

 

mysql_connect()

mysql_select_db()

 

before mysql_query()

 

also... add some error trapping to know which part the error started.

 

[aramikgh]

YES!!!

Thanks man...

i went ahead and added the pagenum number as well as the format so now it works perfect...

this is my final code...for now...

<?php
include 'config.php';
include 'opendb.php';

// the query
$query = "SELECT DISTINCT a.category_name as category, b.Catalog_Name as catalog,
c.pagenum as pagen, c.cat_id as catID FROM c
INNER JOIN b ON c.cat_id = b.Catalog_ID
INNER JOIN a on a.Category_ID = c.Category_ID
ORDER BY a.category_name, b.Catalog_Name";


$result = mysql_query($query);
$category = null;
$previous_cat = null;

while($row = mysql_fetch_array($result)) {
    $category = $row['category'];

    if ($category != $previous_cat) {
        echo "{$category}</br />";
        $previous_cat  =  $row['category'];
    }
    echo "{$row[catalog]}<br />"; echo "<a href='catalogView.php?catalog={$row[catID]}&page={$row[pagen]}'>{$row[pagen]}</a><br />";
}
?>