CODEWITHSUNDEEP
phpsql-server-2008pdo
Jason Miguel
Jason Miguel

Reputation: 192

BETWEEN in Sql Server 2008 using PHP PDO Prepared statement

I already read question here is the link about LIMIT in sql server and they say that i need to use row_number() between i already tried it and worked totally fine. But i need it like in Mysql using prepared statement like this:

   $user_input = $_POST['user_input'];
   $user_input = "".$user_input."%";
   $start = 0; 
   $limit = 20;
   $search_query = "SELECT * FROM item_master WHERE item_desc LIKE ? OR  item_number LIKE ? LIMIT (?,?)";
   $qry_search_query=$conn->select($search_query,array($user_input,$user_input,$start,$limit),'0126181431');

Here is the example code for Sql Server between

select * from (select[UserId], [HomeTown], [HomepageUrl], [Signature], [CreateDate] , row_number() OVER (order by  [CreateDate]) as RowNumber form [UserProfiles]) Derived where RowNumber between 4 and 9

I tried to use between(?,?) but didnt worked.

Note: I add this $dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); in my connection.php page in order to work prepared statement in Mysql. this is the reference: Please click this link

I tried prepared statement between in sql server 2008 and it doesnt work

I know this is sound weird but i should use sql server 2008 in my php application as database because of my current employer still using 2008 version not 2012 or later. Any idea guys?

Upvotes: 1

Views: 127

Answers (2)

Mark Aguirre
Mark Aguirre

Reputation: 13

You can use stored procedure, so your code looks like this

$user_input = $_POST['user_input'];
   $user_input = "".$user_input."%";
   $start = 0; 
   $limit = 20;
   $search_query = "call item_master(?,?,?,?)";
   $qry_search_query=$conn->select($search_query,array($user_input,$user_input,$start,$limit),'0126181431');

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521399

As you might have figured out, SQL Server does not support LIMIT. Instead, it supports TOP. But there is an even bigger problem with your query, which is that it has no ORDER BY clause. It makes no sense to speak of the first N records if there is no ordering defined for the result set. Here is a general pattern you may try for SQL Server:

SELECT *
FROM
(
    SELECT TOP 10 *,
        ROW_NUMBER() OVER (ORDER BY sort_column) rn
    FROM item_master
    WHERE item_desc LIKE ? OR  item_number LIKE ?
    ORDER BY some_col
) t
WHERE rn BETWEEN start AND end;

Here we can use ROW_NUMBER to target any sequence of records. I have not addressed any of the possible PHP issues you may have.

Upvotes: 1

Related Questions