asked on

php and ajax on same page to extract information from the database?

Greetings mates,

Normally, the issue I am posting for is not a difficult task to tackle.

However, this issue has a twist and I am hoping someone can lead me to a solution.

I have a login page that makes an AJAX call to an API to authentic user credentials.

If the supplied credentials are valid, username, a token, as well as validation timestamp is returned to the user is redirected to another page.

This works great.

Here is the problem. The API as stated only returns username of the authenticated user, his/her assigned token and timestamp for what time authentication occurred.

It does not return anything else and WE do *not* have the ability to change what the API returns.

So, the users of the app are asking us that whenever a user is authenticated, that we find a way to retrieve his or her DeptID.

Does anyone know how I can integrate PHP code and the AJAX to retrieve the deptId?

Code below is my attempt to integrate php and jax but I am getting an error that
"An invalid parameter was passed to SQL_Srv query"

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);

session_start();

// Connect to SQL Server database
include("../../connections/Connect.php");

$upass = $_GET['upass'];
// Construct query

$tsql =
"
SELECT
    ISNULL([ORGANIZATION],'')

FROM
 [EMPLOYEE]
WHERE Lower([LOGINNAME]) = lower('$uname')
";

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));
}
$results = array();
// Retrieve and display the results of the query
//$lastFeatType = "";
while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)) {
    array_push($results,$row);
}

 echo json_encode($results);

// Free statement and connection resources
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
</head>
<body>
  <h1>
    FTS Service Requests
</h1>
  <div class="action-container" style="display:none;"></div>


   <div id="tabs">
  <ul>
<div id="dialog" title="Basic dialog">
  <p style="font-size:14pt;">User Log In</p>
</div>
  </ul>
<p>
</p>
<br clear="all" />
<div>
     <br />   <br />
      <br />
       <?php
        //Start session
         session_start();
         header("Cache-Control: no-store, must-revalidate, max-age=0");
         header("Pragma: no-cache");
         header("Expires: Thu, 19 Nov 1981 08:52:00 GMT");
         if(!empty($_GET['status'])){
                echo '<div align=center><font color=firebrick>You have been logged out!</font><br><br>Log in again or close browser.</div>';
         }
       ?>
       <br />
        <br />
      <br />
      <form id="FormToValidate">
        <table>
         <tr>

           <td nowrap>
                   <div class="input text">
              <label><strong>UserName:</strong></label>
              <input  maxlength="40" class="required" name="user" id="user" size="20" placeholder="Enter username!" type="text" title="Please enter a username." tabindex="2" value="" style="width:400px;color:#000;font-size:10pt;height:20px;" />
            </div>
          </td>
            </tr>
            <tr>
              <td nowrap>
               <div class="input text">
                <label><strong>Password:</strong></label>
                <input  maxlength="40" class="required" name="pass" id="pass" size="20" placeholder="Enter password!" type="password" tabindex="3" title="Please enter a password." value="" style="width:400px;color:#000;font-size:10pt;height:20px;" />
               </div>
              </td>

         </tr>

         <tr>
            <td></td>
              <td>
              <div class="buttonSubmit">
              <input type="button" id="btnValidate" style="width:80px; margin-left:-152px;background-color:#fff;" value="Log In" />
             </div><br clear="all"/>
        </td>
      </tr>
    </table>

  </form>
</div>

<script type="text/javascript">

$("#btnValidate").click(function() {

// Creating variables to hold data from textboxes

var uname = $("#user").val();
var upass = $("#pass").val();

$.post("proxyCreate.php",
  { data: JSON.stringify({ LoginName: uname,Password: upass }) })
    .done(function(data) {
        var result = JSON.parse(data);
        switch(result.Status) {
            case 0:
                //login successful
                tokenVal = result.Value.Token;
                location.href = "http://Accounts/accounts.php?token="+tokenVal+ "&user=" + uname;
                break;

            case 2:
                //invalid login
                alert(result.Message);
                break;
        }
    })
    .fail(function() {
        alert("The AJAX request failed!");
    });
  });
