forge104 Posted February 20, 2014 Share Posted February 20, 2014 Hello, I have successfully installed PHP onto an IIS/SQL Server environment. I can get a DB connection and I can also post and display variables from one PHP page to another. I am trying to create a login script for a web application, but I am am having trouble getting any results from any select query that I write. Here is my login.php page: <body> <?phpob_start();session_start(); $username = $_POST['username'];$password = $_POST['password']; $serverName = "localhost";$connectionInfo = array("Database"=>"Derm", "UID"=>"xxxxxxxx", "pwd"=>"xxxxxxxxx");$conn = sqlsrv_connect( $serverName, $connectionInfo); $query = "SELECT username, password, level, type, location FROM user_access WHERE username = '$username'"; $result = sqlsrv_query($conn, $query); if(sqlsrv_num_rows($result) == 0) // User not found. So, redirect to login_form again.{ header('Location: index.html');} $userData = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC); $hash = hash("sha512", $password); if($hash != $userData['password']) // Incorrect password. So, redirect to login_form again.{ header('Location: index.html');} else{ session_regenerate_id(); $_SESSION['username'] = $userData['username']; $_SESSION['level'] = $userData['level']; $_SESSION['type'] = $userData['type']; $_SESSION['location'] = $userData['location']; session_write_close(); header('Location: main.php');} ?> </body> I have checked the query many times in SQL Server management studio and it always returns the correct result using my username. However, here, the page keeps refreshing itself and never progresses to 'main.php'. Is there a way to solve this or are there any alternatives to do the same thing? I don't actually want to display the query results, rather just assign them as session variables. Thanks Quote Link to comment https://forums.phpfreaks.com/topic/286344-php-sqlsrv_query-function-does-not-seem-to-work/ Share on other sites More sharing options...
ginerjm Posted February 20, 2014 Share Posted February 20, 2014 add some echos to help identify where you get redirected back to login page. Echo your query too so you can verify it is what you think it is. Silly question, but does your index.html use a POST action to get to this script, or a GET? Also - you really need to do some filtering on your post vars to ensure that you are getting proper input and not endangering your app/db. Quote Link to comment https://forums.phpfreaks.com/topic/286344-php-sqlsrv_query-function-does-not-seem-to-work/#findComment-1469691 Share on other sites More sharing options...
forge104 Posted February 20, 2014 Author Share Posted February 20, 2014 Hi, I have used echo around the $result variable and I get "resource id#2" However when I put the same query directly into sql server manager it works. I am using a post method from the login box through to this page. Quote Link to comment https://forums.phpfreaks.com/topic/286344-php-sqlsrv_query-function-does-not-seem-to-work/#findComment-1469694 Share on other sites More sharing options...
forge104 Posted February 20, 2014 Author Share Posted February 20, 2014 Hello, I've streamlined the code to this but for some reason it is still registering as 0 results from the query even though the query works in SQL Server. Also I have the same exact code working on a test server that uses MySQL instead of SQL Server. <?php ob_start(); session_start(); $uname = $_POST['username']; $pword = $_POST['password']; $serverName = "localhost";$connectionInfo = array("Database"=>"Derm", "UID"=>"xxxxxxxx", "pwd"=>"xxxxxxxxx");$conn = sqlsrv_connect( $serverName, $connectionInfo); $query = "SELECT username, password, userlevel, usertype, location FROM user_access WHERE username = '$uname' and password='$pword'"; $result = sqlsrv_query($conn, $query); $result2 = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC); if(sqlsrv_num_rows($result) == 0) { header('Location: index.html'); }else{ session_regenerate_id(); $_SESSION['username'] = $result2['username']; $_SESSION['level'] = $result2['userlevel']; $_SESSION['type'] = $result2['usertype']; $_SESSION['location'] = $result2['location']; session_write_close(); header('Location: main.php'); } ?> </body> Quote Link to comment https://forums.phpfreaks.com/topic/286344-php-sqlsrv_query-function-does-not-seem-to-work/#findComment-1469699 Share on other sites More sharing options...
Solution kicken Posted February 20, 2014 Solution Share Posted February 20, 2014 (edited) MS's manual says: sqlsrv_num_rows requires a client-side, static, or keyset cursor, and will return false if you use a forward cursor or a dynamic cursor. (A forward cursor is the default.) In general, you shouldn't use num_rows to test if a query returned a result. Instead, check if the fetch returned anything useful. $result2 = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC); if (!$result2){ header('Location: index.html'); } else { } Edited February 20, 2014 by kicken Quote Link to comment https://forums.phpfreaks.com/topic/286344-php-sqlsrv_query-function-does-not-seem-to-work/#findComment-1469715 Share on other sites More sharing options...
forge104 Posted February 21, 2014 Author Share Posted February 21, 2014 Hi, Changing the code to : $result2 = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);if (!$result2){header('Location: index.html');}else {} worked, thanks a lot you saved me a lot of time! Quote Link to comment https://forums.phpfreaks.com/topic/286344-php-sqlsrv_query-function-does-not-seem-to-work/#findComment-1469816 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.