MySQL Tutorial - How to calculate time correctly

A database server has a database called "Cinema" which has a table called "Movies". This table has next fields:

• ID
• Name
• Duration

Hereafter, you can see table 'movies':

movies_id	movies_name	movies_duration
1	Movie I	02:23:34
2	Movie II	01:44:56
3	Movie III	02:56:34

To calculate duration of all movies, we could write:

SELECT sum(movies_duration) FROM movies;

But it is wrong!

Result:

sum(movies_duration)
62424

To fix this problem, it is necessary convert time to seconds with next SQL statement:

SELECT time_to_sec(movies_duration) FROM movies;

Result:

time_to_sec(movies_duration)
8614
6296
10594

And next, use function SUM() to calculate duration of all movies

SELECT sum(time_to_sec(movies_duration)) FROM movies;

Result:

sum(time_to_sec(movies_duration))
25504

Finally, convert the sum (in seconds) to time using the next statement:

SELECT sec_to_time(sum(time_to_sec(movies_duration))) FROM movies;

Result:

sec_to_time(sum(time_to_sec(movies_duration)))
07:05:04

Thus you can calculate time correctly using three MySQL functions:

1. TIME_TO_SEC(...)
2. SUM(...)
3. SEC_TO_TIME(...)

How to import a MySQL dump file into a Database

To import MySQL dump file into a database you must have a MySQL command line shell.

Step #1: Make sure your database is already created. If database is not created yet, then you have to create one database. For example:

->mysql -h localhost -u root -p;
(enter your password)
->CREATE SCHEMA IF NOT EXISTS 'database_name';
->exit;

Note

You can replace "localhost" by an IP address (e.g. 192.168.1.88).

Step #2: Type the next command to import MySQL dump file into a database.

->mysql -h localhost -u root -p database_name < file.sql
(enter your password)

Voilá! Now you have a database with all data filled.

How to create Graphs using HTML5

To develop a small web application, I would like to use the data of students' grades and translate it into a graph bar.  But how can I do that?

With HTML5, using tag <canvas>, it is possible create a graph bar without third part software. The first thing to do is write some functions in JavaScript. Before you copy code, you have to understand the coordinates on Canvas. The next figure show how coordinates are designed. It is not the same of what we learned on Math classes.

JavaScript code:

<script type="text/javascript">         function drawLine(context,startX,startY,endX,endY,color){             context.beginPath();             context.moveTo(startX,startY);             context.lineTo(endX,endY);             context.closePath();             context.strokeStyle=color;             context.stroke();         }         function drawRectangle(context,startX,startY,width,height,fill){             context.beginPath();             context.rect(startX,startY,width,height);             context.closePath();             context.stroke();             if(fill) context.fill();         }         function clearCanvas(context){             // Store the current transformation matrix             context.save();             // Use the identity matrix while clearing the canvas             context.setTransform(1, 0, 0, 1, 0, 0);             context.clearRect(0, 0, myCanvas.width, myCanvas.height);             // Restore the transform             context.restore();         }         function drawBarChart(context,data,startX,startY,endX, endY,chartHeight,barWidth,markDataIncrementsIn,scale){             //draw x-axis             drawLine(context,startX,startY,endX,startY,"#000");             //draw y-axis             drawLine(context,startX,startY,startX,endY,"#000");             var maxValue=0;             for(var i=0;i<data.length;i++){                 //extract data                 var values = data[i].split(",");                 //column name                 var name=values[0];                 //column value                 var height=parseFloat(values[1])*scale;                 //find maximum value                 if(parseInt(height)>parseInt(maxValue)) maxValue=height;                 //write data to chart                 //fill rectangle with yellow color                 context.fillStyle="#ff0";                 drawRectangle(context,startX+(i*barWidth*2),(startY-height),barWidth,height,true);                 //add column title to x-axis                 context.textAlign="left";                 //text color is black                 context.fillStyle="#000";                 context.fillText(name,startX+(i*barWidth*2),(chartHeight-25),barWidth*2);             }             //add some data markers to y-axis             var numMarkers = Math.ceil(maxValue/parseInt(markDataIncrementsIn));             context.textAlign="right";             context.fillStyle="#000";             var markerValue=0;             for(var i=0;i<=numMarkers;i++){                 context.fillText((markerValue/parseInt(scale)),(startX-5),(startY-markerValue),50);                 if(i==0){                     drawLine(context,startX,(startY-markerValue),endX,(startY-markerValue),"#000");                 }else{                     drawLine(context,startX,(startY-markerValue),endX,(startY-markerValue),"#00f");                 }                 markerValue+=parseInt(markDataIncrementsIn);             }         }         function graph(scale){             var s=0;             var changed=false;             document.getElementById("range").innerHTML=scale;             if(scale==undefined){                 s=1;                 changed=false;             }else{                 s=scale;                 changed=true;             }                           var graphCanvas = document.getElementById("myCanvas");             //ensure that element is available within DOM             if(graphCanvas && graphCanvas.getContext){                 //open a 2d context within canvas                 var context = graphCanvas.getContext("2d");                 //bar chart data                 var data = new Array(5);                 data[0]="Bad,2";                 data[1]="Mediocre,0.45";                 data[2]="Sufficient,5.5";                 data[3]="Good,7.2";                 data[4]="Excelent,3.9";                 //draw bar chart                 if(!changed){                     drawBarChart(context,data,35,560,750,50,myCanvas.height,30,s,s);                 }else{                     //clear canvas to redrawing                     clearCanvas(context);                     //draw bar chart                     drawBarChart(context,data,35,560,750,50,myCanvas.height,30,s,s);                                      }             }             return true;         }     </script>

The second thing to do is write some HTML tags.

<body onload="graph(1);">         <div id="title">Grades</div>         <table>             <tr>                 <td><canvas id="myCanvas" width="800" height="600"></canvas><td>             <td>                 <table>                     <tr>                         <td>Scale : <span id="range">1</span></td>                     </tr>                     <tr>                         <td><input type="range" min="1" max="501" value="1" onchange="return graph(this.value)"/></td>                     </tr>                 </table>             </td>         </tr>     </table> </body>

The third thing to do is write CSS.

<style type="text/css">             #title{                 text-align: center;             } </style>

Now put together all three parts on a single page or you can create a file to JavaScript and another to CSS and then link them on HTML page. How you can do that? Copy the next code and you can change name files.

<head>   <link rel="stylesheet" type="text/css" href="style.css" />   <script type="text/javascript" src="functions.js"></script> </head>

Attention
All this code is working on Google Chrome. Also works on Safari and Opera. If you will use Internet Explorer, you will have problems. For example, you can not use slide bar and you have to type a value instead.

Convert dates in PHP and MySQL

Today I was working in project and I was confronted by one problem. As I live in Europe, the date's format are not the same in United States even MySQL. US use mm/dd/yyyy format, Europe use dd/mm/yyyy and MySQL use yyyy-mm-dd.


After I researched on PHP and MySQL official sites, I wrote algorithms that allowed me insert correctly data in SQL. 


PHP to MySQL:


SQL:


create schema test;


create tabela xpto(
id int not null auto_increment,
start_date date,
end_date date,
primary key(id)
)engine=innodb;




PHP:
<?php
$start_date = "10/10/2011";
$end_date = "12/10/2011";


$query0 = "select str_to_date('$start_date','%d/%m/%Y')";
$result0 = mysql_query($query0) or die(mysql_error());
$row0 = mysql_fetch_array($result0);


$query1 = "select str_to_date('$end_date','%d/%m/%Y')";
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1);


$query2 = "insert into xpto (start_date, end_date) values ('".$row0[0]."','".$row1[0]."')";
$result2 = mysql_query($query2) or die(mysql_error());
?>


To check out the data inserted, type on mysql terminal:
select * from xpto;


MySQL to PHP:
It is enough select data stored and use two functions to show date format correctly.




PHP:
<?php
mysql_connect("localhost","user","pass") or die (mysql_error());
mysql_select_db("test");


$query0="select start_date from xpto where id=1";
$result0 = mysql_query($query0) or die (mysql_error());
$row0 = mysql_fetch_array($result0,MYSQL_NUM);



$query1="select end_date from xpto where id=1";
$result1 = mysql_query($query1) or die (mysql_error());
$row1 = mysql_fetch_array($result1,MYSQL_NUM);

$start_date=$row0[0];//"2011-10-10"
$end_date=$row1[0];//"2011-10-12"


$d0 = strtotime($start_date);
$d1 = date('d/m/Y',$d0);
echo $d1."<br />";


$d2 = strtotime($end_date);
$d3 = date('d/m/Y',$d2);
echo $d3."<br />";
?>


Happy Halloween! :)

How to install osCommerce

osCommerce (open source Commerce) is an e-commerce and can be used on web server that has PHP and MySQL installed. You can simply download on this site. After you download, you have to unzip and copy to a folder on web server.

The goal of this post is show you how to install osCommerce. I am installing on a virtual machine that works like a web server (LAMP) to simulate real scenario of installation osCommerce.

If you don't know install or configure a web server on Ubuntu, please read this post or see this video.

If you already installed LAMP, please following the next steps to install:

1. Go to this site and download "oscommerce-2.3.1.zip";
2. Unzip "oscommerce-2.3.1.zip";
3. Copy to web server ;



Step #3



4. Check out the copy;



Step #4



5. Modify the permissions on specific folders;



Step #5






Step #5



6. After modifying the permissions, will show up  the next screen. Press the button "Continue";



Step #6



7. It will show up a form where you have fill with right values. But I didn't create a database yet. For that, I went to terminal and typed "mysql -h localhost -u root -p" to login MySQL Server. And then, I created database called "oscommerce" with next command: "create schema oscommerce". Finish! Now you can fill form like you can see in third image.

   Note: I made a mistake to input "192.168.1.199" instead a right IP address which is localhost or 127.0.0.1.



