Tag: database

Insertion of Records into Database: PHP & MySQL

Video tutorial illustrates INSERTION of records into database via PHP script.

Simple / basic insertion operation:

First connect to the database.

Next, using simple MySQL query.
index.php

1
2
3
4
5
6
7
8
9

< ?php
include_once('db.php');
 
 
if(mysql_query("INSERT INTO apple VALUES('','Oracle')"))
echo "Successful Insertion!";
else
echo "Please try again";
?>

make sure to watch other video about connecting to the database, to understand include_once(‘db.php’);

Video Tutorial: Insertion of Records into Database: PHP & MySQL


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

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



die function:

1
2
3
4
5
6
7
8
9

< ?php
 
   include_once('db.php');
 
   $res = mysql_query("INSERT INTO apple VALUES('','Oracle')") 
                            or die("sorry, database is busy!");
 
   echo "Successful insertion";
?>

if the insertion operation fails for some reason, the die function is invoked and the message “Sorry, database is busy!” is shown to the use and the script execution halts, and the “Successful insertion” message won’t be echoed.

Data in the database, as shown in the video:
1. Google
2. Apple
3. Microsoft
4. Oracle

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.

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')