[SOLVED] Separating and displaying accurate counts from a MySQL query

[jay.barnes]

Hi all,

 

I've been completely flummoxed by this problem I'm describing below (actually, this is the first of a two-part issue, but I'm hoping that, with an answer to this issue, I can figure out the answer to the second question).

 

Bob and Jim both ship and manage trucks to transport widgets.  I want to keep track of how many shipments and trucks they have in the database.

 

Looking directly at the DB, I see

 

Bob

  Trucks: 10

  Loads: 10

 

Jim

  Trucks:2

  Loads: 4

 

However, after performing the query:

 

<?PHP
mysql_select_db($database_TheBoard, $TheBoard);
$query_AccoountMaintQuery = "
SELECT count(Available_Trucks.Username) AS TruckCount, count(Available_Loads.Username) AS LoadCount, Available_Loads.Username AS LoadsUserName, Available_Trucks.Username AS TrucksUserName, Accounts.Username 
FROM ((Accounts LEFT JOIN Available_Loads ON Available_Loads.Username=Accounts.Username) LEFT JOIN Available_Trucks ON Available_Trucks.Username=Accounts.Username) 
GROUP BY Accounts.Username";
$AccoountMaintQuery = mysql_query($query_AccoountMaintQuery, $TheBoard) or die(mysql_error());
$row_AccoountMaintQuery = mysql_fetch_assoc($AccoountMaintQuery);
$totalRows_AccoountMaintQuery = mysql_num_rows($AccoountMaintQuery);
?>

 

And implementing the dynamic table:

 

<table border="1">
  <tr>
    <td>TruckCount</td>
    <td>LoadCount</td>
    <td>User Info</td>
  </tr>
  <?php do { ?>
    <tr>
      <td>Total Trucks: <?php echo $row_AccoountMaintQuery['TruckCount']; ?></td>
      <td>Total Loads: <?php echo $row_AccoountMaintQuery['LoadCount']; ?></td>
      <td>Username:<?php echo $row_AccoountMaintQuery['Username']; ?></td>
    </tr>
    <?php } while ($row_AccoountMaintQuery = mysql_fetch_assoc($AccoountMaintQuery)); ?>
</table>

 

I'm presented with:

 

Bob

  Trucks: 100

  Loads: 100

 

Jim

  Trucks: 8

  Loads: 8

 

It looks like the query is multiplying the available trucks by available loads, and displaying that result as both the load count and truck count.  How do I separate the two rows so the data is output accurately to the table?

 

Thanks for any suggestions any of you guys may have!

[Ken2k7]

Can you display the structure of the DB?

[jay.barnes]

Sure, I'll try!

 

 

   

Table: Accounts

 

 

   

Username

 

 

   

Bob

 

 

   

Jim

 

[br /]

 

   

Table: Available_Loads

 

 

   

Username

   

Available_Until

 

 

   

Bob

   

2009-3-10

 

 

   

Bob

   

2009-2-20

 

 

   

Bob

   

2009-7-15

 

 

   

Jim

   

2009-2-25

 

 

   

Bob

   

2009-5-16

 

 

   

Bob

   

2009-3-4

 

 

   

Bob

   

2009-3-23

 

 

   

Jim

   

2009-6-6

 

 

   

Bob

   

2009-4-9

 

 

   

Bob

   

2009-5-10

 

 

   

Bob

   

2009-2-14

 

 

   

Bob

   

2009-7-16

 

[br /]

 

   

Table: Available_Trucks

 

 

   

Username

   

Available_Until

 

 

   

Bob

   

2009-3-2

 

 

   

Bob

   

2009-4-8

 

 

   

Bob

   

2009-5-15

 

 

   

Jim

   

2009-3-7

 

 

   

Jim

   

2009-4-4

 

 

   

Bob

   

2009-3-6

 

 

   

Bob

   

2009-5-19

 

 

   

Bob

   

2009-3-14

 

 

   

Bob

   

2009-4-15

 

 

   

Jim

   

2009-4-9

 

 

   

Bob

   

2009-3-18

 

 

   

Bob

   

2009-5-22

 

 

   

Bob

   

2009-4-30

 

 

   

Bob

   

2009-3-9

 

 

   

Jim

   

2009-5-12

 

 

Hope this helps clarify things, and thanks for checking this out!

 

[rami-2.0]

try making 2 separate queries:

$query1="

SELECT count(available_trucks.username) AS TruckCount,username.username AS User FROM (username

LEFT JOIN available_trucks ON available_trucks.username=username.username) GROUP BY username.username";

$query2="

SELECT count(available_loads.username) AS LoadCount,username.username AS User FROM (username

LEFT JOIN available_loads ON available_loads.username=username.username) GROUP BY username.username";

 

$rez1=mysql_query($query1) or die (mysql_error());

$rez2=mysql_query($query2) or die (mysql_error());

echo "Truck Count: ";

while($row1=mysql_fetch_assoc($rez1)) {echo $row1['User']."-".$row1['TruckCount'];}

echo "Load Count: ";

while($row2=mysql_fetch_assoc($rez2)) {echo $row2['User']."-".$row2['LoadCount'];}

 

I think the explanation for your problem is:"MySQL implements an A LEFT JOIN B join_condition as follows:

Table B is set to depend on table A and all tables on which A depends. ",thus the C table depends on A and on B also. In your query, you multiplied TruckCount with UserCount.

[jay.barnes]

Thank you for the offers of assistance, everyone, but I (finally) got it figured out:

 

I created a query for the accounts, and then created a repeating table based on that query, creating cells for the truck count . 

 

In that cell, I put in a DB query looking for recordsets where the truck count username equalled "$row_AccountQuery[username]" (the username called in that row).

 

So, every time the row repeated, the "$row_AccountQuery[username]" would change to equal the username called out in that row, subsequently returning the appropriate number.

 

I hope that made sense, and I wasn't rambling too much.  Thanks again for the assistance, though!  Even though the suggestions didn't get me the desired output, it jogged my mind into comprehending, interpreting, and understanding how queries and repeating regions worked!