Tuesday, January 25, 2011

Mysql joins query:

Mysql joins query:

SELECT oid,empid,name,title,date,start,close,duration,status FROM rept join emp where rept.empid=emp.empid and emp.manid=0071 order by oid desc limit 1,10;

Result of above query:

+-----+-------+---------------+-----------------------------+------------+-------+-------+----------+--------+
| oid | empid | name | title | date | start | close | duration | status |
+-----+-------+---------------+-----------------------------+------------+-------+-------+----------+--------+
| 48 | 1023 | Sriram | The Sentimentalists_5-12 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 47 | 1026 | Suresh | A History of marriage_1-26 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 46 | 1027 | Venkatesh | Bury Your Dead_12-23 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 45 | 4050 | Senthil | A History of marriage_22-54 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 44 | 1026 | Suresh | Bury Your Dead_122-155 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 43 | 4042 | Arul Raj | Bury Your Dead_2-300 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 42 | 1023 | Sriram | Bury Your Dead_1-6 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 21 | 4053 | Vetriselvan | A History of marriage_44-55 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 20 | 4042 | Arul Raj | A History of marriage_33-44 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
| 19 | 4049 | Senthil Kumar | A History of marriage_22-33 | 2011-01-25 | NULL | NULL | 00:00:00 | p |
+-----+-------+---------------+-----------------------------+------------+-------+-------+----------+--------+
10 rows in set

php pagination simple script

simple php pagination script:

'<'?php

$pageNum=1;
$rowsPerPage=10;
if(isset($_REQUEST['p']))
{
$pageNum = $_REQUEST['p'];
}
$offset = ($pageNum - 1) * $rowsPerPage;
$npage=$pageNum+1;
$prev=$pageNum-1;
$sql="SELECT oid,empid,empname,name,mdate,starttime,closetime,duration,status FROM rept join emp where rept.empid=emp.emp_id and emp.man_id='$manid' order by oid desc limit $offset,$rowsPerPage";
$rlts=mysql_query($sql);
while($row=mysql_fetch_array($rlts))
{
$cid = $row['oid'];
$emp_id = $row['empid'];
$empnm = $row['empname'];
$name= $row['name'];
$mod_date = $row['mdate'];
$start_time = $row['starttime'];
$start = date("g:i a", strtotime(".$starttime."));
$close_time = $row['close_time'];
$close = date("g:i a", strtotime(".$closetime."));
$duration = $row['duration'];
$status = $row['status'];
if($status==p)
{
$status='On process';
}
elseif($status==f)
{
$status='Finished';
}
else
{
$status='Hold';
}

$query = mysql_query("select count(*) as numrows from rept join emp where rept.empid=emp.empid and emp.man_id='$manid' order by oid desc") or die (mysql_error($query));
$result = mysql_fetch_object($query);
$numrows = $result->numrows;
$maxPage = ceil($numrows/$rowsPerPage);
$nav = '';
for($page = $pageNum; $page '<'= $pageNum+5; $page++)
{

if ($page '<'= $maxPage)
{
if ($page == $pageNum)
{
if($page !=1)
{
$nav .= "$page"; // no need to create a link to current page
}

}
else
{

$nav .= "'<'a href='typereport.php?p=$page'>'<'font color='#000000'>".$page."'<'/font>'<'/a>";
}
}
}
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " '<'a href='typereport.php?p=$page'>'<'font color='#000000'>'<''<'/font>'<'/a>";

$first = " '<'a href='typereport.php?p=1'>'<'font color='#000000'><<'<'/font>'<'/a> ";
}
else
{
$prev = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}
if ($pageNum '< '$maxPage)
{
$page = $pageNum + 1;
$next = " '<'a href='typereport.php?p=$page'>'<'font color='#000000'>>'<'/font>'<'/a> ";

$last = " '<'a href='typereport.php?p=$maxPage'>'<'font color='#000000'>>>'<'/font>'<'/a> ";
}
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}
?>
'<'tr>'<'td>'<'?php echo $emp_id; ?>'<'/td>'<'td >'<'?php echo ucfirst($coral_empnm); ?>'<'/td>'<'td >'<'?php echo $book_name; ?>'<'/td>'<'td >'<'?php echo $pages; ?>'<'/td>'<'td >'<'a href="#" onclick="popupView('<'?php echo $cid; ?>)">view'<'/a>'<'/td>'<'td >'<'?php if($work_status!=p) { ?>'<'a href="textdown.php?download_file='<'?php echo $book.'_'.$cid; ?>.txt">Download'<'/a>'<'?php } else { echo '-'; } ?>'<'/td>'<'td >'<'?php echo $assign_date; ?>'<'/td>'<'td >'<'?php echo $type_duration; ?>'<'/td>'<'td>'<'?php echo $status; ?>'<'/td>'<'/tr>
'<'?php
}
?>
'<'/table>
'<'div>'<'?php echo $first.$prev.$nav.$next.$last; ?>'<'/div>

onKeyUp search database data to select form value

onKeyUp search database data to select form value using jquery:

Header section:

