Searching and Sorting of The Data Using MySQL and PHP

Searching Techniques and Sorting Techniques Using MySQL

Prepared By Hitesh Vataliya

Searching is the process of selecting one record or more records from a table or combination of tables and showing as the result into PHP form using MySQL Query.  

To perform the query, three items must be identified:

1. Which fields will be used to identify the records required?

2. Which tables are required to produce the data required?

3. What are the criteria for identifying the records required?

For Example : 

v Member is tablename.

v Membernumber, forename,surname,birthdate,gender,address,pincode,telephonenumber are fields of table member.

Question 1: 

Identify the names and telephone numbers of club members with Member Number over 1000

Answer: 

Forename, surname and telephone number are selected from table member

SELECT FORENAME, SURNAME, `TELEPHONE NUMBER` FROM MEMBER WHERE `MEMBER NUMBER` > 1000

Question 2:

Select All the fields or columns of the table member.

Answer: 

The asterisk * symbol can be used in an expression string to extract all fields from a table of the database. 

SELECT * FROM MEMBER WHERE `MEMBER NUMBER` > 1000

Question 3: 

Identify the names and telephone numbers of club members with Surnames beginning with M and Member Number over 1000 

Answer : 

SELECT FORENAME, SURNAME, `TELEPHONE NUMBER` FROM MEMBER WHERE SURNAME LIKE “M*” AND `MEMBER NUMBER` > 1000

Question 4: 

Identity the names and telephone numbers of the club members with name begin with N and 3rd character is R and member number>1000.

Answer : 

SELECT FORENAME, SURNAME, `TELEPHONE NUMBER` FROM MEMBER WHERE FORENAME LIKE “N_R*” AND `MEMBER NUMBER` > 1000

Sorting

Sorting in MySQL performed by keyword order by and then asc or desc according to the requirement we can select either asc for ascending order and desc for descending order sorting of the data.

To perform a sort, two items must be identified:

1. Which field (or fields) will be used to decide the order of records?

2. For each field selected, will the order of sorting be ascending or descending?

To produce a list of people with the tallest first, the records would be sorted in descending order of height.  

To produce a list of people with youngest first, the records would be sorted in ascending order of age.

A very common way of ordering records relating to people is in alphabetical order.  To achieve alphabetical ordering requires the records to be sorted in ascending order of surname. 

A complex sort involves more than one sort condition involving two or more fields.

To achieve “telephone book order”, the name is sorted in ascending order of surname, then ascending order of forename.  In this case, the Surname field is the primary sort key, and the Forename field is the secondary sort key.  

For Example :

Question 1: 

Sort Selected Data in Ascending order. 

Answer :

SELECT FORENAME, SURNAME FROM MEMBER 
WHERE `MEMBER NUMBER` > 1000 ORDER BY SURNAME, FORENAME

Question 2: 

Sort Selected Data in Descending order. 

Answer :

SELECT FORENAME, SURNAME FROM MEMBER WHERE `MEMBER NUMBER` > 1000 ORDER BY `MEMBER NUMBER` DESC

For More Information : Website

XML file access with PHP

What is XML? How to Access the Content Written in XML with PHP Language Code?

Prepared By Hitesh Vataliya

The Full form of XML is Extensible Markup Language

The XML is a widely accepted standard for data description and exchange.

It allows content authors to “mark up” their data with customized machine-readable tags. 

These tags don’t come from a predefined list; instead, create their own tags and structure, suited to their own particular requirements, as a way to increase flexibility and usability.

Every XML document must begin with a declaration that states the version of XML being used.

The document prolog is followed by a nested series of elements.

Elements are the basic units of XML;

They typically consist of a pair of opening and closing tags that enclose some textual content.

The textual data enclosed within elements is known, in XML parlance, as character data. This character data can consist of strings, numbers, and special characters.

Some Rules angle brackets and ampersands

 > Symbol  > is used

 <  Symbol  < is used

 &  Symbol  & is used

XML is the latest standard language used to store and transfer the database among websites or we can say it is a standard language used in webservices.

Here in below Example, XML file given for accessing it's content (address.xml file) using php code :

address.xml

<?xml version='1.0'?>

<address>

<street>168, New Road</street>

<county>Baroda</county>

<city>

<name>Vadodara</name>

<zip>390021</zip>

</city>

<country>India</country>

</address>

PHP Code

<?php

// load XML file

$xml = simplexml_load_file('address.xml') or die ("Unable to load XML!");

