Display ALL Results from DB

[Sitehelp]

Hi! Any ideas why this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
$row_CallsAssignedCount = mysql_fetch_assoc($CallsAssignedCount);
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

Only displays one result and not all StaffIDs? Its meant to list all StaffIDs and the number of calls closed by that staff member. '$row_Leader1[StaffID]' is the coloumn that lists all the client IDs on another SQL command. This lists all StaffIDs fine, however, this SQL command (above)only displays ONE StaffIDs result. Cheers!

 

[sleipnir214]

Of cource it returns only one row from the result set.

As the PHP online manual states, mysql_fetch_assoc() only returns one row from the result set each time it is called.

You're only calling mysql_fetch_assoc() once -- so you only get one row of the result set. The PHP online manual page on mysql_fetch_assoc() has example code on how to use the function.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

I have tried looping the code below but its still only returning one StaffID?

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
while ($row = mysql_fetch_assoc($CallsAssignedCount)) {
echo $row["StaffID"];
}
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

 

[Sitehelp]

by the way sorry for cross posting someone recommended trying there in the SQL forum earlier!

 

[sleipnir214]

Output $query_CallsAssignedCount to the browser. Copy that query from the browser into your preferred MySQL admin tool.

How many rows are returned?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

ok so I copied:

$query_CallsAssignedCount ;

into my code after:

$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

Then when I opened up the browser it gave me one result like before, is this what you asked? still learning...

 

[sleipnir214]

You need to see that actual query string that your code is creating.

Change this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID,  count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
while ($row = mysql_fetch_assoc($CallsAssignedCount)) {
   echo $row["StaffID"];
}
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

to this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID,  count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
[red]print $query_callsAssignedCount;
exit()[/red]
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
while ($row = mysql_fetch_assoc($CallsAssignedCount)) {
   echo $row["StaffID"];
}
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

This will print the text of your query to the browser.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

will remember that one! ok I was getting a parse error so change too, but presume this is ok:

exit();

when I load up the browser it returns a blank page, I presume this is not right!

 

[sleipnir214]

You're right. I was missing a semicolon.

If the code snipped we've been discussing is part of a larger program, the output may be inside an unclosed HTML tag. Do a "View source" in your web browser.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Ah problem was the case of:

print $query_callsAssignedCount;

just remembered its case sensitive and so therefore needs to be:


print $query_CallsAssignedCount;

Ok its now loading up code in the browser and the same in View Source, this is:


SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '' GROUP BY StaffID ORDER BY CallID ASC

 

[sleipnir214]

The next step was copying that from the browser and pasting it into your preferred MySQL admin tool, remember?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Got ya! yep it returns one result! there should be more!

 

[sleipnir214]

Then your query is wrong.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Could the following line cause this?

WHERE callinfo.StaffID = '{$row_Leader1['StaffID']}'

I say this because I have only called $row which I presume is one row?

 

[sleipnir214]

I don't know.

It's your code. What value are you putting in there?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

ok I have another query which displays all the staffIDs and the number of closed calls they have made with the SQL:

SELECT StaffID, count(CallID)
FROM callinfo
WHERE callinfo.CallStatus = 'Closed'
GROUP BY StaffID
ORDER BY CallID ASC

This works! It puts all the data into a dynamic table. The column in that table that I am querying is: {Leader1.StaffID} This is the list of all the StaffIDs in the table. Ok what I want is to add another row in this table and use this new query to list all the calls, open and closed, that are under this staffID, hence the query:

SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC

However, this is only returning the first result not the rest!

 

[sleipnir214]

What in the world does "It puts all the data into a dynamic table" mean? What's a dynamic table?

What is Leader1.StaffID, assuming, as I have pointed out to you in other threads, that dots are used as concatenation operators?

Keeping in mind that "WHERE callinfo.StaffID = '$row_Leader1[StaffID]'" requires $row_Leader1['StaffID'] to be a string, what is in $row_Leader1?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Sitehelp]

Leader1.StaffID is a field in the Dynamic table that contains the StaffIDs. A dynamic table is a table of results from your SQL query that appear in the browser. The table appears ok for everything but this query?

 

[sleipnir214]

A dynamic table is simply a string that your web browser interprets as a hierarchical layout for information. It has no bearing on what's inside variable in PHP.

Want the best answers? Ask the best questions!

TANSTAAFL!!