Trying to obtain rankings

[phppup]

I'm trying to workout the logic before writing my code and I've hit a bump.

I'm setting up a table to receive votes and want to display the rankings.

Example: what's the best party item?

Each row will correlate to the person that submitted a vote.

The essential columns would be balloons, soda, ribbons, bows.

Voting for an item would INSERT a "1" (unless you're of a certain political party. LOL) into a cell.

Now, I want to SUM() each column [I think I can do that] and list them in descending order.

Can I do this in a statement? Or do I need to put the summed data into an array and then sort the array?

What's the best approach?

[Danishhafeez]

You can definitely achieve this directly with SQL using the SUM() function along with GROUP BY and ORDER BY. Here's a basic example of how you could structure your SQL query:

SELECT
    SUM(balloons) AS balloons_total,
    SUM(soda) AS soda_total,
    SUM(ribbons) AS ribbons_total,
    SUM(bows) AS bows_total
FROM
    votes
GROUP BY
    balloons, soda, ribbons, bows
ORDER BY
    balloons_total DESC, soda_total DESC, ribbons_total DESC, bows_total DESC;

This query will sum up the votes for each item (balloons, soda, ribbons, bows) separately, grouping them by their respective columns, and then order the results in descending order based on the total votes for each item.

You don't need to put the summed data into an array and sort it separately in your code; SQL can handle the sorting and aggregation for you.

Best Regard

Danish hafeez | QA Assistant

ICTInnovations

[Barand]

@Danishhafeez,

mysql> select * from votes;
+----+----------+------+---------+------+
| id | balloons | soda | ribbons | bows |
+----+----------+------+---------+------+
|  1 |        1 | NULL |       1 | NULL |
|  2 |        1 | NULL |       1 |    1 |
|  3 |        1 |    1 |    NULL | NULL |
|  4 |        1 | NULL |    NULL |    1 |
+----+----------+------+---------+------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->     SUM(balloons) AS balloons_total,
    ->     SUM(soda) AS soda_total,
    ->     SUM(ribbons) AS ribbons_total,
    ->     SUM(bows) AS bows_total
    -> FROM
    ->     votes
    -> GROUP BY
    ->     balloons, soda, ribbons, bows
    -> ORDER BY
    ->     balloons_total DESC, soda_total DESC, ribbons_total DESC, bows_total DESC;
+----------------+------------+---------------+------------+
| balloons_total | soda_total | ribbons_total | bows_total |
+----------------+------------+---------------+------------+
|              1 |          1 |          NULL |       NULL |
|              1 |       NULL |             1 |          1 |
|              1 |       NULL |             1 |       NULL |
|              1 |       NULL |          NULL |          1 |
+----------------+------------+---------------+------------+
4 rows in set (0.00 sec)

 

[Barand]

@phppup, Given that dog's breakfast of a table that you are storing as a spreadsheet in your data base, you can do it all with SQL but you need a different approach to that above.

(Using the same votes table as above)

SELECT opt as `Option`
     , SUM(vote) as Votes
FROM (
        SELECT 'Balloons' as opt
            ,  balloons as vote
        FROM votes
        UNION ALL
        SELECT 'Soda' as opt
            ,  soda as vote
        FROM votes
        UNION ALL
        SELECT 'Ribbons' as opt
            ,  ribbons as vote
        FROM votes
        UNION ALL
        SELECT 'Bows' as opt
            ,  bows as vote
        FROM votes
     ) data
GROUP BY opt
ORDER BY Votes DESC;

+-------------+-------------+
|  option     |  votes      |
+-------------+-------------+
| Balloons    |      4      |
| Ribbons     |      2      |
| Bows        |      2      |
| Soda        |      1      |
+-------------+-------------+

However, the correct way to do it to store normalized data so that each option that is voted for is stored in its own record. Here's a simple data model and query...

TABLE : poll                 TABLE: poll_option                           TABLE: vote            
+---------+-----------+      +--------+----------+-------------+     +-----+----------+-----------+
|   id    | name      |      | id     | poll_id  |  opt        |     | id  | user_id  | option_id |
+---------+-----------+      +--------+----------+-------------+     +-----+----------+-----------+                                             
|   1     | Pets      |      |   1    |    1     | Cat         |     |  1  |    1     |     4     |
|   2     | Parties   |      |   2    |    1     | Dog         |     |  2  |    1     |     6     |                                             
+---------+-----------+      |   3    |    1     | Iguana      |     |  3  |    2     |     4     |                                             
                             |   4    |    2     | Balloons    |     |  4  |    2     |     6     |                      
                             |   5    |    2     | Soda        |     |  5  |    2     |     7     |                      
                             |   6    |    2     | Ribbons     |     |  6  |    3     |     4     |                      
                             |   7    |    2     | Bows        |     |  7  |    3     |     5     |                         
                             +--------+----------+-------------+     |  8  |    4     |     4     |                      
                                                                     |  9  |    4     |     7     |
                                                                     +-----+----------+-----------+
SELECT o.opt as `option`
     , count(v.option_id) as votes                                                        
FROM poll_option o
     JOIN
     vote v ON o.id = v.option_id
WHERE o.poll_id = 2
GROUP BY o.opt
ORDER BY votes DESC

+-------------+-------------+
|  option     |  votes      |
+-------------+-------------+
| Balloons    |      4      |
| Ribbons     |      2      |
| Bows        |      2      |
| Soda        |      1      |
+-------------+-------------+

 

[phppup]

@Barand Got a little busy, but as always, your help is greatly appreciated.

It always looks so simple when you show the path.  And it seems you anticipated my next phase, which would be something along the line of "Choose 3 cities from the checkbox group that you would be most interested in visiting."

If I'm understanding your example, I can easily expand the "vote" table to accept more INSERTed data with each form submission

(This approach will NOT be inserting zero/null for unselected checkboxes, but without using 3 separate dropdown menus, what is the best/most correct way to format a smooth accumulation of selected itemsto be inserted into the table?)

I haven't had the time to test my actual code, but it would seem modifying it to operate in a normalized methodology will be easier than jumbling through my "dog's breakfast of a table" set-up.  LOL

Also from your reply, you use the character   "o"   , but I'm not sure I'm following its origin or meaning.  Can you clarify or link me to an explanation?

Thanks!!

Edited May 17 by phppup

[Barand]

10 minutes ago, phppup said:

Also from your reply, you use the character   "o"   , but I'm not sure I'm following its origin or meaning.  Can you clarify or link me to an explanation?

"o" and "v" are table aliases for the option and vote tables.

 

2 hours ago, phppup said:

what is the best/most correct way to format a smooth accumulation of selected itemsto be inserted into the table?)

First I made a couple of additions to the tables...

1. Poll table - added "polling_day" date column (so we now know the current poll when voting)
2. Vote table - added unique index on user_id/option_id to prevent double-voting.

CREATE TABLE `vote` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `option_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_vote_user_opt` (`user_id`,`option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `poll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `polling_day` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

The sample code below uses a query of the poll_option table to get the options for the current poll and generates the checkboxe for the form. The checkboxes are names "vote[]" so they are posted as an array.

Note that only "checked" checkboxes are posted so you just need to loop through the array of $POST['vote'] values and insert a vote record for each (with the id of the logged-in user.

CODE

<?php
require 'db_inc.php';                // USE YOUR OWN ...
$pdo = mdbConnect('db1');            // ... CONNECTION CODE

$_SESSION['user_id'] = 123;          // emulate user being logged in   - testing only.

################################################################################
## Handle posted form data   
################################################################################
    if ($_SERVER['REQUEST_METHOD'] == 'POST')  {
    
        // Use "INSERT IGNORE ... " so attemmpts to vote twice for an option are ignored
        $stmt = $pdo->prepare("INSERT IGNORE INTO vote(user_id, option_id) VALUES (?, ?)");
        
        foreach ($_POST['vote'] as $oid)  {
            $stmt->execute( [ $_SESSION['user_id'], $oid ] );
        }
        
        header("Refresh: 0");          // reload form
        exit;
        
    }

################################################################################
## Build arrays of vote options for the current poll 
################################################################################
    $res = $pdo->query("SELECT o.id, o.opt
                          FROM poll_option o
                          JOIN poll p ON o.poll_id = p.id
                          WHERE p.polling_day = CURDATE()
                          ORDER BY opt
                         ");

    $options = array_column($res->fetchAll(), 'opt', 'id');
    
    if (empty($options)) {
        exit ("Today is not a polling day");
    }
       
################################################################################
## Build check box list for the form   
################################################################################
    $chkboxes = '';
    foreach ($options as $val => $label)  {
        $chkboxes .= "<input type='checkbox' name='vote[]' value='$val'>&ensp;$label<br>";
    } 
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset="utf-8">
<title>Example</title>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style type='text/css'>
</style> 
</head>
<body>
<form method='POST'>
    <div class='w3-margin w3-padding w3-border'>
        <h3>Select your preferences</h3>
        <?=$chkboxes?>
        <br>
        <input type='submit'>
    </div>
</form>
</body>
</html>

 

[phppup]

@Barand

Quote

The checkboxes are names "vote[]" so they are posted as an array.

Ok. I had initially built my form in a similar manner with the values and labels coming from an array (which I expected to eventually generate from a table) anyway.

Quote

$pdo

Always confuses me. I know there are advantages to PDO (which I'll likely never be ready for), but I use procedural.