</body>
</html>

Open in new window

Any assistance is greatly appreciated

Gary

And where is DeptID coming from?

sammySeltzer

ASKER

Hi Gary,

DeptID is actually Organization and coming from the Employee table as shown in the query.

What I really would like to do is select the Organization (DeptId) from employee table where loginName matches the login name that the user entered on the username textbox.

Gary

Do you want this to happen at the same time?

sammySeltzer

ASKER

Yes, Gary - thanks.

This way, I can pass the value of organization along with these querystring values to another to page:

location.href = "http://Accounts/accounts.php?token="+tokenVal+ "&user=" + uname;

so once I get that value of organization based on username, then I will have this:

location.href = "http://Accounts/accounts.php?token="+tokenVal+ "&user=" + uname+ "&org=" + orgcode {or something like that};

Thanks alot

Gary

Then move your code to do the select into a page by itself.
In your case 0 add some ajax code to call that page passing the username, then the result back is deptid.

sammySeltzer

ASKER

Ok, Gary, please bear with me because I am not getting it.

The code I posted here is for login page.

So, if I move the code into another page by itself, how do I it in such that it doesn't interface with the login stuff?

This may sound elementary but I am a bit confused.

For instance, here is the code in a page by itself. So, what I am missing here that I need to have?

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);

session_start();

// Connect to SQL Server database
include("../../connections/Connect.php");

$loginName = $_GET['uname'];
// Construct query

$tsql =
"
SELECT
	ISNULL([ORGANIZATION],''),
	LOGINNAME

FROM
 [EMPLOYEE] 
WHERE LOGINNAME = lower('$loginName')
";

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));
}
$results = array();
// Retrieve and display the results of the query
//$lastFeatType = "";
while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)) {
	array_push($results,$row);
}

 echo json_encode($results);

// Free statement and connection resources
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Open in new window

This is what I had attempted before but was getting same error I posted initially.

Gary

Change the script above to return either the deptID if successful else nothing, as plain text (not json) - assume the page is called getID.php

            case 0:
                //login successful
                tokenVal = result.Value.Token;
		$.get("getID.php?user=" + uname).done(function(result) {
                	location.href = "http://Accounts/accounts.php?token="+tokenVal+ "&user=" + uname +"&deptId="+result;
		})
                break;

Open in new window

sammySeltzer

ASKER

Ok, this is what I have so far:

//getDeptId.php

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);

session_start();

// Connect to SQL Server database
include("../../connections/Connect.php");

// Construct query
$tsql =
"SELECT
	ISNULL([ORGANIZATION],''),
	LOGINNAME

FROM
 [EMPLOYEE] 
";

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));
}
$results = array();

while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)) {
	array_push($results,$row);
}

// Free statement and connection resources
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Open in new window

//Login code - relevant stuff:
            case 0:
                //login successful
                tokenVal = result.Value.Token;
		$.get("getDeptId.php?user=" + uname).done(function(result) {
                	location.href = "http://Accounts/accounts.php?token="+tokenVal+ "&user=" + uname +"&deptId="+result;
		})
                break;

Open in new window

When I run it, it passes the following to deptid querystring:
Error in executing query.</br>Array( [0] => Array which is my custom error message.

I can't see anything wrong with the php code.

Gary

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);

session_start();

// Connect to SQL Server database
include("../../connections/Connect.php");

// Construct query
$tsql =
"SELECT
	ISNULL([ORGANIZATION],''),
	LOGINNAME

FROM
 [EMPLOYEE] 
";

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
    // you may want to echo an error number here and check it in the ajax response.
}
else{
    $row = sqlsrv_fetch_array($stmt);
    echo $row['ORGANIZATION'];
}
// Free statement and connection resources
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Open in new window

sammySeltzer

ASKER

So, still not working.

I don't know if php error number to echo. So, I just entered echo 'test' and that's what ajax returned.

Gary

Which line is the error on - I don't see where an array error would come from

sammySeltzer

ASKER

