next »

[jsp123]

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).

Code:

track
id int(11)
site_id smint(6)
query_id medint(9)
campaign_id tinyint(4)
hit_type tinyint(4)
0::raw
1::uniques
2::raw_out
3::unique_out
hit_cat tinyint(4)
0::main
1::gateway
2::site
3::category
4::webcam
user_id int(11)
date datetime

query
id medint(9)
query varchar(120)
code varchar(6)
campaign_id tinyint(4)
type_id  smallint(6)
0::non cat
1::category
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 learned I hope  )

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 

Thanks!

jsp

[perkiset]

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 

[nutballs]

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.

[jsp123]

You are right perk no join is needed  ,  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. 

Thanks Again

jsp

[perkiset]

No worries - look forward to the updated qq.

/p