0

What I am trying to achieve is run both queries (below) at the same time. From one table rvsessions I need to get the count for the total amount of sessions(which is achieved by the number of times the rvsessions_user_id appears in the table) and the number of sessions correct (correct = 1 and incorrect = 0)

<?php
$query = "SELECT * , count(*) AS rvsession_user_id FROM rvsessions 
GROUP BY rvsession_user_id ORDER BY rvsession_user_id DESC";

$query = "SELECT * , count(*) AS rvsession_correct FROM rvsessions 
WHERE rvsession_correct = 1 GROUP BY rvsession_user_id";

$select_all_user_sessions = mysqli_query($connection,$query);
$total_user_session_count = mysqli_num_rows($select_all_user_sessions);

while($row = mysqli_fetch_assoc($select_all_user_sessions)) {

$rvsession_username  = $row['rvsession_username'];
$count_user_sessions = $row['rvsession_user_id'];
$count_sessions_correct = $row['rvsession_correct'];

echo "<tr>";
           echo "<td>$rvsession_username</td>";
           echo "<td>$count_user_sessions</td>";
           echo "<td>$count_sessions_correct</td>";                             
echo "</tr>";
                       }            
?>

Each individual query runs fine and produces the correct results. When I run them together both counts are not producing the correct results. When I try to embed one query in the other as a nested sub-query, I get a syntax-error. I am at a loss... What would be the correct syntax for this?

closed as unclear what you're asking by mustaccio, MDCCL, Md Haidar Ali Khan, Tom V, hot2use Oct 25 '18 at 13:13

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You are only running 1 query, the second one. You define the $query as the first query and then immediately redefine it as the second one before executing it. Other than that it's not really clear what you want to achieve and how your code is supposed to do that or what results you get versus what results you expect. – Tom V Oct 25 '18 at 8:05
0
SELECT *, 
       COUNT(*) AS rvsession_user_id,
       SUM(rvsession_correct=1) AS rvsession_correct
FROM rvsessions 
GROUP BY rvsession_user_id

Not the answer you're looking for? Browse other questions tagged or ask your own question.