MySQL PHP - variable for distinct value output

Question

I am using the query below to count the amount of records with distinct values in the "Period" column. The Period column contains either "Breakfast", "Lunch" or "Dinner", and the output of the formula is grouped by monthly columns and then one row for each distinct value:

Ttl. Feedback : Breakfast: XXX

Ttl. Feedback : Lunch: XXX

Ttl. Feedback : Dinner: XXX

Now, what I would need is a variable for each total number (see $mydata['total'] that is returned for Breakfast, Lunch and Dinner. The current variable only returns the total for each distinct value, and doesnt let me specifically address an individual period score.

    <?
    $whereParts = array();
    if ($property) {
        $whereParts[] = "Holidex = '$property' ";
    } 

    $whereParts = array();
    if ($outlet) {
        $whereParts[] = "Outlet = '$outlet' ";
    } 

    if ($pickyear) {
        $whereParts[] = "YEAR(Date) = '$pickyear' ";
    } 

    $sql = ("select distinct period from FB_Feedback_Card_Data");

    //BUILD THE FINAL QUERY
    if (count($whereParts) > 0) {
        $sql .= " WHERE " . implode('AND ', $whereParts);
    } 
    //count($whereParts) > 0

    $result = $mysqli->query($sql) or die('<p>Query to get case Period data from FB_Feedback_Card_Data table failed:' . mysql_error() . '</p>');

    while ($row = mysqli_fetch_row($result)) 
    {
        $period = $row[0];
        //echo "$status <br>";
        $mydata = stats("period", "$period", "$property", "$outlet", "$pickyear",$mysqli);

    ?>

<tr>
    <td class="result_desc"><?echo "Ttl. Feedback : $period";?></td>
    <td class="result"><? echo $mydata['a']; ?></td>
    <td class="result"><? echo $mydata['b']; ?></td>
    <td class="result"><? echo $mydata['c']; ?></td>
    <td class="result"><? echo $mydata['d']; ?></td>
    <td class="result"><? echo $mydata['e']; ?></td>
    <td class="result"><? echo $mydata['f']; ?></td>
    <td class="result"><? echo $mydata['g']; ?></td>
    <td class="result"><? echo $mydata['h']; ?></td>
    <td class="result"><? echo $mydata['i']; ?></td>
    <td class="result"><? echo $mydata['j']; ?></td>
    <td class="result"><? echo $mydata['k']; ?></td>
    <td class="result"><? echo $mydata['l']; ?></td>
    <td class="result_ytd"><strong><? echo $mydata['total']; ?></strong></td>
</tr>

<?
// end total status stats
}
?>

Nadir · Accepted Answer · 2016-04-13 10:59:16Z

1

You don't need to use DISTINCT for that, you need GROUP BY, which will put all entries with the same value on the same record, and then you only need to count each of them:

SELECT period, count(period) as period_count FROM FB_Feedback_Card_Data WHERE ... GROUP BY period

edited Apr 13, 2016 at 10:59

answered Apr 13, 2016 at 10:03

Nadir

1,81913 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Armitage2k Over a year ago

but how do I get the output for each distinct period into a variable? do I do this with $row[0], $row[1], etc?

Nadir Over a year ago

@Armitage2k I edited the query a bit so its easier to get the values from the array. You would do $periodName = $row['period']; $count = $row['period_count'];

Shimu · Accepted Answer · 2016-04-20 08:31:16Z

0

You can use the php function array_sum With this you get the sum from the $mydata variable.

<td class="result_ytd"><strong><?= array_sum($mydata); ?></strong></td>

The only thing is, that there must not be any other values in the array, but I think that this is the case here.

edited Apr 20, 2016 at 8:31

answered Apr 13, 2016 at 9:42

Shimu

1,16712 silver badges25 bronze badges

Collectives™ on Stack Overflow

MySQL PHP - variable for distinct value output

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related