[SOLVED] mysql SELECT help

[n3mesis125]

Hi fellow coders,

 

I'm having a hard time trying to figure out what type of SELECT statement to use to gather information and display it from my mysql database.

 

I have the following column names in a table called 'csit_actioned':

 

id  username    func_name    entry_date    total_aht

 

An example of data in these columns would be:

 

id username func_name    entry_date    total_aht

1  john.doe  BCB            2008-05-01  300

2  john.doe  BCB            2008-05-01  280

3  john.doe  AREC          2008-05-01  500

4  john.doe  PAC            2008-05-02  120

 

What I am trying to do is have a SELECT statement that will use a WHERE `entry_date` BETWEEN '2008-05-01' AND '2008-05-31', it'll search through all entries within this range and then output how many of each function for each individual day of the month. IE: it would output:

 

2008-05-01

    BCB, Total: 2, AHT: 580

    AREC, Total: 1, AHT: 500

 

2008-05-02

    PAC, Total: 1, AHT: 120

 

I'm just not sure if it is possible to do this in one SELECT statement. I first attempted using a for() statement to spit out all the days of the month and for each date i used a function to query the datebase for that date and used a GROUP BY on the 'func_name' and that would return all of the functions for that day, but using a query in the function was extremely slow and the page took long to load. Is there a quick, simple and fast statement I could use to get the output I described above?

 

Thanks a million,

n3m.

 

 

[wildteen88]

Just use PHP to categories the results in to the correct days. Run your standard SELECT query:

SELECT * FROM csit_actioned WHERE entry_date BETWEEN '2008-05-01' AND '2008-05-31' ORDER by entry_date

Now to categories the times, do

$prev_date = 0;

while($row = mysql_fetch_assoc($result))
{
    if($prev_date != $row['entry_date'])
    {
        echo '<h1>' . $row['entry_date'] . '</h1>';
        
         $prev_date = $row['entry_date'];
    }

    echo $row['username'] . ' - ' . $row['function_name'] . ' - ' . $row['total_aht'] . '<br />';
}

[n3mesis125]

THanks, but its not quite what I was looking for.

 

I want to display each day there is an entry but I don't want to show duplicate function names under the day name:

 

Dont' Want:

 

2008-05-01

PAC

PAC

PAC

BCB

BCB

 

I Want:

 

2008-05-01

PAC  3 Total

BCB  2 Total

[Buddski]

Try doing a GROUP BY `entry_date` and `func_name`

Wrap a COUNT() around the `func_name` and then

SUM() the total_aht column

 

Something like this might work.. (un-tested)

SELECT 
`entry_date`,
`func_name`,
COUNT(`func_name`) as `func_name_total`,
SUM(`total_aht`) as `aht_total`
FROM `csit_actioned` 
WHERE `entry_date` BETWEEN '2008-05-01' AND '2008-05-31' 
GROUP BY `entry_date`, func_name
ORDER by `entry_date`

[n3mesis125]

Try doing a GROUP BY `date` and wrap a COUNT() around the func_name.

 

Something like this might work.. (un-tested)

SELECT 
`entry_date`,
`func_name`,
COUNT(`func_name`) as `func_name_total`,
SUM(`total_aht`) as `aht_total`
FROM `csit_actioned` 
WHERE `entry_date` BETWEEN '2008-05-01' AND '2008-05-31' 
GROUP BY `entry_date`, func_name
ORDER by `entry_date`

 

Unfortunately the above code doesn't do it either   That just counts all of the functions for that day as one total instead of listing them individually and giving the totals of the function itself. IE:

 

If there are 3 PACS, 4 BCBS, your above code gives me on 2008-05-30, 7 as count and adds all of the 7's AHTS. What I am trying to do is make the statement count the total occurrences of each function separately for each day and list their totals for number of occurrences and aht totals. Hopefully that makes sense. So far I have it working by using the below code but I know for sure it is probably sloppy and it takes quite a long time to execute the code and load the page using this way, but maybe if I show what I've done it'll give everyone a better idea of what I am trying to do.

 

