Avatar of sammySeltzer
sammySeltzer
Flag for United States of America 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
PHPAJAXScripting Languages

Avatar of undefined
Last Comment
Gary

8/22/2022 - Mon
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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']
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Gary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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)
Your help has saved me hundreds of hours of internet surfing.
fblack61