How to import excel file into mysql using php

To import an Excel file into MySQL using PHP, you can follow these steps:

1. Connect to your MySQL database using PHP’s mysqli extension.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

Use PHPExcel library to read the Excel file. You can download the library from http://phpexcel.codeplex.com/.

require_once 'PHPExcel/PHPExcel.php';

$inputFileType = 'Excel5'; // or Excel2007 if you have a newer version of Excel file
$inputFileName = 'example.xls';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);

Extract the data from the Excel file and insert it into the MySQL database.

$worksheet = $objPHPExcel->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    $values = array();
    foreach ($cellIterator as $cell) {
        $values[] = $cell->getValue();
    }

    $sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('" . implode("', '", $values) . "')";
    mysqli_query($conn, $sql);
}

mysqli_close($conn);

Note that this code is just an example and you should adjust it to fit your specific needs, such as specifying the correct table name, column names, and data types. Also, make sure to sanitize the data to prevent SQL injection attacks.

What is sanitize the data to prevent SQL injection attacks ?

Sanitizing the data means validating and cleaning the input data to prevent SQL injection attacks. SQL injection attacks occur when an attacker uses malicious input to manipulate the SQL query and access or modify data in the database.

To prevent SQL injection attacks, you can use prepared statements with parameterized queries. Here’s an example of how to use prepared statements to sanitize the data before inserting it into the database:

$stmt = mysqli_prepare($conn, "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)");

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    $values = array();
    foreach ($cellIterator as $cell) {
        $values[] = mysqli_real_escape_string($conn, $cell->getValue());
    }

    mysqli_stmt_bind_param($stmt, "sss", $values[0], $values[1], $values[2]);
    mysqli_stmt_execute($stmt);
}

mysqli_stmt_close($stmt);
mysqli_close($conn);

In this example, we use the mysqli_prepare() function to create a prepared statement with placeholders (?) for the values that will be inserted into the database. We then use mysqli_stmt_bind_param() to bind the values to the placeholders and mysqli_stmt_execute() to execute the statement.

Note that we also use the mysqli_real_escape_string() function to escape special characters in the input data. However, when using prepared statements, you don’t need to use mysqli_real_escape_string() because the prepared statement handles the escaping for you.