What are some best practices for storing form data in a database and exporting it to Excel using PHP?

When storing form data in a database and exporting it to Excel using PHP, it is important to properly sanitize and validate the input data to prevent SQL injection attacks. Additionally, you should use prepared statements to interact with the database to prevent any malicious code execution. When exporting the data to Excel, you can use a library like PHPExcel to generate the Excel file with the retrieved data. 

// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Sanitize and validate form data
$name = mysqli_real_escape_string($conn, $_POST['name']);
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);

// Insert form data into the database using prepared statements
$stmt = $conn->prepare("INSERT INTO form_data (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();

// Export data to Excel using PHPExcel
require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Email');

$row = 2;
$result = $conn->query("SELECT * FROM form_data");
while ($row_data = $result->fetch_assoc()) {
    $objPHPExcel->getActiveSheet()->setCellValue('A' . $row, $row_data['name']);
    $objPHPExcel->getActiveSheet()->setCellValue('B' . $row, $row_data['email']);
    $row++;
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('form_data.xlsx');

// Close the database connection
$conn->close();

Keywords

form data database Excel PHP export

Related Questions