Mysql - phponwebsites.com

Showing posts with label Mysql. Show all posts

15 Jul 2014

Create excel spreadsheet using PHP and Mysql

You can generate excel spreadsheets easily using PHP. You need to follow below steps to create excel file.

Add content type:

First you need to add excel sheet content type to create excel file using PHP.

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

Add name and download excel file:

You can provide name for excel spreadsheet and make your sheet as downloadable file on coding. The Content-Disposition is used to force browser to save a file. For that, your file should be attached following line:

header("Content-disposition: attachment; filename=spreadsheet.xls");

Add content to excel spreadsheet:

Finally you can add contents to your excel file. Consider the following PHP script for generate excel file:

<?php
header("Content-Type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=spreadsheet.xls");
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$q=mysql_query("select * from table1") or die(mysql_error());
if(mysql_num_rows($q)>0)
{
echo"<table><tr><th>Name</th><th>Address</th><th>Phone</th></tr>";
while($res=mysql_fetch_array($q))
{
echo"<tr><td>".$res['name']."</td><td>".$res['address']."</td><td>".$res['phone']."</td></tr>";
}
echo"</table>";
}
?>

Where,
mysql_fetch_array() return values as both an associative and numeric array.
while executing file, it required you to save excel file. After save file, you can view it. Now you got excel spreadsheet with named spreadsheet.xls which is created by PHP.

Related Post:
How to fetch result from mysql table using mysql_fetch_array in PHP
Create json file using PHP and MySQL
PHP : Parse data from XML Sitemap using simplexml_load_file and file_get_elements
Read data from JSON file using PHP
Parse data from XML file using PHP

13 Jul 2014

Send forgotten password to mail in login form using php and mysql

Already you know about validate login form with remember me and signout concepts using php. You want to know Login with remember me and signout using php and mysql.
Now see about how to send forgotten password to mail using php?. First your table contains users mail id. Just imagine this is your table:

Login form using php and mysql:

Consider the following example:

login.php:

<html>

<head>
<style type="text/css">
input{
border:1px solid olive;
border-radius:5px;
}
h1{
color:darkgreen;
font-size:22px;
text-align:center;
}
span{
color:lightgreen;

}
</style>
</head>
<body>
<h1>Login<h1>
<form action='#' method='post'>
<table cellspacing='5' align='center'>
<tr><td>User name:</td><td><input type='text' name='name'/></td></tr>
<tr><td>Password:</td><td><input type='password' name='pwd'/></td></tr>
<tr><td></td><td><input type='checkbox' name='remember' /> <span>Remember me</span></td></tr>
<tr><td></td><td> <span><a href="forgot.php">Forgot password</a></span></td></tr>
<tr><td></td><td><input type='submit' name='submit' value='Submit'/></td></tr>
</table>

</form>
<?php
session_start();
//your values are stored in cookies, then you can login without validate
if(isset($_COOKIE['name']) && isset($_COOKIE['pwd']))
{
header('location:welcome.php');
}
// login validation in php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$name=$_POST['name'];
$pwd=$_POST['pwd'];
if($name!=''&&$pwd!='')
{
$query=mysql_query("select * from login where name='".$name."' and password='".$pwd."' ")  or die(mysql_error());
$res=mysql_fetch_row($query);
if($res)
{
if(isset($_POST['remember']))
{
  setcookie('name',$name, time() + (60*60*24*1));
  setcookie('pwd',$pwd, time() + (60*60*24*1));
}
$_SESSION['name']=$name;
header('location:welcome.php');
}
else
{
echo'You entered username or password is incorrect';
}
}
else
{
echo'Enter both username and password';
}
}
?>
</body>
</html>

While you run the above file, you'll get output like this:

Fogot password in php:

The php script for send forgotten password to mail is:

forgot.php:

<html>
<head>
<style type="text/css">
input{
border:1px solid olive;
border-radius:5px;
}
h1{
color:darkgreen;
font-size:22px;
text-align:center;
}

</style>
</head>
<body>
<h1>Forgot Password<h1>
<form action='#' method='post'>
<table cellspacing='5' align='center'>
<tr><td>Email id:</td><td><input type='text' name='mail'/></td></tr>
<tr><td></td><td><input type='submit' name='submit' value='Submit'/></td></tr>
</table>
</form>
<?php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$mail=$_POST['mail'];
$q=mysql_query("select * from login where mail='".$mail."' ") or die(mysql_error());
$p=mysql_affected_rows();
if($p!=0)
{
$res=mysql_fetch_array($q);
$to=$res['mail'];
$subject='Remind password';
$message='Your password : '.$res['password'];
$headers='From:guruparthiban19@gmail.com';
$m=mail($to,$subject,$message,$headers);
if($m)
{
echo'Check your inbox in mail';
}
else
{
echo'mail is not send';
}
}
else
{
echo'You entered mail id is not present';
}
}
?>
</body>
</html>

