CODEWITHSUNDEEP
phpsql-serverpdo
Kodr.F
Kodr.F

Reputation: 14390

Why is this PHP/PDO sqlsrv query not returning results?

I am trying to get results from a mssql database table using PDO in PHP, but it's not returning any results. However, I can count table rows - any advice?

  //this one working fine return 500 recrod in table
    $sql = "SELECT count(*) FROM Content";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $num_rows = $stmt->fetchColumn();

     //this one not returning anything 
    $sql = " SELECT c.*  FROM (
            SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowID,*  FROM Content
            ) AS c 
        WHERE c.ID > :row_start AND c.ID <= :row_end
        ";
       $stmt = $conn->prepare($sql);
       $stmt->bindParam(':row_start', $row_start);
       $stmt->bindParam(':row_end', $row_end);
       $stmt->execute();

    $allsuck = $stmt->fetchAll(PDO::FETCH_COLUMN);
     print_r($allsuck);

table info : 

Array
(
    [0] => ID
    [1] => Title
    [2] => Fulldata
    [3] => description
    [4] => Catid
    [5] => language
    [6] => Created
    [7] => Userid
    [8] => MetaKey
    [9] => Thumbnail
    [10] => Thumbnail_desc
    [11] => Hits
    [12] => Active
    [13] => ModifiedDate
    [14] => ModifiedBy
    [15] => Fb_image
    [16] => important
    [17] => hashTags
)

Upvotes: 0

Views: 800

Answers (2)

Mark Sinkinson
Mark Sinkinson

Reputation: 976

SELECT * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowID,* 
  FROM Content 
) data
WHERE data.RowID > :row_start 
  AND data.RowID <= :row_end

You're filtering on the wrong column. Id will do nothing for you (otherwise why bother with the ROW_NUMBER()?). The filter should be on RowId

Upvotes: 2

Mike
Mike

Reputation: 24363

Try moving the subquery to the main query because doing a subquery in this case doesn't do anything for you: 

SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowID,* 
FROM Content 
WHERE Content.ID > :row_start 
AND Content.ID <= :row_end

Upvotes: 2

Related Questions