PHP + AJAX not yielding results when querying database

Question

I'm taking my first steps in PHP and now I'm trying to create a simple login form and trying to validate the information using AJAX and PHP by querying a database. I used Ajax because I needed to change a few elements on my page in case the login information wasn't valid and later I found out that you can't actually call a php function directly from jquery, you need to use ajax instead. I have this form in a php file:

  <body>
    <div class="wrapper" id="mainWrapper" style="display:none;">
      <img src="assets/icons/user.png" id="userLogo" alt="user-icon">
      <form>
      <div class="form-div">
        <input type="text" required id="username" name="user" placeholder="Username">
      </div>
      <div class="form-div">
        <input type="password" required id="password" name="pass" placeholder="Password">
      </div>
      <input type="button" name="submit" id="loginButton" value="login" onclick="post();" class="btn-login">
      </form>
      <br>
      <a href="recover.php">Forgot password?</a>
      <br>
      <div id="status"></div>
      </div>
    </div>
  </body>

And I've created this ajax script to send my variables to the php file that is supposed to test the login information, this is the ajax script:

    <script type="text/javascript">
      function post() {

   var hr = new XMLHttpRequest();
   // Create some variables we need to send to our PHP file
   var url = "testLogin.php";
   var username = document.getElementById("username").value;
   var password = document.getElementById("password").value;
   var vars = "username="+username+"&password="+password;
   hr.open("POST", url, true);
   // Set content type header information for sending url encoded variables in the request
   hr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
   // Access the onreadystatechange event for the XMLHttpRequest object
   hr.onreadystatechange = function() {
     if(hr.readyState == 4 && hr.status == 200) {
       var return_data = hr.responseText;

    if(return_data == "false")
    {
      document.getElementById('userLogo').style.display = "none";
      document.getElementById('userLogo').src = "assets/icons/error.png";
      $("#userLogo").fadeIn(1000);
      document.getElementById('status').innerHTML = "Username ou password inválidos";
    }
    else
    {
      document.getElementById('userLogo').style.display = "none";
      document.getElementById('userLogo').src = "assets/icons/user.png";
      document.getElementById('status').innerHTML = "";
      $("#userLogo").fadeIn(1000);
    }
     }
   }
   // Send the data to PHP now... and wait for response to update the status div
   hr.send(vars); // Actually execute the request
   document.getElementById("status").style.visibility = "a processar...";

      }
    </script>

I get the value of the password and username by accessing the elements via their ID and then I send it to the php file entitled "testLogin.php" that contains the script to test the login information this is the content of that file:

<?php
session_start();
include_once('connect.php');
if(isset($_POST['username']))
{
  $username = $_POST['username'];
  $password = $_POST['password'];
  $sql = "SELECT * FROM users WHERE Username = '".$username."' AND password ='".$password."' LIMIT 1";
  $result = mysql_query($sql) or die(mysql_error());
  if(mysql_num_rows($result) == 1)
  {
      $row = mysql_fetch_assoc($result);
      echo "true";
      exit();
  }
}
  else
  {
    echo "false";
    exit();
}
?>

Im the ajax scrip previously shown I'm "listening" for a echo of true if the information is correct and an echo of false if the information is not valid. When I change the code in the test script to this:

if($_POST['username'] == 'Joao' && $_POST['password'] == "meu")
echo "true";
else echo "false";

it works perfectly, because of this I have in mind that maybe there is an error with either ajax calling the php file or something to do with php querying the database, and I can't tell because I feel powerless testing this. I would appreciate if anyone could give me a help to figure out if it's the connection to the database or something to due with ajax or php. Thank you

EDIT: This is the content of the connection file

<?php
$host = "localhost";
$username = "root";
$password = "";
$db = "forum";
mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($db);
 ?>

I've now changed all the deprecated mysql functions to mysqli as suggested:

<?php
session_start();
include_once("config.php")
if(isset($_POST['username']) && isset($_POST['password']))
{

$status = "";
$username = $_POST['username'];
$password = $_POST['password'];
  if($bd = mysqli_connect('localhost','root','','forum'))
  {
     if($command = mysqli_prepare($bd,"SELECT *  FROM users WHERE Username = ? AND password = ? "))
     {
       mysqli_stmt_bind_param($command,"ss",$username,$password);
       mysqli_stmt_execute($command);
       mysqli_stmt_bind_result($command,$testUser,$testPass);
       mysqli_stmt_fetch($command);
       if($username == $testUser && $password == $testPass)
       $status = "true";
       else $status = "false";
       mysqli_stmt_close($command);
     }
    mysqli_close($bd);

  }

}
else $status = "false";

echo $status;
exit();

But I still get the same result