Where,
mysql_query("select * from login where mail='".$mail."' "); is select the values from mysql table.
mysql_affected_rows() return the number of rows selected. If it returns 0, then there is no values is present in mysql table.
mysql_fetch_array() return the results an associated array. You want to know mysql_fetch_array().
Mail() is used to send password to mail. Yow want to know mail() in php.

When you click the 'forgot password' in login page, you'll get output like this:

You entered your email id in text box and click submit button. Now you can get message 'Check your inbox in mail' if the mail is send. Otherwise, you'll get 'Mail is not send' message. If you enetered mail id which is not present is mysql table, then you will get 'You entered mail is is not present' message.

Related Post:
Create Registration form using PHP and MySQL
Login form validation using PHP and Mysql
Remember me in login form validation using PHP and Mysql

7 Jul 2014

Validate login form with remember me using php and mysql

All of you know the simple login validation concepts in php. Otherwise visit login validation using php. Now see about login form with remember me concept in php. You can remember the user using cookies concept in php.
When you click the remember me check box, your name and password is stored in cookie. Though you signout and close the browser, you can login again while open page in browser. Your name kept by cookie until expire the time or you clear the cache in your browser.

Remember me in login form using php:

Consider the following example. You have 3 php pages such as login.php, welcome.php and signout.php.

login.php:

<html>
<head>
<style type="text/css">
input{
border:1px solid olive;
border-radius:5px;
}
h1{
color:darkgreen;
font-size:22px;
text-align:center;
}
span{
color:lightgreen;

}
</style>
</head>
<body>
<h1>Login<h1>
<form action='#' method='post'>
<table cellspacing='5' align='center'>
<tr><td>User name:</td><td><input type='text' name='name'/></td></tr>
<tr><td>Password:</td><td><input type='password' name='pwd'/></td></tr>
<tr><td></td><td><input type='checkbox' name='remember' /> <span>Remember me</span></td></tr>
<tr><td></td><td><input type='submit' name='submit' value='Submit'/></td></tr>
</table>

</form>
<?php
session_start();
//your values are stored in cookies, then you can login without validate
if(isset($_COOKIE['name']) && isset($_COOKIE['pwd']))
{
header('location:welcome.php');
}
// login validation in php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$name=$_POST['name'];
$pwd=$_POST['pwd'];
if($name!=''&&$pwd!='')
{
$query=mysql_query("select * from login where name='".$name."' and password='".$pwd."' ");
$res=mysql_fetch_row($query);
if($res)
{
if(isset($_POST['remember']))
{
setcookie('name',$name, time() + (60*60*24*1));
setcookie('pwd',$pwd, time() + (60*60*24*1));
}
$_SESSION['name']=$name;
header('location:welcome.php');
}
else
{
echo'You entered username or password is incorrect';
}
}
else
{
echo'Enter both username and password';
}
}
?>
</body>
</html>

where,
if(isset($_COOKIE['name']) && isset($_COOKIE['pwd'])) {} means if the user values in cookies, then the user can login directly without login validation in php.

if(isset($_POST['remember'])){
setcookie('name',$name,time() + (60*60*24*1));
setcookie('pwd',$pwd,time() + (60*60*24*1));
}
which means, if the user click the remember me check box, then you have to store them in cookie. If you don't know about cookies in php, then visit Cookies in php.

When you run the login.php fille, you'll get output like this:

validate remember me concept in login form using php

welcome.php:

<?php
session_start();
if(isset($_COOKIE['name']))
{
echo 'Welcome: '. $_COOKIE['name'].'<br>';
}
else
{
echo 'Welcome: '. $_SESSION['name'].'<br>';
}
echo'<a href="signout.php">Singout</a>';
?>

where,
if(isset($_COOKIE['name'])){}else{} means if the values in cookie, then return name from cookie. Otherwise name return from session.

Now you enter username and password 'guru' and 'guru' without select the remember check box, you will navigate to 'welcome.php' page and get output like this:

welcome: guru
Signout

If it is wrong, then you can't login.

Signout in php:

When you click the signout.php, it destroy the session and return back to login page. The php script for signout page:
signout.php:

<?php
session_start();
session_destroy();
header('location:login.php');
?>

You select the remember me check box while login. Then you navigate to welcome.php page. Now you click the signout on welcome.php page, you can't return back to login page. Because the concept is, when you signout the page, you return back to login.php page, where you wrote the php script as like as:

if(isset($_COOKIE['name']) && isset($_COOKIE['pwd'])) {}

It means, if values in cookie, you can login again. Now you comes to login page and again login and navigates to welcome page because your values in cookies.So you are in welcome page until the cookie is expired.

Related Post:
Create Registration form using PHP and MySQL
Simple login form validation using php and mysql
Forgot password to mail in login form validation using PHP and Mysql

6 Jul 2014

Login validation using php and mysql

Everybody should see the login pages in most of websites. You should have doubt about this login form, how they validate username and password. There is nothing, just only 2 steps as follows as for login validation using php.
1. You enter username and password already registered which is stored in mysql database.
2. After you submit a button to login, it check the database values whether the username and password is correct or not using php and mysql query. The mysql query for select a values from mysql database as like this:

ie, SELECT * FROM table_name WHERE name='username' and password='password'

If it return values correctly, then you can login. Otherwise it give error message.

Simple login validation using php and mysql:

Lets see how to validate the username and password using php.

1. First you create table using mysql query like this:

CREATE TABLE login(name varchar(30), password varchar(30), PRIMARY KEY(name))

Now the table 'login' is created. The field 'name' is primary key. Because the username must be unique.

2. Then insert values into table using mysql query like this:

INSERT INTO login VALUES('guru','guru')

Now the table look like this:

3. The php script for login validation as follows as:
login.php:

<html>
<head>
<style type="text/css">
input{
border:1px solid olive;
border-radius:5px;
}
h1{
color:darkgreen;
font-size:22px;
text-align:center;
}
</style>
</head>
<body>
<h1>Login<h1>
<form action='#' method='post'>
<table cellspacing='5' align='center'>
<tr><td>User name:</td><td><input type='text' name='name'/></td></tr>
<tr><td>Password:</td><td><input type='password' name='pwd'/></td></tr>
<tr><td></td><td><input type='submit' name='submit' value='Submit'/></td></tr>
</table>

</form>
<?php
session_start();
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$name=$_POST['name'];
$pwd=$_POST['pwd'];
if($name!=''&&$pwd!='')
{
$query=mysql_query("select * from login where name='".$name."' and password='".$pwd."'") or die(mysql_error());
$res=mysql_fetch_row($query);
if($res)
{
$_SESSION['name']=$name;
header('location:welcome.php');
}
else
{
echo'You entered username or password is incorrect';
}
}
else
{
echo'Enter both username and password';
}
}
?>
</body>
</html>

