Re: Some nestled calculation

Evert Lammerts <evert.lammerts@xxxxxxxxx> · Sat, 12 Apr 2008 21:04:16 +0200

You're talking about storing a tree (which is a special type of graph) 
in a relational database - in your case MySQL. There are a number of 
ways to do this, but if you're a beginning at SQL and / or PHP you might 
get confused easily - but in that case you shouldn't try doing this 
yourself and hire somebody to do it for you.

I'll give you some common implementations.

First of all, if your tree is complete (the tree is symmetric), and 
every node has three children, then a tree of depth 3 has 3^0 + 3^1 + 
3^2 = 1 + 3 + 9 = 13 nodes. Resulting, for a complete tree, the amount 
of children below a given node is SUM( 3^1 + ... + 3^depth).

In your case a simple recursive algorithm might prove sufficient. You 
have a table TREE with the structure:
| ID | NAME | PARENT_ID |

Whenever you add a child to a node, you insert the node's id as the 
parent_id of the child. When you want to know how many children a node 
has, use something like this (I'm doing this without testing, but i 
think it should work):

function getNrOfChildren($id) {
   $sql = "SELECT id FROM tree WHERE parent_id={$id}";
   $res = mysql_query($sql);
   $count = 0;
   while ($row = mysql_fetch_assoc($res)) {
       $count += 1 + getNrOfChildren($row['id']);
   }
   return $count;
}

This will perform fine as long as you keep an index on both parent_id 
and id, AND as long as your table does not get too big - you perform a 
query for every child, so if somebody has a couple of hundred children 
your system needs to perform a couple of hundred queries.

A little harder, but definitely useful, is the nested set model (check 
this article: 
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html). 
The basic idea is that you wrap borders around a node, and every child 
node is placed within these borders. The borders are represented by a 
LEFT and a RIGHT value. The first node A you enter in your table has 
A.LEFT = 1, A.RIGHT = 2. If you add a child B to node A, node B will 
have B.LEFT = 2, B.RIGHT = 3, and A.RIGHT will be set to 4. Adding 
another child C to node A means setting C.LEFT=4, C.RIGHT=5, A.RIGHT=6. 
This means that adding a node can mean an update query that affects a 
big part of the table. However, querying the tree is easy and light - 
SELECT * FROM table WHERE lft BETWEEN 1 AND 26 gives you all children of 
the node with LEFT=1 and RIGHT=23, and SELECT ((lft - rgt - 1) / 2) AS 
nr_of_children FROM table WHERE id=1 gives you the number of children.

This is how a table looks where node A has three children, which all 
three have three children too (node A has SUM(3^1 + 3^2) = 12 children).

| ID | NAME | LFT | RGT |
 1       A           1        26
 2       B           2        9
 3       C           3        4
 4       D           5        6
 5       E            7       8
 6       F           10      17
 7       G           11      12
 8       H           13      14
 9       I            15      16
 10     J            18       25
 11     K           19      20
 12     L            21      22
 13     M           23      24

For more info on this check the article above.

I don't know what this club freedom is or how it works, and I don't need 
an explanation or want a discussion, but do remember that pyramid 
schemes are illegal and that even though many of them implement ways to 
operate in a grey area that cannot be legally controlled, they still are 
morally wrong. Please forgive my political correctness.

A. Joseph wrote:
*
Thank you, it works.

Please take a second and help me consider this.
*The question is.

*Some nestled calculation.*
*How did club freedom did the calculation, or how will the database 
structure looks like?*

*The example is -: *

*Joseph* gave birth to *John*, *James,* and *Johnson*
*John* gave birth to* Peter*, *Matter*, and *Potter*
James gave birth to Juliana, Justin, and *Jane*
Johnson gave birth to Jak, Jake and Jacob

*Continuously like that, *
Peter the son of John also gave birth to another 3 children
And the 3 children also keep giving birth to 3 children each,  so
How can I calculate the Total descendants of *Joseph*?
after that...
How can I calculate the total   descendants of *John* or descendants 
of the grand children?

Because each of the children also start having grand children, while 
Joseph grand descendants increases.

I want to use MySql/PHP
The concept is Like http://www.disneytreasures.biz/ or ClubFreedom

All I want to do is to know who bring who?

On Sat, Apr 12, 2008 at 4:41 PM, Evert Lammerts 
<evert.lammerts@xxxxxxxxx <mailto:evert.lammerts@xxxxxxxxx>> wrote:

    Something like this should work.

    $today = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
    $tomorrow = mktime(0, 0, 0, date("m"), date("d") + 1, date("Y"));
    $sql = "SELECT COUNT(*) FROM table WHERE regdate BETWEEN {$today}
    AND {$tomorrow}";

    $thismonth = mktime(0, 0, 0, date("m"), 1, date("Y"));
    $nextmonth = mktime(0, 0, 0, date("m") + 1, 1, date("Y"));
    $sql = "SELECT COUNT(*) FROM table WHERE regdate BETWEEN
    {$thismonth} AND {$nextmonth}";

    $thisyear = mktime(0, 0, 0, 1, 1, date("Y"));
    $nextyear = mktime(0, 0, 0, 1, 1, date("Y") + 1);
    $sql = "SELECT COUNT(*) FROM table WHERE regdate BETWEEN
    {$thisyear} AND {$nextyear}";

    HOWEVER, consider to use the mysql date functions instead of a
    unix timestamp.

    A. Joseph wrote:

        I want to calculate the registed users today
        Also total users this week
        Total users this month
        Total users this year

        The Mysql table has a row of INT(11) with time() value inserted.

        I did something like this
        $today = strtotime("+1 day")
        Then $sql = "SELECT COUNT(*) FROM table WHERE dateReg <= $today";

        Same with year/months also, only I use strtotime("+1 week) for
        a week,
        strtotime("+1 month) for a month,

        Can someone help me with this calculation?

        On 4/7/08, Bruno Lustosa <bruno.lists@xxxxxxxxx
        <mailto:bruno.lists@xxxxxxxxx>> wrote:

            On Mon, Apr 7, 2008 at 2:42 PM, Dee Ayy <dee.ayy@xxxxxxxxx
            <mailto:dee.ayy@xxxxxxxxx>> wrote:

                 I was thinking of using output buffering and then
                making 1 call to
                 utf8_encode, but I think a better question is, how do
                I stop using
                 utf8_encode completely?

            If all components are using utf-8, you should have no
            problems with
            charsets at all. By all components, I mean:
            - Script files in utf-8;
            - Database in utf-8;
            - Database connection using utf-8;
            - Content-type header set to utf-8.
            With all these, you're free of charset hell, and can enjoy
            the beauty
            of utf-8 completely without problems.

                 The rendered view I see in Firefox 2.0.0.12
                <http://2.0.0.12> is a question mark "?"
                 where the French character should have appeared.  If
                I use
                 utf8_encode, the character appears as it should.

            Question mark means the character is not utf-8. Check
            where it comes
            from. Might be the database or the way you are connecting
            to it. I
            don't know much about mysql, I use postgresql. With it,
            you just have
            to call pg_set_client_encoding() to make the connection in
            utf-8 mode,
            and "create database with encoding='unicode'" to set up a
            database
            using utf-8.

                 Luckily I'm on PHP 4.3.10, so I can't see what
                mb_check_encoding would
                 report -- if that would even help normally.

            Shouls upgrade to PHP 5. PHP 4 is way out of date, is not
            getting
            updates anymore, and will not even get security bugfixes
            after august
            8th. It's been almost 4 years since PHP 5 was released.

            http://www.php.net/archive/2007.php

            Check the PHP 4 end of life announcement.

            --
            Bruno Lustosa <bruno@xxxxxxxxxxx <mailto:bruno@xxxxxxxxxxx>>
            ZCE - Zend Certified Engineer - PHP!
            http://www.lustosa.net/

            --
            PHP Database Mailing List (http://www.php.net/)
            To unsubscribe, visit: http://www.php.net/unsub.php

--
I develop dynamic website with PHP & MySql, Let me know about your site 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php