PHP MSSQL variable not working?

Question

Very confused and have probably overlooked something, but some ideas would be appreciated!

I have a query as follows:

$usernamequery = "select username + ' ' + surname as username, userid from users where username + ' ' + surname = '$username'";
$usernamestmt = sqlsrv_query( $conn, $usernamequery);
if( $usernamestmt === false ) {
 die( print_r( sqlsrv_errors(), true));
}

while( $obj = sqlsrv_fetch_object( $usernamestmt)) 
{
echo    $username1 = $obj->username;
echo    $userid = $obj->userid;
}

which doesn't return anything, however when I echo out the $usernamequery I get

select username + ' ' + surname as username, userid from users where username + ' ' + surname = 'Joe Bloggs'

When I then go and run that directly in SQL it returns the results I'm expecting.

What's more odd is that when I then change the PHP to the actual query (i.e.

select username + ' ' + surname as username, userid from users where username + ' ' + surname = 'Joe Bloggs'

)

it runs like a charm and returns the results I'm expecting.

All in all, I'm dead confused...!

For God's sake, don't inject the $username variable right into your SQL like a caveman. Use a place holder and sqlsrv_prepare()! — Álvaro González
– Álvaro González, Commented Nov 22, 2012 at 10:30
@ÁlvaroG.Vicario I'll have to look at what sqlsrv_prepare does as I'm pretty new to PHP — franglais
– franglais, Commented Nov 22, 2012 at 10:32
Just checked and prepare is actually optional. But you use sqlsrv_query() and there's an example in the manual page for that funciton. — Álvaro González
– Álvaro González, Commented Nov 22, 2012 at 10:34
@ÁlvaroG.Vicario According to PHP guidelines it's saying that you should just use sqlsrv_query() when running a query once, which is what I'm doing on this page. $username is a variable passed to this page from another, not a variable that will be changing ont his page. — franglais
– franglais, Commented Nov 22, 2012 at 10:35
No matter the implementation details: the library offers prepared statements so... use them! ;-) — Álvaro González
– Álvaro González, Commented Nov 22, 2012 at 10:40

Álvaro González · Accepted Answer · 2012-11-22 10:39:56Z

1

My educated guess is that $username does not contain what you think it does. It possibly has a tabulator or more than one spaces, but you're possibly echoing it into HTML and the browser is collapsing blank space into a single space.

You can inspect the exact contents of a variable with var_dump(), e.g.:

var_dump($username);

... of, if you need further details, with bin2hex();

var_dump(bin2hex($username));

answered Nov 22, 2012 at 10:39

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

franglais Over a year ago

Brilliant - looking at the bin2hex and then in SQL looking at what that's coming out as for some reason it's pulling across a </b> tag! I'll see where that's coming from and I think that should sort it out!

franglais Over a year ago

So I've found where the </b> tag is coming from and got rid of that, but it's still not working... gah! I've even compared the hex from PHP to the expected hex in SQL and they are the same! I think I may have to find some other way of looking up the name!

Álvaro González Over a year ago

If the SQL code is the same byte to byte, it should retrieve the same results when used on the same data set, no matter how you compose the $usernamequery string...

franglais Over a year ago

I think I've buggered that up somewhere else then!

Sudar · Accepted Answer · 2012-11-22 10:30:07Z

0

Change the query to

$usernamequery = "select username + ' ' + surname as fullname, userid from users where fullname = '$username'";

answered Nov 22, 2012 at 10:30

Sudar

20.1k32 gold badges94 silver badges134 bronze badges

1 Comment

franglais Over a year ago

I'm looking up against two columns in the users table, username and surname. The input I have is their complete name, so I'm combining the two columns. As a result, I'd just be getting the error that "fullname isn't a column in the users table"

Collectives™ on Stack Overflow

PHP MSSQL variable not working?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related