Showing posts with label Mysql. Show all posts
Showing posts with label Mysql. Show all posts

Friday, September 26, 2014

Create an XML file using MYSQL and PHP

In this tutorial am going to show how to create xml file using php and mysql.This script is used to create xml file dynamically.this script will help when you need to create xml file in php and mysql.


Download Script

The content type of the response header must be set to "text/xml".

header ("content-type: text/xml");

DB Connection in php

 mysql_connect('localhost','root','') or die(mysql_error());
 mysql_select_db('database') or die(mysql_error());

SimpleXMLElement is represents an element in an XML document.

Create XML

    //Create XML and stored in your project folder  
    $xmlobj=new SimpleXMLElement($xml);
    //Your file name
    $xmlobj->asXML("text.xml");

Full Source

<?php
        //content type for XML
            header ("content-type: text/xml");
        //DB Connection
            mysql_connect('localhost','root','') or die(mysql_error());
            mysql_select_db('yourdatabase') or die(mysql_error());
        //Create XML
            $xml='<?xml version="1.0" encoding="UTF-8"?>';
        //Query
            $qr=mysql_query("SELECT * FROM `customers` order by id desc") or die(mysql_error());
            $xml.='<userlist>';
            while($res=mysql_fetch_array($qr))
            {
        //Change field names as per your requirement
             $xml.='<user><name>'.$res['name'].'</name><city>'.$res['city'].'</city><state>'.$res['state'].'</state></user>';
            }
            $xml.='</userlist>';
        //Display Content
            echo $xml;
        //Create XML and stored in your project folder    
            $xmlobj=new SimpleXMLElement($xml);
        //Your file name
            $xmlobj->asXML("text.xml");
    ?>

Please share your comments and feedback.Thanks.Please subscribe my updates via email.

Sunday, June 22, 2014

CSV to Mysql import in PHP

Today i am going to explain how to import values form CSV to Mysql database.It is very useful to import bulk amount data's very easily.

Let's have look.



Step 1: You need a database connection

db.php

     <?php
        define ("DB_HOST", "localhost"); // set database host
        define ("DB_USER", "root"); // set database user
        define ("DB_PASS",""); // set database password
        define ("DB_NAME","db"); // set database name
      
        $link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
        $db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");
    ?>
 
Create a database and import the following sql query.

users.sql

