----- Original Message ----- From: "Michael J. Pawlowsky" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> Sent: Wednesday, April 06, 2005 7:51 PM Subject: Re: I need some help
> Rhino wrote: > > >----- Original Message ----- > >From: "iNFERNo" <[EMAIL PROTECTED]> > >To: "mysql" <mysql@lists.mysql.com> > >Sent: Wednesday, April 06, 2005 5:23 PM > >Subject: I need some help > > > > > > > > > >>Hi, > >> > >> I have a problem: I am using mysql 4.0.24 and I need to make > >>some reports from a database: > >> > >>mysql> describe events; > >> > >>+-------------+------------------+------+-----+---------+----------------+ > >> > >>| Field | Type | Null | Key | Default | Extra | > >> > >>+-------------+------------------+------+-----+---------+----------------+ > >> > >>| id | int(10) unsigned | | PRI | NULL | auto_increment | > >> > >>| user_id | varchar(55) | | | 0 | | > >> > >>| dep_id | int(15) | | | 0 | | > >> > >>| event_id | int(15) | | | 0 | | > >> > >>| year | int(15) | | | 0 | | > >> > >>| day | int(15) | | | 0 | | > >> > >>| month | int(15) | | | 0 | | > >> > >>| ev_status | int(11) | YES | | 0 | | > >> > >>| ev_type | int(11) | YES | | 0 | | > >> > >>| ev_priority | int(11) | YES | | 0 | | > >> > >>+-------------+------------------+------+-----+---------+----------------+ > >> > >>I need something to get: > >> > >>| USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | > >>EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | > >> > >>user_1 1 COUNT COUNT > >>COUNT COUNT COUNT > >>COUNT COUNT > >> > >>user_2 1 COUNT COUNT > >>COUNT COUNT COUNT > >>COUNT COUNT > >> > >>user_3 2 COUNT COUNT > >> COUNT COUNT > >>COUNT COUNT COUNT > >> > >>. > >>. > >>. > >> > >> > >> The problem is that the way I am getting this now is with a query > >>for each user and for each ev_status and the output is in PHP. Is there > >>a better way to do this ? I am only a beginner in MySQL and want to > >>learn more and improve. > >> > >> > >> > >First of all, congratulations on identifying your version of MySQL and > >giving the definition of your table. That is an excellent start to getting > >an answer to your question. Far too many people post here without > >identifying their MySQL version or giving the definition of their tables, > >making it very difficult to answer their questions without having to ask > >many followup questions. > > > > > > > > Am I missing something... but isn't id defined as the primary key? > You are absolutely right; I'm sorry, I missed the primary key designation. (I was expecting the primary key definition after the last column definition which, now that I think about it, was just plain wrong!) > On a different topic.. if you want to make your life easy with PHP. > Instead of saving, the year, month, day... personally I always simply > use an int and save all dates as Unix Timestamps. As long as you are > working more or less in this century, you will be fine. > That is a personal choice, from someone that has built MANY calendars. > > As for selecting all of them like that. I question why.. but.... > > SELECT user_id, dep_id, IF(ev_status=1,1,0), IF(ev_status=2,10) ... > and so on. > > But I really don't get why you would be doing that > I'm afraid I'm a bit confused too: if there really is just one row per ID, what would that row look like? There would have to be a single value for ev_status so wouldn't you just report that value? What is there to count? A small bit of sample data would make it easier to visualize what you want and why you want it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]