Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 11th, 2004, 06:45 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default MultiLevel PHP

Hi all,
How can I write PHP code that shows (MultiLevel) result from MySQL,
I have this PRODUCTS table (MySQL):

categorey sub-categorey
----------------------------
Shampoo PertPlus
Shampoo SunSilk
Soup Lux
ToothPaste Creast
ToothPaste Signal2
ToothPaste Collgate

and I want to display this result like this:

Shampoo
    PertPlus
    SunSlik
Soup
    Lux
ToothPaste
    Creast
    Signal2
    Collgate


Thanx in advance
Reply With Quote
  #2 (permalink)  
Old February 11th, 2004, 01:41 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default


$sql = "SELECT category, subcategory
          FROM products
         ORDER BY category, subcategory";

$result = mysql_query($sql);

$last_category = "";
while ($row = mysql_fetch_array($result))
{
    if ($row['category'] !== $last_category)
    {
        echo "{$row['category']}\n";
        $last_category = $row['category'];
    }
    echo " {$row['subcategory']}\n";
}


If you don't understand why the above code works, please let me know.


Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #3 (permalink)  
Old February 12th, 2004, 01:32 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks nikolai,
it works fine, can you tell me where in this php code I need to add ECHO to print the categorey TOTAL and sub-categorey TOTAL ,please!
TOTAL is records number.

Reply With Quote
  #4 (permalink)  
Old February 12th, 2004, 06:10 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Shampoo (2)
-----------PertPlus
-----------SunSlik

Soup (1)
-----------Lux

ToothPaste (3)
-----------Creast
-----------Signal2
-----------Collgate

Catgeories total is (3)

Reply With Quote
  #5 (permalink)  
Old February 12th, 2004, 06:39 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One method would be to store the entire result set into a nested array in one loop, then output the array in another loop:

$sql = "SELECT category, subcategory
          FROM products
         ORDER BY category, subcategory";

$result = mysql_query($sql);

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

foreach ($products as $category => $subcategories)
{
    $num_subcategories = count($subcategories);
    echo "{$category} ({$num_subcategories})\n";

    foreach ($subcategories as $subcategory)
    {
        echo "-----------{$subcategory}\n";
    }
}


This is a little bit more confusing, I know. In particular, I think the line that creates the arrays deserves attention:

  $products[$row['category']][] = $row['subcategory'];

This is a simple assignment statement -- the right hand side is being assigned to the left hand side:

  <some variable> = <some value>


You can tell immediately that the variable being assigned to on left hand side is an array index. What might be confusing is all the square brackets floating around.

The two empty square brackets ("[]") is a special notation in PHP that appends a new numerical index to the end of an array. For example, the following code:

$foo[] = "Hello";
$foo[] = "World";

Creates an array with two indexes. It's equivalent to this:

$foo = array("Hello", "World");

but spares us from having to call the array() function. This is useful when we aren't CREATING an array, but just tacking things onto the end of an existing one.

So now we understand this much:

  <some array>[] = <some value>;


The right hand side is easy -- it's just the value stored in the "subcategory" index of the result row variable.

So far, what we're saying is "Append the subcategory in the current result row to the end of some array":

  <some array>[] = the subcategory;


Now let's look at the rest of the left hand side, arguably the most complex and confusing part of the line:

  $products[$row['category']]

We see that there are two sets of square brackets. We know that you can access an array using square bracket notation:

  $array_variable[<some index>]

Knowing this, you can see that we're accessing an array called "products":

  $products[<some index>]

The index we're using is the VALUE stored in another array -- more specifically, it's the value stored in the "category" index of the result $row variable.

Let me restate this -- we're not setting the index to "category", we're setting it to the VALUE stored in another array's "category" index.

To use your example, the values stored in the "category" rows are "Shampoo", "Soup", and "ToothPaste".

Suppose we're looking at the "PertPlus" row. The "category" is "Shampoo" and the "Subcategory" is "PertPlus".

Going back to our code:
  $products[$row['category']][] = $row['subcategory'];

We can immediately substitute the values we know:

  $products["Shampoo"][] = "PertPlus";

This looks a lot easier to understand, right?

The last part we need to discuss is the concept of a "nested array". A nested array is an array where the value stored an an index is another array. This is also known as a "multidimensional" array.

So here, we're saying that $products["Shampoo"] is itself another array.

I _strongly_ suggest using the print_r() function to view the contents of arrays. It will be of an incredible aid to understanding the concept of multidimensional arrays and help incredibly when debugging applications that use them.

For example, this is what the print_r() output would look like for our $products array after the first while() loop:

Code:
Array
(
   [Shampoo] =>  Array
      (
         [0]  =>  "PertPlus"
         [1]  =>  "SunSilk"
      )
   [Soup]
      (
         [0]  =>  "Lux"
      )
   [ToothPaste]
      (
         [0]  =>  "Creast"
         [1]  =>  "Signal2"
         [2]  =>  "Collgate"
      )
)

This should help you understand the multidimensional $products array.

