PHP calculation

Question

kyleo92 0 Newbie Poster

11 Years Ago

I'm creating a table with PHP/MySql to enter local election results from my newspaper, I'm not a professional developer, more of just a hobbiest hack (who hasn't worked with PHP for a few years either), so feel free to correct existing code if you so please.

What I want to do is to list vote totals for each precinct, and then have PHP total those totals.

So, the way the data base would be set up is Race ID, Candidate Name, and each available precinct's totals.

This is my current code to return one of the tables. What I would need to do is set up several calculations, one for each candidate, and then be able to show a total at the end of the row on the table. If percent of vote is not too much more difficult, I'd like to do that to, but I'm wiling to stop at vote total if it's much more of a headache.

<div class="racestyle">
<h4>Centerville - Mayor</h4>
<table class="results">
<tr><td class='candidate'>Name</td><td class='numbers'>Ward 1</td><td class='numbers'>Ward 2</td><td class='numbers'>Ward 3</td><td class='numbers'>Absentee</td><td class='numbers'>Total</td></tr>
<?php
// Connects to your Database
mysql_connect("SERVER", "USER", "PASS") or die(mysql_error());
mysql_select_db("DATABASE") or die(mysql_error());
$data = mysql_query("SELECT * FROM `13results` WHERE `Race` = 'CVmayor'")
or die(mysql_error());

while($info = mysql_fetch_array( $data ))
{
Print "<tr><td class='candidate'>".$info['Name'] . "</td><td class='numbers'>".$info['Ward1'] . "</td><td class='numbers'>".$info['Ward2'] . "</td><td class='numbers'>".$info['Ward3'] . "</td><td class='numbers'>".$info['Absentee'] . "</td><td class='numbers'>TOTALHERE</td></tr>";
}
?>
</table>
</div>

mathematics php

2 Contributors
20 Replies
338 Views
15 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by diafol

diafol

11 Years Ago

Are the number of wards the same for every 'race'? If not, then the html table will need to be created dynamically. Anyway I'd advise you to stop mixing html and php and to concatenate output to a variable before echoing...

<?php
//do all your php calcs and loops
//table body in variable called $table
?>
<!doctype html>
...
<table>
<thead>...</thead>
<tbody><?php echo $table;?></tbody>
</table>
...

You can either get MySQL to do the totals or PHP, for this I'd probably let MySQL do it...

SELECT *, Ward1 + Ward2 + Ward3 + Absentee AS race_total FROM `13results` WHERE `Race` = 'CVmayor'

So the last html table column will be filled with $info['race_total']

Percentage for each again can be calualated in MySQL or PHP
That will be slightly different as I think you'll need to use a GROUP BY query.

...Ward1/SUM(Ward1)*100 AS W1P, Ward2/SUM(Ward2)*100 AS W2P...

diafol

11 Years Ago

OK because you have variable wards, I would suggest a more complicated schema, but if you want to keep it simple, don't worry. However, just to give you an idea of what I had in mind...

Candidates

cand_id
party_id
firstname
surname
(other fields)

Parties

party_id
party_name
party_image
(other fields)

Races

race_id
racetype_id
dateheld
area
(other fields)

RaceType

racetype_id
racetype_name

RaceWards

raceward_id
race_id
raceward_num
raceward_name

RaceResults

raceresult_id
raceward_id
cand_id
votes

diafol

11 Years Ago

For an easy set up, sod it and go with what you've got. Otherwise you'll be up to your neck in INNER JOINS and complex form processing. I wouldn't normally suggest this, but if you've got a maximum of 4 wards, then you can make it work. The issue really is when you need to add more wards - that would probably entail changing the php, which is not what you want.

So armed with this info, you need to check the max number of wards for the race. I'm assuming that you fill sequentially 1->4 and not randomally, so if we find that all candidates for a race do not have Ward4 filled, then we can assume that the race is a "3-warder" and we can build out html table accordingly.

In order to help further, could you give the DB schema? The tables and fields and perhaps give a sample row of data for each?

diafol

11 Years Ago

That was off the top of my head, so I'll build a replica of yours and do a quick test. Will come back to you. BTW - not sure how "absentee" is supposed to fit in here. I was assuming that absentee referred to spoiled votes or population not voting. If this is the case, then obviously it shouldn't be in the results table as it wouldn't pertain to any particular candidate.

Edited 11 Years Ago by diafol

diafol

11 Years Ago

OK - this works for me:

<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make daniweb the current db
$db_selected = mysql_select_db('daniweb', $link);
if (!$db_selected) {
    die ('Can\'t use daniweb : ' . mysql_error());
}

$ops = "";
$table = '';
$title = '';

$selNo = (isset($_GET['selrace'])) ? intval($_GET['selrace']) : 0;

    $r = mysql_query("SELECT * FROM races ORDER BY `race_date`, `race_name`");
    while($d = mysql_fetch_assoc($r))
    {
        $sel = ($selNo == $d['race_id']) ? ' selected' : '';
        $ops .= "\n\t<option value='{$d['race_id']}'$sel>{$d['race_name']} - {$d['race_date']}</option>";
        if($selNo == $d['race_id']) $title = "<h3>{$d['race_name']} on {$d['race_date']}</h3>";   
    }

    if($selNo !== 0){

        $r = mysql_query("SELECT v.candidate, v.ward1, v.ward2, v.ward3, v.ward4, absentees, (IFNULL(v.ward1,0) + IFNULL(v.ward2,0)+IFNULL(v.ward3,0)+IFNULL(v.ward4,0)+IFNULL(v.absentees,0))/t.total_value*100 AS pc
        FROM (SELECT IFNULL(SUM(ward1),0)+IFNULL(SUM(ward2),0)+IFNULL(SUM(ward3),0)+IFNULL(SUM(ward4),0)+IFNULL(SUM(absentees),0) AS total_value FROM race_results WHERE race_id=$selNo) AS t
     CROSS
      JOIN race_results AS v WHERE race_id = $selNo ORDER BY pc DESC");
        $table .= "<table><thead><tr><th>Candidate</th><th>Ward1</th><th>Ward2</th><th>Ward3</th><th>Ward4</th><th>Absentees</th><th>%Vote</th></tr></thead><tbody>";
        while($d = mysql_fetch_assoc($r))
        {
            $table .= "<tr><td>{$d['candidate']}</td><td>{$d['ward1']}</td><td>{$d['ward2']}</td><td>{$d['ward3']}</td><td>{$d['ward4']}</td><td>{$d['absentees']}</td><td>{$d['pc']}</td></tr>";   
        }
        $table .= "</tbody></table>";
    }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style>
    table{
        border-collapse:collapse;   
    }
    table th{
        background-color: yellow;   
    }
    table th, table td{
        border: 1px black solid;    
    }
</style>
</head>

<body>
<form>
<select name="selrace">
    <?php echo $ops;?>
</select>
<input type="submit" value="Show" />
</form>

<?php

    echo $title . $table;
?>
</body>
</html>

Note - although I've done quite a bit on this, it's not meant as a compelte solution. There's still enough to do. At the moment we still have 4 wards and absentees on each race, regardless of the number of wards. This can be remedied easily from testing the first record and seeing whether the wards are NULL or not. I deliberately didn't set the default values to 0 as this could suggest that there were always 4 wards but that candidates scores 0 votes in them. You may find a better way.

BTW - int(11) is an extreme number of digits - that was just the default, I suggest you change them to something more suitable.

Edited 11 Years Ago by diafol

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kyleo92 0 Newbie Poster · Answer 1 · 2013-10-24T06:41:29+00:00

Yeah, some races have two "wards", others have four "wards."

I'll work on getting it to the method in which you describe...I've wanted to stop mixing PHP and HTML for a while, just never put in the effort to learn. I'll start working on it, thanks!

kyleo92 0 Newbie Poster · Answer 2 · 2013-10-24T06:59:45+00:00

If that would be better in the long run and we can make it work, that's fine with me. The only thing that really matters is entering results has to be pretty seemless (dealing with one database) as I will be doing it from the auditor's office as results roll in.

kyleo92 0 Newbie Poster · Answer 3 · 2013-10-24T07:01:35+00:00

To give you an idea as well, there are a total of 23 races. Four of those will have the four precincts/wards, the other 19 races just have two precincts/wards

kyleo92 0 Newbie Poster · Answer 4 · 2013-10-24T16:41:46+00:00

#   Column      Type    Collation   Attributes  Null    Default
1   Race        text    latin1_swedish_ci       No      None
2   Name        text    latin1_swedish_ci       No      None
3   Ward1       int(11)                         Yes     NULL
4   Ward2       int(11)                         Yes     NULL
5   Ward3       int(11)                         Yes     NULL
6   Absentee    int(11)                         Yes     NULL
7   Cincinnati  int(11)                         Yes     NULL
8   Mystic      int(11)                         Yes     NULL
9   Unionville  int(11)                         Yes     NULL
10  Plano       int(11)                         Yes     NULL
11  Numa        int(11)                         Yes     NULL
12  Exline      int(11)                         Yes     NULL
13  Udell       int(11)                         Yes     NULL
14  Rathbun     int(11)                         Yes     NULL
15  Moulton     int(11)                         Yes     NULL
16  Win         int(11)                         Yes     NULL

kyleo92 0 Newbie Poster · Answer 5 · 2013-10-24T16:45:04+00:00

Race    Name    Ward1   Ward2   Ward3   Absentee    Cincinnati  Mystic  Unionville  Plano   Numa    Exline  Udell   Rathbun Moulton Win
CVMayor Jan Spurgeon*   20  10  13  27  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
CINCouncil  Patricia A. Fowler  NULL    NULL    NULL    10  15  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
EXMayor Jim Casteel NULL    NULL    NULL    25  NULL    NULL    NULL    NULL    NULL    10  NULL    NULL    NULL    NULL

Hopefully the above table displays ok.

Basically, the idea was to enter the results and then be able to declare a winner in the last row to put a check mark by the winner or something.

Thanks again in advance for all of your guidance!

diafol · Answer 6 · 2013-10-24T17:23:53+00:00

OK, but I don't understand the rationale. You don't seem to have any primary key or an 'id' field. Everything seems to be dumped into one table, making duplication errors extremely likely. Perhaps it's me, but I don't understand why you have City names as fieldnames.

For a cutdown version of the schema I suggested - I was thinking something like this...

races

race_id (PK | INT)
race_name (VARCHAR)
race_date (DATE)

results

result_id (PK | INT)
race_id (foreign key | INT)
candidate_name (VARCHAR)
Ward1 (INT)
Ward2 (INT)
Ward3 (INT)
Ward4 (INT)

You could, cheekily have a non-normalized field for winner, to save calculating this on the fly every time...

results

result_id (PK | INT)
race_id (foreign key | INT)
candidate_name (VARCHAR)
Ward1 (INT)
Ward2 (INT)
Ward3 (INT)
Ward4 (INT)
Winner (TINYINT)

So armed with this you could create a dropdown for all the races - created by a php loop, and then on selection or form submission, display the relevant data for the race. No need to hard-code anything. Example dropdown...

<?php
$ops = "";
$selNo = (isset($_POST['selrace'])) ? intval($_POST['selrace']) : 0;
$r = mysql_query("SELECT * FROM races ORDER BY `race_date`, `race_name`");
while($d = mysql_fetch_assoc($r))
{
    $sel = ($selNo ===$d['race_id']) ? ' selected' : '';
    $ops .= "\n\t<option value='{$d['race_id']}'$sel>{$d['race_name']} - {$d['race_date']}</option>"
}
?>

<select name="selrace">
    <?php echo $ops;?>
</select>

kyleo92 0 Newbie Poster · Answer 7 · 2013-10-24T17:42:10+00:00

Thanks, it's mostly a "noob" thing...I can see the advantages of using multiple databases over just one, just have never done anything over very basic PHP before. I have a few weeks until the election so it's a good time to learn it.

I'm glad you brought up drop downs. In my limited testing, it became apparent 23 races is much too many for one page.

kyleo92 0 Newbie Poster · Answer 8 · 2013-10-24T18:37:43+00:00

So, question...I would only create 4 wards right?

Basically, for the large town on my area's elections, there are three different wards with absentee ballots. For the smaller towns there is only their city hall with absentee ballots.

So, for the purpose of this, though, have a column for each individual precinct/ward/town isn't necessary, correct?

kyleo92 0 Newbie Poster · Answer 9 · 2013-10-24T19:05:16+00:00

Alright, this is what I ended up with from the code. I got the drop down to display, but it's empty.

<?php
$con=mysqli_connect("XXXXXX","XXXXXX","XXXXXX","XXXXXX");
$ops = "";
$selNo = (isset($_POST['selrace'])) ? intval($_POST['selrace']) : 0;
$r = mysql_query("SELECT * FROM races ORDER BY `race_date`, `race_name`");
while($d = mysql_fetch_assoc($r));
{
    $sel = ($selNo ===$d['race_id']) ? ' selected' : '';
    $ops .= "\n\t<option value='{$d['race_id']}'$sel>{$d['race_name']} - {$d['race_date']}</option>";
}
?>
<select name="selrace">
    <?php echo $ops;?>
</select>

"races"

   #    Column      Type        Collation   Attributes  Null    Default Extra 1 race_id     int(3)                              No      None    AUTO_INCREMENT
2   race_name   varchar(40) latin1_swedish_ci       No      None    
3   race_date   date                                No      None

1 Centerville Mayor 2013-11-05

"results"

#   Column          Type        Collation   Attributes  Null    Default Extra
1   result_id       int(3)                              No      None    AUTO_INCREMENT
2   race_id         int(3)                              No      None    
3   candidate_name  varchar(50) latin1_swedish_ci        No     None    
4   Ward1           int(11)                             Yes     NULL    
5   Ward2           int(11)                             Yes     NULL    
6   Ward3           int(11)                             Yes     NULL    
7   Ward4           int(11)                             Yes     NULL

1 1 Jan Spurgeon 1 2 3 4

The race_id in "results" is related to the race_id" in "races." Both tables are Inno DB.

kyleo92 0 Newbie Poster · Answer 10 · 2013-10-24T20:25:25+00:00

Absentee are people who vote by mail prior to the electiond date. So, they are valid voters, but they don't go to the polls on election day to vote.

diafol · Answer 11 · 2013-10-24T20:46:02+00:00

OK that's clear now, so in a way, they count as a non-geographical ward. Right, I've got the SQL for the percentage calculations - that had me scratching my head! Back soon, after updating with absentees

kyleo92 0 Newbie Poster · Answer 12 · 2013-10-24T20:47:35+00:00

Yes, that's a good way of thinking about it.

That's awesome, I really, really appreciate all of the help you've provided with me!

diafol · Answer 13 · 2013-10-24T20:54:35+00:00

For the table:

CREATE TABLE `race_results` (
  `result_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `race_id` INT(11) DEFAULT NULL,
  `candidate` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ward1` INT(11) DEFAULT NULL,
  `ward2` INT(11) DEFAULT NULL,
  `ward3` INT(11) DEFAULT NULL,
  `ward4` INT(11) DEFAULT NULL,
  PRIMARY KEY (`result_id`)
) ENGINE=INNODB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

here's the query...

SELECT v.candidate, v.ward1, v.ward2, v.ward3, v.ward4, absentees, (IFNULL(v.ward1,0) + IFNULL(v.ward2,0)+IFNULL(v.ward3,0)+IFNULL(v.ward4,0)+IFNULL(v.absentees,0))/t.total_value*100 AS pc
    FROM (SELECT IFNULL(SUM(ward1),0)+IFNULL(SUM(ward2),0)+IFNULL(SUM(ward3),0)+IFNULL(SUM(ward4),0)+IFNULL(SUM(absentees),0) AS total_value FROM race_results WHERE race_id=$race_id) AS t
 CROSS
  JOIN race_results AS v WHERE race_id = $race_id ORDER BY pc DESC

Tested - works - I'm sure it can be simplified - but it's getting late. You can try this yourself in your own DB GUI (e.g. phpmyadmin) - just substitute the $race_id (in both places) for a valid race_id integer.

Will test the dropdown...

kyleo92 0 Newbie Poster · Answer 14 · 2013-10-24T21:36:18+00:00

Thank you so much! I believe YOU have gotten me to a point where I can finish up. I had learned a lot about this stuff because of you, wouldn't have been able to do it without your help! Never been able to have PHP do all of the work for me, have always just had multiple connections/queries that just wasn't the best way to do it.

Really, really appreciate your help!

diafol · Answer 15 · 2013-10-24T21:38:50+00:00

You're welcome. I don't usually take off like that - I was bored ;)