Web Development PHP MYSQL Projects: July 2016

<html>
<head>

<style>
.pagNumActive{
color:#000;
border:#060 1px solid;
background-color:#d2ffd2;
padding-left:3px;
padding-right:3px;
}
.paginationNumber a:link{
color:#000;
text-decoration:none;
border:#060 1px solid;
background-color:#F0F0F0;
padding-left:3px;
padding-right:3px;
}

.paginationNumber a:visited{
color:#000;
text-decoration:none;
border:#999 1px solid;
background-color:#f0f0f0;
padding-left:3px;
padding-right:3px;
}

.paginationNumber a:hover{
color:#000;
text-decoration:none;
border:#999 1px solid;
background-color:#f0f0f0;
padding-left:3px;
padding-right:3px;
}

.paginationNumber a:active{
color:#000;
text-decoration:none;
border:#999 1px solid;
background-color:#f0f0f0;
padding-left:3px;
padding-right:3px;
}
</style>

</head>

<body>

<?php
$conn = mysql_connect('localhost', 'root', '') or die(mysql_error);
$db = mysql_select_db('directory', $conn) or die(mysql_error);

$sql = mysql_query("SELECT * FROM linkedin ORDER BY id ASC");
$nr = mysql_num_rows($sql); // get total num of rows
if (isset($_GET['pn'])) { //get pn from url vars if is is present
$pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); //filter every thing but num for security

} else {
$pn = 1;
}

$itemsperpage = 10;

$lastpage = ceil($nr / $itemsperpage);

if ($pn < 1) { //if it is less then 1
$pn = 1; //force it to be 1

} else
if ($pn > $lastpage) // if it is greater then lastpage
{

$pn = $lastpage; //force it to be last value

}

//this create number to click in between next and back buttons

$centerpages = ""; //initialize the veriable
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;

if ($pn == 1) {
$centerpages .= '  <span class ="pagNumActive">' . $pn . '</span>  ';
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 .
'">' . $add1 . '</a>  ';

} else
if ($pn == $lastpage)
{
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 .
'">' . $sub2 . '</a>  ';
$centerpages .= '  <span class ="pagNumActive">' . $pn . '</span>  ';
} else
if ($pn > 2 && $pn < ($lastpage - 1))
{
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 .
'">' . $sub2 . '</a>  ';
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 .
'">' . $sub1 . '</a>  ';
$centerpages .= '  <span class="pagNumActive"> ' . $pn . '</span>  ';
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 .
'">' . $add1 . '</a>  ';
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 .
'">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastpage)
{

$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 .
'">' . $sub1 . '</a>  ';
$centerpages .= '  <span class="pagNumActive"> ' . $pn . '</span>  ';
$centerpages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 .
'">' . $add1 . '</a>  ';

}
// ths line set the limit 2values choese the range
//$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage;
$limit = 'LIMIT ' . ($pn - 1) * $itemsperpage . ',' . $itemsperpage;

$sql2 = mysql_query("SELECT id, fname, lname, company FROM linkedin ORDER BY id ASC $limit ");
///// end adam's paging logic////

////admins paggination display setup///
$paginationDisplay = ""; //initializting pagging output veriable

if ($lastpage != "1") {
$paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastpage;
//if we are not on page 1 we can place the back button
if ($pn != 1) {
$previous = $pn - 1;
$paginationDisplay .= '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous .
'">Back</a>';
}
// lay in to clinckable number display here betweek back and next links
$paginationDisplay .= '<span class="paginationNumber">' . $centerpages .
'</span>';

if ($pn != $lastpage) {
$nextpage = $pn + 1;
$paginationDisplay .= '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextpage .
'">Next</a>';
}
} // end adams pagination display setup////

// build the output section here

$Outputlist = '';
while ($row = mysql_fetch_array($sql2)) {

$id = $row['id'];
$fname = $row['fname'];
$lname = $row['lname'];
$company = $row['company'];

@$outputList .= '
<table width="100%">
<tr>
<td width="13%" rowspan="2"><div style=" height:50px; overflow:hidden;"></div></td>
<td width="14%" class="style7"><div align="right">Name:</div></td>
<td width="73%"><a href="profile.php?id=' . $id .
'" target="_self">' . $fname . '</a> </td>
</tr>
<tr>
<td class="style7"><div align="right">Company:</div></td>
<td>' . $company . '</td>
</tr>
</table>
<hr />';

}

?>

<div style ="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFFFFF; border:#999 solid 1px;">
<?php echo $paginationDisplay; ?>
</div>
<table width="80%" align="center" cellpadding="6">
<tr>
<td><br /><br />
<?php echo $outputList; ?></td>
</tr>
</table>

<div style ="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFFFFF; border:#999 solid 1px;">
<?php echo $paginationDisplay; ?>
</div>

</body>

</html>

We as a developer, often come across situations where we have to work on database related stuffs. Mostly it is done when client send you its data in form of excel sheets and you push that data to database tables after some excel manipulations. I have also done it many times.

A very common problem faced in this approach is that it might result in duplicate rows at times, because data sent is mostly from departments like HR and finance where people are not well aware of data normalization techniques [:-)].

I will use Employee table where column names are id, name, department and email.

Below are the SQL scripts for generating the test data.

Create schema TestDB;

CREATE TABLE EMPLOYEE
(
    ID INT,
    NAME Varchar(100),
    DEPARTMENT INT,
    EMAIL Varchar(100)
);

INSERT INTO EMPLOYEE VALUES (1,'Anish',101,'anish@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (2,'Lokesh',102,'lokesh@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (3,'Rakesh',103,'rakesh@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (4,'Yogesh',104,'yogesh@howtodoinjava.com');

--These are the duplicate rows

INSERT INTO EMPLOYEE VALUES (5,'Anish',101,'anish@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (6,'Lokesh',102,'lokesh@howtodoinjava.com');

Solution:

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

Above sql query will delete rows where name field is duplicate and only those unique rows will be retained where name is unique and ID field is lowest. For example rows with ID 5 and 6 will be deleted and rows with 1 and 2 will be retained.

If you want to retain rows with latest generated ID values, then reverse the condition in where clause to e1.id < e2.id like this:

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

If you want to compare multiple fields and add appropriate where clause.

Note: Please execute above (or modified) query first on test data always to make sure it is producing the expected output.

Happy Learning !!

Web Development PHP MYSQL Projects

Monday, 25 July 2016

Pagination Tutorial for PHP MySQL Programmers

Wednesday, 20 July 2016

Remove Duplicate Rows without Temporary Table

Solution: