Category: mysql

In this tutorial, I am going to tell you how to Format DateTime  in SQL Server

Format DateTime in SQL SERVER

Format DateTime and Date SQL SERVER:- Common Requirement for any developer nowadays is to format Date and DateTime in SQL Server.Below are some scripts to get the required format.

Format DateTime

You must follow the following links.

User Defined Function to Extract Number From String in SQL Server.

Generate Random Number in SQL Server

1)  Format- mon dd yyyy hh:mmAM (or PM)

Query to Format date and DateTime in SQL Server –  SELECT convert(varchar, getdate(), 100)

OUTPUT  — Jun 28, 2016, 3:39PM

2) Format- mm/dd/yyyy

Query to Format date and datetime in Sql Server SELECT convert(varchar, getdate(), 101)

Output- 06/28/2016

3) Format-  yyyy.mm.dd

Query to Format date and datetime in Sql Server- SELECT convert(varchar, getdate(), 102)

Output- 2016.06.28

4) Format-  dd/mm/yyyy

Query to Format date and datetime in Sql Server- SELECT convert(varchar, getdate(), 103)

Output- 28/06/2016

5) Format- dd.mm.yyyy

Query- SELECT convert(varchar, getdate(), 104)

Output- 28.06.2016

6) Format- dd-mm-yyyy

Query- SELECT convert(varchar, getdate(), 105)

Output- 28-06-2016

7) Format- dd mon yyyy

Query- SELECT convert(varchar, getdate(), 106)

Output- 28 Jun 2016

8) Format- mon dd, yyyy

Query- SELECT convert(varchar, getdate(), 107)

Output- Jun 28, 2016

9) Format- mon dd yyyy hh:mm:ss:mmmAM (or PM)

Query- SELECT convert(varchar, getdate(), 109)

Output- Jun 28 2016  3:53:32:580PM

10) Format- mm-dd-yyyy

Query- SELECT convert(varchar, getdate(), 110)

Output- 06-28-2016

11) Format- yyyy/mm/dd

Query- SELECT convert(varchar, getdate(), 111)

Output- 2016/06/28

12) Format- yyyymmdd

Query- SELECT convert(varchar, getdate(), 112)

Output- 20160628

13) Format- dd mon yyyy hh:mm:ss:mmm

Query- SELECT convert(varchar, getdate(), 113)

Output- 28 Jun 2016 15:57:25:063

14) Format- hh:mm:ss:mmm(24h)

Query- SELECT convert(varchar, getdate(), 114)

Output- 15:58:19:950

15) Format- hh:mm:ss:mmm(24h)

Query- SELECT convert(varchar, getdate(), 120)

Output- 2016-06-28 15:59:52

16) Format- yyyy-mm-dd hh:mm:ss.mmm

Query- SELECT convert(varchar, getdate(), 121)

Output- 2016-06-28 16:00:40.233

17) Format- yyyy-mm-ddThh:mm:ss.mmm

Query- SELECT convert(varchar, getdate(), 126)

Output- 2016-06-28T16:01:29.687

Conclusion :-

I hope you would like this post about Format DateTime and Date SQL SERVER.Please give feedback if you want to see improvement.Your Feedback matters to us.

Autocomplete search functionality using Php and MySQL:


Autocomplete search functionality using Php and MySql: 

Hi readers, today we came up with the new technical tutorial on Autocomplete search functionality using Php and MySql. This tutorial mainly concerns on the concept of the Autocomplete functionality. Here we have used the Jquery plugins to create autocomplete text box instead of UI widget class and Jquery UI library.

