Basic Poll System PHP, SQL
👤 Sharing: AI
Okay, here's a basic poll system implemented using PHP and SQL (MySQL). I'll provide the code with explanations interspersed. This example covers creating the database table, displaying the poll, handling voting, and showing results.
**1. Database Setup (MySQL)**
First, you need to create a database and table to store the poll information.
```sql
-- Create a database (if it doesn't exist)
CREATE DATABASE IF NOT EXISTS poll_system;
-- Use the database
USE poll_system;
-- Create the polls table
CREATE TABLE IF NOT EXISTS polls (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
date_created DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Create the poll options table
CREATE TABLE IF NOT EXISTS poll_options (
id INT AUTO_INCREMENT PRIMARY KEY,
poll_id INT NOT NULL,
option_text VARCHAR(255) NOT NULL,
votes INT DEFAULT 0,
FOREIGN KEY (poll_id) REFERENCES polls(id) ON DELETE CASCADE
);
-- Example Poll Data (Insert a sample poll for testing)
INSERT INTO polls (title) VALUES ('What is your favorite color?');
-- Get the ID of the newly inserted poll
SET @poll_id = LAST_INSERT_ID();
-- Insert poll options
INSERT INTO poll_options (poll_id, option_text) VALUES
(@poll_id, 'Red'),
(@poll_id, 'Blue'),
(@poll_id, 'Green'),
(@poll_id, 'Yellow');
```
**Explanation of SQL:**
* `CREATE DATABASE IF NOT EXISTS poll_system;`: Creates a database named `poll_system` if one doesn't already exist.
* `USE poll_system;`: Tells MySQL to use the `poll_system` database for subsequent commands.
* `CREATE TABLE polls`: Creates the `polls` table:
* `id`: A unique identifier for each poll (auto-incrementing primary key).
* `title`: The title of the poll.
* `date_created`: Timestamp of when the poll was created.
* `CREATE TABLE poll_options`: Creates the `poll_options` table:
* `id`: Unique ID for the option.
* `poll_id`: Foreign key referencing the `polls` table. This links each option to a specific poll. The `ON DELETE CASCADE` clause means that if a poll is deleted, all its associated options will also be deleted.
* `option_text`: The text of the poll option (e.g., "Red", "Blue").
* `votes`: The number of votes this option has received.
* `INSERT INTO polls`: Inserts a sample poll into the `polls` table.
* `SET @poll_id = LAST_INSERT_ID()`: Gets the auto-generated ID of the last inserted row (the poll we just created) and stores it in a variable `@poll_id`. This is needed because we need to reference that poll's ID when inserting the options.
* `INSERT INTO poll_options`: Inserts the poll options, using the `@poll_id` to link them to the correct poll.
**2. PHP Code (index.php)**
This PHP code will:
1. Connect to the database.
2. Fetch the poll and its options.
3. Display the poll and a form for voting.
4. Handle the voting when the form is submitted.
5. Display the results after voting.
```php
<?php
// Database connection details
$host = 'localhost';
$username = 'your_username'; // Replace with your database username
$password = 'your_password'; // Replace with your database password
$database = 'poll_system';
// Establish database connection
$conn = new mysqli($host, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Function to get the poll data (first poll found)
function getPoll($conn) {
$sql = "SELECT * FROM polls ORDER BY date_created DESC LIMIT 1"; // Get the most recent poll
$result = $conn->query($sql);
if ($result->num_rows > 0) {
return $result->fetch_assoc();
} else {
return null; // No polls found
}
}
// Function to get the poll options
function getPollOptions($conn, $poll_id) {
$sql = "SELECT * FROM poll_options WHERE poll_id = ?";
$stmt = $conn->prepare($sql); // Use prepared statements to prevent SQL injection
$stmt->bind_param("i", $poll_id); // "i" means integer
$stmt->execute();
$result = $stmt->get_result();
$options = [];
while ($row = $result->fetch_assoc()) {
$options[] = $row;
}
return $options;
}
// Function to handle voting
function handleVote($conn, $option_id) {
$sql = "UPDATE poll_options SET votes = votes + 1 WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $option_id);
if ($stmt->execute()) {
return true;
} else {
return false;
}
}
// --- Main Logic ---
$poll = getPoll($conn);
if (!$poll) {
echo "No polls available.";
exit;
}
$poll_id = $poll['id'];
$options = getPollOptions($conn, $poll_id);
// Check if the user has already voted (using a cookie for simplicity)
$voted = isset($_COOKIE['poll_voted_' . $poll_id]);
// Handle form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
if (!$voted) { // Only process the vote if the user hasn't already voted
if (isset($_POST['poll_option'])) {
$selected_option_id = $_POST['poll_option'];
if (handleVote($conn, $selected_option_id)) {
// Set a cookie to remember the vote (expires in 30 days)
setcookie('poll_voted_' . $poll_id, 'true', time() + (86400 * 30), "/");
$voted = true; // Update the voted status
} else {
echo "Error processing your vote.";
}
} else {
echo "Please select an option.";
}
} else {
echo "You have already voted in this poll.";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Simple Poll</title>
<style>
body { font-family: sans-serif; }
.poll-container { width: 500px; margin: 20px auto; border: 1px solid #ccc; padding: 20px; }
.poll-title { font-size: 20px; margin-bottom: 10px; }
.poll-option { margin-bottom: 5px; }
.results-container { margin-top: 20px; }
.result-bar { background-color: #4CAF50; color: white; padding: 5px; text-align: center; }
</style>
</head>
<body>
<div class="poll-container">
<h2 class="poll-title"><?php echo htmlspecialchars($poll['title']); ?></h2>
<?php if (!$voted): ?>
<form method="post">
<?php foreach ($options as $option): ?>
<div class="poll-option">
<input type="radio" id="option_<?php echo $option['id']; ?>" name="poll_option" value="<?php echo $option['id']; ?>">
<label for="option_<?php echo $option['id']; ?>"><?php echo htmlspecialchars($option['option_text']); ?></label>
</div>
<?php endforeach; ?>
<button type="submit">Vote</button>
</form>
<?php else: ?>
<p>Thank you for voting!</p>
<?php endif; ?>
<div class="results-container">
<h3>Results:</h3>
<?php
$totalVotes = 0;
foreach ($options as $option) {
$totalVotes += $option['votes'];
}
foreach ($options as $option):
$percentage = ($totalVotes > 0) ? round(($option['votes'] / $totalVotes) * 100, 2) : 0;
?>
<div class="result-bar" style="width: <?php echo $percentage; ?>%;">
<?php echo htmlspecialchars($option['option_text']); ?> - <?php echo $percentage; ?>% (<?php echo $option['votes']; ?> votes)
</div>
<?php endforeach; ?>
</div>
</div>
</body>
</html>
<?php
// Close the database connection
$conn->close();
?>
```
**Explanation of PHP Code:**
* **Database Connection:** The first part of the code establishes a connection to your MySQL database using `mysqli`. *Replace `your_username` and `your_password` with your actual database credentials.* It checks for connection errors and exits if it fails.
* **`getPoll()` Function:** Retrieves the most recent poll from the `polls` table. The SQL query uses `ORDER BY date_created DESC LIMIT 1` to ensure we get the latest poll.
* **`getPollOptions()` Function:** Retrieves the options for a specific poll (identified by its `poll_id`) from the `poll_options` table. **Important:** It uses a *prepared statement* to prevent SQL injection. Prepared statements are a crucial security measure. The `bind_param("i", $poll_id)` binds the `$poll_id` variable as an integer to the query.
* **`handleVote()` Function:** Updates the `votes` count for a selected poll option. It *also* uses a prepared statement for security.
* **Main Logic:**
* Gets the poll and options using the functions.
* Checks if the user has already voted using a cookie named `poll_voted_{poll_id}`.
* If the form is submitted (POST request) and the user hasn't voted yet:
* Retrieves the selected option ID from `$_POST['poll_option']`.
* Calls `handleVote()` to update the vote count in the database.
* Sets a cookie to prevent the user from voting again (you could also use sessions or a database to track votes).
* Updates the `$voted` flag.
* **HTML Output:**
* Displays the poll title.
* If the user hasn't voted, it displays a form with radio buttons for each option.
* If the user *has* voted, it displays a thank-you message.
* Displays the poll results, including the percentage of votes for each option. The `style="width: ..."` attribute dynamically sets the width of the result bar based on the vote percentage.
* **Security:** The code uses prepared statements to prevent SQL injection vulnerabilities. Also uses `htmlspecialchars()` to prevent XSS vulnerabilities.
* **Database Closing:** Closes the database connection with `$conn->close()`.
**How to Run:**
1. **Save:** Save the PHP code as `index.php` in a directory on your web server.
2. **Database Setup:** Execute the SQL script (using a tool like phpMyAdmin or the MySQL command-line client) to create the database and tables.
3. **Configure:** Update the `$host`, `$username`, `$password`, and `$database` variables in `index.php` with your actual database credentials.
4. **Access in Browser:** Open the `index.php` file in your web browser (e.g., `http://localhost/poll_system/index.php`).
**Important Considerations and Improvements:**
* **Error Handling:** The code includes basic error handling (checking the database connection). You should add more robust error handling to catch potential exceptions and display user-friendly error messages.
* **Security:**
* **SQL Injection:** The provided code uses prepared statements, which is a good start for preventing SQL injection. Always be cautious about user input and properly escape or validate it.
* **Cross-Site Scripting (XSS):** The code uses `htmlspecialchars()` to escape output, which helps prevent XSS.
* **CSRF:** Consider adding Cross-Site Request Forgery (CSRF) protection to your form.
* **Voting Restrictions:** The current cookie-based voting restriction is very basic and can be easily bypassed. For a more robust system, you should store voting information in the database, potentially using user accounts (if you have user authentication) or IP addresses (though IP-based restrictions can be unreliable due to shared IPs).
* **Sessions:** Using PHP sessions instead of cookies is a better approach for storing user-specific data. Cookies are stored on the client's machine, while sessions are stored on the server.
* **User Authentication:** For a more advanced system, you'd want to implement user authentication so that you can track votes by user account.
* **Multiple Polls:** The current code only displays one poll (the most recent one). You'd need to modify the code to allow users to select from a list of available polls.
* **Admin Interface:** You'll need an admin interface to create, edit, and delete polls and options.
* **CSS Styling:** The CSS is very basic. Use CSS to style the poll to make it look more visually appealing.
* **JavaScript:** You could use JavaScript to enhance the user experience, such as displaying the results dynamically without requiring a page reload.
* **Database Design:** The `polls` and `poll_options` structure is suitable for a basic poll. For more complex scenarios (e.g., ranked choice voting, multiple-choice questions), you might need to adjust the database schema.
This example provides a solid foundation for building a more complete poll system. Remember to prioritize security and add the features needed for your specific use case.
👁️ Viewed: 10
Comments