count children nodes

--00163649a1f573377a047fba3513
Content-Type: text/plain; charset=ISO-8859-1

Hello,

I've the next table structure:

CREATE TABLE tx_cc20_mensajes (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
deleted tinyint(4) DEFAULT '0' NOT NULL,
hidden tinyint(4) DEFAULT '0' NOT NULL,
remitente int(11) DEFAULT '0' NOT NULL,
destinatario int(11) DEFAULT '0' NOT NULL,
padre int(11) DEFAULT '0' NOT NULL,
mensaje text,
leido tinyint(3) DEFAULT '0' NOT NULL,

PRIMARY KEY (uid),
KEY parent (pid)
);

Where padre is the id of the parent message. I need count the messages don't
read in a thread. How can I do it?

With
$query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
(padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
I am counting only the first level, but I need count the rest of children
messages. What is the query?

Thanks!

--00163649a1f573377a047fba3513--

RE: count children nodes

Trees can be complex in SQL; these articles will give some different ideas =
to handle it:
http://hashmysql.org/index.php?title=3DTrees_and_hierarchica l_data_in_SQL
http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.htm

Regards,
Gavin Towey

-----Original Message-----
From: David Arroyo Menendez [mailto:david.arroyo@bbvaglobalnet.com]
Sent: Tuesday, February 16, 2010 8:27 AM
To: mysql@lists.mysql.com
Subject: count children nodes

Hello,

I've the next table structure:

CREATE TABLE tx_cc20_mensajes (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
deleted tinyint(4) DEFAULT '0' NOT NULL,
hidden tinyint(4) DEFAULT '0' NOT NULL,
remitente int(11) DEFAULT '0' NOT NULL,
destinatario int(11) DEFAULT '0' NOT NULL,
padre int(11) DEFAULT '0' NOT NULL,
mensaje text,
leido tinyint(3) DEFAULT '0' NOT NULL,

PRIMARY KEY (uid),
KEY parent (pid)
);

Where padre is the id of the parent message. I need count the messages don'=
t
read in a thread. How can I do it?

With
$query=3D"select count(*) as num from tx_cc20_mensajes msj where hidden=3D0=
and
deleted=3D0 and leido=3D0 and destinatario=3D".$uid." and remitente<>".$uid=
.." and
(padre=3D".$est_row['uid']." or uid=3D".$est_row['uid'].")";
I am counting only the first level, but I need count the rest of children
messages. What is the query?

Thanks!

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: count children nodes

--------------010800070909040002090105
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

David,

>I need count the messages don'tread in a thread.

Have a look at the edge list examples at
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20 .html.

PB

-----

David Arroyo Menendez wrote:
> Hello,
>
> I've the next table structure:
>
> CREATE TABLE tx_cc20_mensajes (
> uid int(11) NOT NULL auto_increment,
> pid int(11) DEFAULT '0' NOT NULL,
> tstamp int(11) DEFAULT '0' NOT NULL,
> crdate int(11) DEFAULT '0' NOT NULL,
> cruser_id int(11) DEFAULT '0' NOT NULL,
> deleted tinyint(4) DEFAULT '0' NOT NULL,
> hidden tinyint(4) DEFAULT '0' NOT NULL,
> remitente int(11) DEFAULT '0' NOT NULL,
> destinatario int(11) DEFAULT '0' NOT NULL,
> padre int(11) DEFAULT '0' NOT NULL,
> mensaje text,
> leido tinyint(3) DEFAULT '0' NOT NULL,
>
> PRIMARY KEY (uid),
> KEY parent (pid)
> );
>
> Where padre is the id of the parent message. I need count the messages don't
> read in a thread. How can I do it?
>
> With
> $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
> deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
> (padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
> I am counting only the first level, but I need count the rest of children
> messages. What is the query?
>
> Thanks!
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.435 / Virus Database: 271.1.1/2691 - Release Date: 02/16/10 07:35:00
>
>

--------------010800070909040002090105--

Re: count children nodes

--0016363b9a5a7d2cf6047fc7e993
Content-Type: text/plain; charset=ISO-8859-1

Thanks!

2010/2/16 Peter Brawley

> David,
>
> >I need count the messages don'tread in a thread.
>
> Have a look at the edge list examples at
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20 .html.
>
> PB
>
> -----
>
> David Arroyo Menendez wrote:
>
> Hello,
>
> I've the next table structure:
>
> CREATE TABLE tx_cc20_mensajes (
> uid int(11) NOT NULL auto_increment,
> pid int(11) DEFAULT '0' NOT NULL,
> tstamp int(11) DEFAULT '0' NOT NULL,
> crdate int(11) DEFAULT '0' NOT NULL,
> cruser_id int(11) DEFAULT '0' NOT NULL,
> deleted tinyint(4) DEFAULT '0' NOT NULL,
> hidden tinyint(4) DEFAULT '0' NOT NULL,
> remitente int(11) DEFAULT '0' NOT NULL,
> destinatario int(11) DEFAULT '0' NOT NULL,
> padre int(11) DEFAULT '0' NOT NULL,
> mensaje text,
> leido tinyint(3) DEFAULT '0' NOT NULL,
>
> PRIMARY KEY (uid),
> KEY parent (pid)
> );
>
> Where padre is the id of the parent message. I need count the messages don't
> read in a thread. How can I do it?
>
> With
> $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
> deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
> (padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
> I am counting only the first level, but I need count the rest of children
> messages. What is the query?
>
> Thanks!
>
>
>
> ------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.435 / Virus Database: 271.1.1/2691 - Release Date: 02/16/10 07:35:00
>
>
>
>

--0016363b9a5a7d2cf6047fc7e993--