PHP - Json For Paginator Problem. No Data.

Hi,


I have a problem with some code not working.

I need it to get the data from a mysql database and then export it as a json array in the format of id , title, author, date, imageUrl, text. (Please not that these are all variables )

PHP CODE:
Code: [Select]
<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

$arr = array();
$rs = ("SELECT `id`, `title`, `author`, `date`, `imageUrl`, `text` FROM `items`");
 
while($obj = mysql_query($rs)) {
$arr[0] =  $obj->id;
$arr[1] =  $obj->title;
$arr[2] =  $obj->author;
$arr[3] =  $obj->date;
$arr[4] =  $obj->imageUrl;
$arr[5] =  $obj->text;
}
 
echo '{"items":'.json_encode($arr).'}';
?>

Thanks For Your Help
borden0108

I am retrieving data from a third party's API using AJAX method. I would like to do two things.

1. Display the data records on a page.

2. Create a pagination on the page to display records more efficiently. I am expecting to retrieve possibly hundreds of records. I can normally do this using PHP but since I am retrieving the records using AJAX, the pagination is gonna be a challenge. 

First things first is to display the records. Here is my AJAX code.

It displays the log data fine. But it doesn't display any data in the "output" div. And it gives this error in the console.

Uncaught SyntaxError: Unexpected token o in JSON at position 1
at JSON.parse (<anonymous>)
at Object.success

<div class="output"></div>

<script>
  $.ajax ({
    type: 'GET',
    url: "https://3rdpartywebsite.com/api/GetCustomers",
    dataType: 'json',
    processData: false,
    contentType: false,
    success: function(data) {
      console.log(data)

      $newData = JSON.parse(data);
      $.each($newData, function(i, v) {
         $('.output').append(v.LastName);
         $('.output').append(v.FirstName);
      });
    }
  });
</script>

 

What am I doing wrong?

Edited November 14, 2020 by imgrooot

Hi-
I have to post to a URL, grab one field our of the JSON output/response and format nicely to the user. I've tried a few things with some help but none seem to be working. So any help or direction would be greatly appreciate it!!

Attempt 1 (works but not formatted nicely)
if I post using the browser, it works. the data is sent to the URL (web service) and I can see in the web service logs that the entry has been recorded. the problem is that since i have the URL in "action", i'm taken to that page and the user gets to see the JSON output

Code: [Select]
//here's the HTML form in offer.php
<form action="http://somewebservice.com/<?php echo $PrizeID;?>" method="post" name="myform">
<input name="account" id="account" type="hidden" value="<?php echo $account;?>"/>
<input name="dob" id="dob" type="hidden"  value="<?php echo $dob;?>"/>
<input name="site" id="site" type="hidden"  value="<?php echo $site;?>"/>
<input id="submit" type="submit" value="submit"/>
</form>

//here's the output (JSON) after clicking submit
{"Prize":"XXXXXX","ConfirmationCode":"######","Error":false,"ErrorMsg":null}


