pushing/adding new elements to an array 1

[mThomas]

I have multiple MS SQL queries. I want to create an associative array with the first query. With each addtional query I want to add elements. Here is the code I'm working with (only using two queries):

$planning_1 = mssql_query("

select count(distinct cdc_id)as Planning_Count,office_name, 'planning jobs' as planning_jobs
FROm Projects inner join office on office.office_id = projects.office_id
Where projects.Web_publish_date is not null
and subsection_id = 4
and typeset_flag in('n','y')
and Office_name not in ('Florida Administration','California Public','Corporate','Data Quality Team','Terminations','Sacramento')
and projects.industry_id not in (787,788,789,790)
Group by Office_name
Order by Office_Name

", $db);



$final = "";

while ($row = mssql_fetch_assoc($planning_1)){
   # now set the value
  //$final[$row['office_name']][$row['Planning_Count']];
    $final[$row['office_name']][$row['planning_jobs']]=$row['Planning_Count'];
}

$planning_2 = mssql_query("

--stale planning jobs in system

select count(distinct cdc_id)as Planning_Stale,office_name, 'stale' as Stale
FROm Projects inner join office on office.office_id = projects.office_id
Where projects.Web_publish_date is not null
and datediff(day,projects.web_publish_date,getdate())>180
and subsection_id = 4
and typeset_flag in('n','y')
and Office_name not in ('Florida Administration','California Public','Corporate','Data Quality Team','Sacramento','Terminations')
and projects.industry_id not in (787,788,789,790)
and left(cdc_id,2)<>'CA'
Group by Office_name
Order by Office_Name

", $db);


while ($row_2 = mssql_fetch_assoc($planning_2)){

    array_unshift($final,$row_2['Stale']=$row_2['Planning_Stale']);

}

print_r($final);

Here is the result:

Array
(
    [0] => 1562
    [1] => 121
    [2] => 693
    [3] => 1295
    [4] => 1864
    [5] => 1811
    [6] => 3180
    [7] => 1526
    [Austin Texas] => Array
        (
            [planning jobs] => 5607
        )

    [Cary NC] => Array
        (
            [planning jobs] => 7314
        )

    [Florida] => Array
        (
            [planning jobs] => 3566
        )

    [New England] => Array
        (
            [planning jobs] => 5486
        )

    [New York] => Array
        (
            [planning jobs] => 5414
        )

    [Pennsylvania] => Array
        (
            [planning jobs] => 2040
        )

    [Retail Market] => Array
        (
            [planning jobs] => 250
        )

    [Yardley Editorial] => Array
        (
            [planning jobs] => 3664
        )

)

Here is what I would like to end up with:

Array
(

    [Austin Texas] => Array
        (
            [planning jobs] => 5607
            [stale] => 1562
        )

    [Cary NC] => Array
        (
            [planning jobs] => 7314
            [stale] => 121
        )

    [Florida] => Array
        (
            [planning jobs] => 3566
            [stale] => 693
        )

    [New England] => Array
        (
            [planning jobs] => 5486
            [stale] => 1295
        )

    [New York] => Array
        (
            [planning jobs] => 5414
            [stale] => 1864
        )

    [Pennsylvania] => Array
        (
            [planning jobs] => 2040
            [stale] => 1811
        )

    [Retail Market] => Array
        (
            [planning jobs] => 250
            [stale] => 3180
        )

    [Yardley Editorial] => Array
        (
            [planning jobs] => 3664
            [stale] => 1526
        )

)

I think I'm on the right rack, but any help will be appreciated.

tia...mike

 

[DRJ478]

It is just a quick reordering of the database calls.
I would put the counting of the stale projects within the loop that iterates the count.
Check for the number of 'stale' right after you add the planning_jobs. Just add a WHERE clause that includes:

...
# take out
and Office_name not in ('Florida Administration','California Public','Corporate','Data Quality
# replace with
and Office_name = '$row[office_name]

Nesting the count query in the iteration of the first result set should solve it.

 

[mThomas]

It worked... thank you!! I didn't replace the office name condition because the result was being skewed, but it works just fine with the original condition.

mike