A lot of the mysql methods are deprecated and you should either look into mysqli or PDO for database access. — Nigel Ren, May 29 '17 at 17:41
@NigelRen the `mysql_` methods* Also, all of them are deprecated and removed entirely from php7. @Newbies, what version of php are you using? — , May 29 '17 at 17:44
@Terminus I'm currently using php version 7.1.1, and I will try to change the commands to mysqli — KingOFNewbies, May 29 '17 at 17:49
@KingOFNewbies Glad to hear! That should solve your problem. While your application is in development (ie, not able to be visited by anyone but you) you should enable [error reporting](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display). You may want to make a `config.php` file and include that file everywhere. Within the config file, you can `include_once 'connect.php';` and the code from the answer I linked. It'll keep your code clean and make it easier to read and change later. Good luck! — , May 29 '17 at 18:03
Ps, don't be afraid to read the [manual](http://php.net/docs.php). It's confusing at first but it gets easier! — , May 29 '17 at 18:04
@Terminus thank you for all the help, I'm changed the entire code to mysqli but I still get the same results :\ could you please check my edit on the post? — KingOFNewbies, May 29 '17 at 18:27
Check your php my giving dummy data in username &password variable. Are they echo correctly. — CoderSam, May 29 '17 at 18:50
I see your edit, looks good to me... could you try turning error reporting as that other link shows? — , May 29 '17 at 21:08
@Terminus after hours upon hours of youtube videos and checking all of your answers I finally got it to work. I will post my answer it may help other fellow coders in the same situation — KingOFNewbies, May 29 '17 at 22:10

score 1 · Answer 1 · answered May 29 '17 at 22:20

Well after many hours of research and reading through documents and all of that, and with the precious help of your questions I got to this code that actually works. I've changed the ajax call to:

<script>
 function confirmar()
 {
   var username = $("#username").val();
   var password = $("#password").val();
   $.ajax({
     //The file containing the script to test the login information
     url: "testLogin.php",
     //The type of the call (POST, GET)
     type: "POST",
     //the information to be tested, send it through the URL (form serialization may be a better choice than this aproach)
     data: "username="+username+"&password="+password
   }).done(function(resp)
   {
     //the server response stating that the test was a failure
     if(resp == "notok")
     {
       $('#loader').hide();
       $("#loginButton").show();
       $('#userLogo').css('display',"none");
       $('#userLogo').attr('src',"assets/icons/error.png");
       $("#userLogo").fadeIn(1000);
       $('#status').html("Username ou password inválidos");
       return;
     }
     else
     {
       $('#loader').show();
       $("#loginButton").show();
       $('#userLogo').css('display',"none");
       $('#userLogo').attr('src',"assets/icons/user.png");
       $('#status').html("");
       $("#userLogo").fadeIn(1000);
       window.location = "userDetails.php";
     }
   })
 }
  </script>

which, in my opinion, is far more readable than what I had and I think it's far more easier to understand as well. After I understood how the ajax call actually works I went to change the php file that fetches data from the database and actually tests it to this:

<?php
if(isset($_POST['username']) && isset($_POST['password']))
{
$username = $_POST['username'];
$password = $_POST['password'];
if($username == "" || $password == "")
{
  //server response if the info or the test wasnt valid
  echo "notok";
  exit();
}
}
else
{
  echo "notok";
  exit();
}
if($bd = mysqli_connect("localhost", "root", "", "forum"))
{
  if($stmt = mysqli_prepare($bd,
  "SELECT User_ID, username, password, email from users WHERE Username = '".$username."'")) {
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $idUser, $name, $pass, $email);
    mysqli_stmt_fetch($stmt);
    if($name == $username && $password == $pass) echo "ok";
    else echo "notok";
    mysqli_stmt_close($stmt);
  }
  mysqli_close($bd);
  }
 ?>

this actually produces the result I was after. I only have 3 questions/problems atm. The first is that in the jquery function that calls the ajax I think this is the best place to test if the username and the password fields are empty. I've done so by adding this lines of code:

   if(password == "" || username ||)
   {
     alert("please fill out the information required for the login");
     return;
   }

but after adding that code the ajax call no longer works... The second thing is that I've found something to do with form serialization for the ajax calls that I think is far more proficient than getting the values by element ID, I havent got this to work tho.

i have edited my answer check it . Hope it will clarify you more. — CoderSam, May 30 '17 at 08:57

CoderSam · Answer 2 · 2017-05-30T08:56:53.513

Your tons of code could be simplified by using jquery ajax call. For example:

$.ajax({
type: "POST",
url: url,
data: data,
success: success,
dataType: dataType
});

For details refer jquery ajax manual it's very simple. In the php script:

Please use PDO in php for code security.
What is the need for mysql_fetch_assoc () when you don't want to see any data.
Besides, selecting rows only to count them simply makes no sense. A count(*) query have to be run instead, with only one row returned.
the if statement is returning true and what if it is false. So a else statement is also required. Your else is returning false only when username is not set.
Also session_start() has no use in your script.

Hope it helps you understand.

Update:

Problem1: How to validate the form?

Solution: See this example:

    $(document).ready(function(){
     $('#form_id').on('submit',function(e){
        var username = $('#username').val();
        var password = $('#password').val();

       if(username == '' || password == '')
      {
      alert('required fields!!');
      e.preventDefault(); //stop submitting the form.
      }else{

     //here your ajax call

     }
   });

});

Problem2: How to use serialize in form submit?

Solution: in jquery do it like this

   $('#form').serialize(); //use in your ajax call

I'm now trying to use that ajax post and I've taken out the fetch and used count (*) instead. — KingOFNewbies, May 29 '17 at 19:09
after you're answer and searching I finally got something working with your ajax example, check my answer and please tell me if there's something wrong or I can change — KingOFNewbies, May 29 '17 at 22:21

PHP + AJAX not yielding results when querying database

2 Answers2