Where,
  if(isset($_POST['submit']){}   means run after submit a results.
  if($name!=''&&$pwd!=''){} means if either username or password is empty, it give message like as 'Enter both username and password'.
  mysql_query("select * from login where name='".$name."' and password='".$pwd."' ") select the values if the username and password is present.
$res=mysql_fetch_row($query) return row as numeric array.
You don't know about mysql_fetch_row, then visit mysql-fetch-row in mysql.
if($res){} validate the result. If it is correct, then your name is stored in session variable and also you'll navigate to 'welcome.php' page. Otherwise you'll get 'You entered username or password is incorrect' message. You want to visit Session in php.
    When you run the above file, it should be like this:

The php script for welcome page:

welcome.php:

<?php
session_start();
$name=$_SESSION['name'];
echo'welcome :'. $name.'<br>';
echo'<a href="signout.php">Signout</a>';
?>

Now you enter username and password 'guru' and 'guru', you will navigate to 'welcome.php' page and get output like this:

welcome: guru
Signout

If it is wrong, then you can't login.

Signout in php:

When you click the signout.php, it destroy the session and return back to login page. The php script for signout page:
signout.php:

<?php
session_start();
session_destroy();
header('location:login.php');
?>

session_destroy is used to destroy the session variable in php.

Related Post:
Create Registration form using PHP and MySQL
Login form validation with remember function using php and mysql
Forgot password to mail in login form validation using PHP and Mysql

2 Jul 2014

Create sitemap dynamically using PHP and MySQL

Do you know why you need to add sitemap into you websites? what is the use of sitemap? Let see here to solve all of your doubts about sitemap.

Sitemap:

Sitemap is very important for all websites. All of you know, the search engines like google, bing are crawling pages on your websites to know about websites. Using this sitemap, webmasters to inform search engines about pages for crawling. Sitemap is a XML file which contains all url of your websites upto to date. If you add new page to your site, then you'll add url of new page to sitemap for crawling.

Sitemap is also provide url structure of websites. Suppose a new user comes to your websites. The sitemap leads new user to all pages on your site.

Structure of sitemap:

The structure of sitemap is:

<?xml version="1.0" encoding="UTF-8">
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">
<url>
<loc>http://www.phponwebsites.com</loc>
<lastmod>today date<lastmod>
<changefreq>daily</changefreq>
<priority>1</priority>
</url>
<url>
<loc>http://www.phponwebsites.com/p/htaccess.html</loc>
<lastmod>today date<lastmod>
<changefreq>daily</changefreq>
<priority>1</priority>
</url>
</urlset>

where,
urlset - encapsulates the file ie, start sitemap
url - parent tag for each entry ie, each page
loc - location of url
lastmod - date for last modification of file
changefreq - how frequently the pag is changed. It should be always, hourly, daily, monthly, weekly, yearly, never
priority - priority of url. It should be between 0.0 to 1.0

Create sitemap dynamically using PHP and MySQL:

First you need to add content type for xml to generate sitemap in PHP. ie,

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

Then add contents to XML file. Then save this file as sitemap.php .

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$dat=date('Y-m-d');
header ("content-type: text/xml");
echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">';
$urls=mysql_query("select * from url")or die(mysql_error());
while($row_Tags=mysql_fetch_array($urls))
{
echo "<url><loc>".$row_Tags['url']."</loc><lastmod>".$dat."</lastmod><changefreq>daily</changefreq><priority>1</priority></url>";
}
echo "</urlset>";
?>

.htaccess:

In your .htaccess, you add following codes.

RewriteRule ^sitemap.xml sitemap.php [L]

When you run sitemap.xml on your website each time, it display upto date contents.

Related Post:

Create RSS Feed dynamically using PHP and MySQL
Read data from RSS Feed using PHP
Create XML file using PHP and MySQL

14 May 2014

Pagination using php and mysql with jquery and ajax

You can display values with pagination using php. But you have to pass the page number and also relevant values through url. So your url look like this:

pagination.php?p=2

The good php website should be contain good url strucutre. Thats why you alter the urls using .htaccess. You don't need to show passing values in url. It can be hidden by .htaccess. If you don't know, just visit Redirect urls using htaccess.
So you need ajax and jquery to pass the values. Already you know the basic pagination concept. Otherwise, please visit Pagination using php and mysql.

Pagination using juery and ajax:

You need small function which is called by onclick event to display values with pagination in php. The jquery function as follows as:

Where,
- function page(e){} is called from click event.
ie, <a onclick="return page('.$i.')">'.$i.'</a>

- e is a page number passed from click event.
- pagination.php is name of this php file.
You define the url, data, dataType and type in ajax post to pass the values.

The overall php script for pagination:

<html>
<body>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<div id='page'>
<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$page=$_REQUEST['p'];
$limit=10;
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}
$query=mysql_query("select * from table1 limit $start, $limit") or die(mysql_error());
$tot=mysql_query("select * from table1") or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);
echo'<table><th>Reg.Id</th><th>Name</th><th>Category</th>';
while($res=mysql_fetch_array($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['game_Title'].'</td><td>'.$res['category_Name'].'</td></tr>';
}
echo'</table>';
function pagination($page,$num_page)
{
echo'<ul style="list-style-type:none;">';
for($i=1;$i<=$num_page;$i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:3px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;margin- left:4px;background:rgb(170,177,35);padding:4px;cursor:pointer;border:1px solid darkgreen ;"><a onclick="return page('.$i.')">'.$i.'</a></li>';
}
}
echo'</ul>';
}
if($num_page>1)
{
pagination($page,$num_page);
}
?>
<script type="text/javascript">
function page(e) {
$.ajax({
url:'pagination.php',
data:{p:e},
dataType:'html',
Type:'post',
success:function(e){
$('#page').html(e);
}
});
}
</script>
</div>
</body>

</html>

You'll get output like like this:

pagination using php and jquery with ajax

Now you can paginated to next page. The url structure of your website should be fine.

Note:
For pagination, you need any jquery reference like:
" <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script> "

Related Post:

Sorting column by clicking column header using php and mysql
Sorting column with pagination by clicking column header using php and mysql

12 May 2014

Sorting column with pagination by clicking column header using php and mysql

All of you know how to sorting column while clicking column header like mysql table in database through my previous post. Don't you know? Please visit this sorting column by clicking column header using php .

Now we are going to see about sorting column with pagination like table in mysql database. You sorting column by clicking column header in mysql table and you paginated to next page. You will get result followed by next value.
ie, you sort column by id. First 30 values displayed in first page in mysql table. When you paginate to next page, you will get output from 31st to 60 in your mysql table. Can you make your table like mysql table with sorting and pagination? Yes, you can also done pagination using php.
Already you know the sorting concepts. So now we will see sorting with pagination concepts.

1. Start and limit values based on page number:

$limit=10;
$page=$_GET['p'];
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}

Where,
$limit is the number rows per page.
$page is page number.
$start is starting point of limit in mysql query.

2. Total number of data in mysql table:
Then we need to calculate the total number of data in table. Then find the maximum pages like below:

$total_values=mysql_query("SELECT game_ID, category_Name, game_Title FROM table1");
$total=mysql_num_rows($total_values);
$maxpage=ceil($total/$limit);

Where,
mysql_num_rows() returns the numbers results in numeric.
ceil() returns the whole digit number. ie, ceil(2.3) => 3.
$maxpage returns the number of pages.

3. Function for pagination:

function pagination($maxpage,$page,$url,$field,$sort)
{
//After you sorting your table by clicking particular column, the following trick is used to display
//values with similar sorted values.
if($sort=='ASC')
{
$sort='DESC';
}
else
{
$sort='ASC';
}
echo'<ul style="list-style-type:none;">';
for($i=1; $i<=$maxpage; $i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="sort.php?p='.$i.'&sorting='.$sort.'&field='.$field.' ">'.$i.'</a></li>';
}
}
echo'</ul>';
}
pagination($maxpage,$page,$url,$field,$sort);

Where, you have to use small tricks. You have to pass the sorting and field values in url using function.
ie, Already we set if the url get 'ASC' value, then it will take as 'DESC'. This is for sorting. Similarly, you have to do same thing for pagination.

Sorting column with pagination by clicking column header:

The total php script as follows for both sorting and pagination by clicking column header:

<?php
error_reporting(E_ALL ^ E_NOTICE);
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new');
$field='game_ID';
$sort='ASC';
if(isset($_GET['sorting']))
{
if($_GET['sorting']=='ASC')
{
$sort='DESC';
}
else { $sort='ASC'; }
}
if($_GET['sorting'])
{
if($_GET['field']=='game_ID')
{ $field = "game_ID"; }
elseif($_GET['field']=='category_Name')
{ $field = "category_Name"; }
elseif($_GET['field']=='game_Title')
{ $field="game_Title"; }
}
//pagination
$url='sort.php';
$limit=10;
$page=$_GET['p'];
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}

$sql = "SELECT game_ID, category_Name, game_Title FROM table1 ORDER BY $field $sort limit $start, $limit";
$total_values=mysql_query("SELECT game_ID, category_Name, game_Title FROM table1");
$total=mysql_num_rows($total_values);
$maxpage=ceil($total/$limit);
$result = mysql_query($sql) or die(mysql_error());
echo'<table border="0">';
echo'<th><a href="sort.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
<th><a href="sort.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="sort.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>';
while($row = mysql_fetch_array($result)) {
echo'<tr><td>'.$row['game_ID'].'</td><td>'.$row['category_Name'].'</td><td>'.$row['game_Title'].'</td></tr>';
}
echo'</table>';
function pagination($maxpage,$page,$url,$field,$sort)
{
//After you sorting your table by clicking particular column, the following trick is used to display
//values with similar sorted values.
if($sort=='ASC')
{
$sort='DESC';
}
else
{
$sort='ASC';
}
echo'<ul style="list-style-type:none;">';
for($i=1; $i<=$maxpage; $i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="sort.php?p='.$i.'&sorting='.$sort.'&field='.$field.' ">'.$i.'</a></li>';
}
}
echo'</ul>';
}
pagination($maxpage,$page,$url,$field,$sort);
?>

Now you will get output like this:

Display table data in both ascending and descending order by clicking column header using php

Now you can sorting column with pagination by clicking column header.

Related Post:

Simple pagination concepts using php and mysql
Pagination using php, mysql and ajax

7 May 2014

Sorting column by clicking column header with php and mysql in table

All of you use the tables in mysql database. When you click the column header at first time, it displays values in ascending order. Likewise it displays values in descending order, while clicking field name second time. Normally the table look like this:

sorting column values using PHP and Mysql at phponwebsites

When you click the category_Name field, it display value by ascending order as follow as:

ascending and descending column values while clicking column header using PHP and Mysql at phponwebsites

We can make our table like tables in mysql database. We can sorting values in column by clicking filed name. It can be done by PHP.
Follow the below steps.
1. Connect file with mysql database as follows as:

mysql_connect('server_name','username','password');
mysql_select_db('db_name');

where,
- server_name means localhost,
- username is username of your database
- password is password of your database
- db_name is name of your database

2. Then, you need to sorting tables based on column filed name. So you have to pass the field name in url. Similarly, you sort table values both ascending and descending. So you need to also pass order types in url. By default, table values displays in ascending. You can fixed order types and also field name.

$field='game_ID';
$sort='ASC';

where, the mysql table display values by ascending order based on game_ID

Now your headings should be like this.

<th><a href="table1.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>

where,
- table1.php is the name of file.
- $sort means order type either ascending or descending
- game_ID, category_Name, game_Title are field names in mysql table.

3. Now you need get the values from url.