Step #7






Step #7






Step #7



8. I agreed with those values by default. If you want to change, you are comfortable to change.



Step #8



9. The next form is about your online store. You have to give information like your online store name, owner name, owner email address, administrator username and administrator password.



Step #9



10. Please read the post-installation notes. It is very important follow the steps to finish the installation.



Step #10



11. The next image will demonstrate you how to set permissions on step #3 and #4 according the previous image.



Step #11



12. Then, type in your browser: http://localhost/oscommerce/catalog/admin. This address will drive at administrator page where you have insert administrator username and password.



Step #12



13. Follow the path Administration Tool -> Tools -> Security Directory Permissions. This page will list permissions that you have change to with command "chmod 777 ..." like you can see in first image. After you changed permissions, press F5 to reload page to see values changed. Finish! You already finished the part of administration. Now let see the online store.



Step #13






Step #13



14. Type "http://localhost/oscommerce-2.3.1/catalog/" and press Enter. Voilá! Congratulations! You installed your online store!



Step #14

And now you can do anything with your online store and sell anything. Good luck for your sales!

How to configure snmpd

Continuing the installation of Cacti, you need do some configuration on another machines. Without doing this configuration, an error message will show up when you try create a device. Please click on this link to see how to do.

How to install Cacti

Cacti has some prerequisites. You need to install these packages before install Cacti:

• RRDTool;
• NET-SNMP;
• MySQL;
• PHP;
• Apache;

You will also need to install some other packages for support. To see a video that show you how to install Cacti, please click this link or you can read the tutorial below.

Apache

Open a terminal and follow the next steps:

1. sudo apt-get install apache2

After the installation is complete, open http://localhost in your favorite browser. If everything goes fine, you will see It Works! on the top.

PHP

Now that your web server is ready, we will install PHP. Open a terminal and follow the next steps:

1. sudo apt-get install php5 libapache2-mod-php5
2. sudo /etc/init.d/apache2 restart
3. sudo gedit
4. write the following PHP code:
1. <?php phpinfo();?>
5. save file as info.php in  /var/www folder
6. open your favorite browser and type http://localhost/info.php.

MySQL

Enter the following command:

1. sudo apt-get install mysql-server-5.0 php5-mysql
2. type your password (e.g.: root)
3. sudo /etc/init.d/apache2 restart
4. open http://localhost/info.php in your favorite browser and check MySQL module.

Great!!! Now you have a LAMP (Linux, Apache, MySQL, PHP) configured system.

Net-SNMP

Open terminal and type the following commands:

1. sudo apt-get install snmp php5-snmp
2. sudo /etc/init.d/apache2 restart
3. Open http://localhost/info.php in your favorite browser and check SNMP module

RRDTool

RRDTool is available in the Debian repository, so you can install it through APT. Type in your terminal

1. sudo apt-get install rrdtool

Cacti

Before continuing, be sure that you got no error previously. You are in the last step of installing Cacti.

1. sudo apt-get install cacti
2. select option 'apache2'
3. type your password of the database's administrative user (e.g.: root)
4. type your MySQL application password for cacti and retype in password confirmation (e.g.: root)
5. To complete the configuration process, open http://localhost/cacti in your favorite browser. 

In the installation type page, you can select if it's new install  or an upgrade. As we are going to install it for the first time, we will select New Install (which is selected by default) and click Next. The path settings page automatically determines the installed paths for RRDTool, PHP, SNMP and Cacti.log as well as the versions for Net-SNMP and RRDTool and then click Next.

In login screen, the username is admin and the default password is admin. After a successful login for the first time, the system will ask you to set a new password. Enter a new password and ensure you remember it. Without this password, you cannot administer the system!

php MyAdmin

Unfortunately I haven't had time to write more posts. Recently I installed phpMyAdmin and you can click this link to see how to install.

If you don't want use phpMyAdmin, you can use MySQL Workbench. With this link you can use to download, install and use it. Sorry, I don't have video to show how to install MySQL Workbench but I think that is not really difficult to install.

PHP5, MySQL and APACHE

I created a web application (PHP) that show the data stored in MySQL's database.
 
The materials needed are:
PHP, MySQL and Apache

Clicking this link (video) that can show how to install php, mysql and apache or following the next steps:

1. Type 'sudo apt-get install mysql-server mysql-client;'
2. Type 'y' when appears the question to install;
3. Note: you have type password to user root.
4. After the installation is finished, type 'sudo apt-get install php5' (in my case, apache was selected to install automatically)
5. Type 'y' when appears the question to install;
6. To check out if the web server apache is working, go to any browser and type 'http://localhost' in address bar;
7. If  web server apache is not working, type 'sudo apt-get install apache2'
8. Type 'y' to install apache2 web server;
9. Congratulations, you finished the installation with success!