PHP - Part 5

Database & Table Creation: phpMyAdmin

Basic Tutorial to illustrate phpMyAdmin: create database and tables.

This basic tutorial is to introduce you to phpMyAdmin.

In this video tutorial, we are creating a database called micro and a table called soft inside database micro.

Eventhough you use the phpMyAdmin interface to create database and table, it executes queries to handle your UI requests.

Database Creation:

1	CREATE DATABASE micro;

Table Creation:

CREATE TABLE  `micro`.`soft` (
`id` INT( 10 ) NOT NULL ,
`powerpoint` VARCHAR( 20 ) NOT NULL
) ENGINE = MYISAM ;

Video Tutorial: Database & Table Creation: phpMyAdmin

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

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

Inside the table, we take 2 fields and enter the needed details.
We have taken id as integer and another field called powerpoint with varchar(20)

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

< ?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

< ?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:

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

Email Verification in PHP: Part 2

This video to illustrates, verification of user email ID by sending a unique URL to be clicked by the user inorder to active his account, to further use the application.

Continuation of Email Verification in PHP: Part 1

login form
login.php

This is bit more than just a simple login form. Because, here we need to check if the user is present in our database, and next, if present, whether the user has activated his/her account or not.

<form action="log.php" method=post>
email: <input type="text" name="u"/>
Pwd: <input type="password" name="p"/>
<input type="submit"/>
</form>

This is just the login form. Below is the coding of log.php file which does all the decision making activities.

login action page
log.php

< ?php
 
include_once('db.php');
 
?>
 
< ?php
 
 $u = $_POST['u'];
 $p = md5($_POST['p']);
 
 $res = mysql_query("SELECT count(*) from login where(email='$u' and pass='$p')");
 
  $row = mysql_fetch_array($res);
 
 if($row[0]>0)
 {
   echo '<b>Successfully logged in</b><br />';
 
   $rs =  mysql_query("SELECT actv from login where email='$u'");
   $rw =  mysql_fetch_array($rs);
 
   if($rw[0] == 0)
   {
 echo '<b>But please activate your email ID, before proceeding</b>';
 exit();
   } 
   else
   {
              $rs = mysql_query("SELECT name from login where email='$u'");
              $rw= mysql_fetch_array($rs);
 echo "Account activated......<br /> <br /> <blockquote><span color=red >
              Welcome ".$rw[0].",</span><br /><br /> This website will be ready 
              soon, and you'll be among the first to hear from us!<br /> 
              Until then, you can browse through our extensive collection of 
              quality tweets submitted by our present members 
              <a href=http://www.twitfever.com>Retweet Club</a><br /><br /> 
              Regards,<br />Satish, CEO Technotip IT Solutions and Training 
              Center</blockquote>";
 
exit();
    }
 
 }
 else
   echo "<br />Login Failed";
 
 
?>

again, we are making use of md5() encryption, inorder to check with the already encrypted data present inside the database.

$res = mysql_query("SELECT count(*) from login where(email='$u' and pass='$p')");
 
  $row = mysql_fetch_array($res);
 
 if($row[0]>0)
 {
   echo '<b>Successfully logged in</b><br />';
 }
 else
   echo "<br />Login Failed";

with this we are checking if the user has already registered or not.

If he/she is a registered user, then we check if the account has been activated or not..

$rs =  mysql_query("SELECT actv from login where email='$u'");
   $rw =  mysql_fetch_array($rs);
 
   if($rw[0] == 0)
   {
 echo '<b>But please activate your email ID, before proceeding</b>';
 exit();
   } 
   else
   {
              $rs = mysql_query("SELECT name from login where email='$u'");
              $rw= mysql_fetch_array($rs);
 echo "Account activated......<br /> <br /> <blockquote><span color=red >
              Welcome ".$rw[0].",</span><br /><br /> This website will be ready 
              soon, and you'll be among the first to hear from us!<br /> 
              Until then, you can browse through our extensive collection of 
              quality tweets submitted by our present members 
              <a href=http://www.twitfever.com>Retweet Club</a><br /><br /> 
              Regards,<br />Satish, CEO Technotip IT Solutions and Training Center
              </blockquote>";
 
exit();
    }