if(isset($_GET['sorting']))
{
if($_GET['sorting']=='ASC')
{
$sort='DESC';
}
else { $sort='ASC'; }
}
if($_GET['field']=='game_ID')
{
$field = "game_ID";
}
elseif($_GET['field']=='category_Name')
{
$field = "category_Name";
}
elseif($_GET['field']=='game_Title')
{
$field="game_Title";
}

where, you handle your statergies. ie, If sorting value is ascending, then you will sort table by descending. Likewise you'll sort table by ascending, if sort value is descending. Then you need to get column field values.

4. Then you write a query in which both order types and field name should be present. The mysql query should be like this.

SELECT game_ID, category_Name, game_Title FROM yobash_game ORDER BY $field $sort

where,
- $filed is a default field name ie, gameId
- $sort is a default order type already we fixed. ie. ASC

Sorting column by clicking column header

5. Finally combine all the PHP codes as follows as:

table1.php

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('db_name') or die(mysql_error());
$field='game_ID';
$sort='ASC';
if(isset($_GET['sorting']))
{
if($_GET['sorting']=='ASC')
{
$sort='DESC';
}
else
{
$sort='ASC';
}
}
if($_GET['field']=='game_ID')
{
$field = "game_ID";
}
elseif($_GET['field']=='category_Name')
{
$field = "category_Name";
}
elseif($_GET['field']=='game_Title')
{
$field="game_Title";
}
$sql = "SELECT game_ID, category_Name, game_Title FROM yobash_game ORDER BY $field $sort";
$result = mysql_query($sql) or die(mysql_error());
echo'<table border="1">';
echo'<th><a href="table1.php?sorting='.$sort.'&field=game_ID">Game Id</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=category_Name">Category Name</a></th>
<th><a href="table1.php?sorting='.$sort.'&field=game_Title">Game Name</a></th>';
while($row = mysql_fetch_array($result))
{
echo'<tr><td>'.$row['game_Id'].'</td><td>'.$row['category_Name'].'</td><td>'.$row['game_Title'].'</td></tr>';
}
echo'</table>';
?>

Finally you'll get PHP scripts for sorting table when you click the column filed name. Now you can sort tables as like as tables in mysql database.

Related Post:
Simple pagination concepts using php and mysql
Pagination using php, mysql and ajax

17 Apr 2014

Pagination using php and mysql

Pagination in php is very simple concepts. You can learn easily. Just follow the below steps.

1. Find the Start values based on page number:

$limit=10;
$page=$_GET['p'];
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}

Where,
$limit is the number rows per page.
$page is page number.
$start is starting point of limit in mysql query.
If the page number is 1, then the start is 0 which is find by $start=$limit*($page-1).
$start=10*(1-1)
$start=10*(0)
$start=0

If the page number is 2, then the start is 10.
Similarly, if the page number is 3, then the start is 20.

2. Find total number of records in mysql table:

Then we need to calculate the total number of data in table. Then find the maximum pages using php script like below:

$tot=mysql_query("SELECT * FROM table1") or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);

Where,
mysql_num_rows() returns the numbers results in numeric.
ceil() returns the whole digit number. ie, ceil(2.3) => 3.
$maxpage returns the number of pages.

3. Function for pagination:

The php script for pagination function is:

function pagination($page,$num_page)
{
echo'<ul style="list-style-type:none;">';
for($i=1;$i<=$num_page;$i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="pagination.php?p='.$i.'">'.$i.'</a></li>';
}
}
echo'</ul>';
}

Where,
You need to use for loop for display number of pages in mysql table.

4.The whole php code for pagination as follows as:

<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$page=$_REQUEST['p'];
$limit=10;
if($page=='')
{
$page=1;
$start=0;
}
else
{
$start=$limit*($page-1);
}
$query=mysql_query("select * from table1 limit $start, $limit") or die(mysql_error());
$tot=mysql_query("select * from table1") or die(mysql_error());
$total=mysql_num_rows($tot);
$num_page=ceil($total/$limit);
echo'<table><th>Reg.Id</th><th>Name</th><th>Category</th>';
while($res=mysql_fetch_array($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['game_Title'].'</td><td>'.$res['category_Name'].'</td></tr>';
}
echo'</table>';
function pagination($page,$num_page)
{
echo'<ul style="list-style-type:none;">';
for($i=1;$i<=$num_page;$i++)
{
if($i==$page)
{
echo'<li style="float:left;padding:5px;">'.$i.'</li>';
}
else
{
echo'<li style="float:left;padding:5px;"><a href="pagination.php?p='.$i.'">'.$i.'</a></li>';
}
}
echo'</ul>';
}
if($num_page>1)
{
pagination($page,$num_page);
}
?>

Now you'll get output like this:

Now you can paginated to next page.

Related Post:

Sorting column by clicking column header using php and mysql
Sorting column with pagination by clicking column header using php and mysql

13 Apr 2014

Display results in html table using php and mysql

Normally, you displayed data results without order in php. If you want to display single data, then you can easily display it using ' <?php echo $name; ?> '. But if you have to display large amount of data from mysql table, then you should be display it properly. So only, you can understand the displayed results. So the best option for display data is table. You can display results orderly in table using php. The following mysql query is used to select the values from mysql table.

SELECT * FROM table_name

Display results in table using php and mysql:

You can retrieve data from mysql table using php. The php script for display data in table as follows as:

<html>
<body>
<style type="text/css">
th,td{
border-width:0px 1px 1px 0px;
}
</style>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query("select * from table1 limit 0,10") or die(mysql_error());
echo'<table border="1" ><th >Reg.Id</th><th>Name</th><th>Category</th>';
while($res=mysql_fetch_array($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['game_Title'].'</td><td>'.$res['category_Name'].'</td></tr>';
}
echo'</table>';
?>
</body>
</html>

Now you'll get output like this:

Now you can get your results in table.

9 Apr 2014

Get database list in mysql using php

You can retrieve databases in your mysql server using php. On mysql server, you can use mysql query in order to get databases in mysql.

Get database list using mysql query:

The following mysql query is used to display database list.

SHOW DATABASES

It displays database list in mysql as like below:

Get database list in mysql using php:

You can display database list in mysql using php in two ways.

1. Display database list using php and mysql query

2. Display database list using php and mysql_list_tables()

Display database list using php and mysql:

Now you can find the database list in mysql server using php scripts:

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
$result = mysql_query("SHOW DATABASES") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo $row[0]."<br>";
}
?>

Display databases in mysql using php and mysql_list_dbs():

The php code for display database list:

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
$db_list = mysql_list_dbs($link);
while ($row = mysql_fetch_object($db_list))
{
echo $row->Database . "<br>";
}

?>

Now you'll get output like below:

display databases available in mysql using php

How to get table list in mysql database using php

7 Apr 2014

Display tables list in mysql database using php

You can display the tables in a mysql database two ways using mysql query.
1. First you select your mysql database. Then use below mysql query:

SHOW TABLES

2.You can get your tables in mysql database without select database using following mysql query.

SHOW TABLES FROM db_name

ex: SHOW TABLES FROM new

Both two ways, you will get output like this:

show tables using mysql_list_tables mysql query

Display table list in database using php:

You can display list of tables in database using php in two ways.

1. Display table list in database using php and mysql

2. Display tables in database using php and mysql_list_tables()

Display table list in database using php and mysql:

You can get list of tables in database using mysql query and php. The following php code is used for display list of tables.

<?php
$link=mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$result=mysql_query("show tables ") or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
echo " {$row[0]}<br>";
}
?>

Display tables in mysql database using php and mysql_list_tables():

You can also get list of tables in database using mysql_list_tables function in php. The following php code is used for display list of tables.

<?php
error_reporting('E_ALL ^ E_NOTICE');
mysql_connect('localhost','root','') or die(mysql_error());
$rs=mysql_list_tables('new') ;
while($res=mysql_fetch_row($rs))
{
echo $res[0].'<br>';
}
?>

Now you'll get output like this:

How to get database list in mysql server using php

2 Apr 2014

Retrieve data from mysql table using php

We are going to see how can fetch the result from mysql table using php.
1. First you need to connect your Mysql database using php script like below:

mysql_connect('server_name','username','password');
mysql_select_db('db_name');

where,
- server_name means localhost,
- username is username of your database
- password is password of your database
- db_name is name of your database

2. Then select the values which is present is table using mysql query.
" SELECT * FROM table_name "
Use above query, if you select all values in table. Otherwise you need to use 'WHERE' clause to retrieve desired data from table.

3. Then you have to use MYSQL function to retrieve data from table such as mysql_fetch_array(), mysql_fetch_row(), and mysql_assoc.
where,
mysql_fetch_array() returns row as numeric array, associative array or both.
mysql_fetch_row() returns numeric array corresponds to fetched row.
mysql_fetch_assoc() returns row as an associative array.

Consider following example:

Fetch result from mysql table using php at phponwebsites

Fetch results from mysql table using mysql_fetch_array() in php:

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_array($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_array in php

Fetch result from mysql table using mysql_fetch_row() in php:

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_row($query))
{
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_row in php

Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use associative index instead numerical, then you will get undefined index error like as follows as:

retrieve data from mysql table using mysql_fetch_row in php

Fetch results from mysql table using mysql_fetch_assoc() in php:

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_assoc($query))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

