Re: Some nestled calculation
am 12.04.2008 21:04:16 von Evert LammertsYou'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-da ta.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
>
>
> 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
>
>
> On Mon, Apr 7, 2008 at 2:42 PM, Dee Ayy
>
>
> 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
>
> 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
> 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