laurence keith albano laurence keith albano - 3 months ago 13
MySQL Question

Having trouble with inserting query after program checks username availability using ajax

I'm having trouble inserting my query into the database after the program checks the username availability on blur using ajax to check if the username is available for register or not. After it checks the username availability, it always insert some empty queries in my database.

Here is the sample output for more details:

enter image description here

But the problem is when it displays username available it will automatically inserts an empty query into the database.

This will insert into the database after it checks the availability of the user:

enter image description here

I want the insert query to be inserted when the user submits or when he/she clicks the register button. The availability of the username is for checking only. Is there a problem with my code?

Here is the code:

Sample.php FILE

<!DOCTYPE html>
<html>
<head>
<title>Sample</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<input type="text" placeholder="Username" id="username" name="username" class="form-control" >
<span id="availability" style="color:green;"></span>
<input class="form-control" placeholder="First Name" name="firstname" type="text" >
<input type="submit" id="signup" name="signupsubmit" class="btn btn-info form-control" value="Register">
</body>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>

<script>
//Check Username availability
$(document).ready(function()
{
$('#username').blur(function()
{
var username = $(this).val();
$.ajax({
url:"SampleProcess.php",
method:"POST",
data:{username:username},
dataType:"text",
success:function(html)
{
$('#availability').html(html);
}
})
});
});
</script>
</html>


I use ajax for checking the username availability inside the script here:

<script>
//Check Username availability
$(document).ready(function()
{
$('#username').blur(function()
{
var username = $(this).val();
$.ajax({
url:"SampleProcess.php",
method:"POST",
data:{username:username},
dataType:"text",
success:function(html)
{
$('#availability').html(html);
}
})
});
});
</script>


The process file for checking username and inserting a query.

SampleProcess.php FILE

<?php
//This is an external file DBController for creating a connection to DB
require_once("DBController.php");
//DBHandler handles the DBController class
$DBHandler = new DBController();

//Call the function mainConnect()
$connect = $DBHandler->mainConnect();

//Check Username Availability
if(isset($_POST["username"]))
{
$sql = "SELECT * FROM tablereminders WHERE username ='".$_POST["username"]."'";
$result = mysqli_query($connect, $sql);

if(mysqli_num_rows($result) > 0 )
{
echo"<span class=\"text-danger\">Username not available</span>";
echo'<script>document.getElementById("username").focus();</script>';
}
else
{
//In this else condition the query will be inserted in the
//database after the user clicks the register button, but it will insert right
//after the program check the availability

echo"<span class=\"text-success\">Username available</span>";

//Problem inserting my query here
$Username = isset($_POST['username']);
$FirstName = isset($_POST['firstname']);

//Query Insert into tablereminders
$query = mysqli_query($connect, "INSERT INTO `tablereminders`(`username` , `firstname`)
VALUES ('$Username' , '$FirstName')");

if($connect->query($query) == TRUE)
{
echo "<script type='text/javascript'>alert('New record created successfully');</script> ";
echo "<script>setTimeout(\"location.href = 'LoginReminder.php'\", 0)</script>";
}
}

}

?>


In the else condition after checking mysqli_num_rows I put my insert query there to perform when the user clicks the register buton. Is there a problem with my if-else condition or is it about the AJAX function inside the Sample.php file, Should I use GET or POST method? Either way I tried it but it doesn't correct my problem here. Please help.

Answer

Try this ;)

There are two things

  1. Check username availability.
  2. Submit the form and create a record.

1. Check username availability.

For this, you have done all the code you need to modify code like this:

<?php

//This is an external file DBController for creating a connection to DB
require_once("DBController.php");
//DBHandler handles the DBController class
$DBHandler = new DBController();

//Call the function mainConnect()
$connect = $DBHandler->mainConnect();

//Check Username Availability
if(isset($_POST["username"])){
  $sql = "SELECT * FROM tablereminders WHERE username ='" . $_POST["username"] . "'";
  $result = mysqli_query($connect, $sql);

  if(mysqli_num_rows($result) > 0){
    echo '<span class="text-danger">Username not available</span>';
    echo '<script>document.getElementById("username").focus();</script>';
  }else{
    echo '<span class="text-success">Username available</span>';
  }
}

2. Submit the form and create a record. Now create new ajax call to some other file to insert record which submits form data on some button click event.

This you can do same you done in case of username availability.

Code sample to insert record:

$Username = isset($_POST['username']);
$FirstName = isset($_POST['firstname']);

//Query Insert into tablereminders
$query = mysqli_query($connect, "INSERT INTO `tablereminders`(`username` , `firstname`) 
        VALUES ('$Username' , '$FirstName')");

if($connect->query($query) == TRUE){
  echo '<script type="text/javascript">alert("New record created successfully");</script>';
  echo '<script>setTimeout(function(){ location.href = "LoginReminder.php"}, 0);</script>';
}

Remember 2 things before inserting new record in database:

  1. Validate user data.
  2. Escape user data may be with this function mysqli_real_escape_string OR you can use prepared statement and bind params.