Web Development PHP MYSQL Projects

Monday, 25 July 2016

Pagination Tutorial for PHP MySQL Programmers

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

Wednesday, 20 July 2016

Remove Duplicate Rows without Temporary Table

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

Friday, 27 June 2014

Get Array Value in Combobox From MYSQL DataBase PHP

<?php
@$conn = mysql_connect("localhost", "root", "");
mysql_select_db("test", $conn);

$query = "SELECT * from member";
$result = mysql_query($query, $conn) or die(mysql_error());
$num= mysql_num_rows($result);

if ($num> 0) {
while ($row = mysql_fetch_assoc($result)) {
$array[] = array(
'uid' => $row["uid"],
'fname' => $row["fname"]
);
}
}
?>

<form name="form" action="next.php" method="get">
<select name="uid">
<?php
foreach($array as $value) {
print("<option value=\"{$value['uid']}\">{$value['fname']}</option>");
}
?>
</select>
<input type="submit" value="Send" />
</form>

Wednesday, 4 June 2014

Access control modifiers in php

There are a number of special keywords you can place before a class, a class function definition, or a class variable to alter the way PHP works with it - here's the full list, along with what each of them does:

Public: This variable or function can be used from anywhere in the script
Private: This variable or function can only be used by the object it is part of; it cannot be accessed elsewhere
Protected: This variable or function can only be used by the object it is part of, or descendents of that class
Final: This variable or function cannot be overridden in inherited classes
Abstract: This function or class cannot be used directly - you must inherit from them first

That is just a vague description of what each of them do - to make sure you fully understand each of them, here are examples:

<?php

class Example {

public $a = 1;

protected $b = 2;

private $c = 3;

function show_abc()

{

echo $this -> a ;

echo $this -> b;

echo $this -> c;

}

$example = new Example();

echo "Publc a: {$example->a}<br/>";

//echo "Protected b: {$example->b}<br/>";

//echo "Private c: {$example->c}<br/>";

$example->show_abc();

understanding class inheritance

<?php

class car{
var $wheels = 4;
var $doors = 4;
function wheelsdoors(){
return $this->wheels + $this->doors;

}


}

class CompactCar extends car{

var $doors = 2;

}

$car1 = new car();
$car2 = new CompactCar();

echo $car1->wheels . "<br>";
echo $car1->doors . "<br>";
echo $car1->wheelsdoors(). "<br>";
echo "<br>";
echo "<br>";
echo "<br>";

echo $car2->wheels . "<br>";
echo $car2->doors . "<br>";
echo $car2->wheelsdoors(). "<br>";

?>

Monday, 26 May 2014

Passing PHP form variables from one page to other pages

File:form.php

<form method="post" action="form2.php">
    <input type="text" name="name">
    <input type="text" name="email_address">
    <input type="submit" value="Go To Step 2">
</form>

<?php

//let's start the session File:form2.php
session_start();

//now, let's register our session variables
$name = $_SESSION['name'];
$email = $_SESSION['email_address'];

//finally, let's store our posted values in the session variables
$_SESSION['name'] = $_POST['name'];
$_SESSION['email_address'] = $_POST['email_address'];

?>
<form method="post" action="form3.php">
<input type="radio" name="membership_type" value="Free">
<input type="radio" name="membership_type" value="Normal">
<input type="radio" name="membership_type" value="Deluxe">
<input type="checkbox" name="terms_and_conditions">
<input type="submit" value="Go To Step 3">
</form>

<?php

//let's start the session File:form3.php
session_start();

//now, let's register our session variables

$membership_type = $_SESSION['membership_type'];
$terms_and_conditions = $_SESSION['terms_and_conditions'];
//session_register('membership_type');
//session_register('terms_and_conditions');

//finally, let's store our posted values in the session variables
$_SESSION['membership_type'] = $_POST['membership_type'];
$_SESSION['terms_and_conditions'] = $_POST['terms_and_conditions'];

?>
<form method="post" action="form_process.php">
<input type="text" name="name_on_card">
<input type="text" name="credit_card_number">
<input type="text" name="credit_card_expiration_date">
<input type="submit" value="Finish">
</form>

<?php
//form_process.php
//let's start our session, so we have access to stored data
    session_start();

    $_SESSION['name_on_card'] = $_POST['name_on_card'];
     $_SESSION['credit_card_number'] = $_POST['credit_card_number'];
      $_SESSION['credit_card_expiration_date'] = $_POST['credit_card_expiration_date'];



echo $name = $_SESSION['name'];
echo $email = $_SESSION['email_address'];
echo $membership_type = $_SESSION['membership_type'];
echo $terms_and_conditions = $_SESSION['terms_and_conditions'];

echo $name_on_card = $_SESSION['name_on_card'];
echo $credit_card_number = $_SESSION['credit_card_number'];
echo $credit_card_expiration_date = $_SESSION['credit_card_expiration_date'];

?>

Friday, 23 May 2014

Taskfeed Project Management Software - CodeCanyon

Demo : http://www.taskfeed.net/about-taskfeed

Download

http://adf.ly/nxEJ5

IMPORTANT!!!

Taskfeed PE v1.45 now available! Supports UTF 8 / Unicode application wide! Please upgrade now

What is TASKFEED?

Taskfeed is a Project Management Software built upon ideas and experiences from real life project cycle and interactions. Over the years I’ve used many Project Management tools. While many of them served the purpose right, I found them bloated or overly complicated at times.

I realized that, Task/Project Management software can do a world of good if there was a Team Environment. A way to keep it simple, clean and elegant. Taskfeed is meant for TeamWork. Create your team and manage your projects together! Get notified on the go and have fun organizing your ever so cluttered project queue.

Taskfeed Currently features (but is not limited to)

Responsive UI
Invitation to join Taskfeed/Your Project by email.
Creating your own team to work with.
Apps for To-Do, Milestones and Discussion.
Attachment in discussion.
Threaded Discussion View.
Email Notification for all major events on a project.
Runs on simple Apache Server
User Roles as Project Owner and Contractors.
Managing your priority Queue
Task Calendar.
Public 3D Business Card!
Task and Project Management Panels.

Manage Your Team!

Create and manage your team easily with TaskFeed! Just type in the email address or username of the person you want in your team and hit ENTER! If the user is already in Taskfeed, he’ll get notified immediately, if he’s not, the system will send an invitation to the email address and request him/her to join TAKSFEED. In Taskfeed or not, after registration and logging in, there will be the notification waiting and reminding the user to join your team!

Dashboard

The TASKFEED dashboard is your hub to link up all the happenings at Taskfeed that you care. You can jump onto all the discussions ToDo and milestones directly from the Dashboard links. You can view the Task Calendar too. All the actions of Taskfeed can be accessed from the colorful buttons on the right side bar.

Manage Your Task

Shows you all the projects you are currently working on. Quickly access and browse through your current projects or take required actions. You may leave any project from this window when and if you need to.

Manage Your Projects

As the title says, it lets you manage all your projects. You can view, jump to discussion or remove a project from here.

Discussion APP

Inside every project, you can install the DISCUSSION App. Helps you keep connected with the contractors and discuss about whats going on or what needs to be done. You and the other parties can attach files and screenshots and whatever needed to assist the progress of the project.