I am going to make some graphs/stats with visitors stats I have collected.  The part of the database that I am grabbing data from is from two tables (more eventually after I get the hang of joins).

id int(11)
site_id smint(6)
query_id medint(9)
campaign_id tinyint(4)
hit_type tinyint(4)
hit_cat tinyint(4)
user_id int(11)
date datetime

id medint(9)
query varchar(120)
code varchar(6)
campaign_id tinyint(4)
type_id  smallint(6)
0::non cat
domain_id smallint(6)
ref_id medint(9)


This is part of my new db layout (I rushed into the script last time without much forethought and ended up not collecting enough data to join tables together, a few days later, lesson


 ed I hope  Applause)

Ok so what I want to be able to do is join them together by query_id where track.date > '$yesterday' which I can do already.  The part that I am a bit confused on is that I want to GROUP BY track.hit_cat and then get the count for each hit_type for each of those.  This way I can see what visitors do at the site when they come in from different search terms (grabbed from the referrer).  I think I should be able to do this in one select with a join for each hit_type (eg WHERE hit_type = 0).  Anyways should I just make four queries and put the results from each query in a multidimensional array and be done with it, or is it doable with joins. 

I hope someone can understand what I am saying, if not let me know and I will wait till the drugs wear off  Applause




Unless I'm being stupid I can't figure out exactly what you're trying to do either...

given what you posted, I can't see the reason for a join in the first case, so I'm wondering if I even understand what you're trying to get.

call me stupid, but if you could post a little hand-created example of what you'd like to see then maybe I'll understand better. Groups and counts are a little funkier than normal field selections, so showing how you want data sorted and aggregated would help.

Sorry  Applause


agreed. im not quite sure i get the question.

but I can say that it is rare that you cant get everything you want i 1 query. though it may be made of a bunch of sub-queries, its still 1 big mega query. so what you want to do, can be done most likely.


You are right perk no join is needed  Applause,  I will repost later when I get to the point where I want to join these local stats with the ones I scraped from the affiliate control panel.  I realize this is only going to be run once in a while so maybe I dont have to worry about efficiency, just going to focus on getting it up and working.  Applause

Thanks Again



No worries - look forward to the updated qq.


Perkiset's Place Home   Politics @ Perkiset's