Need help joining datasets in php

[anksrulz]

I have a report to be made using two tables: downloads and softwares and to be displayed using smarty templates with an option to export to excel.

 

the dataset queries are:

 

$criteria = isset($content["start"], $content["end"]) ? " and timestamp between '".$content["start"]."' and '".$content["end"]."'" : ""; (from $_POST)

if (strlen($criteria) > 0)

{

$db->query("select swc_software.NAME, swc_downloads.PLATFORM, count(*) as dlcount from swc_downloads left join swc_software on swc_software.ID = swc_downloads.PRODUCTID where swc_software.name is not null ".$criteria." and swc_downloads.affiliation like 'student%' and swc_downloads.include =1 group by swc_software.NAME, swc_downloads.PLATFORM order by swc_software.NAME asc", $content["tickets"], $numrows);

} else {$content["tickets"] = array();}

 

and similar two more queries for 'Faculty%' and 'Staff%'

 

from these 3 queries we have to take the counts and integrate into a dataset which would save doin totalling on smarty and ease the export to csv file.

 

$db->query("select swc_software.NAME, swc_downloads.PLATFORM from swc_downloads left join swc_software on swc_software.ID = swc_downloads.PRODUCTID where swc_software.name is not null and swc_downloads.include=1 group by swc_software.NAME, swc_downloads.PLATFORM order by swc_software.NAME asc",$content["dataset"], $datarows);

 

I want to merge the data from 3 queries into the above red colored dataset and zeros for counts for softwares which were not downloaded during the period. Also, a total is required (per platform - win,mac or linux and an overall total) per row of the output table.

 

i tried comparing and putting values in the dataset using multiple foreach loops but got lost because of complication and unfamiliarity with multidimensional arrays in php. Help is greatly appreciated. Let me know if i need to provide more information.

[anksrulz]

Can somebody help me get the data to csv file:- please find the code below..

Somehow i am not able to match the keys from students array to dataset (the red part)

I need to do a similar thing for the other 2 arrays as well.

 

 

 

if (isset($_POST['search'])) {

// Use the specified search values

$content["start"] = strlen($_POST["startdate"]) > 0 ? date("d-M-y", strtotime($_POST['startdate'])) : null;

$content["end"] = strlen($_POST["enddate"]) > 0 ? date("d-M-y", strtotime($_POST['enddate'])) : null;

}

 

// Search using specified values

$criteria = isset($content["start"], $content["end"]) ? " and timestamp between '".$content["start"]."' and '".$content["end"]."'" : "";

 

if (strlen($criteria) > 0) {

$db->query("select swc_software.NAME, swc_downloads.PLATFORM, count(*) as CNT1 from swc_downloads left join swc_software on

swc_software.ID = swc_downloads.PRODUCTID where swc_software.name is not null ".$criteria." and swc_downloads.affiliation like 'student%'

and swc_downloads.include =1 group by swc_software.NAME, swc_downloads.PLATFORM order by swc_software.NAME asc", $content["students"], $numrows);

} else {

$content["students"] = array();

}

if (strlen($criteria) > 0) {

$db->query("select swc_software.NAME, swc_downloads.PLATFORM, count(*) as CNT2 from swc_downloads left join swc_software on

swc_software.ID = swc_downloads.PRODUCTID where swc_software.name is not null ".$criteria." and swc_downloads.affiliation like 'faculty%'

and swc_downloads.include =1 group by swc_software.NAME, swc_downloads.PLATFORM order by swc_software.NAME asc", $content["faculty"], $numrows);

} else {

$content["faculty"] = array();

}

if (strlen($criteria) > 0) {

$db->query("select swc_software.NAME, swc_downloads.PLATFORM, count(*) as CNT3 from swc_downloads left join swc_software on

swc_software.ID = swc_downloads.PRODUCTID where swc_software.name is not null ".$criteria." and swc_downloads.affiliation like 'staff%'

and swc_downloads.include =1 group by swc_software.NAME, swc_downloads.PLATFORM order by swc_software.NAME asc", $content["staff"], $numrows);

} else {

$content["staff"] = array();

}

if (strlen($criteria) > 0) {

$db->query("select swc_software.NAME, swc_downloads.PLATFORM from swc_downloads left join swc_software on

swc_software.ID = swc_downloads.PRODUCTID where swc_software.name is not null and swc_downloads.include=1

group by swc_software.NAME, swc_downloads.PLATFORM order by swc_software.NAME asc",$content["dataset"], $datarows);

} else {

$content["dataset"] = array();

}

/*

$list = array('NAME,PLATFORM,STUDENTS');

$file = fopen("output.csv","w");

foreach($list as $line)

{

fputcsv($file,split(',',$line));

}

foreach($content["dataset"] as $value)

{

$studcount=0;

$name = $value[NAME];

$plat = $value[PLATFORM];

foreach( $content["students"] as $stud)

{

if(($stud[NAME] == $name) && ($stud[PLATFORM] == $plat)

{

$studcount = $stud[CNT1];

break 2;

}

}

$list = "".$name.",".$plat.",".$studcount; 

fputcsv($file, split(',',$list));

}

fclose($file);

*/