[RESOLVED] PHP MSSQL - Feild Issue

fean0r

I'm gathering a few peices of information from an MSSQL database;

$query = "SELECT OrderSubtotal, CapturedOn, OrderNumber FROM Orders WHERE AffiliateID='".$row['AffiliateID']."' AND TransactionState='CAPTURED'";
$result = mssql_query($query);

The code works fine unless I try getting the Referrer feild then I get 0 results.

$query = "SELECT OrderSubtotal, CapturedOn, OrderNumber, Referrer FROM Orders WHERE AffiliateID='".$row['AffiliateID']."' AND TransactionState='CAPTURED'";
$result = mssql_query($query);

An example of the Referrer feild is; http://www.google.ie/search?q=oil+of+olay+and+tigi&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GB:official&client=firefox-a

I've been tried everything I can find of to sort this issue so any ideas would be great!

Thanks

bradgrafelman

Do a [man]var_dump/man on $result - what does it say?

fean0r

It returns NULL

Weedpacket

Interesting: mssql_query is supposed to return one of three things (a result set, true, or false), none of them null.

Here's a daft question: is the field perhaps called Referer? The http protocol misspells it (following its misspelling in the ftp protocol), and just maybe the column name continues the tradition?

fean0r

I tried the spelling both ways withy no luck.

I have this issue on another feild on another script too.

I've noticed another weird thing;

If I use

echo "<BR/> Var Dump: ".var_dump($result);

I get nothing returned

If i use

echo "<BR/> Var Dump: ".var_dump($result);
echo "<BR> Var Dump (Referrer): ".var_dump($row['Referrer']);

I get the NULL (and nothing for the 2nd line).

FYI: The feild is ntext, null in MS SQL 2005

bradgrafelman

Read the manual for [man]var_dump/man.. it doesn't return anything - it outputs the information directly.

fean0r

It just seems like when certain feilds are called nothing happens!

I would be nice to see an error or something..

bradgrafelman

Although you should still see the result, try using [man]var_dump/man properly... e.g. this:

echo "<BR/> Var Dump: ".var_dump($result);

should be this:

echo "<BR/> Var Dump: ";
var_dump($result);

fean0r

I get this: Var Dump: bool(false)

bradgrafelman

Now we're getting somewhere. As the manual for [man]mssql_query/man states, the only time that function should return boolean FALSE is if an error has occurred.

Thus, you need to get the error message from the SQL server and figure out what went wrong with your query (e.g. misspelled column name, perhaps). Something along the lines of:

if(!$result)
    die('MSSQL error: ' . mssql_get_last_message());

fean0r

Thanks for the help.

I came across this

Solution for the following Error:

Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

This is because you are using column types of like ntext instead of text. There are 2 solutions.

Change all ntext column types to text or

Your query must look like: SELECT CAST(field1 AS TEXT) AS field1 FROM table

I've just tested it on one of the feilds and it seems to be working, I'll post back in a few minutes.

fean0r

This worked;

$query = "SELECT OrderSubtotal, CapturedOn, OrderNumber, CAST(Referrer AS TEXT) AS Referrer FROM Orders WHERE AffiliateID='".$row['AffiliateID']."' AND TransactionState='CAPTURED'";

Thanks for the the hekp everyone