<?php

require_once('../Connections/db.php');
mysql_select_db($database_db, $db);

$user = "john.doe";
$tm = 31;

$dates = array();
for ($i=1; $i<$tm; $i++) {

if ($i < 10) {
$i = "0".$i;
}

$currDate = "2008-05-".$i;
$dates[] = array(
'd' => $currDate,
'info' => getData($user, $currDate)
);
}

function getData($user, $day)
{

$sql = "SELECT `func_name`
	FROM `csit_actioned`";
$res = mysql_query($sql) or die(mysql_error());

$i=0;
while ($i < mysql_num_fields($res)) {
   $meta = mysql_fetch_field($res, $i);
   $sql2 = "SELECT `".$meta->name."`, COUNT(".$meta->name.") as Count, SUM(total_aht) as aht, `entry_date`
   			FROM `csit_actioned`
		WHERE `username`='".$user."' AND `entry_date`='".$day."'
	    GROUP BY `".$meta->name."`";
   $res2 = mysql_query($sql2) or die(mysql_error());
   $numrows = mysql_num_rows($res2);
   
if ($numrows != 0)
{
	$data = array();
	while($r = mysql_fetch_assoc($res2)){
	// echo results or store data in array below
	$data[] = array(
		'func' => $r[$meta->name],
		'count' => $r['Count'],
		'aht' => $r['aht']
		);
   		}
} else {
	$data = "";
}
$i++;
}

return $data;

}

foreach ($dates as $day)
{
echo $day['d'] . "<br/>";

$num_funcs = count($day['info']);
foreach ($day['info'] as $list)
{
	echo $list['func'] . "<br/>";
}
echo "<br/><br/>";
}

?>

[Buddski]

I just tested my rendition of the query using the 4 test data options provided by your initial post and I got these results

 

entry_date func_name func_name_total aht_total

5/1/2008    AREC        1                      500

5/1/2008    BCB          2                      580

5/2/2008    PAC          1                      120

 

Are these what you are trying to achieve..

I got these results with no PHP involved.

[n3mesis125]

Yes thats what I'm trying to achieve, I must of used your code above incorrectly then, can you give me an example of how you did it including php to output it?

[Buddski]

<?php

$sql = "
SELECT 
`entry_date`,
`func_name`,
COUNT(`func_name`) as `func_name_total`,
SUM(`total_aht`) as `aht_total`
FROM `csit_actioned` 
WHERE `entry_date` BETWEEN '2008-05-01' AND '2008-05-31' 
GROUP BY `entry_date`, func_name
ORDER by `entry_date`
";

$records = array();

$query = mysql_query($sql);
if (mysql_num_rows($query) > 0) {
while ($data = mysql_fetch_array($query)) {
	$date = $data['entry_date'];
	$aht = $data['aht_total'];
	$func_name = $data['func_name'];
	$count = $data['func_name_total'];
	$records[] = array(
										'func' 	=> $func_name,
										'count' => $count,
										'aht'		=> $aht,
										'date' 	=> $date);
}
}

echo "<pre>";
print_r($records);
echo "</pre>";
?>

 

Will give you this

 

Array
(
    [0] => Array
        (
            [func] => AREC
            [count] => 1
            [aht] => 500
            [date] => 2008-05-01
        )

    [1] => Array
        (
            [func] => BCB
            [count] => 2
            [aht] => 580
            [date] => 2008-05-01
        )

    [2] => Array
        (
            [func] => PAC
            [count] => 1
            [aht] => 120
            [date] => 2008-05-02
        )

)

 

[n3mesis125]

Thanks sooooooo much budd, it is working extremely fast now since I can get all that info in the sql select statement, I find whenever I resort to putting queries into functions that are called within for or while loops it bogs big time