A counting query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

A counting query

Post by dyluck »

for me this is a doozy I really need help on this one...
Im trying to create a query that does the following

I want to count how many rows contain one of the uid in any one of the "columns" shown below. Of course don't count the rows that don't even contain a uid. There will only be 1 uid an any 1 of the columns per row, there will never be more.

activated alevel column1 column2 column3 column4 column5 column6 column7 column8 column9 column10

I want the query to look something like this:
Count rows WHERE activated = 1 and alevel = 2 and (OR column1 = uid OR column2 = uid OR column3 = uid OR column4 = uid OR column5 = uid OR column6 = uid OR column7 = uid OR column8 = uid OR column9 = uid OR column10 = uid)

I don't know the proper MYSQL syntax at all here! I tried searching but couldn't find anything.

Thanks for your help!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: A counting query

Post by califdon »

dyluck wrote:I want the query to look something like this:
Count rows WHERE activated = 1 and alevel = 2 and (OR column1 = uid OR column2 = uid OR column3 = uid OR column4 = uid OR column5 = uid OR column6 = uid OR column7 = uid OR column8 = uid OR column9 = uid OR column10 = uid)
Dump that first "OR".

The entire query would be:

Code: Select all

SELECT COUNT(*) WHERE activated = 1 and alevel = 2 and (column1 = uid OR column2 = uid OR column3 = uid OR column4 = uid OR column5 = uid OR column6 = uid OR column7 = uid OR column8 = uid OR column9 = uid OR column10 = uid)
But I'm suspicious when I see someone trying to do that kind of a query. It's a strong clue that the database structure is flawed. If the same value can be in only one of a number of fields in the same table, it's not a normalized database. As I have often said in these and other forums, when you try to use relational database calculus with a database whose structure violates the "normal forms" of relational database theory, it guarantees bad results.

If you're absolutely stuck with this structure, then good luck; but if you're in a situation where you can go back and reconsider your database schema, I strongly recommend that you do. There are lots of good online tutorials on relational database theory and normalization.
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: A counting query

Post by dyluck »

Thanks for the help on that one!

Well those 10 columns are only updated in the database 1 time and that is it (during registration).
Each column would contain a different uid in it or a null value.
Instead of putting 1 referal's name in 1 column then querying 10 times for the upline on each referal, I could only see 1 solution.

Ok maybe you have a better idea of a database structure, but this table essencially keeps track of 10 levels of an upline. The query above is telling one individual person, how many referals they have within the 10 levels.

honestly if there is a better way to do this I am fully open to suggestions.

humbly,
dyluck
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: A counting query

Post by Benjamin »

I would use a integer with bitwise operators (mysql or php) to test which settings are active.

http://www.php.net/manual/en/language.o ... itwise.php

Then you would reduce the number of required fields to 1 (for those attributes) and benefit from less storage requirements, less code to write, increased performance and increased reliability.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: A counting query

Post by califdon »

dyluck wrote:Thanks for the help on that one!

Well those 10 columns are only updated in the database 1 time and that is it (during registration).
Each column would contain a different uid in it or a null value.
Instead of putting 1 referal's name in 1 column then querying 10 times for the upline on each referal, I could only see 1 solution.

Ok maybe you have a better idea of a database structure, but this table essencially keeps track of 10 levels of an upline. The query above is telling one individual person, how many referals they have within the 10 levels.

honestly if there is a better way to do this I am fully open to suggestions.

humbly,
dyluck
I very likely don't fully understand your needs, but as a long-time student and teacher of relational database theory, I always challenge a sparse array, which is what that essentially is. It's not that it's only updated once that matters, it's how the relational calculus that E. F. Codd developed almost 40 years ago and which is the basis of SQL and every database engine that I am aware of actually works; it's that queries are guaranteed to work with a normalized database. Queries sometimes work with unnormalized databases, but not necessarily under all circumstances. It's a lot better to design a system that you can be assured will work next year, when you might find that you need to make some changes.

I would guess that 75% of programmers who work with databases have never studied relational database theory, and my frequent commentaries sound like cranky preaching. But in addition to being a sort of purist when it comes to database design, I think I'm also practical, having developed hundreds of databases, offline and online. I preach that you must always start with a properly developed data model that clearly defines the entities and relationships on which your database will be based. Since I don't pretend to understand the requirements of hierarchical marketing structures and relationships, I'm not in a position to advise you on what will or won't work there. It may or may not even be the ideal application for a relational database. But I do believe strongly in the value of designing normalized tables. In case you're not already familiar with the subject, I'd recommend some online tutorials:
http://www.kirupa.com/developer/php/rel ... design.htm
http://www.devshed.com/c/a/MySQL/An-Int ... alization/
http://dev.mysql.com/tech-resources/art ... ation.html
http://databases.about.com/od/specificp ... zation.htm
http://support.microsoft.com/kb/283878
http://www.agiledata.org/essays/dataNormalization.html
http://parallel.vub.ac.be/documentation/database/tutor/
User avatar
dyluck
Forum Commoner
Posts: 54
Joined: Thu Jun 26, 2008 1:44 pm

Re: A counting query

Post by dyluck »

Well the fact you are a teacher califdon makes me feel small hehehe :P
Honored to have you help me though!

Ok I'm going to take a look into this. I suppose I better make sure I have my ducks in a line before i go live on this particular one.

Just when I thought I had it all figured out! You should have seen how many coffees I had when I stumbled across our nasty foe called SQL injection.. Still having nightmares about that! The wife wonders why this stuff takes so long... they don't even realize!

Ok well thanks for your help, I will be studdying those links you gave me!

astions: I didn't get the relationship with bitwise operators. The link you gave me doesn't really explain what their purpose is in semi-laymens terms. :?
I find that when I go to that site for stuff, It is massivly confusing. (i know it's the php site, but 90% of the stuff I find there is brutally advanced). I find myself taking a little of what I understand from it and googleing it, but im not coming up with much for this function.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: A counting query

Post by califdon »

dyluck wrote:Well the fact you are a teacher califdon makes me feel small hehehe :P
Honored to have you help me though!
Nah! Besides the fact that I retired several years ago, I'm no better than anyone else. But like they say, experience is what you have left when everything else is gone. I still make mistakes...only my mistakes are based on superior experience! :D
astions: I didn't get the relationship with bitwise operators. The link you gave me doesn't really explain what their purpose is in semi-laymens terms. :?
I think what astions was suggesting was that you might have one field for the uid and another one that would indicate which of the several "columns" it applied to. That might work, and it would also work if you needed to be able to have the uid apply to more than one "column" (which you said wouldn't be the case). Bitwise operations just means that each bit in, say, an 8-bit byte can represent a different option. 0100 0000 could mean the second column, while 1001 0010 could mean the first, fourth and seventh options. Bitwise operators allow you to set or read individual bits within a byte. You could also achieve the same result simply by have one field for the uid and another field that holds the number of the "column" for that record, since you say that you only allow one "column" to be used for any one record. That would be much more efficient than having all those fields. But it's impossible to say unless you understand what the requirements are for the application, which I don't. So much depends on what else depends on these values, how likely it is that requirements to change or expand the application might be needed in the future, etc.
Post Reply