Depending on whether the user has activated his/her account we display the appropriate message.

activate.php
This is the file to which the values will be sent to, once the user clicks on the link sent to his/her email.

Using the values sent by the user(upon clicking the link), this script checks the value against the database to see if its valid. If valid, it updates the act attribute value to 1, which indicated the user verified his/her account.

< ?php include_once('db.php'); ?>
 
< ?php
 
 $act = $_GET['act'];
 $email = $_GET['email'];
 
 $sql = "SELECT rndm from login where email='$email'";
 
 $res = mysql_query($sql);
 $row = mysql_fetch_array($res);
 
 if($row[0] == $act)
 {
  mysql_query("Update login SET actv=1 WHERE email='$email'");
  echo '<b>Your account activated<br /><a href="login.php">Login now</a>';
 }
 else
  echo "Wrong link bro!";
?>

Video Tutorial: Part 2

[youtube https://www.youtube.com/watch?v=Cm5Q0xlir-Q]

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

Other users:
We can make use of this application and allow or disallow some of the features of our application.
This technique highly helps in combating with the spam issues on the web.

You can purchase this simple script directly for as less as $1.95 get here.

Email Verification in PHP: Part 1

This video to illustrates, verification of user email ID by sending a unique URL to be clicked by the user inorder to active his account, to further use the application.

Use
We can restrict certain features of our application for the accounts which has not been verified.
By this we can know if the email ID is valid and authorized user is using it and no one else.

Database

mysql> create database test;
mysql> use test;
mysql> create table login(
   name varchar(25) not null, 
   pass varchar(60) not null, 
   email varchar(30) primary key,
   phn varchar(15), 
   rndm varchar(20) not null, 
   actv int not null);

Note: Do not make phno as integer, as it can’t handle huge numbers like 9844552841 (if its a phone number!)
email is concede red as Primary Key, as we can identify each user uniquely via this attribute. No two user can have the same email id.

Database connection script
db.php

< ?php
 mysql_connect('localhost','DatabaseUsername','DatabasePassword');
 mysql_select_db('DatabaseName');
?>

Registration Code:
register.php

<form action="save.php" method="post">
Name: <input type="text" name="n"/>
eMail ID: <input type="text" name="email"/>
password: <input type="password" name="password"/>
Phn number: <input type="text" name="phn"/>
<input type="SUBMIT"/>
</form>

The user input will be passed to save.php via post method.

save.php

< ?php include_once('db.php'); ?>
< ?php
 
$n = $_POST['n'];
$p = md5($_POST['password']);
$email = $_POST['email'];
$phn = $_POST['phn'];
$rndm = rand(34565, 993240);
 
$sql ="INSERT into login values('$n','$p','$email','$phn', '$rndm', '0')";
 
if(!mysql_query($sql))
 echo "Not updated..".mysql_error();
else
{
 
  $url = "http://yourdomainname.com/reg/activate.php?act=$rndm&email=$email";
  $cont = "click this link $url To activate your account";
 
  if( mail($email, "Activate Account!", $cont, "From: [email protected]") )
   echo '<b>Please visit your email to activate your account
         <br />After Activation, <a href="login.php">log into your account</a>';
  else
   echo '<b>Registration failed..<a href="register.php">please try again</a></b>';
 
}
?>

change yourdomainname.com to the actual domain name the application is hosted on.

md5() is a built-in function. It is a encryption technique for securing the user passwords from intruders.
rand( initialValue, finalValue) is a built-in PHP function which takes range as its parameter and generates a random number between them.

mail() is a built-in PHP function for sending emails.

General Syntax:
mail(To, Subject, Content, From)

Video Tutorial: Part 1

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

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

Also look at: Email Verification in PHP: Part 2

You can purchase this simple script directly for as less as $1.95 get here.