Hi I’m using AJAX to check on the availability of a username before either showing a tick or cross, at the moment it is mostly working apart from when Im using sqlsrv_num_rows to count the number, as below.
<?php
if (isset($_POST["username"]))
{
require_once('config.php');
$username = strtolower(trim($_POST["username"]));
//sanitize username
$username = filter_var($username, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW|FILTER_FLAG_STRIP_HIGH);
$results = sqlsrv_query("SELECT Id from Users where Username='$username'");
$username_exist = sqlsrv_num_rows($results); //records count
echo $username_exist;
//if returned value is more than 0, username is not available
if($username_exist > 0) {
die('<img src="images/not-available.png" />');
}else{
die('<img src="images/available.png" />');
}
}
Im trying to echo it out, but nothing its not counting for some reason.
first, you should use Prepared Statements (cf. sqlsrv_prepare() ). this way you don’t need input sanitising.
second, I’d use SELECT COUNT(*) to get the number of matches. this way you can conveniently fetch the number.
third, you don’t have any error handling, so if something’s going wrong, you’ll never notice. (and false is pretty equivalent to 0)
Hi Dormlilich,
Thanks for the advice, have made a few changes and now it seems the search in the users table is not working.
<?php
if (isset($_POST["username"]))
{
require_once('config.php');
$connecDB = sqlsrv_connect($hostName, $username, $dpassword, $databaseName);
if( $connecDB === false ) {
die( print_r( sqlsrv_errors(), true));
}
$username = strtolower(trim($_POST["username"]));
//sanitize username
$username = filter_var($username, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW|FILTER_FLAG_STRIP_HIGH);
$results = sqlsrv_query($connecDB,"SELECT COUNT(*) from Users where Username='$username'");
$username_exist = sqlsrv_fetch_array($results); //records count
echo $username_exist[0];
//if returned value is more than 0, username is not available
if($username_exist[0]) {
die('<img src="images/not-available.png" />');
} else { ?>
<script>
$('#submitNew').css('visibility', 'visible');
</script>
<?php die('<img src="images/available.png" />');
}
}
I haven’t started using prepared statements yet, was testing this and I couldn’t find the problem
there’s still no error handling so you don’t know if this is a PHP or SQL issue.
Could you give me a hint where you mean, sorry
what do you do in the SQL query is invalid? e.g. due to an apostrophe in the username.
Ok I have added this in
$results = sqlsrv_query($connecDB,"SELECT COUNT(*) from Users where Username='$username'");
$stmt = sqlsrv_query( $connecDB, $results);
if( !$stmt )
{
die( print_r( sqlsrv_errors(), true));
}
But the trouble I got is that I am pulling this code through using AJAX to check the availability of a username, so will the issue of it being pulled through not allow those errors to be shown, as have added the above, and I’m not getting any error or a result
Ok I have trimmed it all back and have it working again, but the problem is that its not recognising any sort of result, as even if I deliberately ensure that images/not-available.png shows, it always displays images/available.png
That seems to be problem
<?php
if (isset($_POST["username"]))
{
require_once('config.php');
$username = strtolower(trim($_POST["username"]));
//sanitize username
$username = filter_var($username, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW|FILTER_FLAG_STRIP_HIGH);
$results = sqlsrv_query("SELECT COUNT(*) from Users where Username='$username'");
echo $results;
$username_exist = sqlsrv_fetch_array($results); //records count
echo $username_exist[0];
//if returned value is more than 0, username is not available
if($username_exist[0]) {
die('<img src="images/not-available.png" />');
} else { ?>
<script>
$('#submitNew').css('visibility', 'visible');
</script>
<?php die('<img src="images/available.png" />');
}
}
So put a breakpoint in your AJAX, and find out what the script is seeing as the message body when it gets the return.
Its not the AJAX that’s fine, as Im getting the value of what I need, its def within the code below, and wondered if someone can see anything im doing wrong here, as I cant work it out.
<?php
if (isset($_POST["username"]))
{
require_once('config.php');
$conn = sqlsrv_connect($hostName , $connectionInfo);
$username = $_POST["username"];
$query = "SELECT COUNT(Id) from Users where Username='$username'";
$res = sqlsrv_query($conn, $query) or die(sqlsrv_errors());
$username_exist = sqlsrv_fetch_array($res, SQLSRV_FETCH_NUMERIC);
$rowID=$username_exist[0];
//if returned value is more than 0, username is not available
if($rowID) {
die('<img src="images/not-available.png" />');
} else { ?>
<script>
$('#submitNew').css('visibility', 'visible');
</script>
<?php die('<img src="images/available.png" />');
}
}
?>
personally I think there’s too much going on in the response. I would expect the AJAX call to return true/false (i.e. the answer to the question whether a username already exists). what to do in either case should be left to the JavaScript code (otherwise you’re tied to what the response does to your page).
note: I like to use HTTP headers as well. HTTP 200/400 is no problem for AJAX to differentiate.
with the jQuery loaded you can do
$.ajax(url, config).done(fn_success).fail(fn_failure);
The reason i said to put a breakpoint in wasnt because i think your AJAX is wrong, but to see what the output is from your page that the AJAX is receiving. I agree with Dormilich that the return is doing too much here. the Javascript can handle the image URL etc.
1 Like
Sorry to be a pain with all this guys, Im pretty sure what I got is too much and I’m taking what your saying on board and trying to work it out, but if it helps this is my AJAX
<script type="text/javascript" src="jscripts/jquery-1.10.2.min.js"></script><script type="text/javascript">
$(document).ready(function() {
$("#username").keyup(function (e) {
//alert( "welcome" );
//removes spaces from username
$(this).val($(this).val().replace(/\s/g, ''));
var username = $(this).val();
if(username.length < 4){$("#user-result").html('');$('#submitNew').css('visibility', 'hidden');return;}
if(username.length >= 4){
$("#user-result").html('<img src="images/ajax-loader.gif" />');
$.post('check_username.php', {'username':username}, function(data) {
$("#user-result").html(data);
});
}
});
});
</script>
So its checking the username on each keystroke, but I think it maybe best that I put a check button there instead, but at the moment I cant get the php to work where it draws out a result from the database so I can then check to see whether to show the tick or cross.
PHP so far
<?php
if (isset($_POST["username"]))
{
require_once('config.php');
$conn = sqlsrv_connect($hostName , $connectionInfo);
$username = $_POST["username"];
$query = "SELECT COUNT(Id) from Users where Username='$username'";
$res = sqlsrv_query($conn, $query) or die(sqlsrv_errors());
$username_exist = sqlsrv_fetch_array($res, SQLSRV_FETCH_NUMERIC);
$rowID=$username_exist[0];
//if returned value is more than 0, username is not available
if($rowID) {
die('<img src="images/not-available.png" />');
} else { ?>
<script>
$('#submitNew').css('visibility', 'visible');
</script>
<?php die('<img src="images/available.png" />');
}
}
?>
The problem I got is that its always showing the tick regardless.
Is the script section of your else() clause in the PHP code causing some kind of problem? I am not very up on AJAX having only done one or two small experiments so far, but it seems that either will be discarded, or be returned as part of the response string.
What is the value of $rowID when it’s displaying the incorrect image? That is, to experiment could you change the code to just stick the value of $rowID instead of the image link, so you can see why it’s not doing what you expect?
this can be done way simpler in vanilla JS:
var username = this.value = this.value.replace(/\s/g, '');
Okay, so go into complete debug mode.
change $_POST to $_GET, add an echo for $query and start manually checking that your query looks correct. Then change back to POST and check what the echo returns when you do it through javascript.
Hi droopsnoot, am going through the feedback now and replying. I think this is the root of the issue, whereby $rowID doesn’t seem to have any value at all, I’m echoing it out and there nothing appearing.
Hi StarLion,
I changed it to get and in honesty there no need to check that bit as using the POST value is fine, that all works perfectly, I’m getting the username and using it in the select query and all looks good.
But when I try and echo out $res I get nothing at all, not even the extra bit I included “lui”
require_once('config.php');
$connectionInfo = array( "Database"=>$databaseName);
$conn = sqlsrv_connect($hostName , $connectionInfo);
$username = $_POST["username"];
$query = "SELECT COUNT(Id) from Users where Username='$username'";
$res = sqlsrv_query($conn, $query) or die(sqlsrv_errors());
echo $res."lui";
What I am getting though is the word ‘Array’ appearing for some reason, where the tick and cross should be
OK im trying ot break things down and see where the problem lies, so have used below to echo out the count and its outputting the letter ‘S’ which is very strange.
$query = "SELECT COUNT(*) AS Counting from Users where Username='$username'";
$fathom = $query['Counting'];
echo $fathom;
Shouldn’t it be a number?
In reply #18, you don’t have the sqlsrv_fetch_array() or sqlsrv_fetch_object() call, I would think that would cause a problem.
If you use var_dump() instead of echo() that might give you more information on what’s being returned.