PHP - phponwebsites.com

Showing posts with label PHP. Show all posts

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

26 Mar 2014

mysql_fetch_row in php and mysql

Mysql_fetch_row() return row as numeric array. We can't use associative indices to retrieve data from mysql table using php.

Consider following example:

Retrieve data from table using mysql_fetch_row() in php:

You can retrieve data from mysql table using mysql_fetch_assoc() in php. The following php script is used for retrieve data.

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

Where,
the result of array should be like this:
Array(
[0] => 1
[1] => Adventures
[2] => Achilles
)

Suppose you'll use following php script,
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:

Where,
'game_Title' is the field name of column. You can't use $res['game_title'] here. Because you retrieved data from table using mysql_fetch_row().

Related Post:

Retrieve data from mysql database using mysql_fetch_assoc in php
Retrieve data from mysql database using mysql_fetch_array() in php

24 Mar 2014

mysql_fetch_assoc in php and mysql

Mysql_fetch_assoc() return row as an associative array. Here, we can use numeric indices to get values from mysql data using php.

Consider following example:

Retrieve data from table using mysql_fetch_assoc() in php:

You can retrieve data from mysql table using mysql_fetch_assoc() in php. The following php script is used for retrieve data.

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

Where,
the result of 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:

Where,
$res[0] represents the first column of mysql table. But you can't use this to retrieve data from table. Because you used mysql_fetch_assoc() mysql function to retrieve data.

Related Post:

Retrieve data from mysql database using mysql_fetch_row in php
Retrieve data from mysql database using mysql_fetch_array() in php

19 Mar 2014

Avoid duplicate values storing in table using php and mysql

We can avoid duplicate values storing in table when we insert data to table. Because the duplicate entry is one of the main problem, while insert data into mysql table . We removed duplicate values using 'DISTINCT' key in mysql query, if it is present in table. We can check whether the value is present in table or not using PHP and Mysql before add data to table.

We can also avoid duplicate values storing in mysql table while inserting by primary key.

Consider following example. You've a database named with 'new' and table named with 'user'. You try to add names in user table. Now you've to check the values is present or not while you inserting. The table look like this:

Avoid duplicat values sotring in table using PHP at phponwebsites

Now you try to add name 'guru' again. You can check whether the name 'guru' is present in table or not using below PHP script.

<?php
if(isset($_POST['submit']))
{
mysql_connect('localhost','root','');
mysql_select_db('new');
$name=$_POST['name'];
$query=mysql_query("select * from user where name='".$name."' ") or die(mysql_error());
$duplicate=mysql_num_rows($query);
if($duplicate==0)
{
$query1=mysql_query("insert into user values('".$name."')") or die(mysql_error());
}
else
{
echo'The name '.$name.' is already present in the user table';
}
}
?>
<html>
<body>
<form method='post' action='#'>
name: <input type='text' name='name'>
<input type='submit' name='submit' value='Submit'>
</form>
</body>
</html>

Where,
mysql_query() - execute the query and select if any similar values are present in table. mysql_num_rows() - returns numeric value of selected rows. If its value is 0, then there is no similar values present in table. Otherwise the value is already present in table.
Where,
you can also use mysql_fetch_row() instead of mysql_num_rows().
If the value is present in table, it could not be stored in table. Now you can avoid duplicate entries in table using PHP and Mysql.

Related Post:

How to store values into mysql table using php

13 Mar 2014

Insert data into mysql table using php

You can insert values to mysql table using php. The following mysql query is used for insert data into table.
INSERT INTO table_name VALUES(values datatype(length))

Store values into mysql table using php:

The following php script is used to store values into mysql table.

<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_select_db('new') or die(mysql_error());
$query=mysql_query("insert into table1 values(1,'sample1', 'sample1.jpg')") or die(mysql_error());
if($query)
{
echo 'Vaules are inserted into table';
}
else
{
echo'Vaules are not inserted';
}
?>

Note:
If you declared field name as varchar, then you must insert values between single quotation marks ( 'sample1').

Now the values are inserted into table. You can see it in your mysql table at phpmyadmin. Your mysql table look like this:

You inserted values to your table using php.

Get values from user and insert into mysql table using php:

Suppose you want to get values from user. First you have to know how to get values from text boxes.

How to get values from textbox using php:

$name=$_POST['name']; if(method is post)
or
$name=$_GET['name']; if(method is get)
or
$name=S_REQUEST['name']; if(method is either post or get)

where,
S_POST[] is used to get values from textbox, If you use POST method.
S_GET[] is used to get values from textbox, If you use GET method.
S_REQUEST[] is used to get values from textbox, If you use either GET or POST method.

Then the following php code is used to insert values into table.

Where,
the values will get from users using post method like as below:
$_POST['textbox_name']

Now you run this file. Your display look like this:

Then you can get values, if user type anything in textbox. Now i added values as like this:

Get values from user and stored into mysql table using php

Now you'll get output like this:

Your mysql table have user inserted values.

Related Post:

How to avoid duplicate values storing in mysql table using php and mysql

6 Mar 2014

Create mysql table using php

You can create mysql table using php. The following mysql query is used to create mysql table.
CREATE TABLE table_name(field_name datatype(length))

The php script as follows as:

<?php
mysql_connect('localhost','root','');
mysql_select_db('new');
$query=mysql_query("create table table1(id int(5),name varchar(40),img varchar(30))") or die(mysql_error());
if($query)
{
echo 'Table is created';
}
else
{
echo'Table is not created';
}
?>

Where,
- mysql_connect() is used to connect the database.
- 'localhost' is server name.
- 'root' is user name of database.
- '' represent the password of database.
- mysql_select_db() is used to select the database for use.
- 'new' is the database new.
- mysql_query() is used for execute the mysql query.
- mysql_error() gives error message when the mysql query is not running successfully.

Now the mysql table 'table1' is created. You can see it in your mysql database at phpmyadmin.
Your mysql database should be like this:

Create mysql table using CREATE TABLE mysql command in php

Now you created your mysql table.

Related Post:

How to connect mysql database using php
How to create mysql database using php

24 Feb 2014

Connect mysql database using php

You must connect mysql database to insert and retrieve add from it. In php, you can connect mysql server easily. It needs only two steps.

1. Open connection to mysql server.
2. Select database to add and retrieve data from mysql.

Open connection to mysql:

You must open connection to mysql server to connect database.

mysql_connect():

The mysql_connect() function is used to open connection on mysql. The syntax for open connection in mysql as follows as:

<?php

mysql_connect('localhost','root','');

where,

'localhost' is the server name.

root is the user name of mysql server. The 'root' is default username in mysql.

'' is the password of mysql server. The empty is default password in mysql.

Select database in mysql using php:

After opened connection to mysql database, you've to select database to add and retrieve data from it.

mysql_select_db():

It is used to select the database in mysql server. The syntax for select database in mysql as follows:

<?php

mysql_select_db('new');

where,

'new' is the database name in mysql.

Connect mysql database in php:

Now combined the above two codes to one for connect mysql database using php. The php code as follows as:

<?php

mysql_connect('localhost','root','') or die('Cannot connect mysql server');

mysql_select_db('new') or die('cannot connect database');

Now you can check whether the connection is opened or not to mysql. You got error message if either mysql server is not connect or database is not selected.

Related Post:

How to create mysql database using php
How to create mysql table using php

21 Feb 2014

Create mysql database using php

All of you know, create mysql database in phpmyadmin. Now we are going to know how to create database using php.

The mysql query is:
CREATE DATABASE db_name

Then you use the particular database. The mysql query is:
USE db_name

The php coding as follows as:

<?php
mysql_connect('localhost','root','');
$query=mysql_query('create database new') or (mysql_error());
$qu=mysql_query('use new') or (mysql_error());
if($query)
{
echo'Database is created';
}
else
{
echo'Database is not created';
}
?>

Where,
- mysql_connect() is used to connect the database.
- 'localhost' is server name.
- 'root' is user name of database.
- '' represent the password of database.
- mysql_query() is used for execute the mysql query.
- 'new' is the database new.
- mysql_error() give error message while the mysql query is execute.
- die() terminate the excution.

