[RESOLVED] More than one field in array? Lost...

plaurent

Obviously I don't know what I'm doing, our developer left, and I'm in a real bind. Many thanks in advance for your patience and assistance.

The script below pulls a variety of data from a table (the same query is used elsewhere), pushing the grouped summary of results of one field to a page. We need to add another field (a description) to the array, at the $tmp_array[] = $row[3] level, so that the array consists of $row[3] and $row[10]. The end result should be:

echo "<td>$key:</td><td>$val ($row10val)</td>";

which replaces the existing

echo "<td>$key:</td><td>$val</td>";

I haven't a clue as to how to do this...

$num = mysql_num_rows($array);
$tmp_array = array();
if ($num > 0){
	mysql_data_seek($array, 0);
	while ($row = mysql_fetch_row($array)){
		if (($row[1] >= $start) && ($row[1] < $finish)){
			$tmp_array[] = $row[3];
			$i++;
		}
	}
	sort($tmp_array);
	$a_count = array_count_values($tmp_array);
		while (list($key, $val) = each($a_count)) {
			echo "<tr>";
			echo "<td>$key:</td><td>$val</td>";
			echo "</tr>";
		}
		echo "<tr><td><b>Total:</b></td><td><b>$i</b></td></tr>";
		echo "</table><p>\n";
}

plaurent

Any help would be appreciated... even a nudge in the right direction would be invaluable. Everything I've tried breaks the script. Thanks in advance (again).

bradgrafelman

That SQL code is extremely superfluous. I don't even see where $start, $finish, or $i are defined - I'm assuming those are defined elsewhere?
I would use a multi-dimensional array.
I would never use mysql_fetch_row() and reference columns by their position; this tells you absolutely nothing about the data coming in, and if you alter the structure at any time your script will be broken. What if you re-organize your table? What does $row[3] tell you in terms of the column you're trying to get data from?

Something like this is what I'd do:

$data = array();
$sort = array();
while($row = mysql_fetch_assoc($array) && $row['column_name_for_1'] >= $start && $row['column_name_for_1'] < $finish) {
	$data[] = array($row['column_name_for_3'], $row['column_name_for_10']);
	$sort[] = $row['column_name_for_3'];
}

array_multisort($sort, SORT_ASC, $data);
$a_count = array_count_values($sort);

// See my comment below

After reaching this point in your script, I stopped because there's a logic error.

You say you want each value of $row[3] and the number of times it appears, but you also want the value of $row[10]? Which $row[10]? If three separate rows have "foo" as a value for $row[3], only one entry in the $a_count will exist, though there would be three different $row[10] choices. How would the script know which one to pick?

plaurent

Thank you so much for replying.

Yes, some values are defined elsewhere, but I didn't think they were part of the critical path.

$row[10] is the same value within each grouping of $row[3], so I could use the average of all the items within a group to achieve the desired number. I'm interested to see how you incorporate that into the script you've started!

I've felt we've needed a total rewrite of our code (as I can't even move it to a PHP5 platform because it breaks. It was a good first start, and got the job done, but it's clearly in need of streamlining. I'll need to outsource to get this done...

bradgrafelman

So you just want unique values of $row[3], how many times they appear, and the value of $row[10] (which is the same for all similar values of $row[3]) ?

In that case, I woudl say let SQL do all of the work:

SELECT col3, COUNT(col3) as num, col10 FROM myTable GROUP BY col3 ORDER BY num

(Note: I don't have a SQL server to test this on at the moment, but if that query causes an error, you may need to use "ORDER BY COUNT(col3)" instead of using the alias I gave it).

That way, you would simply loop through the result set like a normal SQL query.

plaurent

Ugh. After a few days of trying this, it appears that I can't use the grouping at the SQL side with a single query, because other row data (timedate stamps for each record) is needed for processing in other areas of the script. I have to do the grouping in PHP, as I'm assuming it would be better to deal with this in PHP with existing data rather than query 75 tables for the same data twice?? I've learned a lot over the past few days, so I am going to rephrase my issue to be more succinct:

I have the following sample data:

Part Sequence
Widget1 10
Widget1 10
Widget2 30
Widget3 20
Widget3 20
Widget3 20

I would like to summarize the data so that I count the number of each grouped Part in the list, and average the Sequence for each grouping of parts. My output should look like:

Widget1 2 10
Widget2 1 30
Widget3 3 20

Can anyone assist with this? Thanks in advance for your help!

bradgrafelman

What do you mean "average the sequence" ? Do you mean the last value in your output should be an average of the sequences for that part? If so...

$output = array();
while($data = mysql_fetch_assoc($exec)) { // loop through MySQL result set
    $k = $data['Part'];
    $d = $data['Sequence'];

if(isset($output[$k]) {
    $output[$k]['avg'] = ($output[$k]['avg'] + $d)/2;
    $output[$k]['num']++;
} else {
    $output[$k]['avg'] = $d;
    $output[$k]['num'] = 1;
}
}

halojoy

you mean that
output should be:

widg_name - rank - average score

Widget1 - 2 - 10
Widget2 - 1 - 30
Widget3 - 3 - 20

plaurent

I ended up re-writing the whole page to use the SQL method that 'bradgrafelman' suggested. Many thanks for your help! The resultant lookup that was created is:

$query_string = "SELECT item, COUNT(item) as heats, avg(qty) as cavity FROM $table WHERE (sample_datetime >= '$startDate $startTime' AND sample_datetime < '$endDate $endTime') AND start_end > 0 GROUP BY item";