Consfused and needs help with a query

[iskall]

Hi folks,

I just cant bust this one. I want a MYSQL query and a certain output.

 

See below:

 

My database looks like this:

fweek 	| satindex	| id
---------------------------------
31	| 9		| 1
31	| 8		| 1
31	| 9		| 1
32	| 6		| 1
32	| 9		| 1
32	| 8		| 1
32	| 2		| 1
32	| 7		| 1
33	| 4		| 1
33	| 5		| 1
33	| 8		| 1
33	| 9		| 1
31	| 9		| 2
31	| 8		| 2
31	| 9		| 2
32	| 6		| 2
32	| 9		| 2
32	| 8		| 2
32	| 2		| 2
32	| 7		| 2
33	| 4		| 2
33	| 5		| 2
33	| 8		| 2
33	| 9		| 2
--------------------------------

I would like to get an output that looks like so:

ID 1:
Week 31: 100%, Surveys: 3
Week 32: 75%, Surveys: 5
Week 33: 50%, Surveys: 4

 

Explaination, the satindex collum works like so: a number value of 1,2,3,4,5,6 counts as a $nonSat, while 7,8,9 counts as a $Sat, hence the percentage.... anyway, this would be really simple but I cant fix it so that it reports it when I cant search by week, I need to consider everything in one query.

 

I mean, it could be done like this:

SELECT satindex FROM vocdata WHERE id='1' AND fweek='31'.....

But I want everything done in one query.

 

This is probably a really simple thing, so yeah, I am a newbie, please dont flame me  ???

[iskall]

I am sorry, just a small add. This is how my database looks (the code brackets made it look weird);

 

fweek | satindex | id

---------------------------------

31 | 9 | 1

31 | 8 | 1

31 | 9 | 1

32 | 6 | 1

32 | 9 | 1

32 | 8 | 1

32 | 2 | 1

32 | 7 | 1

33 | 4 | 1

33 | 5 | 1

33 | 8 | 1

33 | 9 | 1

31 | 9 | 2

31 | 8 | 2

31 | 9 | 2

32 | 6 | 2

32 | 9 | 2

32 | 8 | 2

32 | 2 | 2

32 | 7 | 2

33 | 4 | 2

33 | 5 | 2

33 | 8 | 2

33 | 9 | 2

--------------------------------

[iskall]

Just to make it even more clear... I just wrote the below code. Now this gets it for all "fweeks" in the database, how can I modify it so it sets an array for each week or whatever?

 

<?php
include "include/db_connect.inc.php";
$nonSat = 0;
$sat = 0;
$week = "28";
$query = "SELECT Satindex FROM vocdata WHERE Vocid='4820'";
$result = mysql_query($query);
$total = mysql_num_rows($result);
while ($row = mysql_fetch_assoc($result)) {
echo $row['Satindex'] . "<br/>";
$index = $row['Satindex'];
if ($index > 6) { $sat++; }
else { $nonSat++; }

}
$satpercent = $sat / $total * 100;
$satpercent = (int)$satpercent;
echo "Displaying fiscal week $week";
echo "<br/>";
echo "Total: $total hits whereof $sat where satisfied and $nonSat where non-satisfied";
echo "<br/>";
echo "This leaves us with a sat percentage of: $satpercent%";
?>

 

Notice how I use different headers and variables in this example.. doesnt really matter

[fenway]

I don't undrestan.

[mtoynbee]

Wild stab in the dark:

 

<?php
include "include/db_connect.inc.php";
$nonSat = array();
$sat = array();
$week = "28";
$query = "SELECT [week],[satindex] FROM vocdata WHERE Vocid='4820' GROUP BY week";
$result = mysql_query($query);
$total = mysql_num_rows($result);
while ($row = mysql_fetch_assoc($result))  // mysql_fetch_array?
{
   $week=$row['week'];
   $wks[]=$week;
   echo $row['Satindex'] . "<br/>";
   $index = $row['Satindex'];
   if ($index > 6) 
   { 
      $sat[$week]++; 
   }
   else 
   { 
      $nonSat[$week]++; 
   }
   
}

foreach ($wks AS $wk)
{
    $satpercent = array_sum($sat[$wk]) / (count($sat[$wk])+count($nonSat[$wk])) * 100;
    $satpercent = (int)$satpercent;
    echo "Displaying fiscal week $wk";
    echo "<br/>";
    echo "Total: ".(array_sum($sat[$wk])+array_sum($nonSat[$wk]))." hits    whereof ".count($sat[$wk])." where satisfied and ".count($nonSat[$wk]))." where non-satisfied";
    echo "<br/>";
    echo "This leaves us with a sat percentage of: $satpercent%";
?>

 

 

 

[iskall]

Thanks mtoynbee, this looks correct!

 

Anyway, I tried it out and got error messages, here's the code:

 

<?php
include "include/db_connect.inc.php";
$nonSat = array();
$sat = array();
$query = "SELECT Fweek, Satindex FROM vocdata WHERE Vocid='4820' GROUP BY Fweek";
$result = mysql_query($query);
$total = mysql_num_rows($result);
while ($row = mysql_fetch_array($result))  // mysql_fetch_array?
{
   $week=$row['Fweek'];
   $wks[]=$week;
   echo $row['Satindex'] . "<br/>";
   $index = $row['Satindex'];
   if ($index > 6) 
   { 
      $sat[$week]++; 
   }
   else 
   { 
      $nonSat[$week]++; 
   }
   
}

foreach ($wks AS $wk)
{
    $satpercent = array_sum($sat[$wk]) / (count($sat[$wk])+count($nonSat[$wk])) * 100;
    $satpercent = (int)$satpercent;
    echo "Displaying fiscal week $wk";
    echo "<br/>";
    echo "Total: ".(array_sum($sat[$wk])+array_sum($nonSat[$wk]))." hits    whereof ".count($sat[$wk])." where satisfied and ".count($nonSat[$wk])." where non-satisfied";
    echo "<br/>";
    echo "This leaves us with a sat percentage of: $satpercent%";
    }
?>

 

I've never worked with array_sum before but the error seems pretty obvious to the error message:

 

Warning: array_sum() [function.array-sum]: The argument should be an array in F:\wamp\www\voca\index.php on line 27

 

Warning: array_sum() [function.array-sum]: The argument should be an array in F:\wamp\www\voca\index.php on line 31

 

Happens on every foreach loop of course, but seems like the variable $sat['$wk'] and $nonSat['$wk'] isnt treated as arrays, or am I out of my reach again?

 

Thanks for your input!

[mtoynbee]

As I said - this is the sort of error when you freestyle

 

If you change:

 

$sat[$week]++; to $sat[$week][]=1;

$nonSat[$week]++; to $nonSat[$week][]=1;

 

Not my best code I admit but hopefully will work.

 

I'll leave it to you to polish.