So we can simply create an Autocomplete functionality by including this Jquery ( //netsh.pp.ua/upwork-demo/1/js/typeahead.js ) in our html file.

Let’s have a look at this tutorial in detail to understand the concept and run the program by using the full source code given here or simply download the Zip file provided at the end.

Simple Example:


Step 1:

First of all, we Create a database and table to store records by using PHPMyAdmin SQL tab:

//create database
create database search;

//create table
create table customer (
customerId int(11) AUTO_INCREMENT PRIMARY KEY,
custName varchar(100),
custCity Varchar(100),
custZip int(11)
);

Step 2:

Create an HTML file named as suggestion.html and copy the code from the source code given in this tutorial and then save it. In this file, we simply include the Jquery plugins in the <script> tag and the simple text box to search data.

Like a suggestion.html file, create a two php file named db.php and autoSearch.php and copy the source code given here and then save it in a php folder.

db.php file is a configuration file which contains login credentials to access the database using MySQLi procedural functions.

An autosearch.php file is the main php file which contains query to search data as soon as we type in the text box and finally encode data into JSON array. Here in the source code one can easily understand the functionality of each statement with the help of comments. So no need to worry simply go through the full source code.

Full Source Code:


suggestion.html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8"> 
    <title>PHP MySQL Typehead drop-down Suggestion</title>
    <link href="bootstrap/bootstrap.min.css" rel="stylesheet">
    <script src="js/jquery-2.1.4.min.js"></script>
    <script src="js/bootstrap.min.js"></script>

    <!--JQUERY PLUGINS used here-->
    <script src="js/typeahead.js"></script> 
    <style> 
       h1 {
            font-size: 30px;
            color: gray;
             margin: 30px;
        } 
      .style{
            height:50px; 
           }
     </style>
     <script> 
       $(document).ready(function() {
            $('input#custName').typeahead({
                name: 'custName',
                remote: 'php/autoSearch.php?query_search=%QUERY'
            });
        })
    </script>
</head>

<body>
  <div class="container" align="center">
    <div class="row">
    <h1>Autocomplete or Suggestion box to search records automatically</h1>
   <form>
        <div class="style">
        <input type="text" id="custName" name="custName" size="50" placeholder="Please Enter Name...">
       <div>
   </form> 
   </div>
 </div>

</body>
</html> 

php/db.php
<?php
//Credentials for database
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "search";

//Create a Connection
$conn=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

// Check Connection
if(!$conn){
 die("connection does not establish,error: ". mysqli_connect_error());
}
?>

php/autoSearch.php
<?php
require_once 'db.php';
if (isset($_REQUEST['query_search'])) {
     $query_search = $_REQUEST['query_search'];
 //query to select matching record
 $sql= "SELECT custName, custCity, custZip FROM customer WHERE custName LIKE '%{$query_search}%'
        OR custZip LIKE '%{$query_search}%'";
 //run the above SQL query    
$result=mysqli_query($conn,$sql);
 //initialise an array
 $array = array();
 //fetch all the row in an associative array
    while ($rows = mysqli_fetch_array($result)) { 
       $array[] = array ( 
           'label' => $rows['custName'].', '.$rows['custZip'],
            'value' => $rows['custName'], $rows['custZip']
        );
    }
    //encode into JSON array
    echo json_encode ($array);}
?>

ScreenShot View:

Autocomplete search functionality Autocomplete search functionality


Download :  search.zip

 

I Hope you would like this tutorial on Autocomplete search functionality using Php and MySql!

Create Web Service Using PHP:- Consist of protocol like XML, JSON,etc..that  interact between other web applications to get output in the form of XML  and JSON to the purpose of exchange data.

You can exchange data between two web applications built on different platform languages.To exchange data we need output in the form of XML and JSON. Which are protocols that are used to exchange information between web applications?

Here we start topic on Create Web Service Using PHP

 XML

XML stands for Extensible Markup Language is an important protocol for web services in PHP.It can be used to store and exchange data while communicating between web applications.XML was designed to be both human- and machine-readable.

JSON

JSON  (JavaScript Object Notation) is a lightweight data-interchange format.which can also store and exchange data.Output comes in the form of text and it is also human-readable and completely language independent.

Create Web Service Using PHP CODE

First of all, we have to create a web service file in which we have to write a code in PHP & MySQL to get a response in the form of JSON and XML.You have to create  a file name with webservice.php on server and copy the below code

The PHP / MySQL Web service Code

/* require the user as the parameter */
if(isset($_GET['userid']) && intval($_GET['userid'])) {

	/* here you can configure format of output,number of records you want to fetch */
	$number_of_posts = isset($_GET['number']) ? intval($_GET['number']) : 20; //20 is the default
	$format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml'; //xml is the default
	$user_id = intval($_GET['userid']); //no default

	/* connect to the mysql database with your own database setting */
	$link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');
	mysql_select_db('db_name',$link) or die('Cannot select the DB');

	/* grab the posts from the database you can customize your query on the basis of table */
	$query = "SELECT post_title, guid FROM wp_posts WHERE post_author = $user_id AND post_status = 'publish' ORDER BY ID DESC LIMIT $number_of_posts";
	$result = mysql_query($query,$link) or die('Errant query:  '.$query);

	/* create one master array of the records */
	$posts = array();
	if(mysql_num_rows($result)) {
		while($post = mysql_fetch_assoc($result)) {
			$posts[] = array('post'=>$post);
		}
	}

	/* output in JSON format */
	if($format == 'json') {
		header('Content-type: application/json');
		echo json_encode(array('posts'=>$posts));
	}
	else {
/*  XML FORMAT Output*/

		header('Content-type: text/xml');
		echo '<posts>';
		foreach($posts as $index => $post) {
			if(is_array($post)) {
				foreach($post as $key => $value) {
					echo '<',$key,'>';
					if(is_array($value)) {
						foreach($value as $tag => $val) {
							echo '<',$tag,'>',htmlentities($val),'</',$tag,'>';
						}
					}
					echo '</',$key,'>';
				}
			}
		}
		echo '</posts>';
	}

	/* disconnect from the db */
	@mysql_close($link);
}


In the above code, First of all, we have configured the input value to interact with the web application.Then we have to write a code to connect to the database after executing a query, we will get a result and that output result we have converted in a format like  JSON and XML in the form of the array.

Take the following example URL for Create Web Service Using PHP:

http://techieflair.com/webservice.php?userid=1&number=10


After Executing above URL you will get result in XML format

 XML Output

<posts>
<post>
<post_title>
How we can create a Login and Logout Form in Php and mysql
</post_title>
<guid>http://techieflair.com/?p=538</guid>
</post>
<post>
<post_title>
How we can create a Simple Registration form in php
</post_title>
<guid>http://techieflair.com/?p=530</guid>
</post>
<post>
<post_title>
How we can use Group By,Having and Order By Mysql Clauses using php script
</post_title>
<guid>http://techieflair.com/?p=510</guid>
</post>
<post>
<post_title>
How we can use Pattern matching in Mysql using php script
</post_title>
<guid>http://techieflair.com/?p=498</guid>
</post>
<post>
<post_title>
How we can retrieve data from Mysql using Php script
</post_title>
<guid>http://techieflair.com/?p=488</guid>
</post>

</posts>

JSON Output

Take this next  URL for Web Sevice  to get result in JSON Format:

http://techieflair.com/webservice.php?userid=2&number=10&format=json
{"posts":[{"post":{"post_title":"How we can create a Login and Logout Form in Php and mysql","guid":"http:\/\/techieflair.com\/?p=538"}}},{"post":{"post_title":"Sort Array Item using Selection Sort In C#","guid":"http:\/\/techieflair.com\/?p=1232"}},{"post":{"post_title":"How to check a string is Polindrome or not in C#","guid":"http:\/\/techieflair.com\/?p=1224"}},{"post":{"post_title":"Simple Example using Jquery DataTable in ASP.NET MVC","guid":"http:\/\/techieflair.com\/?p=1216"}},{"post":{"post_title":"What is Visualforce?","guid":"http:\/\/techieflair.com\/?p=1193"}},{"post":{"post_title":"DIFFERENCE BETWEEN  ABSOLUTE,FIXED ,STATIC & RELATIVE","guid":"http:\/\/techieflair.com\/?p=1192"}},{"post":{"post_title":"MS word shortcut keys","guid":"http:\/\/techieflair.com\/?p=1183"}},{"post":{"post_title":"clear dns cache","guid":"http:\/\/techieflair.com\/?p=1174"}},{"post":{"post_title":"Machine Learning Techniques","guid":"http:\/\/techieflair.com\/?p=1160"}},{"post":{"post_title":"Machine Learning","guid":"http:\/\/techieflair.com\/?p=1157"}}]}

