php5.2.5 and mssql_connect... php too fast???

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
redherring
Forum Newbie
Posts: 6
Joined: Fri Jan 25, 2008 8:52 pm

php5.2.5 and mssql_connect... php too fast???

Post by redherring »

Hi there -

I have a script that interacts with an sql database. Since upgrading from php 4.3.8 to 5.2.5 I've found that most of the time the script no longers returns data... it doesn't produce an error or anything, it just doesn't return any data. Then, "once in a while" - that's an official unit of measurement - it will return the correct data.

It's almost as if, as per someone's post here - http://bugs.php.net/bug.php?id=29074 - "php is getting too fast and giving up before a response is received from SQL 2000?"

Appreciate your thoughts on this.

Thanks.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: php5.2.5 and mssql_connect... php too fast???

Post by Eran »

There's no such thing as PHP giving up before getting a response from a database. Slow database queries are a major factor in slowing down PHP scripts...
More probable you have a bug in your legacy code that fetches the data, since it was written for PHP 4+
If you'd post the code that fetches the data from the database you might get help locating that bug
redherring
Forum Newbie
Posts: 6
Joined: Fri Jan 25, 2008 8:52 pm

Re: php5.2.5 and mssql_connect... php too fast???

Post by redherring »

Yea, sure seems like a weird concept to me, although I bought into it as I could "swear" that sometimes i actually do get data returned. Regardless, here's the corresponding code:

*************************************

Code: Select all

 
 
$clientID = $_GET['clientID'];
 
$server = "localhost,1433";
$username = 'myusername';
$password = 'mypassword';
 
$sqlconnect = mssql_connect($server, $username, $password);
$sqldb = mssql_select_db("mydatabase",$sqlconnect);
$sqlquery = "SELECT companyname AS 'clientname' FROM mytable WHERE companynum = $clientID;";
$result = mssql_query($sqlquery);
 
while ($row=mssql_fetch_array($result)){
$companyname = $row['clientname'];}
 
mssql_close($sqlconnect);
 
 
*****************************

Many thanks.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Re: php5.2.5 and mssql_connect... php too fast???

Post by JAM »

You should add a check to see if you get hits aswell, as example;

Code: Select all

if (mssql_num_rows($result) == 1) {
    // do stuff
} else {
    // debug code here as result was unexpected
}
And perhaps the use of mssql_result() instead of the fishy looking while-loop?
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: php5.2.5 and mssql_connect... php too fast???

Post by Mordred »

What happens if
a) you change db host to 'localhost:1433' (: instead of , - at least that's how it works for mysql, I have no experience with mssql)
b) read about SQL injection
c) check error conditions
d) crerate an index on companynum if there's lots of rows
e) use LIMIT 1 and call fetch_array only once
redherring
Forum Newbie
Posts: 6
Joined: Fri Jan 25, 2008 8:52 pm

Re: php5.2.5 and mssql_connect... php too fast???

Post by redherring »

Thanks.

The query actually returns no results, although clearly it should... one per value of clientID (as such, the note about the fishy looking while loop is even more relevant).

It does not like "localhost:1433" (with a colon vs. a comma).

MSSQL sure seems to be enabled properly via a php-info.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: php5.2.5 and mssql_connect... php too fast???

Post by Mordred »

redherring wrote:(as such, the note about the fishy looking while loop is even more relevant)
I now realize that in mssql it's SELECT TOP 1 blabla, not SELECT blabla LIMIT 1 as in MySQL.
What happens if you try this from ... errr ... does mssql come with a command-line interface? Try it not through your app, but from some administrative interface of sorts.
Don't forget the SQL injection as well.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: php5.2.5 and mssql_connect... php too fast???

Post by Eran »

As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
for example -
... companynum = `" . htmlentities($clientID) . "`";
If you know that $clientID can only be an integer it is even prefered to cast it directly - ... companynum = `" . (int) $clientID . "`";

You should dump the query result (var_dump) to see if it returns a true or false. false would indicate an error in the query itself while true indicates no rows were returned.

Also check the level of error reporting you are using, it should be set to E_ALL (you can set it manually using error_reporting(E_ALL)) to make sure you aren't missing any possible errors
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: php5.2.5 and mssql_connect... php too fast???

Post by Mordred »

Oh, my require('deity.inc.php')!
pytrin wrote:As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
for example -
... companynum = `" . htmlentities($clientID) . "`";
If you know that $clientID can only be an integer it is even prefered to cast it directly - ... companynum = `" . (int) $clientID . "`";

You should dump the query result (var_dump) to see if it returns a true or false. false would indicate an error in the query itself while true indicates no rows were returned.

Also check the level of error reporting you are using, it should be set to E_ALL (you can set it manually using error_reporting(E_ALL)) to make sure you aren't missing any possible errors
No, it's ... wrong. Plainly and (almost) entirely.
Check your database docs for how to escape, IIRC mssql didn't have an explicit escape function like MySQL (... which has two, and both of them not quite right, but still ... ), but maybe addslashes() should work okay if you're using utf-8 or latin1 for talking to your database.

var_dump() and error_reporting() are debug things, they shouldn't be run in production environment. There's "if ()" instead of the first, and server-side settings for the latter. If you don't have such control over your server, make sure your scripts include a common file, and put error_reporting(E_NONE) in it. Only your development server at home should have error_reporting.
As others mentioned, you should filter your variables and quote them before you insert them into the sql string.
Now this is correct, and very important too.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: php5.2.5 and mssql_connect... php too fast???

Post by Chris Corbyn »

Mordred wrote:Check your database docs for how to escape, IIRC mssql didn't have an explicit escape function like MySQL (... which has two, and both of them not quite right, but still ... ), but maybe addslashes() should work okay if you're using utf-8 or latin1 for talking to your database.
PDO FTW! (K, what have done to us??) :)

http://au2.php.net/pdo
Post Reply