To build a filterable election-spending dashboard, import Federal Election Commission (FEC) data into a normalized MySQL database, aggregate it with PHP using PDO prepared statements, and return compact JSON for Chart.js to render. The essential safeguards are to label exactly which committees and spending measure a chart covers, keep total and adjusted disbursements distinct, and show when the underlying data was last imported.
How do I create a dynamic data visualization with PHP and MySQL?
Use a data pipeline with four parts: an authoritative source, a normalized database, a filtered aggregation endpoint, and a browser chart. The chart should receive already-aggregated points rather than thousands of raw transactions.
- Choose the scope. Decide whether the dashboard covers candidate committees, party committees, PACs, independent expenditures, or another defined population. Set the election cycle and date range, and decide whether you are measuring total or adjusted disbursements.
- Retrieve FEC data. Use OpenFEC, a REST service with candidate, committee, report, and contributor endpoints, or use FEC bulk downloads for larger imports. OpenFEC documentation says data are updated nightly. Keep the source filing or transaction identifier so imports can be reconciled and rerun safely.
- Normalize and import. Store candidates, committees, filings, and disbursements separately. Map source labels into a consistent internal taxonomy, preserve identifiers and transaction dates, and record the import time.
- Aggregate with PHP and SQL. Validate filter values, bind them through PDO prepared statements, and group transactions in SQL. Do not concatenate user input into SQL.
- Render the result. Return JSON containing labels and numeric values. In the browser, fetch that response and pass it to Chart.js.
PHP’s PDO interface offers a consistent database-access layer; this example assumes the PDO_MySQL driver is installed and enabled. Parameter markers bind data values, not SQL identifiers such as column names. If users can choose a grouping or sort field, select its SQL expression from a fixed allow-list.
A practical MySQL schema
The following is an application schema, not a claim that FEC source files use these exact column names. Adapt your importer to the fields and formats in the selected API response or bulk file. Store money as DECIMAL, not floating point, and preserve the original source identifier.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall#1 Best Overall
- Wiley
- Language: english
- Book - storytelling with data: a data visualization guide for business professionals
CREATE TABLE candidates (
candidate_id VARCHAR(16) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
state CHAR(2),
district VARCHAR(8)
) ENGINE=InnoDB;
CREATE TABLE committees (
committee_id VARCHAR(16) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
filer_type VARCHAR(24) NOT NULL,
candidate_id VARCHAR(16),
office VARCHAR(24),
state CHAR(2),
district VARCHAR(8),
CONSTRAINT fk_committee_candidate
FOREIGN KEY (candidate_id) REFERENCES candidates(candidate_id),
INDEX idx_committee_candidate (candidate_id),
INDEX idx_committee_type (filer_type)
) ENGINE=InnoDB;
CREATE TABLE filings (
filing_id VARCHAR(32) PRIMARY KEY,
committee_id VARCHAR(16) NOT NULL,
cycle SMALLINT NOT NULL,
report_period_start DATE,
report_period_end DATE,
filed_at DATETIME,
source_updated_at DATETIME,
CONSTRAINT fk_filing_committee
FOREIGN KEY (committee_id) REFERENCES committees(committee_id),
INDEX idx_filing_cycle_committee (cycle, committee_id)
) ENGINE=InnoDB;
CREATE TABLE disbursements (
disbursement_id VARCHAR(40) PRIMARY KEY,
filing_id VARCHAR(32) NOT NULL,
transaction_date DATE,
recipient VARCHAR(255),
purpose VARCHAR(500),
category VARCHAR(80),
amount DECIMAL(14,2) NOT NULL,
state CHAR(2),
source_updated_at DATETIME,
CONSTRAINT fk_disbursement_filing
FOREIGN KEY (filing_id) REFERENCES filings(filing_id),
INDEX idx_disbursement_date (transaction_date),
INDEX idx_disbursement_state (state),
INDEX idx_disbursement_amount (amount)
) ENGINE=InnoDB;
CREATE TABLE import_runs (
import_id BIGINT AUTO_INCREMENT PRIMARY KEY,
source_name VARCHAR(40) NOT NULL,
started_at DATETIME NOT NULL,
completed_at DATETIME,
status VARCHAR(20) NOT NULL
) ENGINE=InnoDB;Choose identifier lengths and field sizes to match the source records you ingest. If a source lacks a usable transaction identifier, create a stable deduplication key from documented source fields and retain the raw record or a source-file reference for auditability. A transaction can be corrected or amended; your import process needs a defined update policy rather than blindly adding each download to the totals.
Import in batches and make reruns safe
- Load source rows into a staging table or process a manageable batch at a time; avoid holding one transaction open for an entire large download.
- Validate dates, identifiers, and amounts during mapping. Keep rejected rows and reasons available for review instead of silently dropping them.
- Use the source identifier as the deduplication key. Apply inserts or updates deliberately so a refreshed source record replaces the prior version rather than counting twice.
- Update the import-run record on completion, including failures. Show a completed import timestamp in the dashboard, not just a page-render time.
How can I visualize election campaign spending?
Choose the chart only after defining the population and question. A total by cycle is different from recipient-level transactions, and neither should be labeled simply “election spending” without a scope.
| Question | Useful grouping | Suitable starting chart | Watch for |
|---|---|---|---|
| How did reported spending change over time? | Cycle or month | Line chart for a consistent time series; bar chart for discrete cycles | Candidate cycles differ by office: House elections use two-year cycles, presidential elections four-year cycles, and Senate elections six-year cycles. |
| Which candidates or committees reported the largest totals? | Candidate or committee | Sorted horizontal bar chart | Keep the filer population and cycle visible; committee totals are not automatically candidate totals. |
| Where did reported spending go? | Recipient, purpose, or disbursement category | Bar chart for a selected set of categories; table for detailed recipients | Recipient and purpose text may vary across filings, and grouping raw text can produce near-duplicate labels. |
| How does spending vary geographically? | State or district | Bar chart or map where the geography is defined and complete | Clarify whether geography means the committee’s location, candidate’s district, or a transaction-related field. |
| What is the scale of outside political spending? | Independent expenditures or electioneering communications | Separate bars or a dedicated view | These are distinct reporting measures and should not be merged into committee disbursement totals without an explicit methodology. |
Use a table alongside a chart when readers need exact amounts, downloadable records, or the ability to inspect recipients. For dense transaction histories, offer filters and pagination rather than plotting every record at once.
Display scope and freshness with the chart
Include the filer population, cycle, date range, and measure in the chart title or nearby caption. For example, identify whether a figure is candidate-committee total disbursements for a selected office and cycle, or an adjusted-disbursement measure. Add a visible “Data imported” timestamp tied to the most recent successful import. The FEC notes that newly filed summary data may not appear for up to 48 hours, so filings can arrive after the displayed chart was generated.
Free tools Windows power users keep installed
One-click scans. No signup required.
How do I get FEC spending data into a chart?
Keep source retrieval, database import, and chart rendering as separate jobs. The API is useful for targeted lookups and incremental updates; bulk downloads are suitable for larger-scale ingestion. In either case, preserve the source’s filing or transaction identifiers and map its fields into your database consistently. The FEC’s browse-data methodology covers Forms 3, 3P, and 3X and describes exclusions used in adjusted-disbursement calculations.
Do not assume that every measure called spending is interchangeable. The FEC spending dashboard’s overall total is the sum of disbursements from candidate committees for the selected office. Its browse-data methodology distinguishes disbursements, independent expenditures, electioneering communications, communication costs, and adjusted disbursements. If your dashboard reproduces an official figure, use the matching population, date scope, and methodology; otherwise label it as your own aggregation.
FEC figures for context
The Federal Election Commission’s 2025 figures below cover January 1, 2023 through December 31, 2024. The categories describe different filer populations or measures, so they are not components that can safely be added together as one undifferentiated total.
| FEC category or measure | Reported amount | Coverage |
|---|---|---|
| Presidential-candidate disbursements | $1.8 billion | FEC 2025; January 1, 2023–December 31, 2024 |
| Congressional-candidate disbursements | $3.7 billion | FEC 2025; January 1, 2023–December 31, 2024 |
| Political-party disbursements | $2.6 billion | FEC 2025; January 1, 2023–December 31, 2024 |
| PAC disbursements | $15.5 billion | FEC 2025; January 1, 2023–December 31, 2024 |
| Independent expenditures | $4.4265 billion | FEC 2025; January 1, 2023–December 31, 2024 |
These historical figures provide scale, not a substitute for a live dashboard query. Your imported dataset may differ in scope or timing, especially if recent filings have not yet appeared in the source summaries.
Build a safe JSON endpoint with PHP PDO
This endpoint aggregates total disbursements for one normalized filer type, cycle, and date range. Its group parameter is restricted to known SQL expressions; all user-provided values are bound. The internal filer-type labels in this example (candidate, party, and PAC) must be mapped from the source data by your importer.
<?php
declare(strict_types=1);
header('Content-Type: application/json; charset=utf-8');
$dsn = 'mysql:host=127.0.0.1;dbname=election;charset=utf8mb4';
$pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$cycle = filter_input(INPUT_GET, 'cycle', FILTER_VALIDATE_INT);
$start = $_GET['start'] ?? '';
$end = $_GET['end'] ?? '';
$population = $_GET['population'] ?? '';
$group = $_GET['group'] ?? 'month';
$populations = ['candidate', 'party', 'PAC'];
if ($cycle === false || $cycle === null || $cycle < 1970 || $cycle > 2100) {
http_response_code(400);
echo json_encode(['error' => 'Invalid cycle']);
exit;
}
$datePattern = '/^d{4}-d{2}-d{2}$/';
if (!preg_match($datePattern, $start) || !preg_match($datePattern, $end)
|| $start > $end || !in_array($population, $populations, true)) {
http_response_code(400);
echo json_encode(['error' => 'Invalid date range or population']);
exit;
}
// SQL expressions are selected from this fixed allow-list, never from raw input.
$groups = [
'month' => "DATE_FORMAT(d.transaction_date, '%Y-%m')",
'category' => 'd.category',
'state' => 'd.state',
];
if (!array_key_exists($group, $groups)) {
http_response_code(400);
echo json_encode(['error' => 'Invalid grouping']);
exit;
}
$groupExpression = $groups[$group];
$sql = "SELECT {$groupExpression} AS label, SUM(d.amount) AS value
FROM disbursements d
JOIN filings f ON f.filing_id = d.filing_id
JOIN committees c ON c.committee_id = f.committee_id
WHERE f.cycle = :cycle
AND c.filer_type = :population
AND d.transaction_date BETWEEN :start_date AND :end_date
GROUP BY label
ORDER BY value DESC
LIMIT 100";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'cycle' => $cycle,
'population' => $population,
'start_date' => $start,
'end_date' => $end,
]);
$rows = $stmt->fetchAll();
$points = array_map(static function (array $row): array {
return [
'label' => $row['label'] ?? 'Unspecified',
'value' => (float) $row['value'],
];
}, $rows);
echo json_encode([
'cycle' => $cycle,
'population' => $population,
'measure' => 'total disbursements',
'group' => $group,
'points' => $points,
], JSON_THROW_ON_ERROR);In production, validate dates as real calendar dates as well as matching the expected format, enforce sensible date-range limits, and handle database exceptions without returning credentials or internal error details. The simple year bounds are input sanity checks, not FEC cycle definitions. The date filter excludes transactions with no transaction date, so disclose that behavior or provide a separate treatment for undated rows. A 100-row limit is appropriate only for a summary; transaction-level results need pagination or a different export path.
This query labels the sum of stored transactions as total disbursements. It does not calculate adjusted disbursements. Do not add an “adjusted” option by changing the label alone: implement the applicable FEC methodology and exclusions, preserve its provenance, and test your result against a matching FEC scope.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Render the API response with Chart.js
After loading Chart.js through your application’s chosen package or asset setup, give the page a canvas and fetch the endpoint whenever a filter changes. This small example draws a bar chart from the JSON structure above; it intentionally uses the response’s measure and population in the title.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsBest Value
<canvas id="spending-chart" aria-label="Election spending chart"></canvas>
<script>
const params = new URLSearchParams({
cycle: '2024',
start: '2023-01-01',
end: '2024-12-31',
population: 'candidate',
group: 'month'
});
const response = await fetch(`/api/spending.php?${params}`);
if (!response.ok) throw new Error('Could not load spending data');
const result = await response.json();
new Chart(document.getElementById('spending-chart'), {
type: 'bar',
data: {
labels: result.points.map(point => point.label),
datasets: [{
label: `${result.population} ${result.measure}`,
data: result.points.map(point => point.value)
}]
},
options: {
responsive: true,
plugins: {
title: {
display: true,
text: `${result.population} ${result.measure}, ${result.cycle} cycle`
}
}
}
});
</script>When a user changes the cycle, date range, population, or grouping, issue a new request and update the existing chart rather than layering a new canvas chart on top. Include an accessible text summary or data table for readers who cannot use the chart, and format displayed amounts as currency while keeping the endpoint values numeric.
Keep large datasets responsive
Aggregation in MySQL is the first performance improvement: send Chart.js only the points needed for the selected view. For larger time series, Chart.js performance guidance recommends preparing data in its internal format, using parsing: false where appropriate, keeping indices sorted and consistent, setting normalized: true only when the data satisfy its requirements, and decimating dense series. These options do not replace database filtering or sensible limits.
Interpret totals without mixing unlike measures
The FEC dashboard’s overall total sums disbursements from candidate committees for the selected office. A broad sum over all committees in a database is not the same thing. In particular, set an office and cycle when reproducing candidate-office totals, and distinguish the FEC’s two-year House, four-year presidential, and six-year Senate candidate cycles in labels or filters.
Keep separate dashboard views or series for committee disbursements, independent expenditures, electioneering communications, communication costs, and adjusted disbursements. Adjusted totals depend on defined exclusions; use the FEC browse-data methodology for the relevant forms and measure rather than inferring adjustments from raw transaction categories. Name the filer population and method next to any chart that departs from the official dashboard definition.
Quick Recap
Common implementation failures to avoid
- Double-counted imports: repeated downloads can inflate totals if source identifiers are not used for upserts or deduplication.
- Misleading freshness: a nightly API refresh does not guarantee every newly filed summary is immediately visible; FEC says some may take up to 48 hours to appear.
- SQL injection through filters: bind values with PDO and map grouping or sorting identifiers through a fixed allow-list.
- Wrong scale or measure: distinguish total disbursements from adjusted disbursements and from independent expenditures and communication-related reporting.
- Overloaded charts: do not send every transaction to the browser when the reader needs a cycle or monthly summary; aggregate, filter, and paginate.
- Ambiguous geography: explain whether state or district refers to a candidate, committee, recipient, or another source field.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
