Tag: mysql

Connecting To MySQL server From PHP

Most application programs need to be connected to the database inorder to do some basic operations like saving and retrieving the user details / data.

In this video tutorial we illustrate, how we need to connect to MySQL server from PHP script.

Connecting to MySQL server
db.php

1
2
3
4

< ?php
$conn = mysql_connect('localhost','root','');
$db   = mysql_select_db('database_name', $conn);
?>

Include this file in all other files which needs to communicate with this database database_name

1
2
3
4
5
6

< ?php
include_once('db.php');
?>
 
INSERT, SELECT, UPDATE, DELETE, DROP etc ..operations 
on the datbase.

Video Tutorial: Connecting To MySQL server From PHP


[youtube https://www.youtube.com/watch?v=EAJ0XsQysf8]

YouTube Link: https://www.youtube.com/watch?v=EAJ0XsQysf8 [Watch the Video In Full Screen.]



There are many variations of include:
include();
include_once();

require();
require_once();

require produces fatal error if the required file is not found. include produces warning when the file is not found.
include_once and require_once makes sure the files are included only once and not included multiple times in the same file.

Primary Foreign Unique Keys, AUTO_INCREMENT: MySQL

Video tutorial illustrates the use of Primary Key, Foreign Key, Unique Key, AUTO_INCREMENT and NOT NULL in MySQL.

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys have unique values.
Primary Keys are NOT NULL by default.

1
2
3
4
5
6

CREATE TABLE Apple
(
 id int NOT NULL,
 name varchar(255) NOT NULL,
 PRIMARY KEY(id)
);

Unique Key
The UNIQUE constraint uniquely identifies each record in a database table.

UNIQUE keys are not Primary Keys. but all Primary keys are Unique keys.

1
2
3
4
5
6

CREATE TABLE Apple
(
 id int NOT NULL,
 name varchar(255) NOT NULL,
 UNIQUE(id)
);

Foreign Key
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

1
2
3
4
5
6
7

CREATE TABLE Orders
(
 id int NOT NULL,
 O_No int NOT NULL,
 PRIMARY KEY(O_No),
 FOREIGN KEY(id) REFERENCES Apple(id)
);

If your MySQL uses InnoDB engine, then use the following query:

1
2
3
4
5
6
7

CREATE TABLE Orders
(
 id int NOT NULL,
 O_No int NOT NULL,
 PRIMARY KEY(O_No),
 FOREIGN KEY(id) REFERENCES Apple(id)
)ENGINE=InnoDB;

Video Tutorial: Primary Foreign Unique Keys, AUTO_INCREMENT: MySQL


[youtube https://www.youtube.com/watch?v=01Y1T44D9JU]

YouTube Link: https://www.youtube.com/watch?v=01Y1T44D9JU [Watch the Video In Full Screen.]



phpMyAdmin UI Interface Options
Options for assigning Primary key, unique key to the attributes..also the edit and delete keys.

primary-unique-edit-delete

AUTO_INCREMENT and NOT NULL

auto-increment-not-null


1
2
3
4
5
6

CREATE TABLE Apple
(
 id int NOT NULL AUTO_INCREMENT,
 name varchar(255) NOT NULL,
 PRIMARY KEY(id)
);

AUTO_INCREMENT is used for integer type data.. the advantage being, we need not explicitly send the data to this field. MySQL automatically inserts the value to this field.

Database & Table Creation: MySQL console (commands/Queries)

Basic Tutorial to illustrate mysql console: create database and tables. Desc Tables; show tables; show databases;

Left click, as well as right click on the WAMP/XAMP/MAMP/XAMPP icon in your task bar and see the options.
MySQL console will be present under the MySQL folder.

Usually your local installation of MySQL will have username as root and there will be no password. If you’re trying this on your commercial server, then make sure to use your correct username and password.

Video Tutorial: Database & Table Creation: MySQL console (commands/Queries)


[youtube https://www.youtube.com/watch?v=6nrtU4q7J2Q]

YouTube Link: https://www.youtube.com/watch?v=6nrtU4q7J2Q [Watch the Video In Full Screen.]



Database Creation

1

mysql> CREATE DATABASE software;

CREATE DATABASE are keywords.
software is the name given by us to the new database;

1

mysql> show databases;

This query / command would list all the databases present inside our MySQL installation.

1

mysql> use software;

This query / command tells which database we will be working on from now.

1

mysql> show tables;

This query / command would list all the tables present inside the database we are using or working on.

1

mysql> CREATE table Apple(id int, name varchar(20));

This would create a table by name Apple, which has two fields id and name.
Here id is of integer type: default size 11.
and name is of type varchar which is of size 20 – user allocated and its not default.

1

mysql> desc Apple;

This would show the description or the structure of the table Apple.

Format Date and Insert Into Database Table: PHP

Convert string type variable data to formatted date type variable and insert it into database table..

Connecting the PHP Script to database
db.php

1
2
3
4

< ?php
$conn = mysql_connect("localhost","username","password");
$db      = mysql_select_db("table_name",$conn);
?>

Our database name is technotip and table name is temp.
date-time.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

< ?php
       include_once('db.php');
 
// [created_at] => Sun Mar 23 06:39:16 +0000 2008
 
$raw = "Sun Mar 23 06:39:16 +0000 2008";
 
$xplod = explode(' ', $raw);
 
print_r($xplod);
 
$string = "$xplod[5]-$xplod[1]-$xplod[2] $xplod[3]";
 
echo "<br />$string";
 
$date = date("Y-m-d H:i:s", strtotime($string));
 
echo "<br />$date";
 
 
if(msql_query("INSERT INTO test VALUES('$date')"))
echo "Inserted successfully!";
else
echo "Failed .. please try again!";
 
?>

Twitter and other API’s return these kind of string Sun Mar 23 06:39:16 +0000 2008
which we store into a variable. using PHP standard function explode we separate the elements based on space.

Video Tutorial: Format Date and Insert Into Database Table: PHP


[youtube https://www.youtube.com/watch?v=SceCb09fwSo]

YouTube Link: https://www.youtube.com/watch?v=SceCb09fwSo [Watch the Video In Full Screen.]



Now we use print_r() function to see the structure of the array:

1
2
3
4
5
6
7
8

Array ( 
  [0] => Sun 
  [1] => Mar 
  [2] => 23 
  [3] => 06:39:16 
  [4] => +0000 
  [5] => 2008 
)

Next, by using its index value we arrange the date according to the format that is suitable to be stored into mysql table.

Convert the string to time format using strtotime() php standard function.

Finally, using date() function match the date formats and store it inside another variable which is then used to pass the date to the database.

Here is the SQL Query:

1

INSERT INTO test VALUES('$date')