BETWEEN in Sql Server 2008 using PHP PDO Prepared statement

Question

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?

Okay. my question is not similar to that question because im using sql server 2008. i dont have problem in `LIMIT` using Mysql. — Jason Miguel, Feb 01 '18 at 01:30
You should be using `ATTR_EMULATES_PREPARES = true` if you want this to work. — Barmar, Feb 01 '18 at 01:32
Sql Server 2008 doesnt support `$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );` i tried it already sir. only works in Mysql. I want to clarify that i am using Sql server 2008 not Mysql. I only tried it in Mysql using Php PDO prepared statement like this `LIMIT(?,?)` and works totally fine. My problem is Sql Server 2008 doesnt have `LIMIT` only `BETWEEN` i tried `BETWEEN(?,?)` but didnt work. — Jason Miguel, Feb 01 '18 at 01:39

score 1 · Answer 1 · answered Feb 01 '18 at 01:48

1

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.

answered Feb 01 '18 at 01:48

Tim Biegeleisen

502,043
27
286
360

can i use `BETWEEN ? AND ?` in your code? because i already tried that and i have an error. but if i only put number on it, it works totally fine e.g `BETWEEN 0 AND 20` – Jason Miguel Feb 01 '18 at 02:09
@JasonMigz Yes, `BETWEEN 0 AND 20` should work with my query. Just get it working first on SQL Server, then worry about porting it to your PHP code. You're using prepared statements, which is already on the right track. – Tim Biegeleisen Feb 01 '18 at 02:09
Yes @TimBiegeleisen, i already tried it on Sql server, and the query has no error and retrieve the data. my problem is my php code, because the start and limit in `BETWEEN` in coming from `$_POST['start']` `$_POST['limit]` ajax request thats why i need to store it in variable and pass it in the prepared statement. – Jason Miguel Feb 01 '18 at 02:15

score 0 · Answer 2 · answered Feb 01 '18 at 07:56

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');

BETWEEN in Sql Server 2008 using PHP PDO Prepared statement

2 Answers2