1 more left join problem :(

[sabret00the]

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

[miz]

EDIT :
[SQL] COUNT(user.posts) AS totalteamposts, [/SQL]

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

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

Originally Posted by sabret00the

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]

Originally Posted by 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

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

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


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]

Originally Posted by 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

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]

Originally Posted by 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

Well i thought about nicer idea
this is what i made :

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

PHP Code:

 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 :
[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;[/SQL]

im getting this :

[SQL]

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). [/SQL]

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