Re: bit_count(bit_or(1<<table1.id)) (Re: [PHP-DB] Howto make a double LEFT JOIN)

Yeah, I didn't think about the fact that left shifting a 1 65 times would move it beyond max_size and zero the number. Sorry. I'm working on an alternative. Anyone have any ideas? Again, the issue is... There are two tables which capture user actions. We want a select that will list the ID's from table1 (users who have performed the first action), a count by ID from table1, and a count by ID2 from table2. Any join which is performed seems to makes the count inacurate... That is if user 1 has performed action1 2 times and action2 2 times a join will return 1 | 4 | 4 instead of 1 | 2 | 2. Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com ----- Original Message ----- From: "Bas Jobsen" <bas@startpunt.cc> To: ""Sheridan Saint-Michel"" <webmaster@foxjet.com>; <php-db@lists.php.net> Sent: Sunday, October 07, 2001 7:34 AM Subject: [PHP-DB] bit_count(bit_or(1<<table1.id)) (Re: [PHP-DB] Howto make a double LEFT JOIN) > Hello, > > > >> select table1.id,bit_count(bit_or(1<<table1.sid)) as > > >> count1,bit_count(bit_or(1<<table3.sid)) as count2,table2.url as url > from > > >> table1 left join table3 using(id) left join table2 using(id) group by > > >> table1.id; > > > What is the best way to do if id become a sting (a-z-chars)? > Oke, this had nothing to do with the problem. > > bit_count(bit_or(1<<test.id)) > gives me some problems as soon as id>64 > > my table definition: > > CREATE TABLE test2 ( > id int(20) unsigned zerofill NOT NULL auto_increment, > ip varchar(15) binary NOT NULL, > userid varchar(15) binary NOT NULL, > hits tinyint(1) DEFAULT '1' NOT NULL, > time int(10) DEFAULT '0' NOT NULL, > PRIMARY KEY (id), > UNIQUE ip (ip), > UNIQUE id (id) > ); > > THE DATA: > 1|1|bas|1|1| > 2|2|bas|1|2| > 3|3|bas|1|3| > .. > .. > 256|256|bas|1|256| > > now my query: > select userid, bit_count(bit_or(1<<test2.id)) as hits FROM test2 GROUP BY > userid ORDER BY hits DESC > > the result: > bas|63| > > the result have to be: > bas|256| > > what going wrong? thanks, > > Bas > > > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net