Fetch result from mysql table using mysql_fetch_assoc in php

Suppose you'll use
echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

Retrieve data from mysql table using mysql_fetch_assoc in php

Finally you can retrieve data from mysql table using php.

31 Mar 2014

mysql_fetch_array in php and mysql

Mysql_fetch_array() returns row as an associative, numeric array or both based on parameters passed in it. Three types of parameters can be passed in mysql_fetch_array function.

1. MYSQL_BOTH - return both associative and numeric array
2. MYSQL_ASSOC - return associative array
3. MYSQL_NUM - return numeric array

Retrieve data from table using mysql_fetch_array() in php:

Consider following example:

mysql_fetch_array in php and mysql at phponwebsites

Mysql_fetch_array() with MYSQL_BOTH in php:

It returns result as both associative and numeric array. You can retrieve data using both associative and numeric indices in php.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_array($query, MYSQL_BOTH))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res[1].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

mysql_fetch_array with MYSQL_BOTH in php

Where,
the array should be like this:

Array(
[game_ID] => 1
[category_Name] => Adventures
[game_Title] => Achilles
)

Array(
[0] => 1
[1] => Adventures
[2] => Achilles
)

mysql_fetch_array() with MYSQL_NUM in php:

It returns result as numeric array. ie, indices are numeric like 0,1,2...

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','')  or die(mysql_error());
mysql_select_db('new')  or die(mysql_error());
$query=mysql_query('select * from table1')  or die(mysql_error());
while($res=mysql_fetch_array($query, MYSQL_NUM))
{
echo'<tr><td>'.$res[0].'</td><td>'.$res[1].'</td><td>'.$res[2].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

In this type, the array of result should be like below:

Array(
[0] => 1
[1] => Adventures
[2] => Achilles
)

mysql_fetch_array() with MYSQL_ASSOC in php:

It returns result as an associative array.

<html>
<body>
<table>
<th>Game_ID</th><th>Category</th><th>Title</th>
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query('select * from table1') or die(mysql_error());
while($res=mysql_fetch_array($query,MYSQL_ASSOC))
{
echo'<tr><td>'.$res['game_ID'].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';
}
echo'<table>';
?>
</body>
</html>

Now you'll get output like this:

mysql_fetch_array with MYSQL_ASSOC in php

Where,
the array should be like this:
Array(
[game_ID] => 1
[category_Name] => Adventures
[game_Title] => Achilles
)

Suppose you'll use

echo'<tr><td>'.$res[0].'</td><td>'.$res['category_Name'].'</td><td>'.$res['game_Title'].'</td></tr>';

That means you use numeric index instead associative, then you will get undefined offset error like as follows as:

mysql_fetch_assoc,php, mysql at phponwebsites

Note:

mysql_fetch_array() without array_type means return row as an associative, numeric array or both

Related Post:

Retrieve data from mysql database using mysql_fetch_row in php
Retrieve data from mysql table using mysql_fetch_assoc in php

Pages

15 Jul 2014

Add content type:

Add name and download excel file:

Add content to excel spreadsheet:

13 Jul 2014

Login form using php and mysql:

Fogot password in php:

7 Jul 2014

Remember me in login form using php:

Signout in php:

6 Jul 2014

Simple login validation using php and mysql:

Signout in php:

2 Jul 2014

Sitemap:

Structure of sitemap:

Create sitemap dynamically using PHP and MySQL:

.htaccess:

14 May 2014

Pagination using juery and ajax:

The overall php script for pagination:

12 May 2014

Sorting column with pagination by clicking column header:

7 May 2014

Sorting column by clicking column header

17 Apr 2014

13 Apr 2014

Display results in table using php and mysql:

9 Apr 2014

Get database list using mysql query:

Get database list in mysql using php:

You can display database list in mysql using php in two ways. 1. Display database list using php and mysql query 2. Display database list using php and mysql_list_tables()

Display database list using php and mysql:

Display databases in mysql using php and mysql_list_dbs():

7 Apr 2014

Display table list in database using php:

Display table list in database using php and mysql:

Display tables in mysql database using php and mysql_list_tables():

2 Apr 2014

Fetch results from mysql table using mysql_fetch_array() in php:

Fetch result from mysql table using mysql_fetch_row() in php:

Fetch results from mysql table using mysql_fetch_assoc() in php:

31 Mar 2014

Retrieve data from table using mysql_fetch_array() in php:

Mysql_fetch_array() with MYSQL_BOTH in php:

mysql_fetch_array() with MYSQL_NUM in php:

mysql_fetch_array() with MYSQL_ASSOC in php:

You can display database list in mysql using php in two ways.

1. Display database list using php and mysql query

2. Display database list using php and mysql_list_tables()