1 more left join problem :(

[miz]

ok i tried to run this query

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


this query should get all users which in $teamid and their online status

now query works fine but
if userid show up few times in sessions
then its show up the user few times
but i want it to show each user only 1 time

what am i doing worng ?

thanx for help

[Andreas]

This is tricky ...

Which information from the session table do you need?
Because if you GROUP BY session.userid, it will just take one session for each user, but not necessarily the active one

[miz]

hmmm
this is what im tring to do

$datecut = TIMENOW - $vboptions['cookietimeout'];
$teamusers = $DB_site->query("SELECT * FROM " . TABLE_PREFIX . "user
LEFT JOIN " . TABLE_PREFIX . "session ON(session.userid = user.userid) WHERE user.teamid='$teamid' ");
while ($members = $DB_site->fetch_array($teamusers))
{
$useronline = fetch_online_status($members);
}


now basiclly this query should give me
list of users which in $teamid and their online status

but its get ++++ed up some where

[Dean C]

Change:

SELECT *

to

SELECT DISTINCT session.userid, user.username

[miz]

Change:

SELECT *

to

SELECT DISTINCT session.userid, user.username ok i tested it
and its show me only 1 time the user
but its also give me worng online status


btw what the word DISTINCT means ?

EDIT :
=====
for online status you should get also session.lastactivity
i tired add it and its again gave me 2 user names

[Dean C]

DISTINCT means you only return unique rows. Ummm try:


SELECT
*
FROM
" . TABLE_PREFIX . "user
LEFT JOIN
" . TABLE_PREFIX . "session ON(session.userid = user.userid)
WHERE
user.teamid='$teamid'
GROUP BY
userid
ORDER BY
session.lastactivity DESC

[/sql]

[Andreas]

As I already said above, GROUP BY will not necessarily catch the last session ...

[sabret00the]

deans would probably work if you select distinct and remove the group by as it's getting the last session to check the timeout it should return the wanted information.

[miz]

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

now please 1 more qustion

$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
");

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

[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 :)?

[sabret00the]

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

now please 1 more qustion

$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
");

now i want it to take all userposts and count them togeder
is that possible ?
do a SELECT COUNT * FROM user WHERE teamid = $teamid

[Dean C]

You need to put parentheses around the asterix sabe :):


SELECT
COUNT(*) as total
FROM
user

[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]

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

$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

You need to put parentheses around the asterix sabe :):


SELECT
COUNT(*) as total
FROM
user
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


$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
");

[sabret00the]

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]

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

$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

$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]

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.

[sabret00the]

i honestly can't think of a way to do it all in one query.

[miz]

EDIT :
COUNT(user.posts) AS totalteamposts,

does the counting part - so no problems for me on counting

problem is to set new value on teams.teamposts where teamid = teams.teamid

i honestly can't think of a way to do it all in one query.
so you say that my only option is to run 100 exstra query's
no-way
there must be a way to figure it out..

[Dean C]

miz I'm finding it difficult to understand what you're trying to accomplish here. Can you just spend a couple of minutes thinking through about what you're asking and try and structure your request in a way we can understand it better. Thanks

[miz]

miz I'm finding it difficult to understand what you're trying to accomplish here. Can you just spend a couple of minutes thinking through about what you're asking and try and structure your request in a way we can understand it better. Thanks
ok ill try to explain
i want my php page will show list of all teams on board
then
take all users on $teamid and count there posts
then update teamposts in table teams

this is the main idea

now this query


$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

");


doing all of what i need except the db update
now my qustion is :

is there any way to combine the UPDATE of teams table with the query above
now the easy way to do update is on the while loop
but its mean the script will make 1 exstra query for each team
so if i got 100 teams on board it will make 100 querys more then should be

in other words, i want to update all teams info in 1 query
how i can do that ?

[Dean C]

You cannot do a select and update in one query. Drop the COUNT(user.posts) bit and then count the posts as you go along within the while loop. Before the while have $totalposts = 0; then in the while loop have $totalposts += $var['userposts'];

Then you do your update query outside the loop :)

[miz]

You cannot do a select and update in one query. Drop the COUNT(user.posts) bit and then count the posts as you go along within the while loop. Before the while have $totalposts = 0; then in the while loop have $totalposts += $var['userposts'];

Then you do your update query outside the loop :)
how ?
cuse every team got other count of posts
and it got replaced in every time the while loop runs

or i got you all worng
can you show me exsample ?

[Dean C]

Oh I see what you mean now, I thought you had a clause on the query to show that info for one team only. Um in that case a better idea would be to run a vB3 cron every 24hours to update the total posts for the team :)

[miz]

Oh I see what you mean now, I thought you had a clause on the query to show that info for one team only. Um in that case a better idea would be to run a vB3 cron every 24hours to update the total posts for the team :)
Well i thought about nicer idea
this is what i made :


$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


");

$teamscount = 0;
while ($teams = $DB_site->fetch_array($teamslist))
{
$teamscount++;

$query .= "UPDATE teams SET posts=$teams[totalteamposts] WHERE teamid=$teams[teamid] \n";


eval('$teamsbit .= "' . fetch_template('teams_teamslistbit') . '";');


}
$DB_site->query($query);


Well this query should work (i dont like the idea of getting updated every 24 hours)

but im getting this error

Invalid SQL: UPDATE teams SET posts=5 WHERE teamid=2
UPDATE teams SET posts=1 WHERE teamid=3
UPDATE teams SET posts=0 WHERE teamid=4
UPDATE teams SET posts=0 WHERE teamid=5
UPDATE teams SET posts=0 WHERE teamid=6

mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE teams SET posts=1 WHERE teamid=3
UPDATE teams SET posts


when i run this query via phpmyadmin :
UPDATE teams SET posts=5 WHERE teamid=2;UPDATE teams SET posts=1 WHERE teamid=3;UPDATE teams SET posts=0 WHERE teamid=4;UPDATE teams SET posts=0 WHERE teamid=5;UPDATE teams SET posts=0 WHERE teamid=6;

im getting this :



our SQL-query has been executed successfully SQL-query:
UPDATE teams SET posts =5 WHERE teamid =2;# MySQL returned an empty result set (i.e. zero rows).
UPDATE teams SET posts =1 WHERE teamid =3;# MySQL returned an empty result set (i.e. zero rows).
UPDATE teams SET posts =0 WHERE teamid =4;# MySQL returned an empty result set (i.e. zero rows).
UPDATE teams SET posts =0 WHERE teamid =5;# MySQL returned an empty result set (i.e. zero rows).
UPDATE teams SET posts =0 WHERE teamid =6;# MySQL returned an empty result set (i.e. zero rows).

so my qustion is what am i doing worng on php so its not working?

i think we very close to get and answer.

[Dean C]

You can only run one query at a time via mysql_query. Like I said above the most efficient way is via vBcron. I would strongly advise against running a query within a loop. And especially when it's being run everytime that page is loaded!

[miz]

i guss i dont have much options left
thanks for all of help to all of you guys