Mysql only outputs last loops data

Question

i am trying to output statistics of calls made by agents across all dialing campaigns called for the day. there are multiple campaigns, currently 3, dialed in a day. I get the campaign data from a table name that changes by campaign like so custom_16546546 and custom_1564654. I run a query to get the numeric part of the column name then pass that as a variable in to another query that outputs the users statistics. This is where it only displays data from what i can tell is the last query loops information. I have displayed the array data from the first query to confirm the table names are being stored and it looks fine.

    $today = "2013-05-29";
$customquery = "SELECT distinct( entry_list_id) FROM vicidial_list where entry_list_id > 0 and last_local_call_time between '$today 00:00:00' and '$today 23:59:59';" ;
$customresult = mysql_query($customquery) or die(mysql_error());
$customrows = mysql_num_rows($customresult);
        while ($customrowResult = mysql_fetch_array($customresult)) 
                {
                $customID[] = $customrowResult["entry_list_id"];
                }
$z = 0;

if(!$customID){
//echo " Please Select a valid date range: ".$today. " - ".$today." is not valid" ;
}else{

while($z<$customrows)
{

$query = "SELECT 
            vicidial_users.user,
            vicidial_users.full_name,
            vicidial_log.list_id,
            COUNT(CASE WHEN (vicidial_log.status = 'SALE') THEN 1 ELSE null END) as sumcountSamnt,
            COUNT(CASE WHEN (vicidial_log.status = 'SALE' AND custom_$customID[$z].up_amt <> '') THEN 1 ELSE null END) as sumcountupAmnt,
            COUNT(CASE WHEN (vicidial_log.status = 'SALE' AND custom_$customID[$z].cc_num <> '') THEN 1 ELSE null END) as sumccverifiedcountAmnt,
            COUNT(CASE WHEN (vicidial_log.status = 'SALE' AND custom_$customID[$z].bank_act <> '') THEN 1 ELSE null END) as sumbankverifiedcountAmnt,
            SUM(CASE WHEN (vicidial_log.status = 'SALE' AND custom_$customID[$z].cc_num <> '') THEN custom_$customID[$z].s_amount ELSE null END) as sumccverifiedAmnt,
            SUM(CASE WHEN (vicidial_log.status = 'SALE' AND custom_$customID[$z].bank_act <> '') THEN custom_$customID[$z].s_amount ELSE null END) as sumbankverifiedAmnt,
            SUM(CASE WHEN (vicidial_log.status = 'SALE') THEN custom_$customID[$z].d_amt ELSE null END) as sumDamnt,
            SUM(CASE WHEN (vicidial_log.status = 'SALE') THEN custom_$customID[$z].up_amt ELSE null END) as sumUpamnt,
            SUM(CASE WHEN (vicidial_log.status = 'SALE') THEN custom_$customID[$z].md_amt ELSE null END) as sumMdamnt,
            SUM(CASE WHEN (vicidial_log.status = 'SALE') THEN custom_$customID[$z].s_amount ELSE null END) as sumSamnt

        FROM
            vicidial_log
        INNER JOIN
            vicidial_users
        ON
            (vicidial_log.user = vicidial_users.user)
        INNER JOIN
            custom_$customID[$z]
        ON
            (vicidial_log.lead_id = custom_$customID[$z].lead_id)
        WHERE 
            call_date
        BETWEEN
            '$today 00:00:00'
        AND
            '$today 23:59:59'
        AND
            vicidial_log.user != 'VDAD'
        AND
            vicidial_users.user_group != 'TX-Training'
        GROUP BY
            vicidial_log.user, vicidial_log.campaign_id
        ";

//This query is used to sum loop data data 
$queryResult = mysql_query($query) or die(mysql_error());    //This line perfomrs query error handling 

$z++;
}

here is a snippet of how the data is displayed

    $result = mysql_query($query) or die(mysql_error());
    while ($row = mysql_fetch_assoc($result)) 
 {   
     echo "<tr>\n";
  //--fullname and userID 
     echo "<td> ".$row["full_name"]. " - ".$row["user"]."</td>\n";

  //Agent total sales amount

             if ($row["sumcountSamnt"] == '0') {
    echo "<td>$nosalestxt</td>\n";
    } else {
     echo "<td> $".$row["sumSamnt"]."</td>\n";
     }
    }

So i want to be able to display a sum total of data for each user from all the campaigns they dialed from.

Thanks in advance for your help

EDIT: i use &row from a while loop to output the data and yes Chris i would like to store it an array, but i guess im just missing it, thanks again

What do you expect? You run that huge query, fetch the results, then immediately loop again. You don't process the data, you don't output it. So in the end, you're left with the results of the loop's **LAST** iteration... — Marc B, May 30 '13 at 16:24
In addition to what @MarcB said, Please do not use `mysql_*` functions anymore. They are no longer maintained and are [**deprecated as of PHP 5.5.0**](https://wiki.php.net/rfc/mysql_deprecation). Read [this](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for a more detailed explanation. Instead, use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) and learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) can help you decide which API to use. — PLPeeters, May 30 '13 at 21:22

score 0 · Answer 1 · answered May 30 '13 at 16:36

Based on the code snippets you have given it looks like you are overwriting the value of $queryResult after each execution of the loop. Perhaps you meant to execute the query and put the results into an array (or something similar) to loop over and display later.

Also I notice you reference $row[] in your output, how are you assigning a value to that variable?

Mysql only outputs last loops data

1 Answers1