CSS DEMOS HTML MYSQL PHP PHP PROJECT

How to Fetch Data in Excel or Generate Excel file in PHP

September 30, 2018October 13, 2018 Web Design Comment(0)

File structure for this tutorial :

config.php (database connection file)

index.php (For fetching data )

genrate-excel.php (For genrating excel file)

Create a sql table tblemployee.

Structure of sql table employe .

CREATE TABLE `tblemploye` (
  `id` int(11) NOT NULL,
  `fullName` varchar(120) NOT NULL,
  `emailId` varchar(150) NOT NULL,
  `phoneNumber` int(11) NOT NULL,
  `department` varchar(100) NOT NULL,
  `joiningDate` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `tblemploye`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `tblemploye`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

CREATE TABLE `tblemploye` (

`id` int(11) NOT NULL,

`fullName` varchar(120) NOT NULL,

`emailId` varchar(150) NOT NULL,

`phoneNumber` int(11) NOT NULL,

`department` varchar(100) NOT NULL,

`joiningDate` varchar(100) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tblemploye`

ADD PRIMARY KEY (`id`);

ALTER TABLE `tblemploye`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

COMMIT;

Now insert some data into this table.

INSERT INTO `tblemploye` (`id`, `fullName`, `emailId`, `phoneNumber`, `department`, `joiningDate`) VALUES
(1, 'Ogbugbu Ejiroghene, 'ogbugbu14@gmail.com', 08154761214, 'IT', '2018-05-01'),
(2, 'Ejiro', 'john@test.com', 45455454, 'HR', '2017-08-12'),
(3, 'Kefe', 'demo@test.com', 23423423, 'Account', '2016-10-01'),
(4, 'Test', 'test@test.com', 834856384, 'IT', '2017-12-01');

INSERT INTO `tblemploye` (`id`, `fullName`, `emailId`, `phoneNumber`, `department`, `joiningDate`) VALUES

(1, 'Ogbugbu Ejiroghene, 'ogbugbu14@gmail.com', 08154761214, 'IT', '2018-05-01'),

(2, 'Ejiro', 'john@test.com', 45455454, 'HR', '2017-08-12'),

(3, 'Kefe', 'demo@test.com', 23423423, 'Account', '2016-10-01'),

(4, 'Test', 'test@test.com', 834856384, 'IT', '2017-12-01');

Index.php in this file we will read the data from database.

?php
$query=mysqli_query($con,"select * from tblemploye");
$cnt=1;
while ($row=mysqli_fetch_array($query)) {
 
?>
  <tr>
                <td><?php echo $cnt;  ?></td>
                <td><?php echo $row['fullName']?></td>
                <td><?php echo $row['emailId']?></td>
                <td><?php echo $row['phoneNumber']?></td>
                <td><?php echo $row['department']?></td>
                <td><?php echo $row['joiningDate']?></td>
            </tr>
<?php 
$cnt++;
} ?>       
</table>

?php

$query=mysqli_query($con,"select * from tblemploye");

$cnt=1;

while ($row=mysqli_fetch_array($query)) {

<tr>

</tr>

<?php

$cnt++;

} ?>

</table>

genrate-excel.php in this file we will fetch the data from database and generate excel file for the same data.

<?php
// Database Connection file
include('config.php');
?>
<table border="1">
<thead>  
<tr>
<th>Sr.</th>
<th>Name</th>
<th>Email id</th>
<th>Phone Number</th>
<th>Department</th>
<th>Joining Date</th>
</tr>
</thead>
<?php
// File name
$filename="EmpData";
// Fetching data from data base
$query=mysqli_query($con,"select * from tblemploye");
$cnt=1;
while ($row=mysqli_fetch_array($query)) {
 
?>
 
            <tr>
                <td><?php echo $cnt;  ?></td>
                <td><?php echo $row['fullName'];?></td>
                <td><?php echo $row['emailId'];?></td>
                <td><?php echo $row['phoneNumber'];?></td>
                <td><?php echo $row['department'];?></td>
                <td><?php echo $row['joiningDate'];?></td>
            </tr>
<?php 
$cnt++;
// Genrating Execel  filess
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename."-Report.xls");
header("Pragma: no-cache");
header("Expires: 0");
} ?>
</table>

<?php

// Database Connection file

include('config.php');

<thead>

<tr>

<th>Email id</th>

<th>Phone Number</th>

<th>Department</th>

<th>Joining Date</th>

</tr>

</thead>

<?php

// File name

$filename="EmpData";

// Fetching data from data base

$query=mysqli_query($con,"select * from tblemploye");

$cnt=1;

while ($row=mysqli_fetch_array($query)) {

<tr>

</tr>

<?php

$cnt++;

// Genrating Execel filess

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$filename."-Report.xls");

header("Pragma: no-cache");

header("Expires: 0");

} ?>

</table>

Contact form with mail function and Storing data in the database

August 25, 2018August 25, 2018 Web Design

Contact form with mail function and Storing in the database project has two module : User Module admin Module User Module Sql table structure for tblcontactdata table where we will store our data

CREATE TABLE `tblcontactdata` (
  `id` int(11) NOT NULL,
  `FullName` varchar(200) DEFAULT NULL,
  `PhoneNumber` char(12) DEFAULT NULL,
  `EmailId` varchar(200) DEFAULT NULL,
  `Subject` varchar(255) DEFAULT NULL,
  `Message` mediumtext,
  `UserIp` varbinary(16) DEFAULT NULL,
  `PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Is_Read` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `tblcontactdata`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

CREATE TABLE `tblcontactdata` (

`id` int(11) NOT NULL,

`FullName` varchar(200) DEFAULT NULL,

`PhoneNumber` char(12) DEFAULT NULL,

`EmailId` varchar(200) DEFAULT NULL,

`Subject` varchar(255) DEFAULT NULL,

`Message` mediumtext,

`UserIp` varbinary(16) DEFAULT NULL,

`PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`Is_Read` int(1) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tblcontactdata`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Now create a HTML for user inputs. index.php

<form name="ContactForm" method="post">
 
<h4>your name</h4>
<input type="text" name="name" class="user" placeholder="Johne"  autocomplete="off" required>
 
<h4>your phone number</h4>
<input type="text" name="phonenumber" class="phone" placeholder="0900.234.145678" maxlength="10" required autocomplete="off">
 
<h4>your email address</h4>
<input type="email" name="emailaddres" class="email" placeholder="Example@mail.com" required autocomplete="off">
 
<h4>your subject</h4>
<input type="text" name="subject" class="email" placeholder="Subject" autocomplete="off">
 
<h4>your message</h4>
<textarea class="mess" name="message" placeholder="Message" required></textarea>
<input type="submit" value="send your message" name="submit">
</form>

<h4>your phone number</h4>

<h4>your email address</h4>

<h4>your subject</h4>

<h4>your message</h4>

</form>

MYSql database connection

<?php 
// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME','contactdb');
// Establish database connection.
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?>

<?php

// DB credentials.

define('DB_HOST','localhost');

define('DB_USER','root');

define('DB_PASS','');

define('DB_NAME','contactdb');

// Establish database connection.

try

{

$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

}

catch (PDOException $e)

{

exit("Error: " . $e->getMessage());

}

Insert Data into Database

include('config.php');
if(isset($_POST['submit']))
{
// getting Post values	
$name=$_POST['name'];
$phoneno=$_POST['phonenumber'];
$email=$_POST['emailaddres'];
$subject=$_POST['subject'];
$message=$_POST['message'];
$uip = $_SERVER ['REMOTE_ADDR'];
$isread=0;
// Insert quaery
$sql="INSERT INTO  tblcontactdata(FullName,PhoneNumber,EmailId,Subject,Message,UserIp,Is_Read) VALUES(:fname,:phone,:email,:subject,:message,:uip,:isread)";
$query = $dbh->prepare($sql);
// Bind parameters
$query->bindParam(':fname',$name,PDO::PARAM_STR);
$query->bindParam(':phone',$phoneno,PDO::PARAM_STR);
$query->bindParam(':email',$email,PDO::PARAM_STR);
$query->bindParam(':subject',$subject,PDO::PARAM_STR);
$query->bindParam(':message',$message,PDO::PARAM_STR);
$query->bindParam(':uip',$uip,PDO::PARAM_STR);
$query->bindParam(':isread',$isread,PDO::PARAM_STR);
$query->execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
//mail function for sending mail
$to=$email; 
$headers .= "MIME-Version: 1.0"."\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1'."\r\n";
$headers .= 'From:PHPGurukul Contact Form Demo<info@phpgurukul.com>'."\r\n";
$ms.="<html></body><div>
<div><b>Name:</b> $name,</div>
<div><b>Phone Number:</b> $phoneno,</div>
<div><b>Email Id:</b> $email,</div>";
$ms.="<div style='padding-top:8px;'><b>Message : </b>$message</div><div></div></body></html>";
mail($to,$subject,$ms,$headers);
echo "<script>alert('Your info submitted successfully.');</script>";
}
else 
{
echo "<script>alert('Something went wrong. Please try again');</script>";
}
}

include('config.php');

if(isset($_POST['submit']))

{

// getting Post values

$name=$_POST['name'];

$phoneno=$_POST['phonenumber'];

$email=$_POST['emailaddres'];

$subject=$_POST['subject'];

$message=$_POST['message'];

$uip = $_SERVER ['REMOTE_ADDR'];

$isread=0;

// Insert quaery

$sql="INSERT INTO tblcontactdata(FullName,PhoneNumber,EmailId,Subject,Message,UserIp,Is_Read) VALUES(:fname,:phone,:email,:subject,:message,:uip,:isread)";

$query = $dbh->prepare($sql);

// Bind parameters

$query->bindParam(':fname',$name,PDO::PARAM_STR);

$query->bindParam(':phone',$phoneno,PDO::PARAM_STR);

$query->bindParam(':email',$email,PDO::PARAM_STR);

$query->bindParam(':subject',$subject,PDO::PARAM_STR);

$query->bindParam(':message',$message,PDO::PARAM_STR);

$query->bindParam(':uip',$uip,PDO::PARAM_STR);

$query->bindParam(':isread',$isread,PDO::PARAM_STR);

$query->execute();

$lastInsertId = $dbh->lastInsertId();

if($lastInsertId)

{

//mail function for sending mail

$to=$email;

$headers .= "MIME-Version: 1.0"."\r\n";

$headers .= 'Content-type: text/html; charset=iso-8859-1'."\r\n";

$headers .= 'From:PHPGurukul Contact Form Demo<info@phpgurukul.com>'."\r\n";

$ms.="<html></body><div>

<div><b>Phone Number:</b> $phoneno,</div>

<div><b>Email Id:</b> $email,</div>";

$ms.="<div style='padding-top:8px;'><b>Message : </b>$message</div><div></div></body></html>";

mail($to,$subject,$ms,$headers);

echo "<script>alert('Your info submitted successfully.');</script>";

}

else

{

echo "<script>alert('Something went wrong. Please try again');</script>";

}

How to insert data into MySql using PHP

August 21, 2018 Web Design

In this tutorial i will explain how to insert data into MySql using PHP. [aio_button align=”none” animation=”none” color=”red” size=”small” icon=”play” text=”View Demo” target=”_blank” relationship=”dofollow” url=”https://phpgurukul.com/demos/mysqlinsert/”] First Create a database with name demo. Now create a table with name data. Here the strcuture of table

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `contactno` bigint(20) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `education` varchar(255) NOT NULL,
  `address` longtext NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `data` (

`id` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

`email` varchar(255) NOT NULL,

`contactno` bigint(20) NOT NULL,

`gender` varchar(255) NOT NULL,

`education` varchar(255) NOT NULL,

`address` longtext NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Code for mysql connection with php

<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'demos');
$conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS)
or
die('localhost connection problem'.mysql_error());
mysql_select_db(DB_NAME, $conn);
?>

<?php

define('DB_SERVER','localhost');

define('DB_USER','root');

define('DB_PASS' ,'');

define('DB_NAME', 'demos');

$conn = mysql_connect(DB_SERVER,DB_USER,DB_PASS)

die('localhost connection problem'.mysql_error());

mysql_select_db(DB_NAME, $conn);

Create a HTML Form for user inputs

<form name="insert" action="" method="post">
<table width="100%"  border="0">
<tr>
<th height="62" scope="row">Name </th>
<td width="71%"><input type="text" name="name" id="name" value=""  class="form-control" required />
</td>
</tr>
<tr>
<th height="62" scope="row">Email id </th>
<td width="71%"><input type="email" name="email" id="email" value=""  class="form-control" required />
</td>
</tr>
<tr>
<th height="62" scope="row">Contact no</th>
<td width="71%">
<input type="text" name="contactno" id="contactno" value=""  class="form-control" maxlength="10" required /></td>
</tr>
<tr>
<th height="62" scope="row">Gender</th>
<td width="71%"><input type="radio" name="gender" value="Male" checked >Male
<input type="radio" name="gender" value="Female" >female</td>
</tr>
<tr>
<th height="62" scope="row">Education</th>
<td width="71%"><select name="education" id="email" class="form-control" required >
<option value="">Select your Education</option>
<option value="Post Graduate">Post Graduate</option>
<option value="Graduate">Graduate</option>
<option value="Intermediate">Intermediate</option>
<option value="High School">High School</option>
<option value="Other">Other</option>
</select></td>
</tr>
<tr>
<th height="62" scope="row">Address</th>
<td width="71%"><textarea name="addrss" class="form-control"  required></textarea> </td>
</tr>
<tr>
<th height="62" scope="row"></th>
<td width="71%"><input type="submit" name="submit" value="Submit" class="btn-group-sm" /> </td>
</tr>
</table>
</form>

<tr>

</td>

</tr>

<tr>

<th height="62" scope="row">Email id </th>

</td>

</tr>

<tr>

<th height="62" scope="row">Contact no</th>

</tr>

<tr>

<th height="62" scope="row">Gender</th>

<td width="71%"><input type="radio" name="gender" value="Male" checked >Male

<input type="radio" name="gender" value="Female" >female</td>

</tr>

<tr>

<th height="62" scope="row">Education</th>

<option value="">Select your Education</option>

<option value="Post Graduate">Post Graduate</option>

<option value="Graduate">Graduate</option>

<option value="Intermediate">Intermediate</option>

<option value="High School">High School</option>

<option value="Other">Other</option>

</select></td>

</tr>

<tr>

<th height="62" scope="row">Address</th>

</tr>

<tr>

</tr>

</table>

</form>

Now get […]

How to Fetch Data in Excel or Generate Excel file in PHP

Related Post

Like this:

Leave a Reply Cancel reply

Related Post

Like this:

Related Articles

Contact form with mail function and Storing data in the database

Like this:

How to insert data into MySql using PHP

Like this:

How to Create Altering tables in MySql

Like this:

Leave a Reply Cancel reply