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

5 Jan 2014

Group by in mysql table

If your column in mysql table contains similar values, then you will use 'GROUP BY' mysql command to display unique values. It is used to eliminate the duplicate values in mysql table. It takes only first row value from top to bottom in mysql table. The mysql query as follows as:

SELECT * FROM table_name GROUP BY field_name

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:

display unique values using group by in mysql

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.

Group by multiple columns in mysql:

You can group the multiple columns in single mysql query. The mysql query as follows as:

SELECT * FROM table1 GROUP BY id , count

Where,
- The column 'id' got the first preference to group.
- Then the column 'count' is grouped.
Your output like this:

You can see the mysql table 'table1' which is grouped by both id and count. But you can't the get unique values in both mysql columns.

Related Post:

distinct in mysql
Order by in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
Primary key in mysql

Pages