// access XML data

echo "City: " . $xml->city->name . "\n";

echo "Postal code: " . $xml->city->zip . "\n";

?>

If you wants to learn more concepts of XML and PHP Visit : our website

 

Creating & Managing Notepad File Using PHP Code

File Handling in PHP

Using the Built in Functions like,

1. fopen(),
2. fclose(),
3. feof(),
4. realpath(),
5. dirname(),
6. basename(), etc.

fopen() : Open a file with PHP Language, fopen() function is required to create connection between our PHP Coding file and Other file like Notepad file or we can say .txt file.

It works same as other languages built-in functions works like C Programming Language or C++ Programming Language.

Syntax of Function fopen()

fopen(filename,mode)

For Example

$file1= fopen("Hitesh.txt", "w");

We can open a file in different modes as given below

Writing the Content on to the File.

If you have a version of PHP below version 5, then you can use the fwrite() function. But you first need to use fopen( ) to get a file handle.

In the next script, we'll try to write some text to a file. We'll use the "w" option, as this will create a file for us, if we don't have one with the filename chosen.

<?PHP

$file = fopen("Hitesh.txt", "w");

fwrite($file, "Hi, This is file Handling Program of PHP");

fclose($file);

print "file created and one line of text is written inside Hitesh.txt";

?>

In Latest Version of 5 of Above version of PHP, We can use file_put_contents() Function

It is used in the same way, but has an optional third parameter:

file_put_contents($file_handle, $file_contents, context);

The context option can be FILE_USE_INCLUDE_PATH, FILE_APPEND, LOCK_EX. 

So to append to the file, just do this:

file_put_contents($file_handle, $file_contents, FILE_APPEND);

  

Reading From the File

<?PHP

$file_contents = fopen( "Hitesh.txt", "r" );
print $file_contents;
fclose($file_contents);

?>

Here Print $file_content will print all the content written in the file.

Same way we can write or print the content of the file by using fgets() function which reads content line by line.

<?PHP

$file = fopen("Hitesh.txt", "r");

while (!feof($file)) {

$line_of_text = fgets($file);
print $line_of_text . "<BR>";

}

fclose($file);

?>

fclose() function is used to close connection between PHP code or Hitesh.txt File.

Fopen() function is used for creating connection between PHP coding and Hitesh.txt file, and if you have created connection then your responsibility to close it after reading or writing work is completed.

Some Useful Functions for Printing FileName, FilePath and Directory on which file is there.

Create a file with filename “Hitesh.php” and Store it on to the folder “PHP1”

Print the Path of a File By Using realpath() function

<?PHP

$filepath = realpath("Hitesh.php");

print "The path of the File Hitesh.php is: " . $filepath;

?>

If You wants to get the exact path of file, then, you can use realpath(). In between the round brackets of the function, type the name of the file which is created by you Here in above example Hitesh.php is the file name and $filepath variable prints the name of the file.

Print the Folder or Directory Name, but not the file name

<?PHP

$f = dirname("PHP/Hitesh.php");

print "The folder or directory name is: " . $f . "<BR>";

?>

You can get the names of the folders, you can use the dirname( ) function. This will strip off the name of the file and return the rest of the text between the round brackets of the function.

Print the Filename By Using basename() function

<?PHP

$v = basename("PHP/Hitesh.php");

print "The File Name is: " . $v . "<BR>";

?>

In above example the $v prints the file name “Hitesh.php”.

For learning PHP PROJECT TRAINING in  VADODARA, GUJARAT visit website : WWW

Vataliyatuitionclasses.com

Validations using PHP code

For Applying Validations using PHP. we have to first create a webpage using HTML for example here login page validation page is created in HTML.

<html>
<head>
<title>Login form</title>
</head>
<body bgcolor="yellow">
<form id="form1" method="POST" action="n.php">
<center><h1 ><font color="Blue" size="30px">Login Form</font></h1>

Enter Username: <input type="text" name="username" size="28" /> <br />
Enter Password: &nbsp;<input type="password" name="password" size="28" /> <br />


<input type="button" name="submit" value="submit" />
<input type="button" name="cancel" value="Cancel" />

</center>
</form>
</body>
</html>

For Learning How to give validations in the PHP.
Visit Website: PHP Training

PHP Project Training in Vadodara

Learn PHP Project from professional trainer. PHP Project Training course includes the topics from report writing to testing of your project.

Course Content : 

1. Project Introduction and Report Writing

2. PHP Programming

3. HTML

