CSV to My SQL Database, All Data Upload
Publish: 18 June 2023, 4:15 am IST | Views: 
101
This is Sample Data
Change Your Choice Table Name, Open Database and Copy This Code and Paste and GO
CREATE TABLE persons (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
age INT
);
PHP Code for CSV Upload (upload.php):
<?php
// MySQL database configuration
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "persons";
// Check if form is submitted
if (isset($_POST["submit"])) {
// Check if a file is selected
if ($_FILES["file"]["name"]) {
$filename = $_FILES["file"]["tmp_name"];
$file_extension = pathinfo($_FILES["file"]["name"], PATHINFO_EXTENSION);
// Check if the uploaded file is a CSV
if ($file_extension == "csv") {
// Create a new PDO instance
$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare the INSERT statement
$stmt = $pdo->prepare("INSERT INTO persons (first_name, last_name, age) VALUES (?, ?, ?)");
// Open the CSV file
if (($handle = fopen($filename, "r")) !== FALSE) {
// Read each line of the CSV file
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
// Insert data into the database
$stmt->execute($data);
}
fclose($handle);
echo "CSV data imported successfully!";
} else {
echo "Error opening the CSV file.";
}
} else {
echo "Invalid file format. Please upload a CSV file.";
}
} else {
echo "No file selected. Please choose a file.";
}
}
?>
$dbname = "persons"; Change Your Table Name
<!DOCTYPE html>
<html>
<head>
<title>CSV Upload Form</title>
<style>
body {
font-family: Arial, sans-serif;
}
.container {
max-width: 400px;
margin: 0 auto;
padding: 20px;
border: 1px solid #ccc;
border-radius: 5px;
}
.container h2 {
text-align: center;
}
.container input[type="submit"] {
display: block;
margin: 0 auto;
padding: 10px 20px;
background-color: #4CAF50;
color: white;
border: none;
border-radius: 4px;
cursor: pointer;
}
</style>
</head>
<body>
<div class="container">
<h2>CSV Upload Form</h2>
<form action="upload.php" method="post" enctype="multipart/form-data">
<input type="file" name="file" accept=".csv">
<input type="submit" name="submit" value="Upload">
</form>
</div>
</body>
</html>
index.php and any Name
Display Data
display_records.php Create New File Name
<?php
// MySQL database configuration
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
$table = "persons";
$records_per_page = 20;
// Establish a database connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get the current page number from the query string
$current_page = isset($_GET['page']) ? $_GET['page'] : 1;
// Calculate the starting record index for the current page
$start_index = ($current_page - 1) * $records_per_page;
// Query to retrieve records with pagination
$query = "SELECT * FROM $table LIMIT $start_index, $records_per_page";
$result = $conn->query($query);
// Check if records exist
if ($result->num_rows > 0) {
// Display records
echo "<table class='records-table'>";
echo "<tr><th>First Name</th><th>Last Name</th><th>Age</th></tr>";
while ($row = $result->fetch_assoc()) {
// Output the record data (customize as needed)
echo "<tr>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "No records found.";
}
// Calculate the total number of pages
$query_total = "SELECT COUNT(*) AS total FROM $table";
$result_total = $conn->query($query_total);
$total_records = $result_total->fetch_assoc()['total'];
$total_pages = ceil($total_records / $records_per_page);
// Display pagination links
echo "<div class='pagination'>";
for ($i = 1; $i <= $total_pages; $i++) {
echo "<a href='display_records.php?page=$i'>$i</a> ";
}
echo "</div>";
// Close the database connection
$conn->close();
?>
<style>
.records-table {
width: 100%;
border-collapse: collapse;
}
.records-table th,
.records-table td {
padding: 8px;
text-align: left;
border-bottom: 1px solid #ddd;
}
.records-table th {
background-color: #f2f2f2;
font-weight: bold;
}
.pagination {
margin-top: 20px;
text-align: center;
}
.pagination a {
display: inline-block;
margin: 0 5px;
padding: 5px 10px;
background-color: #f7f7f7;
color: #333;
text-decoration: none;
border: 1px solid #ccc;
border-radius: 3px;
}
.pagination a:hover {
background-color: #ccc;
}
</style>
Add Your Database Connection
Categories: Uncategorized
