EMBEDDED: July 2013

Thursday, 25 July 2013

Store a file in MySQL database

Firstly, create a table in database. We use longblob as the data type to store the content of the file.

mysql> CREATE TABLE IF NOT EXISTS `Files` (
-> `ID` int(25) NOT NULL auto_increment,
-> `Title` varchar(250) NOT NULL default '',
-> `File_Name` varchar(250) NOT NULL default '',
-> `File_Type` varchar(15) NOT NULL default '',
-> `File_Size` varchar(45) NOT NULL default '',
-> `File_Content` longblob NOT NULL,
-> `File_Extension` varchar(10) NOT NULL default '',
-> PRIMARY KEY (`ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.76 sec)

The html form to call the php script. The enctype of the form must be multipart/form-data.

<html>
<body>

<form action="upload_file.php" method="post"
enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file"><br>
<input type="submit" name="submit" value="Submit">
</form>

</body>
</html>

The php script to read the file, store it into the database, and copy it to upload folder of the web server.

<?php
$allowedExts = array("gif", "jpeg", "jpg", "png", "txt");
$temp = explode(".", $_FILES["file"]["name"]);
$extension = end($temp);
if ((($_FILES["file"]["type"] == "image/gif")
|| ($_FILES["file"]["type"] == "image/jpeg")
|| ($_FILES["file"]["type"] == "image/jpg")
|| ($_FILES["file"]["type"] == "image/pjpeg")
|| ($_FILES["file"]["type"] == "image/x-png")
|| ($_FILES["file"]["type"] == "image/png")
|| ($_FILES["file"]["type"] == "text/html")
|| ($_FILES["file"]["type"] == "text/plain"))
&& ($_FILES["file"]["size"] < 2000000)
&& in_array($extension, $allowedExts))
{
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br>";
}
else
{
echo "Upload: " . $_FILES["file"]["name"] . "<br>";
echo "Type: " . $_FILES["file"]["type"] . "<br>";
echo "Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>";
echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br>";

if (file_exists("upload/" . $_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"] . " already exists. ";
}
else
{
move_uploaded_file($_FILES["file"]["tmp_name"],
"upload/" . $_FILES["file"]["name"]);
echo "Stored in: " . "upload/" . $_FILES["file"]["name"];
}

// store in SQL
$user_name = "root";
$password = "root";
$database = "test";
$server = "localhost";

$fileName = $_FILES['file']['name'];
$tmpName = $_FILES['file']['tmp_name'];
$fileSize = $_FILES['file']['size'];
$fileType = $_FILES['file']['type'];

// Slurp the content of the file into a variable
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);
if(!get_magic_quotes_gpc()){
$fileName = addslashes($fileName);
}
$file_info = pathinfo($_FILES['file']['name']);

// Connect to the database
$dbh = mysql_connect($server,$user_name,$password) or die("There was a problem with the database connection.");
$dbs = mysql_select_db($database, $dbh) or die("There was a problem selecting the categories.");
$sql = "INSERT INTO Files SET
Title = 'Title',
File_Name = '".$fileName."',
File_Type = '".$fileType."',
File_Size = '".$fileSize."',
File_Content = '".$content."',
File_Extension = '".$file_info['extension']."'";
//print $sql;

$result = mysql_query($sql);
if(!$result){
echo "Could not add this file.";
}
else{
echo "New file successfully added.";
}
mysql_close($dbh);
}
}
else
{
echo "Invalid file";
}
?>

Wednesday, 17 July 2013

Using Python to access MySQL Database

Setup Apache2.4 for Python

In Apache httpd.conf:

edit the Options field, add in ExecCGI.

Options Indexes FollowSymLinks ExecCGI

edit the AddHandler field, add in .py

AddHandler cgi-script .cgi .py

Run Python script in Web browser

If you use Python3 interpreter, the script must be written in Python3 version.

The test.py script to output the Hello World on the browser. Notice the first line #!/Python33/python is the syntax to refer to Windows Python interpreter.

#!/Python33/python

# enable debugging
import cgitb

cgitb.enable()

print("Content-Type: text/plain;charset=utf-8")
print()

print("Hello World!")

The script to output cgi environment.

#!/Python33/python

import cgi

cgi.test()

If you want to access to MySQL database from python, here is another simple script to do so. In the script, the database is called "test", the table is called "doctor".

#!/Python33/python

import datetime
import mysql.connector

config = {
'user': 'root',
'password': 'root',
'host': '127.0.0.1',
'database': 'test',
'raise_on_warnings': True,
}

print("Content-Type: text/plain;charset=utf-8")
print()
print("read doctor database")

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

query = ("SELECT * FROM doctor")

cursor.execute(query)

results = cursor.fetchall()
print(results)

cursor.close()
cnx.close()

Tuesday, 16 July 2013

Using PHP to access MySQL database

After we have setup the Apache httpd and php environment, we can write php script to access the MySQL database.

For example, we can build a MySQL database upfront. Then we use a php script to display the data items in the browser.

An example php script to do this. eg: display_worker.php

<?PHP

$user_name = "root";
$password = "xxxxxx";
$database = "dummyonly";
$server = "localhost";

$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

$SQL = "SELECT * FROM worker";
$result = mysql_query($SQL);

print "<table border=\"1\" style=\"border:1px solid black;\" cellspacing=\"2\" cellpadding=\"4\">";
print "<tr>";

print "<td>";
print "name<BR>";
print "birth<BR>";
print "skill<BR>";
print "</td>";

while ( $db_field = mysql_fetch_assoc($result) ) {

print "<td>";
print $db_field['name'] . "<BR>";
print $db_field['birth'] . "<BR>";
print $db_field['skill'] . "<BR>";
print "</td>";

}
print "</tr>";
print "</table>";

mysql_close($db_handle);

}
else {

print "Database NOT Found ";
mysql_close($db_handle);

}

?>

We can also use a php script to insert new data to the database.

Firstly, we need the HTML form. eg: insert_worker.html

<html>
<body>

<form action="insert_worker.php" method="post">
Name: <input type="text" name="name">
Birthdate: <input type="text" name="birth">
Skill: <input type="text" name="skill">
<input type="submit">
</form>

</body>
</html>

Then, the php script to insert new data. eg: insert_worker.php

<?php

$user_name = "root";
$password = "xxxxxx";
$database = "dummyonly";
$server = "localhost";

$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO worker (name, birth, skill)
VALUES
('$_POST[name]','$_POST[birth]','$_POST[skill]')";

if (!mysql_query($sql))
{
die('Error: ' . mysql_error($db_handle));
}
echo "1 record added";

mysql_close($db_handle);

}

?>

Setup Apache, Php on Windows 7 and Centos

The check-list on Windows 7:

- Use Win32 Apache 2.4.4 from Apache Lounge and get Visual Studio 2012 run-time

- Download Thread Safe (TS) version of php 5.4.x from http://windows.php.net/download/

- Be sure you installed the Visual Studio 2012 (VC11) run-time, see left column at http://windows.php.net/download/

- Download php5apache2_4.dll-php-5.4-win32.zip from Apache Lounge and use the correct version in the zip

- Follow the instructions in the Readme in the php5apache2_4.dll zip

- First try to run without php extensions !

- Then run with php extensions !

Download:

Download the latest Apache 2.4.4 (as of writing this) from http://www.apachelounge.com/download/ and unzip it in C:/Apache24
Download latest PHP version 5.4 from http://windows.php.net/download and unzip it in c:/php. While downloading PHP, choose the Thread Safe version for Windows.
Download the PHP and Apache connector DLL file from http://www.apachelounge.com/download/ and extract the file php5apache2_4.dll and copy it to C:/php.

Apache:

Open the file C:/Apache24/conf/httpd.conf in any text editor then find and make the following changes/configurations as per your installation path.

Set the server root
ServerRoot "C:/Apache24"

Set the document root
DocumentRoot "C:/Apache24/htdocs"

Enable required Apache modules. I just un-comment one more for mod_rewrite module.

Add the following lines somewhere (You can add it at the end of the file)
LoadModule php5_module "C:/php/php5apache2_4.dll"
AddHandler application/x-httpd-php .php
PHPIniDir "C:/php"

Change server admin email address
ServerAdmin info@yoursite.com

Change the document root:
DocumentRoot "C:/Apache24/htdocs"
<Directory "C:/Apache24/htdocs">

If you want to allow .htaccess to be used under document root do the following under
<Directory “c:/Apache24/htdocs”>
AllowOverride All

Find the following and replace the path:
ScriptAlias /cgi-bin/ "C:/Apache24/cgi-bin/"
<Directory "C:/Apache24/cgi-bin">

Add info.php in directory index
DirectoryIndex index.html info.php

PHP:

Rename php.ini-development to php.ini
Find extension directory setting and set the path as per your installation.
extension_dir = "C:\php\ext"
Uncomment the extensions that you want to enable them. i.e.
extension=php_curl.dll
extension=php_mysql.dll
extension=php_mysqli.dll

If you want to test email sending from the PHP in local system, then you need to set the SMTP. i.e.
SMTP = smtp.yoursite.com
smtp_port = 25
sendmail_from = youremail@sender.com
Rest of the changes/configurations are totally depends on requirement project wise.

Check to make sure php shows loaded modules.
C:\> php -m

Putting it together:

Set Environment Variables for PHP and Apache (Windows 7):

To use PHP and Apache globally in Windows, the path of the PHP and Apache has to be added to the Path Environment Variables.
Right click on Computer and click Properties
Click on Advanced system settings on the left side (top).
Click on the Environment variables
Under System variables, double click the Path variable name and add ;C:\php without double quotes at the end where semicolon (;) is the separator.
Click OK to save. Sometimes you may need to restart the computer this to take it into effect.

Install Apache as Service:

Since we have just copied the files in a folder, the Apache is not yet not a windows service. To install it as a service follow the steps. We run the command line (cmd.exe) As Administrator:
C:\Apache24\bin>httpd -k install

Now restart the computer and test both PHP and Apache. To test both together, create a PHP file called info.php in C:\Apache24\htdocs. We will see if Apache parses the info correctly to display the results.

Open Notepad and type and save the following.

<?php
phpinfo();
?>

Now open the browser and enter http://127.0.0.1/info.php. If you can see the PHP information then now you are done !

MySQL data Location:

On Windows 7, the MySQL data directory is, by default, "C:/ProgramData/MySQL/MySQL Server 5.5/Data/". Note that "C:/ProgramData" is a hidden directory.

Troubleshooting:

When i start the server it shows this message:

Apache 2.4 daemon is Starting ...
##########################################
## Stop Apache Please Close This Window ##
##########################################
(OS 10048)Only one usage of each socket address (protocol/network address/port)
is normally permitted. : make_sock: could not bind to address 0.0.0.0:80
no listening sockets available, shutting down
Unable to open logs
Press any key to continue . .

If you see this error message, you check netstat -nato and find the current proccess that occupies your port 80.

It could happen like that (to have problems starting Apache) if you have Skype installed. It uses port 80 by default if has connection problems.

On Centos

Install Apache and ssl module

sudo yum install httpd mod_ssl

Start apache

sudo /etc/init.d/httpd start

If you see the error Starting httpd: httpd: Could not reliably determine the server's fully qualified domain name, using 127.0.0.1 for ServerName.

You can modify ServerName value in the httpd.conf in /etc/httpd/conf, change it to your hostname

ServerName hostname

Reload apache

sudo /etc/init.d/httpd reload

Install Php

sudo yum install php-common php-gd php-mcrypt php-pear php-pecl-memcache php-mhash php-mysql php-xml

After that, please modify the Apache httpd.conf to enable php in Apache


#
# Cause the PHP interpreter to handle files with a .php extension.
#
LoadModule php5_module modules/libphp5.so
AddHandler php5-script .php
AddType text/html .php

#
# Add info.php to the list of files that will be served as directory
# indexes.
DirectoryIndex index.html info.php

Monday, 15 July 2013

SQL Tutorial

Some basic SQLite commands:

.schema table_name
select * from table_name;
select * from table_name where id='640';
.table
delete from devices;
delete from devices where id=25;
alter table devices ADD batteryStatus VARCHAR(255);
create table if not exists table_name (number, name);
create table table_name (Column1 int,
Column2 varchar(255),
Column3 varchar(255)
);
drop table table_name;
.quit

Basic MySQL:

You can access MySQL in command line just by typing:
C:\Program Files\mysql\bin> mysql -u root -p

After which you can type sql commands normally such as:
mysql> SHOW DATABASES;

(Here, I am assuming you mySQL installation directory is "C:\Program Files\mysql")

The sample commands:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.17 sec)

mysql> select version();
+------------+
| version() |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.03 sec)

mysql> select version(), current_date;
+------------+--------------+
| version() | current_date |
+------------+--------------+
| 5.6.12-log | 2013-07-16 |
+------------+--------------+
1 row in set (0.07 sec)

mysql> select sin(pi()/4), (4+1)*5;
+--------------------+---------+
| sin(pi()/4) | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 | 25 |
+--------------------+---------+
1 row in set (0.06 sec)

mysql> select version(); select now();
+------------+
| version() |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

+---------------------+
| now() |
+---------------------+
| 2013-07-16 11:14:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> create database medical;
Query OK, 1 row affected (0.06 sec)

mysql> use medical
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table doctor (name varchar(25), birth date);
Query OK, 0 rows affected (0.19 sec)

mysql> show tables;
+-------------------+
| Tables_in_medical |
+-------------------+
| doctor |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into doctor
-> values ('ken','1999-3-3');
Query OK, 1 row affected (0.04 sec)

mysql> describe doctor;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(25) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from doctor;
+------+------------+
| name | birth |
+------+------------+
| ken | 1999-03-03 |
+------+------------+
1 row in set (0.02 sec)

mysql> insert into doctor
-> values ('boa','1977-3-3');
Query OK, 1 row affected (0.05 sec)

mysql> update doctor set birth = '1989-3-3' where name = 'ken';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from doctor;
+------+------------+
| name | birth |
+------+------------+
| ken | 1989-03-03 |
| boa | 1977-03-03 |
+------+------------+
2 rows in set (0.00 sec)

mysql> select * from doctor where name = 'ken';
+------+------------+
| name | birth |
+------+------------+
| ken | 1989-03-03 |
+------+------------+
1 row in set (0.00 sec)

mysql> select * from doctor where birth >= '1970-1-1';
+------+------------+
| name | birth |
+------+------------+
| ken | 1989-03-03 |
| boa | 1977-03-03 |
+------+------------+
2 rows in set (0.00 sec)

mysql> alter table doctor ADD skill VARCHAR(255);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe doctor;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(25) | YES | | NULL | |
| birth | date | YES | | NULL | |
| skill | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> delete from doctor where birth='1111-01-01';
Query OK, 1 row affected (0.04 sec)

mysql> rename table cus_item to patient_item;
Query OK, 0 rows affected (0.14 sec)

mysql> alter table patient_item change bp_time item_time datetime;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

**SQL script**
use medical;
SET SQL_SAFE_UPDATES=0;
update doctor set skill = 'gastronomy' where name = 'ken';
select * from doctor;

Advanced MySQL commands

** change a column to be a primary key **
mysql> alter table patient add column patient_id int(10) unsigned primary key auto_increment;
Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

** delete a column from table **
mysql> alter table patient drop column patient_id;
Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

** Output to Excel format **
mysql> select * from doctor into outfile 'doctor.csv' fields terminated by ',';

** move SQL column order sequence **
mysql> ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

** change SQL column data type **
mysql> alter table patient modify column Gluco int;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

** select and count **
mysql> select a.*, count(name) as total from doctor a;
+------+------------+----------+-------+
| name | birth | skill | total |
+------+------------+----------+-------+
| ken | 1989-03-03 | surgeon1 | 3 |
+------+------------+----------+-------+
1 row in set (0.00 sec)

** foreign key, primary key **
mysql> create table patient
-> (cus_id int NOT NULL, cus_name varchar(50), primary key(cus_id));
Query OK, 0 rows affected (1.14 sec)

mysql> describe patient;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| cus_id | int(11) | NO | PRI | NULL | |
| cus_name | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.26 sec)

mysql> create table cus_item
-> (cus_item_id int NOT NULL, cus_id int, bp int, bp_time datetime, primary
key(cus_item_id), foreign key(cus_id) references `patient`(cus_id));
Query OK, 0 rows affected (0.15 sec)

mysql> describe cus_item;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| cus_item_id | int(11) | NO | PRI | NULL | |
| cus_id | int(11) | YES | MUL | NULL | |
| bp | int(11) | YES | | NULL | |
| bp_time | datetime | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.09 sec)

mysql> select * from `patient` o join cus_item oi
-> on o.cus_id = oi.cus_id
-> where o.cus_id = 5;
Empty set (0.11 sec)

mysql> insert into patient values
-> (5,'poi');
Query OK, 1 row affected (0.08 sec)

mysql> insert into cus_item values
-> (1, 5, 89, '1999-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into cus_item values
-> (2, 5, 69, '1999-02-01 00:00:00');
Query OK, 1 row affected (0.04 sec)

mysql> select * from `patient` o join cus_item oi
-> on o.cus_id = oi.cus_id
-> where o.cus_id = 5;
+--------+----------+-------------+--------+------+---------------------+
| cus_id | cus_name | cus_item_id | cus_id | bp | bp_time |
+--------+----------+-------------+--------+------+---------------------+
| 5 | poi | 1 | 5 | 89 | 1999-01-01 00:00:00 |
| 5 | poi | 2 | 5 | 69 | 1999-02-01 00:00:00 |
+--------+----------+-------------+--------+------+---------------------+
2 rows in set (0.00 sec)

** save a file directly into MySQL **
mysql> CREATE TABLE IF NOT EXISTS `Files` (
-> `ID` int(25) NOT NULL auto_increment,
-> `Title` varchar(250) NOT NULL default '',
-> `File_Name` varchar(250) NOT NULL default '',
-> `File_Type` varchar(15) NOT NULL default '',
-> `File_Size` varchar(45) NOT NULL default '',
-> `File_Content` longblob NOT NULL,
-> `File_Extension` varchar(10) NOT NULL default '',
-> PRIMARY KEY (`ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.76 sec)

** insert a row into the Files table **

mysql> insert into Files set title = 'me', file_name = 'me.txt', file_type = 'text', file_size = '4k', file_content = '444555', file_extension = 'txt';
Query OK, 1 row affected (0.08 sec)

** update a column with new datetime data Format: YYYY-MM-DD HH:MM:SS **
mysql> update patient_item set item_time = '2012-8-1 15:21:11' where patient_item_id = 107;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

** select data based on time and id **
mysql> select * from cus_item where bp_time <='2012-08-02 08:00:00' and cus_id=5;
+-------------+--------+------+---------------------+-------------+
| cus_item_id | cus_id | bp | bp_time | doc_message |
+-------------+--------+------+---------------------+-------------+
| 1 | 5 | 89 | 1999-01-01 00:00:00 | NULL |
| 2 | 5 | 69 | 1999-02-01 00:00:00 | NULL |
+-------------+--------+------+---------------------+-------------+
2 rows in set (0.05 sec)

SQL Like Opeartor

The Like Operatoe is able to do partial matching with the sql table.

Select * from profile where last_name like "%Mary%" or first_name LIKE "%Mary%"
select * from profile where last_name like '%M%';