SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in

    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:

    PHP Code:
        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

    PHP Code:
        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

    Which relates to this line of code:

    PHP Code:
    while($data sqlsrv_fetch_array($stmtSQLSRV_FETCH_ASSOC))           

    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.

  2. #2
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe I should have added the data types which back this up.

    The country database data type is 'text' and the contract_number data type is 'nvarchar(8)'.

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Where is your definition for $stmt? I need to see that line.

  4. #4
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    Here it is, sorry should have thought and added it.

    $stmt = sqlsrv_query($conn, $query);

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $stmt = sqlsrv_query($conn, $query);
    var_dump($stmt);
    What doe the above output?

  6. #6
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right I see, it outputs.

    bool(false)

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Okay, the fact that it is bool(false) means either your $conn is bad or your $query variable is bad.

    So now change the var_dump to this
    Code:
    var_dump($conn, $query);

  8. #8
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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"

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Try running
    Code:
    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).

  10. #10
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'.

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    The url below also agrees, that text cannot be used in sorting.
    http://twogeeks.mindchronicles.com.dnpserver.com/?p=7

  13. #13
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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)

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    One thing you could do is use the trim() function in php when writing the drop down values (it will remove all trailing spaces).

  16. #16
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you cpradio, will give the first one a go and if its does create the trailing space will use trimm.

    Thanks again.

  17. #17
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,067
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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))


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •