PHP 7 Script to Convert MySQL Database Tables into CSV File in Browser Using HTML5 & Javascript Full Project For Beginners

  • Post author:
  • Post category:PHP
  • Post comments:0 Comments

 

 

index.php

 

 

<?php
// This application requires index.php and conveterscript.php to work

// first we need to insert  data into a Mysql database
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "csvconvertdb";

//  to connect to the databae we will be creating an instance of
// the MySQLi constructor which takes 4 parameters, the server name, username,
//password (in my case is blank), and database name in my case is called csvconvertdb.
$conn = new MySQLi ($servername, $username, $password, $dbname);

//we will create an if condition that goes off when the submit button is clicked
//we don't want to keep populating the database each time we refresh the browser



//we will insert multiple values into the mysql database
//you can also achieve the same result using simple arrays
$sql = "INSERT INTO userdata (name, phone_number, age, sex, city ) 
		VALUES 	('Alice Tyson', '325226115', '21', 'female', 'Newyork'),
				('Merissa Stone','12522546','24','Female','San Diego'),
				('Tom ford','222565656','28','male','Boston'),
				('Brad Stone','223545654','29','male','Texas'),
				('Luke page','1234895446','35','male','Los angeles'),
				('Bill stone','232323222','29','Male','Alaska'),
				('katie miller','989524542','25','female','Boston')";
				
				(mysqli_query($conn, $sql));
				
// we are inserting multiple values at the sametime, making it look like 

//If we view our databas we can see that the values have been added, we can begin working on how 
//to display the data for the user, which we are going to convert to CSV. 

// [picture]show the picture of your Mysql database

//so now let's start retrieving the data we wish to convert from Mysql. 
$sql = "SELECT * FROM userdata";
$result = $conn->query($sql);

//'SELECT *' simply means to select all fields from our table known as userdata
//you can replace the '*' with the name of the fields you want display.

echo ("<table>
			  <tr>
				<th>User ID</th>
				<th>Name</th>
				<th>Phone Number</th>
				<th>Age</th>
				<th>Sex</th>
				<th>City</th>
			  </tr>");

$file = fopen("userdata.csv","w");
while ($row = $result->fetch_assoc()){
		$dbuserid = $row["userid"];
		$dbname = $row["name"];
		$dbphone = $row["phone_number"];
		$dbage = $row["age"];
		$dbsex = $row["sex"];
		$dbcity = $row["city"];
		
		// so we create HTML table elements to make our data more presentable
		
		echo ("
			  <tr>
				<td>$dbuserid</td>
				<td>$dbname</td>
				<td>$dbphone</td>
				<td>$dbage</td>
				<td>$dbsex</td>
				<td>$dbcity</td>
				</tr>
				
		       ");
 	}
//in the above we createda  while loop to traversea all the databseelements we are retrieving
//the fetch_assoc () function returns an associative array that corresponds  to the rows
// beign fetched and NULL if there are no rows to return


// To make our values appear more refined we will put them in tables and add some styling
//add the table code below and after it include the css code for the table 
//[create a table for line 63 and add some css styling]

// Now we will begin the conversion by first calling he fopen() function. This function takes
// two parameters, the first is the filename and the second is the file which can be 'r', 'a', 'w', 
// or 'x'

//No we want to convert and download the CSV file when we click the convert and download button
//So to do that, we set an if condition that sets to tru when the button is clicked


	

?>
<!--Here we need to call our HTML tags and Link our stylesheets, which we called stylesheet.css -->
<!DOCTYPE html>
<html>
<head>

<link href="style.css" rel="stylesheet">
</head>
<body>
<tr><td><form method = 'post' action ='converterscript.php'>
	<input type = 'submit' name = 'submit' class='button' value ='Convert and Download'/>
	</form></td></tr>
</table>
</body>
</html>

 

See also  PHP 7 MySQL Database GST Tax Billing Invoice PDF Document Generator Web App in Browser Using Javascript Full Project For Beginners

 

csvconvertdb.sql

 

-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 08, 2018 at 06:41 PM
-- Server version: 10.1.9-MariaDB
-- PHP Version: 5.6.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `csvconvertdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `userdata`
--

CREATE TABLE `userdata` (
  `userid` int(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `phone_number` int(15) NOT NULL,
  `age` int(100) NOT NULL,
  `sex` varchar(7) NOT NULL,
  `city` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `userdata`
--
ALTER TABLE `userdata`
  ADD PRIMARY KEY (`userid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `userdata`
--
ALTER TABLE `userdata`
  MODIFY `userid` int(100) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=767;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

 

converterscript.php

 

 

<?php
// This application requires index.php and conveterscript.php to work

//  to connect to the databae we will be creating an instance of
// the MySQLi constructor which takes 4 parameters, the server name, username,
//password (in my case is blank), and database name in my case is called csvconvertdb.
$conn = new MySQLi ("localhost", "root", "", "csvconvertdb");

// open the file "demosaved.csv" for writing
//$file = fopen('demosaved16.csv', 'w');
//we need to send the CSv file directly to the browser without opening in an external file.
//so we create a file pointer connected to the output stream
$document = fopen('php://output', 'w');

//we need to prepare the file for download rather than being displayed
//So we create output headers
header('Content-Type: text/csv; charset=utf-8');
//so lets add a time stamp to give the file a unique value each time it is downloaded 
header('Content-Disposition: attachment; filename=userdata_'.date("H:i:s").'.csv');
 
// save the column headers. We have created 6 columns to reflect what's stored in the database
//they will appear as the row header in the csv file when viewed after downloading
fputcsv($document, array('ID', 'Name', 'Phone', 'Age', 'Sex', 'City'));




//  to connect to the databae we will be creating an instance of
// the MySQLi constructor which takes 4 parameters, the server name, username,
//password (in my case is blank), and database name in my case is called csvconvertdb.
$conn = new MySQLi ("localhost", "root", "", "csvconvertdb");

//so now let's start retrieving the data we wish to convert from Mysql. 
$sql = "SELECT * FROM userdata";
$result = $conn->query($sql);


while ($row = $result->fetch_assoc()) {
	fputcsv($document, $row);
}

// so the next thing we need to do is add random numbers to our downloaded file so that
// it gets a unique file name each time file is downloaded so we would add a time stamp 
 
?>

 

See also  PHP 7 Script to Send HTML Email With Attachment on Form Submission Using Mail() Function in Browser Full Project For Beginners

 

style.css

 

 

table {
    font-family:"Trebuchet MS", Arial, Helvetica, sans-serif
    border-collapse: collapse;
    width: 80%;
}

th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}
td {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}

tr:nth-child(even) {
    background-color: #dddddd;
}
.button {
    background-color: rgb(66, 133, 244);
    border: none;
    color: white;
    padding: 16px 33px;
    text-align: center;
    text-decoration: none;
    display: inline-block;
    font-size: 17px;
    margin: 4px 2px;
    cursor: pointer;
	border-radius: 3px;
	letter-spacing: 1px;
}

 

 

Mysql-CSV-Converter-

PHP script that converts Mysql data into CSV

To install application

  1. Download application
  2. Unzip the file and move to your server’s directory
  3. Import the csvconvertdb.sql file into mysql
  4. Run the script in your browser

 

Leave a Reply