Wednesday, August 26, 2015

How to : Export Data From Mysql To Excel (Spreadsheet) With PHP

Peace be upon you, This tutorial will demonstrate how to export / convert our PHP data set from MySQL into Excel spreadsheet document. I hope this tutorial knowledge will be useful for us. Without delay our time, let go through with our example. First we need to create a database, called "my_kripto_db" with one table called "users" and 3 column which is idusers, username and password. Just import and execute SQL file below:
CREATE DATABASE  IF NOT EXISTS `my_kripto_db`;
USE `my_kripto_db`;

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `idusers` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`idusers`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Lets insert a couple of dumb data into this database table :
INSERT INTO `users` VALUES (1,'Sera','nasikLemak1'),(2,'Mika','tehtarikkopi0');

We a done with database, and lets get our hand dirty by wrote our PHP code which I named as "data.php" which later will be use to query and display this data into tables when requested from web server by users.
<html>
<head>
 <title></title>
</head>
<body>
<table border="1">
    <tr>
     <th>No</th>
  <th>Username</th>
  <th>Password</th>
 </tr>
 <?php
 //connection to mysql
 mysql_connect("localhost", "root", ""); //server , username , password
 mysql_select_db("my_kripto_db");
 
 //query get data
 $sql = mysql_query("SELECT * FROM users ORDER BY idusers ASC");
 $no = 1;
 while($data = mysql_fetch_assoc($sql)){
  echo '
  <tr>
   <td>'.$no.'</td>
   <td>'.$data['username'].'</td>
   <td>'.$data['password'].'</td>
  </tr>
  ';
  $no++;
 }
 ?>
</table></body>
</html>

From this "data.php" table, we will export it into Excel spreadsheet document by sending a header function and let users download the file when this "data-set.php" are executed:
<?php
// Sending raw excel header 
header("Content-type: application/vnd-ms-excel");
 
// Defines the name of the export file "kriptonium-data.xls"
header("Content-Disposition: attachment; filename=kriptonium-data.xls");
 
// Add data table
include 'data.php';
?> 

Done and this is our result :

Thursday, August 20, 2015

MySQL - Check all database size and free size in MB

SQL command :
SELECT table_schema "Data Base Name", 
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", 
sum( data_free )/ 1024 / 1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema ;

Example result:
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| e-virtue           |           0.06250000 |       0.00000000 |
| ict_iptho          |           0.07812500 |       0.00000000 |
| information_schema |           0.00976563 |       0.00000000 |
| mysql              |           0.78106689 |       0.00000000 |
| performance_schema |           0.00000000 |       0.00000000 |
| spppm              |           0.02952194 |       0.00000000 |
| sptrba_db          |           0.14062500 |       0.00000000 |
+--------------------+----------------------+------------------+
8 rows in set (0.02 sec)

Saturday, August 1, 2015