CREATE TABLE IF NOT EXISTS `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `email` varchar(200) NOT NULL,
  `city` varchar(200) NOT NULL,
  `image` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Used functions and Definition:

is_file() — Checks wheather the specified file is a regular file.
fopen - Used to open a file or url.
fgetcsv — Gets line from file pointer and parse for CSV fields

csv.php

      <?php
    if (isset($_POST['csvimport']))
{
  $csv_file = $_FILES['csvfile']['tmp_name'];

  if (!is_file($csv_file))
    exit('File not found.');

  $sql = '';

  if (($handle = fopen( $csv_file, "r")) !== FALSE)
  {
      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
      {
          $sql .= mysql_query("INSERT INTO `users` SET
            `id` = '$data[0]',
            `name` = '$data[1]',
            `email` = '$data[2]',
            `city` = '$data[3]',
            `image` = '$data[4]'") or die("could not insert into DB");
      }
      fclose($handle);
  }

  exit( "Complete!");
}
?>

csv.html

<form enctype="multipart/form-data" method="POST" action="">
      <input name="csvfile" type="file">
      <input type="submit" name="csvimport" value="Upload">
    </form>

Please share your comments and feedback.Thanks.Please subscribe my updates via email.

Friday, June 13, 2014

Auto Refresh using PHP & Jquery

In this post i want to explain how to autorefresh data's.We can fetch values without loading whole page using this script.Most popular Social websites are using this script like twitter,facebook etc.

Let's have look.


Live DemoDownload Script

Create table using the following command.

Mysql Table

        CREATE TABLE IF NOT EXISTS `users` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `uname` varchar(225) NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

conn.php

<?php
        $conn=mysql_connect("localhost","root","");
        $db=mysql_select_db("autorefresh",$conn); //replace with your DB name
    ?>

JQuery Function

<script type="text/javascript">
    var autorefresh = setInterval(
    function (){
        $('#items').load('count.php').fadeIn("slow"); //Include PHp file for dynamic update
    }, 1000); //refresh every 1000 milliseconds
  
    </script>

Display dynamic results using PHP.I have stored values in mysak and retrived from table.

PHP function

<?php
    $res=mysql_query("select * from users order by id ASC"); ?>
<table width="500" border="1" cellspacing="0" cellpadding="0" style="border:1px solid #ccc;">
  <tr style="border-bottom:1px solid #ccc">
    <td width="250" style="padding:5px;">SNo</td>
    <td width="250" style="padding:5px;">Username</td>
  </tr>
  <?php
    while($row=mysql_fetch_array($res)){ ?>
  <tr>
    <td width="250" style="padding:5px;"><?php echo $row['id'];?></td>
    <td width="250" style="padding:5px;"><?php echo $row['uname'];?></td>
  </tr>
  <?php } ?>
</table>


Please share your comments and feedback.Thanks.Please subscribe my updates via email.

Friday, May 9, 2014

PHP Search using Jquery

In this artcile am going to shows how to get search results using PHP and Jquery



Table.sql

Create a table using the following sql comments.
    
      --
      -- Table structure for table `users`
      --
    
      CREATE TABLE IF NOT EXISTS `users` (
      `userid` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(225) NOT NULL,
      PRIMARY KEY (`userid`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Conn.php

      <?php

$connection=mysql_connect("localhost","root","");
$db=mysql_select_db("database",$connection);

// Check connection
if (!$connection) {
    die('Could not connect: ' . mysql_error());
}

?>

Search.php

  <?php $data='';

if(isset($_POST['search'])) {
    $uname = $_POST['search'];
    $uname = preg_replace("#[^0-9a-z]#i","",$uname);
    $query = "select username from users where username LIKE '%$uname%'";
    $result = mysql_query($query);
    $count = mysql_num_rows($result);
if($count>0)
{
while($row = mysql_fetch_array($result))
    {
        $data = $data."<div>".$row['username']."</div>";
    }  
        echo "<h1>Search results:</h1><br>";
        echo $data;
    }

}
    else
{
?>
<html>
<head>
<title>PHP search using Ajax</title>
<script type="text/javascript" src="http://code.jquery.com/jquery-git2.min.js" ></script>
<script type="text/javascript">
$(function(){
    $('.search_results').keyup(function(){
        var search = $('.search_results').val();
            $.post("search.php",{"search":search},function(data){
                $('.results').html(data);
        });
    });
});
</script>
</head>
<body>
<form action="search.php" method="post">
  <input type="text" name="search" class="search_results" />
  <input type="submit" value="search"/>
</form>
<div class="results"></div>
</body>
</html>
<?php
}
?>
Please share your comments and feedback.Thanks.

Tuesday, April 29, 2014

Password reset using PHP and Mysql

In this article i am going to explain how to reset password using PHP,Mysql.

There are many methods available for password reset.In this i have used php email function to reset the password.I have used both $_POST and $_GET method for this.

If not registered user entered email address it shows error,if email was exists in database shows success message and sent mail with reset link.

The email contains reset link the user.if you click the link it will go to  password reset page.





Functionality files:

1.forgotpassword.php
2.resetpassword.php

In this you will learn the following things.

1.Send mail using PHP
2.UPDATE Query

Form

 <form name="forgotpwdform" action="#" method="post">
          <h3><a href="#" title="Forgot Password">Forgot Password</a></h3>
          <label>Email</label>
          <input type="email" name="Email" required/>
          <p>
            <input type="submit" name="ForgotPassword" value="Submit" id="submit"/>
          </p>
        </form>

Mail function with reset option

      <?php
if(isset($_POST['ForgotPassword'])){

        $email=$_POST['Email'];
        //Query
        $Password=mysql_query("SELECT Password FROM users WHERE Email='$email'");
      
        $NumRows=mysql_num_rows($Password);
       
                     if($NumRows == '0') { 
                        echo 'Sorry..Your Email not found in our database. Please try again';
                        header('Location:forgotpassword.php');
                        exit();
 } else {
               
                $to=$email; //To Mail
                $subject="Password Assistance"; //Subject
                $headers = "From: yourweb@website.in\r\n";
                $headers .= "MIME-Version: 1.0\r\n";
                $headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
                $headers .= "X-Priority: 1\n";
                $headers .= "X-MSmail-Priority: High\n";
                $headers .= "X-Mailer: PHP". phpversion() ."\r\n" ;
           
            //Message
            $message = " Hi ,\r\n";
            $message .= "To initiate the password reset process for your $Email Account, click the link below:\r\n";
            $message .= "http://www.yourwebsite.com/resetpassword.php?Email=$email\r\n";
            $message .= "Cheers,\r\n";
            $message .= " Team\n\n";
         
            //Mail Script
            $sentmail=mail($email, $subject, $message,$headers);
        }
                       
    if($sentmail) {
        echo 'Your Password reset link Has Been Sent To Your Email Address.Please check your Inbox!';
        header('Location:forgotpassword.php');
        exit();
 }  else {

         echo 'Error.Please try again!';
         header('Location:forgotpassword.php');
        exit();
         }
    }
?>

Password Reset Form

<form accept-charset="UTF-8"  name="ProfileUpdate"  method="post" action="#" id="reset-form" style="width:590px">
          <h3>Reset Password</h3>
          <div >
            <div>
              <label>New Password</label>
            </div>
            <div>
              <input type="password" name="Password1"  id="Password1" class="user-text" />
            </div>
          </div>
          <div>
            <div>
              <label>Confirm New Password</label>
            </div>
            <div >
              <input type="password" name="Password2" id="Password2" class="user-text1"/>
            </div>
          </div>
          <div>
            <p>
              <input type="submit" name="ResetPassword" value="Submit"  id="submit" />
            </p>
          </div>
        </form>

Reset function in PHP

 <?php
//Database config
$connection=mysql_connect("localhost","root","");
$db=mysql_select_db("campustiger",$connection);

// Check connection
if (!$connection) {
    die('Could not connect: ' . mysql_error());
}

if(isset($_POST['ResetPassword'])) {

     $currentpassword = $_POST['Password']; // get the current password from                                                                   
     $newpassword = $_POST['Password1']; // get the new password from the                                                                       
     $hash = md5($newpassword); // encrypt the current password supplied by the user
                                                          
     $email = $_GET['Email'];
     $result = mysql_query("SELECT Password from users  where Email='$email'");  // query the database for getting password for a the user.

     while($row = mysql_fetch_array($result)) {
         $p=$row['Password'];
     }
    // get the encrypted password from the database for the user
     ?>
      <?php                                    

           $result = mysql_query("UPDATE users set Password='$hash' where Email='$email' ");
          
           if($result) {
                     header('Location:login.php');
         }
         else {
                    echo 'OOPS!.Please try again';
                    header('Location:resetpassword.php');
                    exit();
 }
    }
?>
Please share your comments and feedback.Thanks.

Thursday, April 24, 2014

Simple search using PHP and Mysql

In this article i am going to explain how to search values using PHP,Mysql.

In this you will learn the following things:

1.$_POST Method
2.Databse Connection
3.Communication between Database and PHP
4.Fetch results from datbase and display.



Functionality Files:

1.Search.php
2.conn.php


Database Connection

   <?php
          $conn=mysqli_connect("localhost","root","","search") or die('Error connection');
        /*Hostname = "localhost".Replace with your hostname*/
         /*Databse Username="root".Replace with your Username*/
         /*Databse Password="".Replace with your Password*/
         /*Databse Name="search".Replace with your database name*/

    ?>

Search.php

     <?php
  $conn=mysqli_connect("localhost","root","","search") or die('Error connection');
  $search_results = '';
  if(isset($_POST['search']))
  {
    $str = $_POST['search'];
    $str = preg_replace("#[^0-9a-z]#i","",$str);
    $query = "SELECT UserName FROM users WHERE UserName LIKE '%$str%'";
    $result = mysqli_query($conn,$query);
    $count = mysqli_num_rows($result);
    if($count>0)
    while($row = mysqli_fetch_array($result))
      {
        $search_results = $search_results."<div>".$row['UserName']."</div>";
      }
  }

?>
    <form action="#" method="post">
      <input type="text" name="search" />
      <input type="submit" value="search"/>
    </form>
    <?php if(isset($_POST['search'])) { ?>
    <h2>Search Results:</h2>
    <?php echo $search_results; ?>
    <?php } ?>
Please share your comments and feedback.Thanks.

Tuesday, April 15, 2014

INSERT Values in Mysql table using php

In this post am going to explain how to write database connection and how to insert values in mysql table.Let's take a look .

Description

Insert values in mysql using php.it's very easy to insert values.
Functionality files:
1.Index.php
2.conn.php
3.insert.php


index.php

<div class="wrapContainer">
  <div class="codeContainer">
    <p class="Blue">
    <form id="formID" action="insert" name="registerform" method="pos">
      <p>
        <input type="text" name="name" value="" placeholder="Name"/>
      </p>
      <p>
        <input type="text" name="phonenumber" value="" "" placeholder="Phonenumber"/>
      </p>
      <p>
        <input type="text" name="address" value="" "" placeholder="Address"/>
      </p>
      <p>
        <input type="submit" name="submit" value=""/>
      </p>
    </form>
  </div>
</div>

conn.php

  
  <?php
$conn=mysql_connect("localhost","root","")or die('Database not connected');
$db=mysql_select_db("Your Database")or die('Database not connected');
?>



insert.php

  <?php
if(isset($_POST['submit']))
{
$name=$_POST['namename'];
$number=$_POST['phonenumber'];
$address=$_POST['address'];
$insert=mysql_query("insert into ddtable(name,number,address)values('$name','$number','$address')");
}
?>

Tuesday, April 1, 2014

Select values using multiple id’s in mysql


Let’s have a look how to select values using multiple id’s in where condition

Use IN keyword to perform this action

Definition of INkeyword:
                The IN keyword is used to specify multiple values in WHERE clause
 

SELECT * FROM mytable WHERE id IN (2,5,11);

mytable – Replace with your table name
id – Your auto increment id

Saturday, March 29, 2014

Export table values from MYSQL to CSV Using PHP

In this article we will discuss about export table values from MYSQL to CSV.

Let's have look.


Change values as per your requirement.

conn.php

<?php

        //config file
      
        define('host','localhost');/*Hostname*/
       
        define('username','root');/*Username*/
       
        define('password','');/*Password*/
       
        define('database','register');/*Database*/
       
        $conn=mysql_connect(host,username,password) or die(error);
       
        $db=mysql_select_db(register,$conn);
       
        ?>

sqltocsv.php

<?php

include("conn.php"); //Database config file

$result = mysql_query("select * from reg");/*edit as per your requirement*/

if (mysql_num_rows($result) > 0)

{

$csv="FIRSTNAME".","."LASTNAME".","."GENDER".","."MONTH".","."COUNTRY".","."EMAIL".","."PASSWORD".","."PASSWORD1".","."EMAIL1".","."SECERT1".","."ANSWER".","."SECRET2".","."ANSWER1"."\n";

while ($row = mysql_fetch_array($result))

{

$csv.= $row['firstname'].",".$row['lastname'].",".$row['gender'].",".$row['month'].",".$row['country'].",".$row['email']

.",".$row['password'].",".$row['password1'].",".$row['email1'].",".$row['secert1'].",".$row['answer'].",".$row['secret2'].",".$row['answer1'];

$csv.= "\n";

}

}

//print $csv;

$filename = date("Y-m-d/H-i",time());

header("Content-type: application/vnd.ms-excel");

header( "Content-disposition: filename=".$filename.".csv");

print $csv;

exit;

?>

Please share your comments and feedback.Thanks.Please subscribe my updates via email.

Friday, March 28, 2014

Date wise search in mysql using php

Query for date wise search:

select * from *table_name* where *datetime_column* >= '01/01/2009' and *datetime_column* <= curdate()

Generate random letter in php

Use the following PHP code to generate a random letters.

<?php 
for($i=0;$i<5;$i++) 
    $let[$i] = randLetter(); 
function randLetter()
{
    $int = rand(0,51);
    $a_z = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $rand_letter = $a_z[$int];
    return $rand_letter;
}
$word = implode('',$let);
echo $word;
?>

PHP Login Example



Simple login example using PHP & Mysql.

Download Link



Step 1:
Create table using the following sql commands.

CREATE TABLE IF NOT EXISTS `admin` (
  `AdminId` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(225) NOT NULL,
  `Password` varchar(225) NOT NULL,
  PRIMARY KEY (`AdminId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Step 2:
HTML codes for Login form (Login.php)

<form action="#" method="POST">
    <fieldset>
    <p>
      <label for="email">E-mail address</label>
    </p>
    <p>
      <input type="text" id=" username " name="username" value="" required >
    </p>
    <p>
      <label for="password">Password</label>
    </p>
    <p>
      <input type="password" name="password" id="password" value="" required>
    </p>
    <p>
      <input type="submit" name="Login" value="Sign In">
    </p>
    </fieldset>
  </form>

Step 3:
Write database connection(config.php)

<?php
session_start();
$host = "localhost"; /*Your Hostname*/
$user = "root"; /*Your Databse Username*/
$pass = ""; /*Your Databse Password*/
$db_name = "login"; /*Your Databse Name*/
//database connection
$link = mysql_connect($host, $user, $pass);
$db=mysql_select_db($db_name);
?>

Step 4:
Check posted username and password are correct(include this code top of the login.php)
<?php
                //Database file
                require_once("config.php");
               
                if(isset($_POST['Login'])) {
                                //Get values from post
                                $username=mysql_real_escape_string($_POST['username']);
                                $password1=mysql_real_escape_string($_POST['password']);
                                $password=md5($password1); // Encrypted Password
                               
                                //Check method.Query is correct or not.
                                //echo "SELECT * FROM admin WHERE username='$username' AND Password='$password'";
                                //exit;
                               
                                //Query for login
                                                $res="SELECT * FROM admin WHERE username='$username' AND password='$password'";
                                                $result=mysql_query($res);
                                                $count=mysql_num_rows($result);
                                               
                                // If result matched $username and $password, table row must be 1 row
                                if($count==1)
                                                                                {   //Store values in SESSION
                                                                                                $_SESSION['username'] = $_POST['username'];
                                                                                                $username=$_SESSION['username'];
                                                                                                header("location:welcome.php");//redirect to welcome page
                                                                                } else {
                                                                                                ?>
                                                                                <script type="text/javascript">
                                                                                                 alert('OOPS!.Either the username or the password is wrong. Please try again');
                                                                                                 window.top.location="index.php";
                                                                                </script>
                                                                                <?php }
                                                               
                }
