For each SQL result, another SQL query? in PHP

Question

I need help at getting data from MySQL Database. Right now I have a query that gives me:

Tournament ID

Tournament Name

Tournament Entry fee

Tournament Start and End date

For tournaments I am registered in. Now I want, for each tournament I am registered in, to count how many users are in that tournament, my points in that tournament, etc.

That info is in table called 'ladder'

ladder.id

ladder.points

ladder.userFK

ladder.tournamentFK

Database: http://prntscr.com/99fju1

PHP CODE for displaying tournaments I am registered in:

<?php
include('config.php');
$sql = "SELECT distinct tournaments.idtournament, tournaments.name, tournaments.entryfee, tournaments.start, tournaments.end
                from tournaments join ladder
                on tournaments.idtournament= ladder.tournamentFK and ladder.userFK=".$_SESSION['userid']."
                group by tournaments.idtournament";

$result = $conn->query($sql);               
    if($result->num_rows > 0){
        while($row = $result->fetch_assoc()) {                      
            $tournament="<li class='registered' data-id=".$row['idtournament']." data-entryfee=".$row['entryfee']." data-prize=".$tournamentPrize."><span class='name'>".$row['name']."</span><span class='entry-fee'>Entry fee: ".$row['entryfee']."&euro;</span><span class='prize-pool'>Prize pool: &euro;</span><span class='date-end'>".$row['start']."-".$row['end']."</span><span class='btns'><button>Standings</button></span></li>";
            echo $tournament;
        }                   
    }
$conn->close();
?>

Not sure what you mean, for each tournament I would like to count how many users are in that tournament, dont know how to do it in PHP. Do I make a query within a while statement? @NanaPartykar — Troix, Dec 02 '15 at 11:28
Edit with your exact table schema and you will get answer easy. — Anand Singh, Dec 02 '15 at 11:33

score 0 · Answer 1 · edited May 23 '17 at 12:01

0

Usually you can combine JOIN, COUNT() and GROUP BY in your query.

Some examples: MySQL joins and COUNT(*) from another table

edited May 23 '17 at 12:01

Community

1
1

answered Dec 02 '15 at 11:44

Alex Babak

489
3
15

score 0 · Answer 2 · answered Dec 02 '15 at 12:07

This would be the query I think.Change column and table name if its not correct. Not tested but I am sure this will give you some idea to make required query

select count(ladder.tournamentId)as userCount,tournaments.name 
    from 
     ladder left join tournaments 
      on ladder.tournamentId =  tournaments.id 
       where ladder.tournamentId in 
         (
         select tournaments.id from 
           tournaments left join ladder 
            on ladder.tournamentId =  tournaments.id 
             where  ladder.userId='yourId'
          ) and ladder.userId <> 'yourId'
            group by ladder.tournamentId

Thanks for the effort, but it does not work the way I want to. I think what I want to achieve can not be done in a single SQL query, it needs PHP approach, and thats what im looking to find here — Troix, Dec 02 '15 at 15:51

For each SQL result, another SQL query? in PHP

2 Answers2