As you will see from the code below I’m using a load of case statements to provide my users with options to display contracts in different ways.
This one works fine:
case "AllContractsTerminated"://complete but not tested with pageing
$sql = "SELECT COUNT(*) FROM Intranet WHERE (Contract_Status='Terminated')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE (Contract_Status='Terminated'))
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number";
break;
But this one below causes an error
case "ByCountryA-Z"://complete
$sql = "SELECT COUNT(*) FROM Intranet";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet)
select * from LIMIT WHERE {limit clause} Order by Country ASC";
break;
This is the error:
Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\csfintranet\contracts.php on line 720
What I have noticed is that if I change (ORDER BY Country ASC) which is text to (ORDER BY ID ASC) which is a number, it works fine then, which is the reason I think why the first case example and the second one doesnt.
I dont think its the $conn is it, as all the other case work if you change the order by the a number value.
I’m probably miles off.
Here is what it outputted.
resource(2) of type (SQL Server Connection) string(173) "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 25 Order by Country ASC"
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet)
select * from LIMIT WHERE RowNumber BETWEEN 1 AND 25 Order by Country ASC
In SQL Server (SSMS) to see what it says. I am almost 90% certain that your issue is doing an order by on a text column type (assuming you are using text and not varchar or nvarchar).
I ran it through sql server management studios debugging and it came back with an error that basically it coulsnt connect with the ip address, must be somethng to do with that i am using vpn.
But your exactly right with your comment. The data type field the country is using is ‘text’ type, and the others are mostly ‘nvarchar’.
Is there a reason why text was used? text is primarily for large amounts of data (up to a couple of gigs), I highly doubt you need to store that much data for country.
Umm, well what happened was they where originally using nvarchar(100), but I had a problem with drop downs not working because there was too much space after the word, so I changed them to text and they worked.
Do you know what I mean. Basically I needed to not have the space generated after the word for the drop downs to work for the admin user.
To my knowledge, unless you write the spaces, nvarchar or varchar will not force a padding of 100 characters on your input. So if you state, store ‘test’ in country when it was nvarchar or varchar type, it should only store ‘test’, not ‘test_______________________________’ (assume 96 spaces)
char on the other hand or nchar WOULD pad all values to 100 characters, so ‘test’ would become ‘test_______________________________’ (assume 96 spaces)
Now, one more thing, when you switch the column type to nvarchar or varchar, since it has the padding right now you may need to run an update query to get rid of the padding. Then all new values from that point on, SHOULD NOT be padded when using nvarchar or varchar (unless your INSERT has the value already padded)
UPDATE Intranet SET country = RTRIM(LTRIM(country))