1 more left join problem :(

[sabret00the]

Originally Posted by miz

THANK YOU VERY MUCH
you guys have been replly helpfull to me

now please 1 more qustion

[SQL]$teamslist = $DB_site->query("
SELECT teams.*,user.username AS username, user.userid AS userid ,user.posts AS userposts, user.reputation AS userrep
FROM " . TABLE_PREFIX . "teams AS teams
LEFT JOIN user ON(teams.teamid = user.teamid)
WHERE teams.teamid > 1
GROUP BY teams.teamid
");[/SQL]

now i want it to take all userposts and count them togeder
is that possible ?

do a

PHP Code:

SELECT COUNT * FROM user WHERE teamid = $teamid 


[Dean C]

You need to put parentheses around the asterix sabe :

[sql]
SELECT
COUNT(*) as total
FROM
user
[/sql]

[Dean C]

Btw, the reason you were returning more than one result is because of the session timeout. You shouldn't need to group by at all if you add this clause to your query:

AND session.lastactivity > " . (TIMENOW - $vboptions['cookietimeout']) . "

[miz]

Originally Posted by Dean C

You'll need to do the count in a seperate query AFAIK

Btw can you show the query you used in the end to get the sessions working ?

this is the query

PHP Code:

$teamusers =  $DB_site->query("SELECT user.*  AS user
       FROM " . TABLE_PREFIX . "user 
       LEFT JOIN " . TABLE_PREFIX . "session
        ON(session.userid = user.userid)
       WHERE user.teamid='$teamid' 
      GROUP BY user.userid ORDER BY session.lastactivity DESC
       "); 


its also helped me in other query
i run about 100 tests lol and i surely can say that i understand
left joins better
now i want to run count inside the left join
and i need to update counters for each team :\
sucks

Originally Posted by Dean C

You need to put parentheses around the asterix sabe :

[sql]
SELECT
COUNT(*) as total
FROM
user
[/sql]

but i dont want to count rows

i want to do like

user.posts =5 (userid=1)
user.posts=6 (userid=5)

total teams posts = 6
can i do query for that?

never mind i fixed it
this is the query

[SQL]
$teamslist = $DB_site->query("
SELECT teams.*,user.username AS username, user.userid AS userid ,user.posts AS userposts, user.reputation AS userrep,COUNT(user.posts) AS totalteamposts
FROM " . TABLE_PREFIX . "teams AS teams
LEFT JOIN user ON(teams.teamid = user.teamid)
WHERE teams.teamid > 1
GROUP BY teams.teamid
");
[/SQL]

[sabret00the]

Originally Posted by miz

user.posts =5 (userid=1)
user.posts=6 (userid=5)

shouldn't total team posts = 11?

[miz]

heh my mistake - to tierd you right shuold be 11
how am i doing that ?
do i need an exstra query for update ?
cuse now i will need to run 1 exstra query for each team...

[sabret00the]

Originally Posted by miz

heh my mistake - to tierd you right shuold be 11
how am i doing that ?
do i need an exstra query for update ?
cuse now i will need to run 1 exstra query for each team...

no you need to do the count query that dean just gave you.

or if you've already got the posts, just do a mysql_num_rows() on the query, either will give you the info you desire.

[miz]

you got me worng

i did this query

PHP Code:

$teamslist = $DB_site->query("
     SELECT teams.*,user.username AS username,
      user.userid AS userid ,user.posts AS userposts,
      user.reputation AS userrep,
     COUNT(user.posts) AS totalteamposts,
     COUNT(user.reputation) AS totalteamrep
     FROM " . TABLE_PREFIX . "teams AS teams
      LEFT JOIN user  ON(teams.teamid = user.teamid)
     WHERE teams.teamid > 1
     GROUP BY teams.teamid
     
 ");
 
 
 while ($teams = $DB_site->fetch_array($teamslist)) 
 {
     
     
     eval('$teamsbit .= "' . fetch_template('teams_teamslistbit') . '";');
 
 
 } 


now in table teams i got teamposts
so i want to update teams.teamposts
now the value of total team posts is in
$teams['totalteamposts']
now my qustion is
if there a way to update teams.teamposts=$teams['totalteamposts']
without exstra query
or if i must use exstra query
do i need to run the query for each team (in the while loop)
so code should look like that

PHP Code:

$teamslist = $DB_site->query("
      SELECT teams.*,user.username AS username,
       user.userid AS userid ,user.posts AS userposts,
       user.reputation AS userrep,
      COUNT(user.posts) AS totalteamposts,
      COUNT(user.reputation) AS totalteamrep
      FROM " . TABLE_PREFIX . "teams AS teams
       LEFT JOIN user  ON(teams.teamid = user.teamid)
      WHERE teams.teamid > 1
      GROUP BY teams.teamid
      
  ");
  
  
  while ($teams = $DB_site->fetch_array($teamslist)) 
  {
      
      $DB_site->query_first("UPDATE teams SET teamposts=$teams[totalteamposts] WHERE teamid=$teamid ");
      eval('$teamsbit .= "' . fetch_template('teams_teamslistbit') . '";');
  
  
  } 


if i do it this way so i add 1 exstra query for each team
if i got 100 teams i add 100 query's
something i dont want to do
so im looking for other way...
any ideas ?

[sabret00the]

you're best bet would be to psuedo cache the data

in the while have

$count++
$row[$count] = $totalteamposts

then outside of the while add up the data from $row[0] -> $row[10] and beneath that to the update

saying that, not being sure if you can do that, it might now work.

actually ignore that, i know it's simpler than that, i'll have to come back to this, my minds gone blank

ahhh that's it try

$totalteamposts .= $teamposts[posts] . " + ";
then just remove the extra " + " at the end and throw it into a ceil()

[miz]

Originally Posted by sabret00the

you're best bet would be to psuedo cache the data

in the while have

$count++
$row[$count] = $totalteamposts

then outside of the while add up the data from $row[0] -> $row[10] and beneath that to the update

saying that, not being sure if you can do that, it might now work.

actually ignore that, i know it's simpler than that, i'll have to come back to this, my minds gone blank

heh
well dosent help me much
my problem isnt about counting cuse im doing it on the first query
my problem is to update
do i need to run exstra query for every team ?
there must be an other way... to update all teams in 1 query.