?>

Step 5:
If login successful redirect to welcome page.(welcome.php)
<?php
                //Database file
                require_once("config.php");
                $username=$_SESSION['username'];
?>
Wecome <?php echo $username; ?><a href="logout.php">Logout</a>

Step 6:
Logout.php

<?php

/**
 * The logout file
 * destroys the session
 * expires the cookie
 * redirects to login.php
 */
session_start();
setcookie('Email', '', time() - 1*24*60*60);
setcookie('Password', '', time() - 1*24*60*60);
//Session Destroy
if(session_destroy())
{
                header("location:index.php");
} ?>


Thursday, March 27, 2014

Database connection in PHP

Simple code for mysql database connection

Syntax

<?php
$host = "localhost"; /*Host Name*/
$user = "root";  /*User Name*/
$pass = ""; /*Password*/
$db_name = "realestate";  /*Database Name*/

//database connection
$link = mysql_connect($host, $user, $pass);
$db=mysql_select_db($db_name);

?>

Monday, March 24, 2014

Export Excel table details form Mysql database using PHP

Let's take a look at how to export details from mysql database using PHP

We need library function,Please click here to download working files.

ExportExcel.php

<?php
require_once("dbconfig.php"); /*replace with your dbconfig file*/

require 'exportlib.php'; /*replace with library file(Downloaded file)*/

$table="your table name"; /*replace with your table name*/

exportMysqlToCsv($table);
?>

dbconfig.php

PHP database configuration file

<?php

$hostname = "Your Host name";
$user = "Your Database UserName";
$password = "Your Database  Password";
$database = "Your Database Name";
$db = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
mysql_select_db($mysql_database, $db) or die("Could not select database");
?>