SQL Question

Arc

I am using MS SQL for a DB and PHP on a WINDOWS server.

What i'm trying to do is the standard thign where it pulls a certian amount of items for the DB ans lists them then creates links at the bottom for different pages... i.e if i set the limit to 4 this would show 4 items on each page and create a link on the bottom for every 4 items in teh DB.

Exmaple:

News Title
News Body

Pages 1 2 3 4 MORE...

OK i can get everything to work except i cant figure out how to add the offset to this SQL statement... The offset would be the number of pages * the Limit. So if i click on the Link to page 3 it would get the 12-16 items in teh DB...

Am i making an sense here? Hee is the SQL statement i am using.

$limit = 10; // Number Of News Items Per Page
  $query = " Select Top $limit vchTitle, txtSummary, txtFullArticle, intNewsID, convert(varchar(10),dtmDateAdded,1) AS DateAdded FROM tblNewsItems ORDER BY intNewsID DESC";

Now that pulls 10 items from the DB and displays them but when i click on the link for say page3 it still shows the first 10 items becasue its nopt reading the Offset.

Thanks!

😃

barand

Some databases support TOP, mysql uses LIMIT- not both! Also the limit clause goes at the end as is in the form LIMIT offset, count

$offset = 0;
$limit = 10; // Number Of News Items Per Page
  $query = " Select vchTitle, txtSummary, txtFullArticle, intNewsID, convert(varchar(10),dtmDateAdded,1) AS DateAdded FROM tblNewsItems ORDER BY intNewsID DESC LIMIT $offset, $limit";

From mysql manual

The LIMIT clause can be used to constrain the number of rows returned by the SELECT
statement. LIMIT takes one or two numeric arguments.
If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row
is 0 (not 1):
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
If one argument is given, it indicates the maximum number of rows to return:
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.

hth

barand

Sorry Arc, thought it said MySQL, not MS SQL - must get reading glasses🆒

AFAIK TOP is right for MS SQL but I do not think it supports the offet like mySql's limit clause. I'll dig out my manual and come back.

barand

Can't find anything helpful

My only thought is to order by a unique key and do subsequent queries where key > last key of previous selection

Arc

Thanks alot, but that wont work..Items are always being deleted and added so the unique key get all outta wack and could cause problems..

Surely there is someway to tell SQL server to get the next 10 rows or the previous 10 rows... isnt there?

Lars_Berg

select top 10 * from t where
id not in (select top 10 id from t order by id desc) order by id desc

Arc

Can you splain that a bit better Lars? I'm not sure what "id" and "not in" are supposed to mean/do..

nnichols

Where Lars had 'id' you would have 'intNewsID'. The 'NOT IN' means that it would give you the top 10 that are not in the top 10 - in other words it will give you 11-20 and so on. So you need to dynamically produce queries with the following structure -

This would give you 11-20 -

SELECT TOP 10 vchTitle, txtSummary, txtFullArticle, intNewsID, convert(varchar(10), dtmDateAdded,1) AS DateAdded 
FROM tblNewsItems
WHERE intNewsID NOT IN (SELECT TOP 10 intNewsID FROM tblNewsItems ORDER BY intNewsID DESC)
ORDER BY intNewsID DESC

This would give you 21-30 -

SELECT TOP 10 vchTitle, txtSummary, txtFullArticle, intNewsID, convert(varchar(10), dtmDateAdded,1) AS DateAdded 
FROM tblNewsItems
WHERE intNewsID NOT IN (SELECT TOP 20 intNewsID FROM tblNewsItems ORDER BY intNewsID DESC)
ORDER BY intNewsID DESC

Hope this helps 😉

Arc

Nah that wont work, or maybe i'm just misunderstanding... I cant write a new SQL query for each page...it has to be 1 SQL query that works no matter how many pages there may be.

nnichols

You have to write a sql statement that follows that fromat, dynamically - using php. Have a go. If you can't figure it out come back and I'll have another look.

Arc

Yah i tried it. Using a variable in the second Select statement after the WHERE clause which passed the Offset which would be 0 or 10 or 20 etc... But it returned nothing at all from the DB.

nnichols

Try something like this -

$records_per_page = 10;
if (!isset($_GET['page']))
	$offset = 0;
else
	$offset = ($_GET['page']-1) * $records_per_page;

$sql = "SELECT TOP $records_per_page vchTitle, txtSummary, txtFullArticle, intNewsID, convert(varchar(10), dtmDateAdded,1) AS DateAdded 
FROM tblNewsItems
WHERE intNewsID NOT IN (SELECT TOP $offset intNewsID FROM tblNewsItems ORDER BY intNewsID DESC)
ORDER BY intNewsID DESC";

Hope this helps 😉

Arc

I just realized what the problem is... that SQL statement is pulling item from the DB based on the Unique Key of intNewsId.. as i've already mentioned News items are constantly deleted from the DB and this will not work as the Unique ID as of right now doesnt even start at 1 it starts at 32..... If 32 got deleted then the earliest would be 33... So i cant do this based on a Unique ID in teh DB... It has to be done using the same logic as the MYSQL SQL stament.

 
$offset = 0; 
$limit = 10; // Number Of News Items Per Page 
$query = " Select vchTitle, txtSummary, txtFullArticle, intNewsID, convert(varchar(10),dtmDateAdded,1) AS DateAdded FROM tblNewsItems ORDER BY intNewsID DESC LIMIT $offset, $limit";

Is there no way to get this same logic with MS SQL? surely there is. This statement just gets the $limit amount of rows from the DB starting at the $offset number and isnt based on any unique key.

For example if limit=10 and offset=10 it would pull 10 item from the db starting at the 10 th row in the db reguardless of any unique key.

nnichols

I'm sure you can. I never use mssql as it is ***. I'll get back to you in a mo.

nnichols

Try having a look at this post -

http://www.phpbuilder.com/annotate/message.php3?id=1013835

barand

Less efficient as it always returns the whole set, but how about

$limit = X;
$offset = Y;
$result = mssql_query($query)
mssql_data_seek($result,$offset);
for ($i=0; $i<$limit;$i++) {

//process
}