Attempt 2 (doens't work)
i tried using php but after submit, the page refreshes and no entry is recorded in the web service log

Code: [Select]
//here's the HTML form in offer.php
<form name="myform">
<input name="account" id="account" type="hidden" value="<?php echo $account;?>"/>
<input name="dob" id="dob" type="hidden"  value="<?php echo $dob;?>"/>
<input name="site" id="site" type="hidden"  value="<?php echo $site;?>"/>
<input id="submit" type="submit" value="submit"/>

//here's the php in offer.php
<?php 
if(isset($_POST['submit'])){
$options = array(
            'http'=>array(
                'method'=>"POST",
                'content'=>http_build_query(array(
                        'account' => $account,
                        'dob' =>   $dob,
                        'site' => $site
                    ))
            ));

        $context = stream_context_create($options);
        $result =  file_get_contents("http://somewebservice.com/{$PrizeID}",NULL,$context);
        var_dump($result);
}
?>

Last attempt (doesn't work)
i tried using php & curl so that i can process in the back and show only the ConfirmationCode to the user in a nice format but it doesn't work. after submit, i'm taken to process.php where i see nothing and no entry is recorded in the web service log

Code: [Select]
//here's the HTML form in offer.php
<form action="process.php" method="post" name="myform">
<input name="account" id="account" type="hidden" value="<?php echo $account;?>"/>
<input name="dob" id="dob" type="hidden"  value="<?php echo $dob;?>"/>
<input name="site" id="site" type="hidden"  value="<?php echo $site;?>"/>
<input id="submit" type="submit" value="submit"/>
</form>

//here's the PHP in process.php
<?php 
$postvars=file_get_contents("php://input");
$curl=curl_init("http://somewebservice.com/{$PrizeID}");
curl_setopt($curl,CURLOPT_POSTFIELDS,$postvars);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); 
$result=curl_exec($curl);
$decodedresult=json_decode($result,true);
?>


i haven't gotten to the point where i grab the ConfirmationCode I want because i want to get this fixed first and also because i can't get that to work but will focus on that once i fix this.
Thanks.

This is more of a database architect question.   I am building a CMS for website development. I am ready to use it for the first time on one of my clients. Now, my issue is is how to set up some or one of my table(s) for some areas that my client wants to be able to change in the admin section.   In their website on the front page, I put together this nice block slider. Its 6 blocks with text, a background image and nice effects. At first it was always going to be static, which was no issue at all, but later they came to me and asked if they could be able to change words in the boxes themselves in the admin section..   Of course this can be easily done by creating a table that is associated with this home page block thing and each row in the table will correspond to one of the blocks on the home page.   But I believe there might be an easier way to store this type of data without having to create another table for every feature the client wants to edit, for example they also want to be able to edit a slider on another page.   The WordPress database then came into my mind and how they store most of their data as JSON. So I was then thinking of creating a table named 'modules' for examples with three columns (id, title, data). Then inside the data column store the editable fields as a JSON string object.   For example a row could like like :   1, "Front Page Box Slider", {"slider-home" : [ {"data" : "value", "img_1", "value1"}, {"data" : "value", "img_2", "value2"} ]}   I've been doing a bit of research on this and people have just been saying, never do this if you do want your data to be searched through, which I don't.   Can anyone tell me why or why not I should do this, or maybe a better solution to my problem?   Thank you
Edited by carlosmoreeira, 17 September 2014 - 07:37 PM.

good evening folks, i have a question regarding the foreach syntax when needing to access something say 3 levels deep into the json architecture here's the example I'm trying to accomplish but when echoing it's not returning the value i need from 3 levels deep. I believe i just don't understand the correct syntax so here's the example any help would he sincerely appreciated.
https://pastebin.com/mEBiMUW5

I implemented the Content-Security-Policy (CSP). I also included the report-uri so it sends a POST request to myserver.com/csp-report.php
test.php

<?php
header("Content-Security-Policy: default-src 'self'; report-uri http://127.0.0.1/csp-report.php");
?>

I attempted to run these two inline scripts in my test.php file.
 

<img src="http://evil.example.com/image.png">
<script>alert('XSS');</script>

nothing is saved in the report-uri file! However, using Firebug, the policy is working as it should.   Kindly advise if I have the report-uri synthax wrongly.   Thank you
Edited by terungwa, 20 October 2014 - 09:06 AM.

Hi, I have a connection set up to an API using a PHP script - the API sends back data in JSON format, and if I capture it and echo it, it displays in extremely unfriendly format on the screen.

I've tried to find a way to convert this data into readable format, ideally in a HTML table, but although there are articles on converting manually inputted JSON data into a HTML table using Javascript, I can't find a way to do it from a PHP variable.

This is what I have so far:

if ($_POST['getcompany']) {
$companyname = $_POST['_Name'];
	
$ch = curl_init();

$data_array2 =  array(
      'token' => $token
);
$make_call2 = json_encode($data_array2);
	//echo 'Token is '.$token;
	$token2 = substr($token, 14);
	$token3 = substr($token2, 0, -3);
	
	//echo 'Token 2 is '.$token3;
	//echo 'Company Name is '.$companyname;

//curl_setopt($ch, CURLOPT_GET, 1);
//curl_setopt($ch, CURLOPT_POSTFIELDS, $make_call2);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json','Authorization: '.$token3.'')); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_URL, 'https://connectionurl?countries=GB&name='.$companyname);

//Execute the request
$result = curl_exec($ch);

echo 'Companies: '.$result; //This displays the data provided by the search, but is in JSON format

 

So I need a way to get the data held in $result, into a HTML table. Any idea how I can do this?

I am having a nightmare sending / recieving some JSON.....

I have a test page which creates a JSON packet and posts it to another page. This page then processes it and returns a response... the data is in the post, but after decoding it the data seems to be lost....

Here is the code for the sending page:


Code: [Select]


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<?php

require_once ("inc/jsonwrapper/JSON/JSON.php");

$RequestID ='121'; // :ID to which this request relates to allow site to keep track of things


$Method = 'login'; //:Which method the call is directed at
$referer  = 'abc';  // :Name of referring network
$affiliateid  =2468;  // :ID of referring site in affiliate network if any
$user_Country  ='gb';  // :Users two letter country code
$UserID  = '12345';  // :Numeric  ID to identify players in game database belonging to network or portal 
$GameuserID = '1' ; // UserID in game database

$params = array ( 'referer' => $referer , 'affiliateid' => $affiliateid , 'userCountry' => $user_Country , 'UserID' => $UserID , 'GameuserID' => $GameuserID);


$response = array ( 'jsonrpc'=>'2.0', 'id'=> $RequestID , 'Method' => $Method , 'params' => $params) ;

$data = json_encode($response);

// URL of web api page

$url =  'http://xxx.com/webapi.php';
 
$reply = do_post_request($url, $data);

// setup new JSON service
$json = new Services_JSON();

$jsonPacket = $json->decode($reply,true); 

//THIS IS WHERE I PRINT THE RESPONSE 
echo print_r ($jsonPacket,1);

 

function do_post_request($url, $data, $optional_headers = null)
{
     $params = array('http' => array(
                  'method' => 'POST', 
      'header'=>"Content-Type: text/xml; charset=utf-8",
                  'content' => $data
               ));
     
  
  if ($optional_headers !== null) {
        $params['http']['header'] = $optional_headers;
     }
     $ctx = stream_context_create($params);
     $fp = @fopen($url, 'rb', false, $ctx);
     if (!$fp) {
        throw new Exception("Problem with $url, $php_errormsg");
     }
     $response = @stream_get_contents($fp);
     if ($response === false) {
        throw new Exception("Problem reading data from $url, $php_errormsg");
     }
     return $response;
}
?>



And the replying page

Code: [Select]

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>


<?

//setup database connection
require_once('globals.php');

//setup JSON
require_once ("inc/jsonwrapper/JSON/JSON.php");
$secret_Key = '12345';//SECRET KEY
$app_Id = '6789';//APP ID
$token = '';


// setup new JSON service
$json = new Services_JSON();

// accept incoming POST data
$input = $GLOBALS['HTTP_RAW_POST_DATA'];


//hashes
$hash = $_GET['authHash'];
$sHash  = strtolower(md5($input . $secret_Key));

$hash = 0;
$sHash  = 0;

if (strcmp($sHash, $hash) == 0) {


//check if incoming is JSON as we MIGHT use XML later too
$isJSON=  substr_count($input,"json");

if ($isJSON > 0){

processJSON ($input);
}


global $hash, $sHash;


}



//JSON FUNCTION TO PARSE JSON
function processJSON($input){


global $json;
$jsonPacket = $json->decode($input,true);
$json_RequestId = $jsonPacket->id;



//check method and go to correct function
switch ($jsonPacket->Method){

case 'Game.login':
game_login ($jsonPacket, $json_RequestId) ;
break;


}
}





//JSON FUNCTIONS


function game_login($jsonPacket, $json_RequestId){


$aid = ($jsonPacket->params->affiliateid);

$country_code = ($jsonPacket->params->userCountry);

$GameuserID = ($jsonPacket->params->GameuserID);

$UserID = ($jsonPacket->params->UserID);
//mail ("martyn@staggan.com" , "SNSUserID" , $UserID  );

$result_userIDcheck = mysql_query("SELECT snsUserid FROM player where player_id = $GameuserID;");
$result_UserID = mysql_result ($result_userIDcheck, 0);


if ($UserID == $result_UserID){
$result = 'OK';
$id = $json_RequestId;

//create token
global $secret_Key ;
$token = hash ('md5', $secret_Key.$aid.$GameuserID);

$logindetails = array ( 'token=' => $token , 'UID'=> $GameuserID , 'snsUID' => $UserID , 'aid' => $aid , 'name' => $name);

$params= array ('result'=>$result, 'logindetails'=> $logindetails, 'GameuserID'=>$GameuserID);
$response = array ( 'jsonrpc'=>'2.0', 'id'=> $id , 'result' => $params) ;
echo json_encode($response);
}

else mail ("test@test.com", "login", 'false');
}



?>


If I print the $reply var I see this:

{"jsonrpc":"2.0","id":"121","result":{"result":"OK","logindetails":{"token=":"68c49918c353a3e5d86d165da1cb2f72","UID":"1","snsUID":"12345","aid":"6789","name":null},"GameuserID":"1"}}

But trying to decode to an array leaves me with nothing....

Any help would be greatly appreciated

I got some code to make a simple quiz, but I'm struggling to get it to work. I think the form and the following JavaScript code are set up correctly, but I'm running into problems with the PHP code at the end. I'm especially confused by the JSON reference at the very end; I don't have a clue what it means or what do do with it.   Does anyone know how to fix/complete this script?   On another note, why is it necessary to use JSON in the first place? Aren't the answers already provided in the PHP code (e.g. $answer1 = $_POST['A']?   Thanks.  

<form action="">
    <fieldset>
        What color is an apple?
        <label>blue <input type="radio" value="A" name="question-1"></label>
        <label>red <input type="radio" value="B" name="question-1"></label>
        <label>yellow <input type="radio" value="C" name="question-1"></label>
        <label>gray <input type="radio" value="D" name="question-1"></label>
    </fieldset>
    <fieldset>
        What color is a banana?
        <label>yellow <input type="radio" value="A" name="question-2"></label>
        <label>blue <input type="radio" value="B" name="question-2"></label>
        <label>red <input type="radio" value="C" name="question-2"></label>
        <label>gray <input type="radio" value="D" name="question-2"></label>
    </fieldset>
    <fieldset>
        What color is the sky?
        <label>yellow <input type="radio" value="A" name="question-3"></label>
        <label>blue <input type="radio" value="B" name="question-3"></label>
        <label>red <input type="radio" value="C" name="question-3"></label>
        <label>gray <input type="radio" value="D" name="question-3"></label>
    </fieldset>
    <fieldset>
        What color is snow?
        <label>white <input type="radio" value="A" name="question-4"></label>
        <label>blue <input type="radio" value="B" name="question-4"></label>
        <label>red <input type="radio" value="C" name="question-4"></label>
        <label>gray <input type="radio" value="D" name="question-4"></label>
    </fieldset>
  <input type="button" value="Submit" class="submit" id="Submit2">
</form>
 
<script>
$(function(){
 
    // Trap the form submit event
    $('form').submit(function(e){
        e.preventDefault() // Prevent browser refresh
        var answers = $(this).seralize()
 
        // Manually send the data with AJAX
        $.post({
            url: $(this).attr('action'),
            data: answers,
 
            // Stuff to do after we get a response
            success: function(response){
                $('#results').text(response.correct)
            }
        })
    })
})
</script>
 
<?php 
$answer1 = $_POST['A'];
$answer2 = $_POST['A'];
$answer3 = $_POST['C'];
$answer4 = $_POST['D'];
$totalCorrect = 0;
if ($answer1 == "B") { $totalCorrect++; }
if ($answer2 == "A") { $totalCorrect++; }
if ($answer3 == "B") { $totalCorrect++; }
if ($answer4) { $totalCorrect++; }
 
// Send the data back as JSON
die json_encode(array(
    'correct'    => $totalCorrect;
));
?>

Hey there,

I am using prototype to send JSON information to a PHP file which will then use the data to update a database, the AJAX presently looks as follows:

Code: [Select]
function saveprofile()
{
var email = document.getElementById("email").value;

var profileDetails =
{
"email":email,
"titleitalic":titleitalic,
"profileDetails":titlefontcolor,
"titlefontcolor":titleunderline,
"titleoffsettop":titleoffsettop,
"titleoffsetleft":titleoffsetleft,
"aboutbackgroundcolour":aboutbackgroundcolour,
"aboutunderline":aboutunderline,
"aboutfontfamily":aboutfontfamily,
"aboutbold":aboutbold,
"aboutitalic":aboutitalic,
"aboutfontcolor":aboutfontcolor,
"aboutoffsettop":aboutoffsettop,
"aboutoffsetleft":aboutoffsetleft,
"statusoffsetleft":statusoffsetleft,
"statusinfooffsetright":statusinfooffsetright,
"pictureoffsetleft":pictureoffsetleft,
"profilebold":profilebold,
"profileunderline":profileunderline,
"profileitalic":profileitalic,
"profilefontcolor":profilefontcolor,
"profilefontfamily":profilefontfamily,
"profilebackgroundcolour":profilebackgroundcolour
}

var jsonstring = JSON.stringify(profileDetails);
alert(jsonstring);

   var request = new Ajax.Request
        ('scripts/updateprofile.php',
            { method: 'POST',
              parameters: jsonstring,
              onComplete: responseReturned }
        );
}


Which outputs the following JSON which has been tested on http://www.jsonlint.com/ as valid:

Code: [Select]
{
    "email": "l-wilson-1986@hotmail.co.uk",
    "titleitalic": "normal",
    "profileDetails": "#d0deea",
    "titlefontcolor": "none",
    "titleoffsettop": 100,
    "titleoffsetleft": 100,
    "aboutbackgroundcolour": "transparent",
    "aboutunderline": "none",
    "aboutfontfamily": "Arial",
    "aboutbold": "normal",
    "aboutitalic": "normal",
    "aboutfontcolor": "#d0deea",
    "aboutoffsettop": 325,
    "aboutoffsetleft": 100,
    "statusoffsetleft": 450,
    "statusinfooffsetright": 250,
    "pictureoffsetleft": 100,
    "profilebold": "normal",
    "profileunderline": "none",
    "profileitalic": "normal",
    "profilefontcolor": "#fff",
    "profilefontfamily": "Arial",
    "profilebackgroundcolour": "#2c3c49"
}

The PHP file which receives the JSON looks as follows:

Code: [Select]
<?php

$myjson = $_POST['jsonstring'];
$jsonchars = json_decode(($myjson), true);

$json_errors = array(
    JSON_ERROR_NONE => 'No error has occurred',
    JSON_ERROR_DEPTH => 'The maximum stack depth has been exceeded',
    JSON_ERROR_CTRL_CHAR => 'Control character error, possibly incorrectly encoded',
    JSON_ERROR_SYNTAX => 'Syntax error',
);
 echo 'Last error : ', $json_errors[json_last_error()], PHP_EOL, PHP_EOL;

// do something with the data

?>

The problem is the PHP constantly returns the following error: Last error : Syntax error and any attempts to access the JSON data returns NULL values. I have looked at several online examples and cannot see where I might be going wrong, and although users have had problems with syntax errors it normally relates to incorrect JSON.

Any advice on this topic would be greatly appeciated

Leanne

This topic has been moved to PHP Regex.

http://www.phpfreaks.com/forums/index.php?topic=315198.0

Hi,

This is an odd one (to me anyway). I've created an API for deploying gig information in either XML or JSON format.

It works fine as it is, both formats of output giving me what I want.

When I take out the mySQL login information and replace it with "include 'connect_include.php';" (where the file connect_include.php contains the exact login code that I replced).
The JSON output works fine but the XML output doesn't run.

What could be causing this??


JSON API: -http://www.api.the-guards.co.uk/jsontest.php?apikey=123456&format=json

XML API: - http://www.api.the-guards.co.uk/jsontest.php?apikey=123456&format=xml


API code:
Code: [Select]
<?php
if (($_GET['apikey'])==123456 and isset($_GET['format'])){

//Set variables
$format = strtolower($_GET['format']);

//Connect to the Database
 include 'connect_include.php';

//Run our query
$result = mysql_query('
SELECT * 
FROM tg_gig_list 
where
gl_date >= curdate()
and gl_publish = 1
order by gl_date

 ');

//Preapre our output
if($format == 'json') {

 while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{ 
$array[] = array($row['gl_date']=>($row['gl_venue'].', '.$row['gl_city']));
};


$output = json_encode($array);




//Output the output.
echo $output;
header('Content-type: application/json');

}

elseif($format == 'xml') {
header('Content-type: text/xml');
$output  = "<?xml version=\"1.0\"?>\n";
$output .= "<tggiglist>\n";

for($i = 0 ; $i < mysql_num_rows($result) ; $i++){
$row = mysql_fetch_assoc($result);
$output .= "<giglist> \n";
$output .= "<giglist_date>" . $row['gl_date'] . "</giglist_date> \n";
$output .= "<giglist_venue>" . $row['gl_venue'] . "</giglist_venue> \n";
$output .= "<giglist_city>" . $row['gl_city'] . "</giglist_city> \n";
$output .= "</giglist> \n";
}

$output .= "</tggiglist>";

}


}
else {
die ('no way');
}

//output the output
echo $output;
?>
Contents of 'connect_include.php:

Code: [Select]
<?php 
 
mysql_connect("database.lcn.com", "dbname", "password") or die(mysql_error()); 
mysql_select_db("db") or die(mysql_error()); 
 ?>
 .....this is the same code that I replaced.

Obviously, the 'connect' works ok as the JSON version runs fine. There's something being added to the scripting when I use the include file that isn't there when the code goes straight in.....I think????

I have a simple query and need to print the result.
My code does not print a resulr but the query is valid and returns a result.
Any help appreciated.
Code: [Select]
$query = ("SELECT * FROM wp_book WHERE Author_ID = '$Author_ID'");

            $result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
            echo mysql_error();
            if (!$result) {
                echo "<br />";
                echo"No Result";
            }
            echo"Result";
           

            while($row = mysql_fetch_assoc($result)){
           
            echo $row['WorkingTitle'];

            }

Hi...

I create a payroll system and now I have an issue or problem in saving data before I save data using save button now i want to save data when  clicking the employee name at the navigalist list from the left side.


Here is my code for the searching and displaying employee name at navigation list:


Here is my code for search.php:

Code: [Select]
<?php

session_start();
include 'config.php';

$queryString = $_GET["query"];

if ($queryString == "" || $queryString == null) {

$sql = "SELECT EMP_ID, CONCAT(LNAME, ', ',  FNAME, ' ', MI, '.') AS FULLNAME FROM PERSONAL 
        ORDER BY FULLNAME ASC";
}
else {

$sql = "SELECT EMP_ID, CONCAT(LNAME, ', ',  FNAME, ' ', MI, '.') AS FULLNAME FROM PERSONAL WHERE CONCAT(LNAME, ', ',  FNAME, ' ', MI, '.')  LIKE '" . $queryString . "%' ORDER BY FULLNAME ASC";

}

$recPersonalQuery = $conn->Execute($sql);
if (!$recPersonalQuery->BOF) {
    $recPersonalQuery->MoveFirst();
}

echo "<hr />";
echo "<ul>";
while (!$recPersonalQuery->EOF) {
    $empID   = $recPersonalQuery->fields["EMP_ID"]; 
    $empFullName = $recPersonalQuery->fields["FULLNAME"];

  echo "<li onclick=changeEmployeePay('$empID'); >$empFullName</li>";
  //echo "<li onkeyup=changeEmployeePay('$empID'); >$empFullName</li>";
    echo "<hr />";
    $recPersonalQuery->MoveNext();
} 
echo "</ul>";

$recPersonalQuery->Close();
exit();    
?>


here is the javascript code and the template for displaying employee name list:
Code: [Select]
<html>
<head>
<title></title>
<script>
function searchemppay(queryString) {
    var ajaxRequest = remoteRequestObject();
    ajaxRequest.onreadystatechange = function() {
        if (ajaxRequest.readyState == 4 && ajaxRequest.status == 200) {
            var result = ajaxRequest.responseText;
         
            document.getElementById('searchpayroll').innerHTML = result;
        }
    }

    var url = "search.php?query=" + queryString;
    ajaxRequest.open("GET", url, true);
    ajaxRequest.setRequestHeader("If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT");
    ajaxRequest.send(null);

}

function changeEmployeePay(queryID) {
 
window.location = "SearchData.php?queryEmpID=" + queryID;
}
</script>

</head>
<body>
<div id="Search">
<form>
<p class="serif"><b>Search Lastname:</b></p>
<input type="text" name="search_" size="20" onkeyup="searchemppay(this.value);"> 
<div id="searchpayroll" style="overflow:auto; height:390px; width:auto; margin-left:2px" >
<hr />
<ul>
{section name=co_emp loop=$personalAll}
<li onclick="changeEmployeePay('{$personalAll[co_emp].EMP_ID}')">{$personalAll[co_emp].FULLNAME}</li>
<!--<li onkeyup="changeEmployeePay('{$personalAll[co_emp].EMP_ID}')">{$personalAll[co_emp].FULLNAME}</li>-->
<hr />
{sectionelse}
<li>No records found</li>
{/section}
</ul>
</div>
</div>

</body>
</html>

 and here is the code for displaying employee data
Code: [Select]
<?php
include 'config.php';
$currentEmpID = $_SESSION['empID']; 
 
$sql = "SELECT EMP_ID, CONCAT(LNAME, ', ' , FNAME, ' ', MI) AS FULLNAME FROM PERSONAL ORDER BY LNAME ASC";
$recPersonalNav = $conn->GetAll($sql);
$smarty->assign('personalAll', $recPersonalNav);

$EMP_NO = $_POST['EMP_NO'];

$sql = "SELECT em.EMP_NO, p.EMP_ID, CONCAT(LNAME, ', ',  FNAME, ' ', MI, '.') AS FULLNAME FROM PERSONAL p, EMPLOYMENT em  WHERE p.EMP_ID='$currentEmpID' AND em.EMP_ID = '$currentEmpID'";      
  
$recPersonalHead = $conn->Execute($sql);
$fullName = $recPersonalHead->fields["FULLNAME"];
$empno = $recPersonalHead->fields["EMP_NO"];

$smarty->assign('empid', $currentEmpID);
$smarty->assign('fullname', $fullName);
$smarty->assign('empno', $empno);

 $sql = "SELECT EMP_ID, RATE FROM wage WHERE EMP_ID = '$currentEmpID'";
 $rsWage = $conn->Execute($sql);
 
 $Rate      = $rsWage->fields['RATE'];    
 

 $sql = "SELECT EMP_ID,EMP_NO, STATUS FROM employment WHERE EMP_ID = '$currentEmpID'";
 $rsStatus = $conn->Execute($sql);
 
 $STATUS = $rsStatus->fields['STATUS'];
 $EMP_ID = $rsStatus->fields['EMP_ID'];


$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours, o.OT_Hours 
FROM $ADODB_DB.employment AS em 
INNER JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
LEFT JOIN $PAYROLL.regular_sum_hours AS r ON em.EMP_NO = r.EMP_NO
LEFT JOIN $PAYROLL.ot_data AS o ON r.EMP_NO = o.EMP_NO
WHERE  em.EMP_ID = '$currentEmpID' AND o.STATUS = 'Offset'";
 
  $RsEarnings = $conn2->Execute($sql); 
  
  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Hours'];
  $Offset = $RsEarnings->fields['OT_Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
  $Hours = ($Hours + $Offset);
  
 $Amount = $_POST["Amount"];
 
 $Amount = round(($Hours/8)* $Rate, 2); 

  $smarty->assign('Rate', $Rate);
  $smarty->assign('Hours', $Hours);
  $smarty->assign('Amount', $Amount);



$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours, o.STATUS FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'RegularOvertime' AND o.STATUS = 'Overtime' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$OTReg_Hours = $rsOTData->fields['OT_Hours'];
$OTReg_Hours = round($OTReg_Hours, 2);

$OTReg_Amt = round((($Rate / 8 * 1.3) * $OTReg_Hours), 2);

$smarty->assign('OTReg_Hours', $OTReg_Hours);
$smarty->assign('OTReg_Amt', $OTReg_Amt);



$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em  WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'SundayRegular' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$SunReg_Hours = $rsOTData->fields['OT_Hours'];
$SunReg_Hours = round($SunReg_Hours, 2);

$SunReg_Amt = round((($Rate / 8 * 1.35) * $SunReg_Hours), 2); 

$smarty->assign('SunReg_Hours', $SunReg_Hours);
$smarty->assign('SunReg_Amt', $SunReg_Amt); 


$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'SundayOvertime' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$OTSun_Hours = $rsOTData->fields['OT_Hours'];
$OTSun_Hours = round($OTSun_Hours, 2);

$OTSun_Amt = round((($Rate / 8 * 1.35 * 1.35) * $OTSun_Hours), 2);

$smarty->assign('OTSun_Hours', $OTSun_Hours);
$smarty->assign('OTSun_Amt', $OTSun_Amt); 


$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'HolidayRegular' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$HolReg_Hours = $rsOTData->fields['OT_Hours'];
$HolReg_Hours = round($HolReg_Hours, 2);

$HolReg_Amt = round((($Rate / 8 * 1.5) * $HolReg_Hours), 2);

$smarty->assign('HolReg_Hours', $HolReg_Hours);
$smarty->assign('HolReg_Amt', $HolReg_Amt); 

$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'HolidayRegularOvertime' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$HolRegOT_Hours = $rsOTData->fields['OT_Hours'];
$HolRegOT_Hours = round($HolRegOT_Hours, 2);

$HolRegOT_Amt = round((($Rate / 8 * 2.05 * 1.35) * $HolRegOT_Hours), 2);

$smarty->assign('HolRegOT_Hours', $HolRegOT_Hours);
$smarty->assign('HolRegOT_Amt', $HolRegOT_Amt); 


$sql = "SELECT em.EMP_NO, SUM(l.HOURS_LEAVE) AS HOURS_LEAVE FROM $PAYROLL.leave_data l, $ADODB_DB.employment em WHERE em.EMP_NO = l.EMP_NO AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);


$HolLeave_Hours = $rsOTData->fields['HOURS_LEAVE'];

$HolLeave_Hours = round($HolLeave_Hours, 2);

$HolLeave_Amt = round((($Rate) * $HolLeave_Hours), 2);

$smarty->assign('HolLeave_Hours', $HolLeave_Hours);
$smarty->assign('HolLeave_Amt', $HolLeave_Amt); 


$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'NightPremiumRegular' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$NPReg_Hours = $rsOTData->fields['OT_Hours'];
$NPReg_Hours = round($NPReg_Hours, 2);

$NPReg_Amt = round((($Rate / 8 * 0.15) * $NPReg_Hours), 2);


$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'NightPremiumSunday' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$NPSun_Hours = $rsOTData->fields['OT_Hours'];
$NPSun_Hours = round($NPSun_Hours, 2);

$NPSun_Amt = round((($Rate / 8 * 1.35 * 0.15) * $NPSun_Hours), 2);


$sql = "SELECT em.EMP_NO, o.OT_Category, SUM(OT_Hours) AS OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category = 'NightPremiumHoliday' AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$NPHol_Hours = $rsOTData->fields['OT_Hours'];
$NPHol_Hours = round($NPHol_Hours, 2);

$NPHol_Amt = round((($Rate / 8 * 2.05 * 0.15) * $NPHol_Hours), 2);


$NP_Hours = round(($NPReg_Hours + $NPSun_Hours + $NPHol_Hours), 2);
$NP_Hours = round($NP_Hours, 2);

$NP_Amt =  round(($NPReg_Amt + $NPSun_Amt + $NPHol_Amt), 2); 

$smarty->assign('NP_Hours', $NP_Hours);
$smarty->assign('NP_Amt', $NP_Amt);

$sql = "SELECT COUNT(o.EMP_NO) AS EMP_NO, o.OT_Category, o.OT_Hours, o.STATUS FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category IN ('RegularOvertime', 'HolidayRegular', 'HolidayRegularOvertime') AND o.OT_Hours > 2  AND em.EMP_ID = '$currentEmpID' AND o.STATUS = 'Overtime' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);
$MealReg_Hours = $rsOTData->fields['EMP_NO'];
$MealReg_Hours = round($MealReg_Hours, 2);

$MealReg_Amt = round(($MealReg_Hours * 23), 2);

$sql = "SELECT COUNT(o.EMP_NO) AS EMP_NO, o.OT_Category, o.OT_Hours FROM $PAYROLL.ot_data o, $ADODB_DB.employment em WHERE em.EMP_NO = o.EMP_NO AND o.OT_Category IN ('SundayRegular', 'SundayOvertime') AND o.OT_Hours > 2 AND em.EMP_ID = '$currentEmpID' GROUP BY em.EMP_NO";
$rsOTData = $conn2->Execute($sql);

$MealSun_Hours = $rsOTData->fields['EMP_NO'];
$MealSun_Hours = round($MealSun_Hours, 2);


$MealSun_Amt = round(($MealSun_Hours * 30), 2);

$Meal_Hours = round(($MealReg_Hours + $MealSun_Hours), 2);

$Meal_Amt = round(($MealReg_Amt + $MealSun_Amt), 2);
$smarty->assign('Meal_Hours', $Meal_Hours);
$smarty->assign('Meal_Amt', $Meal_Amt);


$sql = "SELECT COUNT(a.EMP_NO) AS EMP_NO, w.RATE FROM $ADODB_DB.wage w, $ADODB.employment em, $PAYROLL.attendance a WHERE em.EMP_NO = a.EMP_NO AND w.RATE = 302 AND em.EMP_ID = '$currentEmpID' GROUP BY w.RATE";
$rsOTData = $conn2->Execute($sql);

$Cola_Hours = $rsOTData->fields['EMP_NO'];
$Cola_Hours = round($Cola_Hours, 2);


$Cola_Amt = round(($Cola_Hours * 28), 2);
      
$smarty->assign('Cola_Hours', $Cola_Hours);
$smarty->assign('Cola_Amt', $Cola_Amt);

$TotEarn = $_POST['TotEarn'];

$TotEarn = round(($Amount + $OTReg_Amt + $SunReg_Amt + $OTSun_Amt + $HolReg_Amt + $HolRegOT_Amt + $HolLeave_Amt + $NP_Amt + $Meal_Amt + $Cola_Amt), 2);

$smarty->assign('TotEarn', $TotEarn);


$HDMF = $_POST['HDMF'];

$sql = "SELECT Ref_No, Range FROM $PAYROLL.hdmf, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
$rs = $conn2->Execute($sql);

$Range = $rs->fields['Range'];
 
if ($TotEarn <= $Range) {
    $HDMF = round(($TotEarn * 0.01), 2);
} else {
    $HDMF = round(($TotEarn * 0.02), 2);
}  

$smarty->assign('HDMF', $HDMF);


$SSS = $_POST['SSS'];

$sql = "SELECT Ref_No, From_Range, To_Range, Employee_Share FROM $PAYROLL.sss, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID' AND $TotEarn BETWEEN From_Range AND To_Range";
$rs = $conn2->Execute($sql);

$SSS = $rs->fields['Employee_Share'];

$smarty->assign('SSS', $SSS);     



$PCHL = $_POST['PCHL'];

$sql = "SELECT Ref_No, From_Range, To_Range, Employee_Share FROM $PAYROLL.pchl, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID' AND $TotEarn BETWEEN From_Range AND To_Range";
$rs = $conn2->Execute($sql);

$PCHL = $rs->fields['Employee_Share'];

$smarty->assign('PCHL', $PCHL);

$TAX = $_POST['TAX'];

$sql = "SELECT EMP_ID, EMP_NO, W4_STATUS, DEPENDENTS FROM employment  WHERE EMP_ID = '$currentEmpID'";
$rsTax = $conn->Execute($sql);

$W4_STATUS = $rsTax->fields['W4_STATUS'];
$DEPENDENTS = $rsTax->fields['DEPENDENTS'];

if($W4_STATUS == 1 AND $DEPENDENTS == 0 AND $TotEarn >= 0 AND $TotEarn <= 2083){
  $TAX = round($TotEarn * .05);
}
elseif($W4_STATUS == 2 AND $DEPENDENTS == 0 AND $TotEarn >= 0 AND $TotEarn <= 2083){
  $TAX = round($TotEarn * .05);
  
}
elseif($W4_STATUS == 1 AND $DEPENDENTS == 0 AND $TotEarn >= 2083 AND $TotEarn <= 2500) {
  $TAX = round($TotEarn - 2083);
  $TAX = round(20.83 + ($TAX * .10));
}
elseif($W4_STATUS == 2 AND $DEPENDENTS == 0 AND $TotEarn >= 2083 AND $TotEarn <= 2500) {
  $TAX = round($TotEarn - 2083);
  $TAX = round(20.83 + ($TAX * .10));
}
elseif($W4_STATUS == 1 AND $DEPENDENTS == 0 AND $TotEarn >= 2500 AND $TotEarn <= 3333) {
  $TAX = round($TotEarn - 2500);
  $TAX = round(104.17 + ($TAX * .15));
} 
else{
    $TAX = round(0);
}

$smarty->assign('TAX', $TAX);


 $sql = "SELECT s.EMP_NO, s.SSSAmor FROM $PAYROLL.sssloan s, $ADODB_DB.employment em WHERE em.EMP_NO = s.EMP_NO AND em.EMP_ID = '$currentEmpID'";
 $RsDed = $conn2->Execute($sql);
 
 $SSSAmor = round($RsDed->fields['SSSAmor']);
 
$sql = "SELECT h.EMP_NO, h.HDMFAmor FROM $PAYROLL.hdmfloan h, $ADODB_DB.employment em WHERE em.EMP_NO = h.EMP_NO AND em.EMP_ID = '$currentEmpID'"; 
 $RsHDMF = $conn2->Execute($sql);
 
 $HDMFAmor = round($RsHDMF->fields['HDMFAmor']);
 
 
$sql = "SELECT u.EMP_NO, u.UDTAmor FROM $PAYROLL.udtloan u, $ADODB_DB.employment em WHERE em.EMP_NO = u.EMP_NO AND em.EMP_ID = '$currentEmpID'";
 $RsUDT = $conn2->Execute($sql);
 
 $UDTAmor = round($RsUDT->fields['UDTAmor']);

$TotalDed = $_POST['TotalDed'];

 $sql = "SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther FROM $PAYROLL.other_deductions o, $ADODB_DB.personal p, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID' AND em.EMP_NO = o.EMP_NO";
 $rsOtherDed = $conn2->Execute($sql);
 
 $BurialSep = round($rsOtherDed->fields['BurialSeparationCont']);
 $TaxAjt = round($rsOtherDed->fields['TaxAjt']);
 $CashAdvance = round($rsOtherDed->fields['CashAdvance']);
 $AdvancesShirt = round($rsOtherDed->fields['AdvanceShirt']);
 $AdvancesMed = round($rsOtherDed->fields['AdvanceMed']);
 $AdvancesOthers = round($rsOtherDed->fields['AdvanceOther']);
 
 $smarty->assign('BurialSep', $BurialSep);
 $smarty->assign('TaxAjt', $TaxAjt);
 $smarty->assign('CashAdvance', $CashAdvance);
 $smarty->assign('AdvancesShirt', $AdvancesShirt);
 $smarty->assign('AdvancesMed', $AdvancesMed);
 $smarty->assign('AdvancesOthers', $AdvancesOthers);
 

$TotalDed = round(($SSS + $HDMF + $PCHL + $TAX + $SSSAmor + $HDMFAmor + $UDTAmor + $BurialSep + $TaxAjt + $CashAdvance + $AdvancesShirt + $AdvancesMed + $AdvancesOthers), 2); 

 $smarty->assign('SSSAmor', $SSSAmor);
 $smarty->assign('HDMFAmor', $HDMFAmor);
 $smarty->assign('UDTAmor', $UDTAmor);
 $smarty->assign('TotalDed', $TotalDed);
 
$THP = $_POST["TakeHomePay"];

$THP = round($TotEarn - $TotalDed, 2);

$smarty->assign('THP', $THP);

 
 $sql = "SELECT EMP_NO, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt FROM other_earnings WHERE EMP_NO = '$empno'";
 $RsOtherEarnings = $conn2->Execute($sql);
 
 $numrowsOtherEarnings = $RsOtherEarnings->RecordCount();
 
 if($numrowsOtherEarnings > 0){ 
 
 $saverec['EMP_NO'] = $empno;
 $saverec['OTReg_Amt'] = $OTReg_Amt;
 $saverec['SunReg_Amt'] = $SunReg_Amt;
 $saverec['OTSun_Amt'] = $OTSun_Amt;
 $saverec['HolReg_Amt'] = $HolReg_Amt;
 $saverec['HolRegOT_Amt'] = $HolRegOT_Amt;
 $saverec['HolLeave_Amt'] = $HolLeaveAmt;
 $saverec['NP_Amt'] = $NP_Amt;
 $saverec['Meal_Amt'] = $Meal_Amt;
 $saverec['Cola_Amt'] = $Cola_Amt;
 
  $updateOtherEarnings = $conn2->GetUpdateSQL($RsOtherEarnings, $saverec); 
  $conn2->Execute($updateOtherEarnings); 
 }
 else{
 $sql = "SELECT o.EMP_NO, o.OTReg_Amt, o.SunReg_Amt, o.OTSun_Amt, o.HolReg_Amt, o.HolRegOT_Amt, o.HolLeave_Amt, o.NP_Amt, o.Meal_Amt, o.Cola_Amt FROM $PAYROLL.other_earnings o, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
 $RsOtherEarnings = $conn2->Execute($sql);
 
$saverec['EMP_NO'] = $empno;
 $saverec['OTReg_Amt'] = $OTReg_Amt;
 $saverec['SunReg_Amt'] = $SunReg_Amt;
 $saverec['OTSun_Amt'] = $OTSun_Amt;
 $saverec['HolReg_Amt'] = $HolReg_Amt;
 $saverec['HolRegOT_Amt'] = $HolRegOT_Amt;
 $saverec['HolLeave_Amt'] = $HolLeaveAmt;
 $saverec['NP_Amt'] = $NP_Amt;
 $saverec['Meal_Amt'] = $Meal_Amt;
 $saverec['Cola_Amt'] = $Cola_Amt;
 
  $insertOtherEarnings = $conn2->GetInsertSQL($RsOtherEarnings, $saverec); 
  $conn2->Execute($insertOtherEarnings); 
 }

 
 $sql = "SELECT EMP_NO, SSS, TAX, PCHL, HDMF FROM deductions WHERE EMP_NO = '$empno'";
 $RsDeduction = $conn2->Execute($sql);
 
 $numrowsDeduction = $RsDeduction->RecordCount(); 
 
 if($numrowsDeduction > 0){
 $saverec['EMP_NO'] = $empno;
 $saverec['SSS'] = $SSS;
 $saverec['TAX'] = $TAX;
 $saverec['PCHL'] = $PCHL;
 $saverec['HDMF'] = $HDMF;
 
 
 $updateDeductionSQL = $conn2->GetUpdateSQL($RsDeduction, $saverec); 
 $conn2->Execute($updateDeductionSQL); 
 }
 else{
  $sql = "SELECT d.EMP_NO, d.SSS, d.TAX, d.PCHL, d.HDMF FROM $PAYROLL.deductions d, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
 $RsDeduction = $conn2->Execute($sql);
 
 $saverec['EMP_NO'] = $empno;
 $saverec['SSS'] = $SSS;
 $saverec['TAX'] = $TAX;  
 $saverec['PCHL'] = $PCHL;
 $saverec['HDMF'] = $HDMF;
 
 $insertSQL = $conn2->GetInsertSQL($RsDeduction, $saverec); 
 $conn2->Execute($insertSQL); 
 }
 
 
 $sql = "SELECT EMP_NO, SSSLoan, HDMFLoan, UDTLoan FROM loan_deductions WHERE EMP_NO = '$empno'";
 $RsLoan = $conn2->Execute($sql);
 
 $numrowsRsLoan = $RsLoan->RecordCount();
 
 if($numrowsRsLoan > 0){
 $saverec['EMP_NO'] = $empno;
 $saverec['SSSLoan'] = $SSSAmor;
 $saverec['HDMFLoan'] = $HDMFAmmor;
 $saverec['UDTLoan'] = $UDTAmor;
 
 $updateLoanSQL = $conn2->GetUpdateSQL($RsLoan, $saverec); 
 $conn2->Execute($updateLoanSQL); 
 }
 else{
 
 $sql = "SELECT l.EMP_NO, l.SSSLoan, l.HDMFLoan, l.UDTLoan FROM $PAYROLL.loan_deductions l, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
 $RsLoan = $conn2->Execute($sql);
 
 $saverec['EMP_NO'] = $empno;
 $saverec['SSSLoan'] = $SSSAmor;
 $saverec['HDMFLoan'] = $HDMFAmor;
 $saverec['UDTLoan'] = $UDTAmor;
 
 $insertSQL = $conn2->GetInsertSQL($RsLoan, $saverec); 
 $conn2->Execute($insertSQL); 
 }

   $sql = "SELECT EMP_NO, Amount, TotalEarnings, TotalDeductions, TakeHomePay FROM totalpay WHERE EMP_NO = '$empno'";
  $rsTotal = $conn2->Execute($sql);
  
  $numrows = $rsTotal->RecordCount();
  
  if($numrows > 0){
  
  $saverec['EMP_NO'] = $empno;
  $saverec['Amount'] = $Amount;
  $saverec['TotalEarnings'] = $TotEarn;
  $saverec['TotalDeductions'] = $TotalDed;
  $saverec['TakeHomePay'] = $THP;
  
  $updateSQL = $conn2->GetUpdateSQL($rsTotal, $saverec); 
  $conn2->Execute($updateSQL);  
  }
  else{ 
  $sql = "SELECT t.EMP_NO, t.Amount, t.TotalEarnings, t.TotalDeductions, t.TakeHomePay FROM $PAYROLL.totalpay t, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
  $rsTotal = $conn2->Execute($sql);
  
  $saverec['EMP_NO'] = $empno;
  $saverec['Amount'] = $Amount;
  $saverec['TotalEarnings'] = $TotEarn;
  $saverec['TotalDeductions'] = $TotalDed;
  $saverec['TakeHomePay'] = $THP;
  
  $insertSQL = $conn2->GetInsertSQL($rsTotal, $saverec); 
  $conn2->Execute($insertSQL);  
  } 
$smarty->display('header.tpl');
$smarty->display('left.tpl');
$smarty->display('empPayrollData.tpl');
$smarty->display('footer.tpl'); 

?>

I have a problem in saving data from other deductions and the total deductions and takehomepay, because other deductions is insertable textfield and when I add other deductions the total deductions will change also the Take Home Pay. Idon't know where Can I add this code for saving data of other deductions and updating the changes if Total Deductions and Take Home Pay.

Code: [Select]
$sql = "SELECT EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther FROM other_deductions WHERE EMP_NO = '$empno'";
  $rsOtherDed = $conn2->Execute($sql);
 
  $numrows1 = $rsOtherDed->RecordCount();
 
  if($numrows1 > 0){ 
  $saverec['EMP_NO'] = $empno;
  $saverec['BurialSeparationCont'] = $BurialSep;
  $saverec['TaxAjt'] = $TaxAjt;
  $saverec['CashAdvance'] = $CashAdvance;
  $saverec['AdvanceShirt'] = $AdvancesShirt;
  $saverec['AdvanceMed'] = $AdvancesMed;
  $saverec['AdvanceOther'] = $AdvancesOthers;

  $updateSQL = $conn2->GetUpdateSQL($rsOtherDed, $saverec);

  $conn2->Execute($updateSQL);

 
  }
  else{
  $sql = "SELECT o.EMP_NO, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther FROM $PAYROLL.other_deductions o, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
  $rsOtherDed = $conn2->Execute($sql);
 
  $saverec['EMP_NO'] = $empno;
  $saverec['BurialSeparationCont'] = $BurialSep;
  $saverec['TaxAjt'] = $TaxAjt;
  $saverec['CashAdvance'] = $CashAdvance;
  $saverec['AdvanceShirt'] = $AdvancesShirt;
  $saverec['AdvanceMed'] = $AdvancesMed;
  $saverec['AdvanceOther'] = $AdvancesOthers;

  $insert = $conn2->GetInsertSQL($rsOtherDed, $saverec);
  $conn2->Execute($insert);
 
 }
   
   $Amount = $_POST["Amount"];
   $TotEarn = $_POST["TotEarn"];
   $TotalDed = $_POST["TotalDed"];
   $THP = $_POST["TakeHomePay"];
 
   $sql = "SELECT EMP_NO, Amount, TotalEarnings, TotalDeductions, TakeHomePay FROM totalpay WHERE EMP_NO = '$empno'";
  $rsTotal = $conn2->Execute($sql);
 
  $numrows = $rsTotal->RecordCount();
 
  if($numrows > 0){
 
  $saverec['EMP_NO'] = $empno;
  $saverec['Amount'] = $Amount;
  $saverec['TotalEarnings'] = $TotEarn;
  $saverec['TotalDeductions'] = $TotalDed;
  $saverec['TakeHomePay'] = $THP;
 
  $updateSQL = $conn2->GetUpdateSQL($rsTotal, $saverec);
  $conn2->Execute($updateSQL); 
  }
  else{
  $sql = "SELECT t.EMP_NO, t.Amount, t.TotalEarnings, t.TotalDeductions, t.TakeHomePay FROM $PAYROLL.totalpay t, $ADODB_DB.employment em WHERE em.EMP_ID = '$currentEmpID'";
  $rsTotal = $conn2->Execute($sql);
 
  $saverec['EMP_NO'] = $empno;
  $saverec['Amount'] = $Amount;
  $saverec['TotalEarnings'] = $TotEarn;
  $saverec['TotalDeductions'] = $TotalDed;
  $saverec['TakeHomePay'] = $THP;
 
  $insertSQL = $conn2->GetInsertSQL($rsTotal, $saverec);
  $conn2->Execute($insertSQL); 
  }

I don't know where I can put this code to save the data that i inserted in other deductions

Hi, i have a transaction table which includes following fields.
 
    tran_ID,account_number,account_type,transaction_type,amount,Date

I want to know how these data can be retrieved and display in another table. The table is filled with several records.

Thanks,
Heshan