# [PHP-DB] Re: Some nestled calculation

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

```
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 <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> 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 <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
```
```            On Mon, Apr 7, 2008 at 2:42 PM, Dee Ayy <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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 <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
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

```