I am sorry Gary, in my last post I said, "I don't know *IF* php error...
I meant to say that I don't know OF any php error to put in there. So, I just echoed test just to see if it is being returned by the AJAX call.

Gary

Ohh just noticed in getDeptId.php you are just doing a select all, you are not doing the select based on the passed user name - that's why there is an array error.

sammySeltzer

ASKER

Yes, I noticed that too but I was confused as to what to pass there.

I know that the ajax is passing uname via querystring.

Is that what I need to use like:

$loginName = $_GET['uname']??

Gary

$loginName = $_GET['user']

sammySeltzer

ASKER

Man, I was so confident that was going to work but it didn't!!!

Same error.

Gary

Can you post getDeptId.php
If you call the page directly using
getDeptId.php?user=some_valid_username

do you get the correct result?

sammySeltzer

ASKER

Great question, Gary.

When I run getDeptId.php?user='james2222'

I get similar error but something more useful came out. It says incorrect syntax near 'james222'

Below is current code:

$loginName = $_GET['user'];

// Construct query

$tsql =
"
SELECT
	ISNULL([ORGANIZATION], '')

FROM
[EMPLOYEE]
 WHERE [LOGINNAME] = lower('$loginName')
";

$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));
}
$results = array();
// Retrieve and display the results of the query

while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)) {
	array_push($results,$row);
}

// Free statement and connection resources
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Open in new window

Gary

No apostrophes

getDeptId.php?user=james2222

Or is that how the API returns the username?

sammySeltzer

ASKER

Actually, this is the weirdest thing I have seen so far.

When I ran the code in SSMS, I found that user name was being sent as ''james222'' (these are single quotes, not apostrophies).

So, when removed the single quotes in loginName as in lower($'loginName').

When I removed the single quotes, that error went away but another replaced it. This is the new one:

[Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "james222" could not be bound. ))" class="changeable" />

In my experience with sql server, this would mean that you are referencing a field by the wrong table or when using aliases the wrong way.

That's not the case here.

SIGH!

Gary

Try

$tsql ="SELECT [ORGANIZATION]
FROM
[EMPLOYEE]
WHERE [LOGINNAME] = lower('$loginName')";

Open in new window

Ray Paseur

Is this using OAuth by any chance? If not, please tell us who the authentication service is and post a link to their API documentation, thanks.

sammySeltzer

ASKER

I have that already.

The error is now gone gone

I did WHERE lower([LOGINNAME]) lower('$loginName') and the error is gone but I am not seeing the value on the markup.

Am I handling the markup incorrectly?

On this page:

Remember code below?

            	location.href = "http://Accounts/accounts.php?token="+tokenVal+ "&user=" + uname +"&deptId="+result;

Open in new window

On accounts.php page, I am grabbing the values of user and deptId like this:

  $user = $_GET['user'];
  $deptId = $_GET['deptId'];

Open in new window

Then on markup, I have these:

  <input name="user" id="user" type="text"  width="300" value="<?=$user;?>"  class="changeable" />
  <input name="deptId" id="deptId" type="text"  width="300" value="<?=$deptId;?>"  class="changeable" />

Open in new window

I can see the value of user but not deptId. Am I not calling it correctly?

I am just trying to figure out why I can't see the value of Organization.

sammySeltzer

ASKER

These are all internal stuff Ray, sorry.

I would have been more than happy to do this to save Gary much time and effort which I greatly appreciate Gary.

I do think we are very close though.

One more thing Ray, per my last post, the API is returning all the other stuff passed to it.

This is just not part of what their API exposes. This is Gary's magic we are working with.

ASKER CERTIFIED SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sammySeltzer

ASKER

GARY, YOU ARE INVINCIBLE!!!!!!!

It WORKED!!!!!

Yes, you are right, I changed the code again.

That's what happens when you are confused.

THANK YOU!

Our most reverend PHP programmer who works here with me could not help after several days.

This is where 500 points is not enough.

Thank you so much.

sammySeltzer

ASKER

Incredible help, Gary.
Thanks so much

Gary

No problem :o)