Demo Link

It is very easy to create  Web Service Using PHP. Share others to do same.Hope this article will help you.

Two dependent dropdown list in PHP using MySQL, jQuery and Ajax  :


two Dependent dropdown list in PHP :   Hi readers, here we start with the topic “Two Dependent dropdown list in PhP ” which is mainly used in the form to select from the given list of items like select country, state, city, categories, etc according to the first drop down list. Here in this tutorial, we will explain the details of how to make two dependent dropdowns which populate data from MySQL using jQuery and Ajax in PHP that has been already discussed in the previous post.

Later on, we provide the full source code with snapshot view which makes this concept more clear and concise. In fact, at the end of this tutorial, we will provide the zip folder to download the full source code.

Let’s start the tutorial on two Dependent dropdown list in PHP!

Simple Example of two Dependent Dropdown list in PHP using MySQL, jQuery & Ajax: (Step-by-Step Guide)


Step1: First and foremost step is to create a Database and table directly from the PHPMyAdmin 

open localhost/phpmyadmin using web browser URL and then open sql tab and run the below sql command.

//create database
Create Database dropdown;

//create "State" table
CREATE TABLE IF NOT EXISTS state (
s_id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
stateName varchar(50) NOT NULL,
);

//create "city" table
CREATE TABLE IF NOT EXISTS city(
  c_id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cityName varchar(50) NOT NULL,
  s_id int(11) NOT NULL,
 );

 


Step 2: After table creation creates an HTML Form containing two drop-down menus using PHP script.

Create a file named “main.php” file and copy the code below and then save it. 