Now you'll get output as " Database is created ". Then you can see the new database in your phpmyadmin.

create database in mysql using php at phponwebsites

The database 'new' is created and ready for use.

Related Post:

How to connect mysql database using php
How to create mysql table using php

6 Feb 2014

Remove auto_increment from column in mysql

You created a table in which the column with specific definitions in mysql. You created table column with auto increment. To add auto increment to existing column, visit add-auto-increment-to-existing-column in mysql. Then how can you remove it from column in mysql. The following mysql query is used to remove auto increment from column in mysql.

Mysql Query:

Alter table table_name DROP PRIMARY KEY,
change column_name column_name datatype(length) definition

Consider the example:
The structure of table1 in database new look like this:

remove auto increment from column in mysql using alter, drop and change mysql command

Now we are going to remove auto increment from column 'No' in mysql table 'table1'. The mysql query is:

Alter table table1 DROP PRIMARY KEY,
change No No int(5) NOT NULL

Now your structure look like this:

remove auto increment from column in mysql

Now you can see the column name 'No' is normal. There is no auto increment.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to reset auto increment initial value in mysql
How to use order by before group by in mysql
Select distinct values in mysql

3 Feb 2014

Select distinct values in mysql

If your column in mysql table contains duplicate values, then you need to display distinct values by mysql command 'DISTINCT' and 'GROUP BY'.

Select unique values in Mysql by distinct:

The mysql command 'DISTINCT' is used to select distinct values. The mysql query for select distinct values as follows as:

SELECT DISTINCT column_name FROM table_name

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'DISTINCT' mysql command like this:

SELECT DISTINCT id FROM table1

Now you'll get output like this:

select unique values using distinct in mysql

You can see the table 'table1' which have unique values.

Select unique values in Mysql by 'Group by':

You can also display unique values using 'GROUP BY' mysql command. If you want to select distinct values from one column and also other values from table, then you can use mysql command 'GROUP BY'. The mysql query is:

SELECT * FROM table_name GROUP BY column_name

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

display unique values using group by in mysql

You can see the column 'id' values in mysql table 'table1' are unique.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

How to remove auto increment from column in mysql
How to reset auto increment initial value in mysql

31 Jan 2014

Use order by before group by in mysql

Normally you can use order by before group by in mysql. Now the question is rise, can you use order by before group b in mysql? Yes you can use order by before group by in mysql. It is possible. You've to use subquery for this. ie,

SELECT * FROM (
SELECT * FROM `table` ORDER BY column_name DESC
) t
GROUP BY column_name
Where,
t is alias because you use subquery. If you don't add it, then you'll get error message "Every derived table must have its own alias".

Group by in Mysql:

Consider the following table. table1 is name of this table.

Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

SELECT * FROM table1 GROUP BY id

Now you'll get output like this:

You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.

Use order by before group by:

On above example, you want to select highest count of each id and id should be unique. Then you have to use subquery to select highest count values. The mysql query as follows as:

SELECT * FROM (
SELECT * FROM `table1` ORDER BY count DESC
) t
GROUP BY id

where,
SELECT * FROM `table1` ORDER BY count DESC run first. It ordered the data in descending order by count.
SELECT * FROM ( ... ) t GROUP BY id it takes unique values.
Now you'll get output like this:

Now you'll get id with highest count values.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

Pages

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:

26 Mar 2014

Retrieve data from table using mysql_fetch_row() in php:

24 Mar 2014

Retrieve data from table using mysql_fetch_assoc() in php:

19 Mar 2014

13 Mar 2014

Store values into mysql table using php:

Get values from user and insert into mysql table using php:

How to get values from textbox using php:

6 Mar 2014

24 Feb 2014

Open connection to mysql:

mysql_connect():

Select database in mysql using php:

mysql_select_db():

Connect mysql database in php:

21 Feb 2014

6 Feb 2014

3 Feb 2014

Select unique values in Mysql by distinct:

Select unique values in Mysql by 'Group by':

31 Jan 2014

Group by in Mysql:

Use order by before group by:

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