In the output loop, we use the foreach() construct. foreach() iterates over each element in the array, and extracts the index and value from the array into variables that you name. In our code, we had this:

  foreach ($products as $category => $subcategories)

Here, $category holds the index, and $subcategories is the value.

If you look at the print_r() output I typed above, you can determine that the first iteration of the foreach() loop will set $category to "Shampoo", and $subcategories to an Array.

We use the count() function to get the number of elements in the $subcategories array. This lets us print it out next to the category name.

Then, we use foreach() again to iterate over all the items in the subcategory array:

  foreach ($subcategories as $subcategory)

Notice that this foreach() looks a little different than the first one... There's only one variable after the "as". This is because we don't want (or care about) the indexes in this array. These indexes, if you recall, were created by the empty brackets ("[]") in the first loop. All we care about are the values in that array. We print each value ot the screen.

After we're done printing all the values, we print a newline character to separate this category from the next.



Does this all make sense?


Again, there are several ways of doing this. Another way would've involved making multiple queries, but I think that this way is sufficient and pretty easy to understand.

It does have its drawbacks, though -- notice that we store the entire result set into the $products array before printing it out. If you have a very large database table, this approach might take up too much memory because the $products array might get large, especially when you consider that you're creating it for every user that's running the script at the same time.

The other approach would be to make one query to return just the names of the categories and the number of rows belonging to each category, and another query to return all the sub categories.

This approach has the benefit that you don't have to store any large amounts of data in a PHP array, but has the drawback that you need to make multiple queries of the database.

HTH!


Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #6 (permalink)  
Old February 13th, 2004, 01:37 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank Nik,this is realy helpful,
your explaination clarified many things for me,
with this code I can get TOTAL for each subs in category,
I want to know how to get the main cagtories TOTAL, there are three, SHAMPOO, SOUP, TOOTHPASTE
the total is (3)
plz

best reagrds

Reply With Quote
  #7 (permalink)  
Old February 13th, 2004, 04:35 AM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

count($products);

Look at the output of print_r() -- $products is the array that holds categories. There are three indexes in the $products array, each represents a category. In my last message, I showed you why count($products['Shampoo']) returned 2 -- because there are two indexes in that array.

For more info, I strongly suggest you read the manual entry on arrays:
  http://www.php.net/types.array


Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #8 (permalink)  
Old February 13th, 2004, 04:56 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

YES nik this is exactly what I want,
as you told me I added this line, after the loop
Code:
echo "the categories total is: ".count($products);
and now I get the subs total and the categories total too,
I'll check the tutorial link,
thanks

Reply With Quote
  #9 (permalink)  
Old February 13th, 2004, 02:47 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No problem! Again, once you become more familiar with PHP and SQL, I recommend revisiting this problem and try to discover a method that doesn't require storing the entire result set in an array. This method becomes useful if you're selecting large amounts of data from your database.

For smaller result sets, though, I think that this method is perfectly fine. It also allows you to separate your database calls from your output. For example, you can write a function to get the data from the database:

function get_products()
{
   $products = false;

   $sql = "SELECT category, subcategory
             FROM products
            ORDER BY category, subcategory";

   $result = mysql_query($sql);

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

   return $products;
}


If you defined this function in a file containing just DB access functions, then the rest of your script wouldn't know (or care) where the data came from (database, text file, XML, etc...) or what the table structure was like.

The function defines the format in which the data will be returned (the multidimensional array), so as long as the rest of the program uses that array, you can change how & where you store the data if you need to, and the program will work fine as long as you rewrite the function to build the same multidimensional array.


The rest of your program would simply call that function:


$products = get_products();

foreach ($products as $category => $subcategories)
{
    $num_subcategories = count($subcategories);
    echo "{$category} ({$num_subcategories})\n";

    foreach ($subcategories as $subcategory)
    {
        echo "-----------{$subcategory}\n";
    }
}


HTH.

Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #10 (permalink)  
Old February 14th, 2004, 12:20 AM
Authorized User
 
Join Date: Feb 2004
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again nik,
I checked your CV and I found out that you know many programming languages, by the way can we consider FUNCTION in(php) as METHOD in(c++,c#), I asked this question because I try to learn c# (application), and PHP (server side scripting),
and Im wondering if we can add to this function a code that shows the ORDER of subs like this:

Shampoo (2)
1----------PertPlus
2----------SunSlik

Soup (1)
1----------Lux

ToothPaste (3)
1----------Creast
2----------Signal2
3----------Collgate

Catgeories total is (3)


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multilevel subtotal of columns yogeshyl SQL Language 1 July 15th, 2008 02:12 PM
Multilevel Grouping with XSLT 2.0 rzegwaard XSLT 2 January 9th, 2008 04:13 PM
Multilevel unlimited subcategory ? atul PHP Databases 0 September 22nd, 2007 12:37 AM
begin php & mysql - chapter 12, user_form.php jon_stubber Beginning PHP 1 March 9th, 2006 10:57 AM
Error: movie.php & commit.php on p182-186, ch6 willburke BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 October 12th, 2004 02:48 PM



All times are GMT -4. The time now is 05:21 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.