'<'script type="text/javascript" src="jquery-1.2.1.pack.js">'<'/script>
'<'script type="text/javascript">
function lookup(bknm) {
if(bknm.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
$.post("search.php", {queryString: ""+bknm+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
} // lookup

function fill(thisValue) {
$('#bknm').val(thisValue);
setTimeout("$('#suggestions').hide();", 200);
}

'<'/script>
'<'style type="text/css">
.formtab{border:1px #000000 solid; }
.suggestionsBox {
position: relative;
left: 80px;
margin: 10px 0px 0px 0px;
width: 200px;
background-color: #212427;
-moz-border-radius: 7px;
-webkit-border-radius: 7px;
border: 2px solid #000;
color: #fff;
}

.suggestionList {
margin: 0px;
padding: 0px;
}

.suggestionList li {

margin: 0px 0px 3px 0px;
padding: 3px;
cursor: pointer;
}

.suggestionList li:hover {
background-color: #659CD8;
}
'<'/style>

Inside the body:

'<'form action="jqry.php" method="post">
'<'input class="formtab" name="bknm" type="text" id="bknm" onkeyup="lookup(this.value);" onblur="fill();">
'<'input name="submit" type="submit" value="submit" />
'<'/form>


Search.php:

'<'?php
$db = new mysqli('localhost', 'root' ,'', 'dbname');
if(!$db) { echo 'ERROR: Could not connect to the database.';}
else {
if(isset($_POST['queryString']))
{
$queryString = $db->real_escape_string($_POST['queryString']);
if(strlen($queryString) >0)
{
$query = $db->query("SELECT name FROM employee WHERE name LIKE '%$queryString%' LIMIT 10");
if($query)
{
while ($result = $query ->fetch_object())
{

$name=
$result ->name;
echo ''<'li onClick="fill(\''.$book_name.'\');">'.$book_name.''<'/li>';
}
}
else
{
echo 'ERROR: There was a problem with the query.';
}
}
else
{
}
}
else
{
echo 'There should be no direct access to this script!';
}
}

?>

download jquery-1.2.1.pack.js

Sunday, January 23, 2011

Display clock Your html page

Display clock using javasript:

java script header section code:

function tS(){ x=new Date(); x.setTime(x.getTime()); return x; }
function lZ(x){ return (x>9)?x:'0'+x; }
function tH(x){ if(x==0){ x=12; } return (x>12)?x-=12:x; }
function dT(){ if(fr==0){ fr=1; document.write(''+eval(oT)+''); } document.getElementById('tP').innerHTML=eval(oT); setTimeout('dT()',1000); }
function aP(x){ return (x>11)?'pm':'am'; }
var fr=0,oT="tH(tS().getHours())+':'+lZ(tS().getMinutes())+':'+lZ(tS().getSeconds())+' '+aP(tS().getHours())";


inside the html page add below this code (Add where ever you want display clock):

'<'script language="JavaScript">dT();'<'/
script>

.htaccess file (Error Redirection) code

Error Redirection : .htaccess
--------------------------------------
ErrorDocument 400 /typing/logout.php
ErrorDocument 401 /typing/logout.php
ErrorDocument 403 /typing/logout.php
ErrorDocument 404 /typing/logout.php
ErrorDocument 500 /typing/logout.php

php simple login script

login script:

'<'?php
function confirmUser($username, $password){
global $conn;
if(!get_magic_quotes_gpc()) {
$username = addslashes($username);
}
$query = "select emp_id,job_id from emp where emp_name = '$username'";
$result = mysql_query($query);
if(!$result || (mysql_numrows($result)'<'1)){ return 1;
}

$dbarray = mysql_fetch_array($result);
$dbarray['emp_id'] = stripslashes($dbarray['emp_id']);
$jobid = $dbarray['job_id'];
$_SESSION['job_id'] = $jobid;
$password = stripslashes($password);
$md5pass = md5($dbarray['emp_id']);
if($password == $md5pass){
return 0;
}
else{
return 2;
}
}
function checkLogin(){
global $_SESSION;
if(isset($_COOKIE['cookname']) && isset($_COOKIE['cookpass'])){
$_SESSION['username'] = $_COOKIE['cookname'];
$_SESSION['password'] = $_COOKIE['cookpass'];
}
if(isset($_SESSION['username']) && isset($_SESSION['password'])){
if(confirmUser($_SESSION['username'], $_SESSION['password']) != 0){
unset($_SESSION['username']);
unset($_SESSION['password']);
return false;
}
return true;
}
else{
return false;
}
}

function displayLogin(){
global $logged_in;
if($logged_in)
{
$jbid =$_SESSION['job_id'];

switch ($jbid)
{
case 1:
include 'index.php';
break;
case 2:
include 'index1.php';
break;
case 3:
include 'index2.php';
break;
case 4;
header('index3.php');
break;
}


}
else{
?>

'<'form id="login-form" action="" method="post">
UserName: '<'input type="text" name="user" maxlength="20" class="inputbox">
Password: '<'input type="password" name="pass" maxlength="4" class="inputbox">
Remember:'<'input type="checkbox" name="remember">

'<'input type="submit" class="button" name="sublogin" value="Log in">
'<'/form>

'<'?php
}
}
if(isset($_POST['sublogin'])){
if(!$_POST['user'] || !$_POST['pass']){
$report1 = 'You didn\'t fill in a required field.';
echo "";
}
$md5pass = md5($_POST['pass']);
$result = confirmUser($_POST['user'], $md5pass);
if($result == 1){
$report2 = 'That username doesn\'t exist in our database.';
echo "";
}
else if($result == 2){
$report3 = 'Incorrect password, please try again';
echo "";
}
$user = stripslashes($_POST['user']);
$_SESSION['username'] = $user;
$_SESSION['password'] = $md5pass;
$passwrd = $_POST['pass'];
$_SESSION['empid'] = $passwrd;

if(isset($_POST['remember'])){
setcookie("cookname", $_SESSION['username'], time()+60*60*24*6, "/");
setcookie("cookpass", $_SESSION['password'], time()+60*60*24*6, "/");

}

return;
}
$logged_in = checkLogin();
?>