Php Function to Upload Csv to Mysql Db
PHP is widely used for building a broad range of products ranging from web apps to enterprise level applications. The key to efficient PHP code is to follow proper workflows and automate processes. The issue is high quality and bug-complimentary lawmaking.
In almost all PHP applications, data is stored, accessed and exchanged between various components of the app. To make sure that this exchange and access to information goes smoothly and without any issues, the evolution squad must make sure that the databases and information dumps are in proper format.
Import and export of data to and from databases is a common enough procedure in PHP development. Some other important activity is the fill-in and transfer of databases.
In this article, I volition explain how to save tables from CSV files to MySQL and vice versa. You need to signup at Cloudways to launch a server and PHPstack awarding. Before signing up, looking at all the pricing options from the world-form hosting providers similar AWS, DigitalOcean, Linode, Vultr and GCP is a adept thought so y'all tin discover the ane that perfectly fits your needs.
PHP Hosting: All-time PHP 7 & PHP five.6 Web Hosting
Create a Database in MySQL
The first footstep in this tutorial is the creation of a MySQL database. Since Cloudways provides the custom mysql manager in the platform which contains a database for app. yous can create tables by running SQL queries. Create a table ` employeeinfo` in database using the post-obit SQL query.
CREATE TABLE employeeinfo( emp_id VARCHAR(50) UNSIGNED PRIMARY Cardinal, firstname VARCHAR(30) NOT Cypher, lastname VARCHAR(xxx) NOT Cypher, email VARCHAR(50), reg_date VARCHAR(50) )
This will create a new table ` employeeinfo` in the database. I volition use this table to insert data from the CSV file.
Stop Wasting Fourth dimension on Servers
Cloudways handle server management for you and so y'all can focus on creating great apps and keeping your clients happy.
Create MySql Connection in PHP
For importing and exporting database in MySql volition make a separate file `config.php`. Add the following code and supersede the database credentials with yours. You can notice your db credentials in Awarding Access details:
<?php office getdb(){ $servername = "localhost"; $username = "huscqxzwaw"; $password = "2WWKxxxxHr"; $db = "huscqxzwaw"; effort { $conn = mysqli_connect($servername, $username, $password, $db); //echo "Connected successfully"; } catch(exception $eastward) { repeat "Connectedness failed: " . $e->getMessage(); } return $conn; } ?> Related: How To Connect MySQL Database With PHP Websites
Import CSV to MySQL in PHP
Later on the database has been created, I next need an HTML file that could upload CSV file. For this HTML file, I will utilise HTML File uploader in a simple bootstrap form.
Create a file and proper name it ` index.php` . This is a unproblematic form for uploading CSV file. This file will likewise show the results in a elementary table on the same page. When the user submits the class, all records will be saved in the database.
First, I volition add together Bootstrap CDN to index.php .
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="bearding"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.seven/css/bootstrap-theme.min.css" crossorigin="anonymous"> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.three.seven/js/bootstrap.min.js" crossorigin="bearding"></script>
Side by side, in the ` body` tag, add the following HTML code for the Bootstrap course.
<!DOCTYPE html> <html lang="en"> <head> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/three.three.7/css/bootstrap.min.css" crossorigin="anonymous"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.iii.7/css/bootstrap-theme.min.css" crossorigin="bearding"> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/three.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script> </head> <body> <div id="wrap"> <div class="container"> <div course="row"> <form class="form-horizontal" action="functions.php" method="post" name="upload_excel" enctype="multipart/form-data"> <fieldset> <!-- Course Name --> <legend>Form Name</fable> <!-- File Push --> <div class="form-grouping"> <characterization course="col-md-4 command-label" for="filebutton">Select File</label> <div class="col-md-4"> <input type="file" proper noun="file" id="file" class="input-big"> </div> </div> <!-- Push button --> <div class="form-group"> <label class="col-physician-4 control-label" for="singlebutton">Import information</label> <div course="col-medico-4"> <button type="submit" id="submit" proper name="Import" course="btn btn-primary button-loading" data-loading-text="Loading...">Import</button> </div> </div> </fieldset> </form> </div> <?php get_all_records(); ?> </div> </div> </torso> </html>
You might notice that I have set an action to ` functions.php` file. In the side by side step, I volition create this file and add lawmaking to information technology. I accept also include a method ` get_all_records()` near the end of the file. This method fetches all the records from the database and display the records in the table on the index page.
Next up, I volition create ` functions.php` file and add the post-obit code in it.
<?php if(isset($_POST["Import"])){ $filename=$_FILES["file"]["tmp_name"]; if($_FILES["file"]["size"] > 0) { $file = fopen($filename, "r"); while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) { $sql = "INSERT into employeeinfo (emp_id,firstname,lastname,email,reg_date) values ('".$getData[0]."','".$getData[ane]."','".$getData[2]."','".$getData[iii]."','".$getData[four]."')"; $consequence = mysqli_query($con, $sql); if(!isset($result)) { echo "<script blazon=\"text/javascript\"> alert(\"Invalid File:Please Upload CSV File.\"); window.location = \"index.php\" </script>"; } else { echo "<script type=\"text/javascript\"> warning(\"CSV File has been successfully Imported.\"); window.location = \"index.php\" </script>"; } } fclose($file); } } ?> When the upload button is clicked, the temporary file proper name volition be stored in memory and using the ` while` loop the data is saved in $getData variable. Once the process has been completed, the data is sorted column wise and then finally inserted in the ` employeeinfo` table.
Note that ` fgetcsv()` parses lines from the open file, checking for CSV fields and ` fopen()` opens a file or a URL. This code could be tested by importing a CSV file with test data.
Display the Saved Records
Once the CSV file has been imported, I will display the data through a unproblematic role, ` get_all_records()`, initialized in ` index.php`. Copy this function to ` function.php` .
function get_all_records(){ $con = getdb(); $Sql = "SELECT * FROM employeeinfo"; $result = mysqli_query($con, $Sql); if (mysqli_num_rows($consequence) > 0) { echo "<div class='tabular array-responsive'><tabular array id='myTable' grade='table table-striped table-bordered'> <thead><tr><th>EMP ID</th> <th>First Proper noun</th> <th>Concluding Proper name</th> <th>E-mail</th> <th>Registration Date</th> </tr></thead><tbody>"; while($row = mysqli_fetch_assoc($result)) { repeat "<tr><td>" . $row['emp_id']."</td> <td>" . $row['firstname']."</td> <td>" . $row['lastname']."</td> <td>" . $row['electronic mail']."</td> <td>" . $row['reg_date']."</td></tr>"; } echo "</tbody></tabular array></div>"; } else { echo "yous accept no records"; } } In this really simple method, I simply selected all the records and displayed these records on the index page through the method. Whenever the user uploads a CSV file, the records will go saved in the table and then displayed on the alphabetize page.
Consign MySQL to CSV With PHP
Exporting data from MySQL database to a CSV file is similarly very easy. To demonstrate this, I will apply the index.php that I created earlier.
Add the following code to the file.
<div> <form grade="class-horizontal" activity="functions.php" method="postal service" proper name="upload_excel" enctype="multipart/form-data"> <div class="form-group"> <div class="col-medico-4 col-md-start-4"> <input type="submit" name="Export" class="btn btn-success" value="export to excel"/> </div> </div> </class> </div>
After calculation this HTML markup, the Exportbutton volition announced below the tabular array. Now add the following condition in functions.php.
if(isset($_POST["Export"])){ header('Content-Blazon: text/csv; charset=utf-8'); header('Content-Disposition: attachment; filename=data.csv'); $output = fopen("php://output", "westward"); fputcsv($output, array('ID', 'First Name', 'Last Name', 'Email', 'Joining Date')); $query = "SELECT * from employeeinfo Club Past emp_id DESC"; $upshot = mysqli_query($con, $query); while($row = mysqli_fetch_assoc($result)) { fputcsv($output, $row); } fclose($output); } When the ` Export` push is clicked, the headers ` Content-Type: text/csv` with an attachement ` data.csv` is sent.
Since ` php://output` is a write-only stream that allows write access to the output buffer mechanism, I selected all data from table in the next line, and passed it to ` fputcsv()` method. This method formats a line (passed as a fields array) as CSV and write it (terminated by a newline) to the specified file. Finally, the file with all the desired data is downloaded.
Finally, after integrating all the code, you will see the following final shape of application.
You might also similar: Simple CRUD in PHP and MySQL
Conclusion
In this article, I discussed how you could consign data from and to CSV files using PHP and MySQL. This is a elementary instance you can Add together more complex logic and validations as per your requirements. You lot can also create examination cases to verify the code and Integerate with GitHub using PHP Continuous Integeration Tools. If you lot wish to add to the word or would like to ask a question, go out a annotate below.
How exercise I import and consign CSV using php and MySQL?
- Approve the submitted record, whether a substantial CSV file.
- Audit the CSV file transfer status utilizing PHP is_uploaded_file() function.
- Admission the CSV file utilizing PHP fopen() function.
- Parse data from the CSV record utilizing PHP fgetcsv() role.
- Insert or updade data into the database based on the member'southward e-postal service.
Share your opinion in the comment section. COMMENT At present
Share This Article
Client Review at 
"Cloudways hosting has one of the all-time customer service and hosting speed"
Sanjit C [Website Developer]
Source: https://www.cloudways.com/blog/import-export-csv-using-php-and-mysql/
0 Response to "Php Function to Upload Csv to Mysql Db"
Post a Comment