The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 17, 2019, 06:03:36 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Quick mysql join question...  (Read 4946 times)
jsp123
Rookie
**
Offline Offline

Posts: 20


View Profile
« on: May 09, 2007, 08:10:53 PM »

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

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  ROFLMAO

Thanks!

jsp
Logged

No links in signatures please
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: May 09, 2007, 08:45:01 PM »

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  Embarrassed
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #2 on: May 09, 2007, 11:59:36 PM »

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

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jsp123
Rookie
**
Offline Offline

Posts: 20


View Profile
« Reply #3 on: May 10, 2007, 10:45:47 AM »

You are right perk no join is needed  D'oh!,  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.  Smiley

Thanks Again

jsp
Logged

No links in signatures please
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: May 10, 2007, 03:30:39 PM »

No worries - look forward to the updated qq.

/p
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
qweqwe
Rookie
**
Offline Offline

Posts: 14


View Profile
« Reply #5 on: January 12, 2010, 07:25:14 PM »

<spammer dick go boom>
« Last Edit: January 12, 2010, 08:32:59 PM by nutballs » Logged

No links in signatures please
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!