4. CSS

5. JavaScript

6. MySQL

7. DataBase Connectivity

8. Session Management

9. Testing

For More Details visit : http://www.vataliyatuitionclasses.com

PHP Project Training Vadodara

ASP.Net Project Training

JAVA Project Training

How to upload a file using PHP ?

For uploading a file in PHP + MySQL, we have to use datatype as varchar(250) to store the filename and it's path.

Before uploading any photo or doc we must have to make our form tag as enctype. And inside the phpcode we must have to check weather the file is already exists in that location or not.

For Example. Table of Database is given
tblimagedemo

Data Type            Column Name 

id                           int                             PK  Auto_Increment  

photo                    varchar(250)

On Webpage file upload control is given in below image to choose file with button submit.

For that write HTML code.

<form method="post" name="pform" id="pform" action="" enctype="multipart/form-data">

<table>

<tr>

<td>Image</td>

<td>

<input type="file" value="" name="file" id="file"/>

</td>

</tr>

<tr>

<td></td>

<td>

<input type="submit" value="submit" name="btnsubmit"/>

<input type="button" value="reset" name="btnreset"/>

</td>

</tr>

</table>

</form>

Write PHP code in head or body section.

<?php

if(isset($_POST['btnsubmit']))

{

if($_FILES['file']['error']>0)

   {

         echo"Error in uploading";

   }

else

   {

       echo"upload:".$_FILES['file']['name']."<br>";

        $i="image/".$_FILES['file']['name'];

         if(file_exists("image/".$_FILES['file']['name']))

          {

                       echo $_FILES['file']['name']."File is already  Exists";

          }

          else

          {

                   move_uploaded_file($_FILES['file']['tmp_name'],"image/".$_FILES['file']['name']);

                  echo "File is uploaded successfully";

          }

    }

if(empty($i))

{

echo "Upload your image";

}

else

{

$sql = "INSERT INTO `superstore`.`tblimagedemo` (`id`, `photo`) VALUES (NULL, '$i')";

                 if(mysqli_query($con,$sql))

                {

                      echo"Record inserted";

                 }

                else

                {

                        echo"error";

                 }

}

}

Learn PHP Project with MySQL 
visit website : Final Year Project Training in Vadodara for student

Date Datatype in PHP with Different Date Format.

Date in PHP with Example code.

Date Datatype's syntax is the 

date(format in string, time stamp);

Default format which the developer wants is dd/mm/yyyy we can do it by following code.

 date("d/m/Y");

This code will print the current date with 05/04/2014

Formats a time and date according to the format string provided in the first parameter. If the second parameter is not specified, the current time and date is used.As shown in the above example.

 date("m/d/Y");

This code will print the current date with 04/05/2014

The mktime() function returns the timestamp for the date and we can store the output of the function mktime() to any variable in php for printing suitable date.

Syntax of mktime() is given below

mktime(hour,minute,second,month,day,year,is_dst)

We can change the format of the date by mktime function.By providing the time stamp as a second argument in the date function.
if we want to print the date after 10 days then we can use below formatting.with the help of mktime and date.
hour=0
minite=0
second=0
month = m means current month April (4)
day = d+10 means 10 days will be added to the current date and
year = Y means current year (2014)

$dateten = mktime(0,0,0,date("m"),date("d")+10,date("Y"));
echo "The date after 10 days is ".date("d/m/Y", $dateten);

output : The date after 10 days is 15/04/2014

Here in above example the $dateten variable will print the date after 10 days as output.

Date Format

D
Instead of current date 05 if we have to print name of the day then use above char D.It will print first three letters of the current day.

 date("D/m/Y");

This code will print the current date with SAT/04/2014

l (small l)
Instead of current date 05 if we have to print name of the day then use above char D.It will print name the current day.

 date("l/m/Y");

This code will print the current date with Saturday/04/2014


M
Instead of current Month 04 if we have to print name of the month then use above char F.It will print first three letters of the current month.
 date("d/M/Y");

This code will print the current date with 05/Apr/2014

F
Instead of current Month 04 if we have to print name of the month then use above char F.It will print name of the current month.

 date("d/F/Y");

This code will print the current date with 05/April/2014

y
If we have to print the Current year's last two digits then use y.Year with two digits; e.g., “14”.

date("d/m/y");

This code will print the current date with 05/04/14

PHP PRoject Training in Vadodara Gujarat.
Best PHP training provider in Vadodara.
Visit Website : PHP Training Center Gujarat