[SOLVED] array structure problem (php / mysql)

[skope]

I'm trying to extract data from a mySQL database, pass the array to a flash graph system to render the results however the array is not being created as I need.

 

The MySQL table is

idrecordsemployeeNamecustType

1John DoePromoter

2Jane DoePassive

3Jill DoeDetractor

4Jack DoePassive

5John DoeDetractor

6Jane DoePromoter

7Jill DoePassive

8Jack DoePromoter

and so on...

(there are other columns (location, custCalled, etc) but those are irrelevant to the problem I'm having)

 

I'm querying the db with the following:

 

$db->Query('SELECT DISTINCT employeeName FROM '.$tableName.' WHERE custCalled="1" '.$locationQuery.'');
while ($row = $db->Row()) {
$employees[] = $row->employeeName;
}
foreach ($employees as $employee) {
	if ($db->Query('SELECT * FROM '.$tableName.' WHERE custType="Promoter" '.$locationQuery.' AND employeeName="'.$employee.'"')) {
		if ($db->RowCount() == "") {
			$promoter = 0;
		} else {
			$promoter = $db->RowCount();
		};
	} else {
	    echo ("<p>Promoter Query Failed</p>");
	};
	if ($db->Query('SELECT * FROM '.$tableName.' WHERE custType="Passive" '.$locationQuery.' AND employeeName="'.$employee.'"')) {
		if ($db->RowCount() == "") {
			$passive = 0;
		} else {
			$passive = $db->RowCount();
		};
	} else {
	    echo "<p>Passive Query Failed</p>";
	};
	if ($db->Query('SELECT * FROM '.$tableName.' WHERE custType="Detractor" '.$locationQuery.' AND employeeName="'.$employee.'"')) {
		if ($db->RowCount() == "") {
			$detractor = 0;
		} else {
			$detractor = $db->RowCount();
		};
	} else {
	    echo "<p>Detractor Query Failed</p>";
	};

$recordSet[] = 	array($employee,$promoter,$passive,$detractor);

}

 

The Flash Graph requires the array to be in the following format:

$chart [ 'chart_data' ] = array ( array ( "","John Doe","Jane Doe","Jill Doe","Jack Doe"),
                                  array ( "Promoter",5,2,3,1),
                                  array ( "Passive",4,2,0,4),
                                  array ( "Detractor",1,0,0,2),
                                );

 

so I have

$chart['chart_data'] = $recordSet;

 

however the array generated is

$chart [ 'chart_data' ] = array ( array ( "Employee","Promoter","Passive","Detractor"),
                                  array ( "John Doe",5,2,3,1),
                                  array ( "Jane Doe",4,2,0,4),
                                  array ( "Jill Doe",1,0,0,2),
                                  array ( "Jack Doe",1,0,0,2),
                                );

 

I've been looking at this for too long and am not thinking straight so I'm putting it out there for your help. Is there a way to convert the rows to columns? or am I querying the db wrong in the first place?

 

Thanks again!

[Barand]

try

<?php

$initial = array();
$data = array();
$chart = array();

$sql = "SELECT DISTINCT employeename FROM $tablename
        ORDER BY employeename";
$res = mysql_query($sql);
while ($row = mysql_fetch_row($res))
{
    $initial[$row[0]] = 0;                  // set initial counts for each custType/employee
}

$sql = "SELECT custType, employeename FROM $tablename";
$res = mysql_query($sql);
while (list($cust,$emp) = mysql_fetch_row($res))
{
    if (!isset($data[$cust])) $data[$cust] = $initial;
    $data[$cust][$emp]++;                                // count data
}

    /**
    * construct required arrays
    */
$chart['chartdata'][0] = array_merge(array(''), array_keys($initial));
foreach ($data as $ct => $d)
{
    $chart['chartdata'][] = array_merge(array($ct), array_values($d));
}

    /**
    * view result
    */
echo '<pre>', print_r($chart, true), '</pre>';
?>

[skope]

That's getting there..... it has the correct orientation now!

 

I  changed the array constructor to

<?php
$recordSet[0] = array_merge(array(''), array_keys($initial));
foreach ($data as $ct => $d)
{
    $recordSet[] = array_merge(array($ct), array_values($d));
}
?>

as it was one array too deep.

Chart required this line:

$chart['chart_data'] = $recordSet;

 

2 problems still remain

 

Array
(
    [0] => Array
        (
            [0] => 
            [1] => John Doe
            [2] => Jane Doe
            [3] => Jack Doe
            [4] => Jill Doe
            [5] => Another Name
            [6] => Another Name
            [7] => Another Name
            [8] => Another Name
            [9] => Another Name
            [10] => Another Name
            [11] => Another Name
            [12] => Another Name
            [13] => Another Name
            [14] => Another Name
            [15] => Another Name
            [16] => Another Name
            [17] => Another Name
        )

    [1] => Array
        (
            [0] => 
            [1] => 1
            [2] => 1
            [3] => 1
            [4] => 1
            [5] => 1
            [6] => 1
            [7] => 1
            [8] => 1
            [9] => 1
            [10] => 1
            [11] => 1
            [12] => 1
            [13] => 1
            [14] => 1
            [15] => 1
            [16] => 1
            [17] => 1
            [18] => 1
            [19] => 1
            [20] => 1
            [21] => 1
            [22] => 1
            [23] => 1
            [24] => 1
            [25] => 1
            [26] => 1
            [27] => 1
        )

    [2] => Array
        (
            [0] => Promoter
            [1] => 1
            [2] => 1
            [3] => 1
            [4] => 0
            [5] => 1
            [6] => 1
            [7] => 1
            [8] => 1
            [9] => 1
            [10] => 1
            [11] => 1
            [12] => 1
            [13] => 1
            [14] => 0
            [15] => 1
            [16] => 1
            [17] => 1
        )

    [3] => Array
        (
            [0] => Passive
            [1] => 0
            [2] => 1
            [3] => 0
            [4] => 0
            [5] => 1
            [6] => 0
            [7] => 0
            [8] => 0
            [9] => 1
            [10] => 1
            [11] => 0
            [12] => 0
            [13] => 0
            [14] => 0
            [15] => 0
            [16] => 0
            [17] => 1
        )

    [4] => Array
        (
            [0] => Detractor
            [1] => 0
            [2] => 1
            [3] => 0
            [4] => 1
            [5] => 1
            [6] => 1
            [7] => 0
            [8] => 1
            [9] => 0
            [10] => 1
            [11] => 0
            [12] => 1
            [13] => 0
            [14] => 1
            [15] => 1
            [16] => 0
            [17] => 1
        )

)

1. the top level array is outputting an extra array (key 1) which is not wanted or needed.

and

2. It appears it is not counting (adding total occurances of each custType for each employee) but is simply showing true/false (1/0) for each of the custTypes found

 

[skope]

forget point 2.....

 

I had a redundant piece of code lingering that i missed causing the count to go screwy.

 

Now... the array key I need removed appears to be a count of the number of times that the employee's name appears in the table.

 

 

[Barand]

code wa tested so it could be your data

 

My data

+-----------+--------------+-----------+
| idrecords | employeeName | custType  |
+-----------+--------------+-----------+
|         1 | John Doe     | Promoter  |
|         2 | Jane Doe     | Passive   |
|         3 | Jill Doe     | Detractor |
|         4 | Jack Doe     | Passive   |
|         5 | John Doe     | Detractor |
|         6 | Jane Doe     | Promoter  |
|         7 | Jill Doe     | Passive   |
|         8 | Jack Doe     | Promoter  |
|         9 | John Doe     | Promoter  |
|        10 | Jane Doe     | Passive   |
|        11 | Jill Doe     | Detractor |
|        12 | Jack Doe     | Passive   |
|        13 | John Doe     | Detractor |
|        14 | Jane Doe     | Promoter  |
|        15 | Jill Doe     | Passive   |
|        16 | Jack Doe     | Promoter  |
|        17 | John Doe     | Promoter  |
|        18 | Jane Doe     | Passive   |
|        19 | Jill Doe     | Detractor |
|        20 | John Doe     | Promoter  |
|        21 | Jane Doe     | Passive   |
|        22 | Jill Doe     | Detractor |
|        23 | John Doe     | Promoter  |
|        24 | Jane Doe     | Passive   |
|        25 | Jill Doe     | Detractor |
|        26 | Jack Doe     | Passive   |
|        27 | John Doe     | Detractor |
|        28 | Jane Doe     | Promoter  |
|        29 | Jill Doe     | Passive   |
|        30 | Jack Doe     | Promoter  |
+-----------+--------------+-----------+

 

My results

Array
(
    [chartdata] => Array
        (
            [0] => Array
                (
                    [0] => 
                    [1] => Jack Doe
                    [2] => Jane Doe
                    [3] => Jill Doe
                    [4] => John Doe
                )

            [1] => Array
                (
                    [0] => Promoter
                    [1] => 3
                    [2] => 3
                    [3] => 0
                    [4] => 5
                )

            [2] => Array
                (
                    [0] => Passive
                    [1] => 3
                    [2] => 5
                    [3] => 3
                    [4] => 0
                )

            [3] => Array
                (
                    [0] => Detractor
                    [1] => 0
                    [2] => 0
                    [3] => 5
                    [4] => 3
                )

        )

)

[skope]

Nailed it!

Thank you very much!

 

The unwanted array was a count of total number of occurances of each employee in the table.

 

My first query limited the results by 'custCalled'

SELECT DISTINCT employeeName FROM '.$tableName.' WHERE custCalled="1" ORDER BY employeeName

 

however the second query wasn't limiting it

SELECT custType,employeeName FROM '.$tableName.' WHERE custCalled="1"

 

Once again, thank you for your time and efforts.