Thursday, July 12, 2012

Avoid Data Repetition in 2 colomns PHP MySQL


Avoid Data Repetition in 2 columns PHP MySQL

create the following table in my_database and Study the  example

 CREATE TABLE IF NOT EXISTS `staff` (
`ID` int(20) NOT NULL AUTO_INCREMENT,
`Division` varchar(50) NOT NULL,
`Designation` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `Staff`
--

INSERT INTO `Staff` (`ID`, `Division`, `Designation`) VALUES
(1, 'Production', 'Manager'),
(2, 'Production', 'Clerk'),
(3, 'Production', 'Clerk'),
(4, 'Sale', 'Manager'),
(5, 'Sale', 'Manager'),
(6, 'Sale', 'Clerk'),
(7, 'Sale', 'Clerk'),
(8, 'Sale', 'Clerk'),
(9, 'Stores', 'Store Keeper'),
(10, 'Stores', 'Labourer'),
(11, 'Stores', 'Labourer'),
(12, 'Stores', 'Labourer'),
(13, 'Stores', 'Clerk'),
(14, 'Stores', 'Clerk');

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("my_database",$con);
$query='SELECT ID,Division,Designation FROM Staff ';
$result = mysql_query($query);
?>
<table bgcolor=#000000 align="center">
 <tr>
<td align="center" bgcolor="#FFFFFF"><strong>ID</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Division</strong></td>
 <td align="center" bgcolor="#FFFFFF"><strong>Designation</strong></td>

<?php
while ($rows = mysql_fetch_assoc($result)){
?>
<tr>
<td bgcolor="#FFFFFF"><?php echo $rows['ID']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['Division']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['Designation']; ?></td>

</tr>
 
<?php    
}

mysql_close($con);
?>
ID Division Designation
1 Production Manager
2 Production Clerk
3 Production Clerk
4 Sale Manager
5 Sale Manager
6 Sale Clerk
7 Sale Clerk
8 Sale Clerk
9 Stores Store Keeper
10 Stores Labourer
11 Stores Labourer
12 Stores Labourer
13 Stores Clerk
14 Stores Clerk

Division and Designation both are repeating SELECT DISTINCT and Group by will not be given desired results.So,I have created 3 quires with html table alterations.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("my_database",$con);
$query="SELECT ID,Division,Designation, COUNT(Designation) as Number FROM Staff  WHERE Division='Production' GROUP BY Designation";
$result = mysql_query($query);
?>
<table bgcolor=#000000 align="center">
 <tr>
<tr><td align="center" bgcolor="#FFFFFF" colspan=3><strong>Production</strong></td></tr>
<tr>
 <td align="center" bgcolor="#FFFFFF"><strong>Designation</strong></td>
 <td align="center" bgcolor="#FFFFFF"><strong>Number</strong></td></tr>

<?php
while ($rows = mysql_fetch_assoc($result)){
?>


<td bgcolor="#FFFFFF"><?php echo $rows['Designation']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['Number']; ?></td>
</tr>
 
<?php    
}

mysql_close($con);
?>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("my_database",$con);
$query1="SELECT ID,Division,Designation, COUNT(Designation) as Number FROM Staff  WHERE Division='Sale' GROUP BY Designation";
$result1 = mysql_query($query1);
?>

 <tr>
<tr><td align="center" bgcolor="#FFFFFF" colspan=3><strong>Sale</strong></td></tr>
<tr>
 <td align="center" bgcolor="#FFFFFF"><strong>Designation</strong></td>
 <td align="center" bgcolor="#FFFFFF"><strong>Number</strong></td></tr>

<?php
while ($rows = mysql_fetch_assoc($result1)){
?>


<td bgcolor="#FFFFFF"><?php echo $rows['Designation']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['Number']; ?></td>
</tr>
 
<?php    
}

mysql_close($con);
?>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("my_database",$con);
$query2="SELECT ID,Division,Designation, COUNT(Designation) as Number FROM Staff  WHERE Division='Stores' GROUP BY Designation";
$result2 = mysql_query($query2);
?>

 <tr>
<tr><td align="center" bgcolor="#FFFFFF" colspan=3><strong>Sale</strong></td></tr>
<tr>
 <td align="center" bgcolor="#FFFFFF"><strong>Designation</strong></td>
 <td align="center" bgcolor="#FFFFFF"><strong>Number</strong></td></tr>

<?php
while ($rows = mysql_fetch_assoc($result2)){
?>


<td bgcolor="#FFFFFF"><?php echo $rows['Designation']; ?></td>
<td bgcolor="#FFFFFF"><?php echo $rows['Number']; ?></td>
</tr>
 
<?php    
}

mysql_close($con);
?>
Production
Designation Number
Clerk 2
Manager 1
Sale
Designation Number
Clerk 3
Manager 2
Sale
Designation Number
Clerk 2
Labourer 3
Store Keeper 1
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)