Solved

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

Posted on 2014-12-04
28
255 Views
Last Modified: 2014-12-04
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
0
Comment
Question by:sammySeltzer
  • 14
  • 13
28 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40481573
And where is DeptID coming from?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40481598
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40481681
Do you want this to happen at the same time?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40481696
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
0
 
LVL 58

Expert Comment

by:Gary
ID: 40481707
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40481749
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40481763
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

0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40481820
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40481921
<?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

0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482019
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40482022
Which line is the error on - I don't see where an array error would come from
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482025
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.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40482032
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482043
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']??
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 58

Expert Comment

by:Gary
ID: 40482051
$loginName = $_GET['user']
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482055
Man, I was so confident that was going to work but it didn't!!!

Same error.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40482059
Can you post getDeptId.php
If you call the page directly using
getDeptId.php?user=some_valid_username

do you get the correct result?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482076
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

0
 
LVL 58

Expert Comment

by:Gary
ID: 40482083
No apostrophes

getDeptId.php?user=james2222

Or is that how the API returns the username?
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482092
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!
0
 
LVL 58

Expert Comment

by:Gary
ID: 40482094
Try

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

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40482102
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482108
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482110
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.
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40482114
Your code seems to have gone back to returning an array
I did amend the file to just return the deptid here though that will need amending with the new sql.

getDeptId.php should only return the department ID and nothing else.
Run the file directly in the browser with the username and if that returns the dept id then everything else should fall into place.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 40482118
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.
0
 
LVL 28

Author Closing Comment

by:sammySeltzer
ID: 40482119
Incredible help, Gary.
Thanks so much
0
 
LVL 58

Expert Comment

by:Gary
ID: 40482120
No problem :o)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now