Here in this html file, there are two drop-down menus to select items from the first dropdown list and according to that it will display results in the second dropdown list.

main.php


<?php
require_once 'php/db.php';
?>
<!DOCTYPE html>
<html>
<head>
<title>Two Dependent Drop Downs using PHP, jQuery and Ajax</title>
<script src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/drop.js"></script>
</head>
<body>
<h3>Two Dynamic Dependent Dropdowns to select city according to the state</h3>
<center>
<div>
<h2 style="color:blue;"> Select STATE:</h2>
<select id="state">
    <option value="select" selected="selected">--SELECT STATE--</option>
</select>
</div>
<div>
 <h2 style="color:blue;"> Select CITY:</h2>
 <select id="city">
  <option value="select1" selected="selected">--SELECT--</option>
 </select>
</div>
</center>
<hr />
</body>
</html>


 

Step 3: Create a .js file to use jQuery and Ajax code to populate list without page refresh

Here in this JavaScript file, we have made two functions named “state()” and “change_state()” under which we have used ajax function to populate data from Mysql in a dropdown list without page refresh. Later on, call these two functions when the document is loaded.

Note:

In the beginning, we have to use the below code to empty all the records of dropdown with id=” state” and id=”city”.

 //remove all existing options
  $("#state").empty();
  $("#city").empty();

dropdown.js


function state(){
 $("#state").empty();
 //$("#state").append("<option>---LOADING--</option>");
 
 //ajax function
 $.ajax
 ({
 type: 'POST',
 url: 'php/dropdown.php',
 contentType: 'application/json',
 dataType: 'json',
 cache: false,
 success: function(data){
 $("#state").empty();
 $("#state").append("<option value='0'>--SELECT STATE---</option>");
 $.each(data, function(i,item){
 $("#state").append("<option value=" + data[i].s_id +">"+ data[i].stateName+"</option>");
 
 });
 },
 complete: function(){}
 }); 
 }
 //change funtion
 function change_state(){
// change function of listbox
$('#state').change(function(){
var s_id=$(this).val();
$('#city').empty(); //remove all existing options
$.ajax
 ({
 type: 'POST',
 url: 'php/dropdown1.php',
 dataType: 'text',
 data: {s_id: s_id},
 cache: false,
 success: function(data){
 $('#city').html(data);
 },
 complete: function(){}
 }); 
 });
}
//main javascript code
$(document).ready(function(){
 state();
 change_state();
});

Step 4: Create a PHP file to populate records from MySQL to the dropdown list.

Here in this step first we create a “PHP” named folder in which we make three .php files named db.php, dropdown.php and dropdown1.php . Copy the below code and then save it one by one.

In the db.php file, we simply create and check connection with MySQLi or we can say that it is a configuration file.

Whereas dropdown.php and dropdown1.php file are mainly used to run the SELECT query command to search all records of table state and city of database dropdown using MySQLi. Also, fetch the result and display records accordingly in the dropdown list each time using while loop.

db.php


<?php

// database login
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "dropdown";
 
//Create a Connection
$conn=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
 
// Check Connection
if(!$conn){
  die("connection does not establish,error: ". mysqli_connect_error());
}

?>

php/dropdown.php


<?php
require_once 'db.php';

//display records
$sql="select s_id,stateName from state order by stateName";
$display=mysqli_query($conn,$sql);
if(mysqli_num_rows($display))
{ 
  //initialise an array
  $data=array();
 while($rows=mysqli_fetch_assoc($display))
 {
  $data[]=array(
      's_id'=> $rows["s_id"],
      'stateName'=>$rows["stateName"]);
  } 
//encode data in json format
 header('Content-type: application/json');
 echo json_encode($data);
 }

//close the connection
mysqli_close($conn);

?>

php/dropdown1.php


<?php
require_once 'db.php';

//get the value of sate Id using post method
$s_id=$_POST['s_id'];$output='';

//SELECT Query
 $sql="select * from city where s_id=".$s_id." order by cityName";
 $output.='<option value="">SELECT CITY--</option>';

//run the above query
 $display=mysqli_query($conn,$sql);

//display records
 if(mysqli_num_rows($display))
{
 while($rows=mysqli_fetch_assoc($display))
 {
 $output.='<option value="'.$rows["c_id"].'">'.$rows["cityName"].'</option>';
 } 
 echo $output;
 }

//close the connection
mysqli_close($conn);

?>

Snapshot view of Dependent dropdowns in PHP using MySQL, jQuery, and Ajax:

two Dependent dropdown list in PHP
two Dependent dropdown list in PHP

Download file:    dropdown.zip

I hope you would like this tutorial on how we can make two Dependent dropdown list in PHP using MySQL, jQuery, and Ajax!