On 2/19/04 4:09 PM, "[EMAIL PROTECTED]"

> From: Karen Resplendo <[EMAIL PROTECTED]>
> Date: Thu, 19 Feb 2004 11:37:48 -0800 (PST)
> Subject: Stored procedure with UNION---no unique id, how to page?
> I have a stored procedure in msSQL that UNIONs 3 queries. There is no unique
> identifier that is sequential that I can use to page on webpage because the
> data comes from different tables/databases.
> Is there a way to save the resultset into a temp table and add an
> @@Identity(autonumber) to it? From that I can select Top 50, greater than last
> ID, etc, to do my pageing on web page? The resultset is pretty unique to what
> the user wants, so it can only be used by that session.
> I hope my problem(database) is understandable.
> Here is the stored procedure code, just in case:
> CREATE PROCEDURE sp_MarkerChemSummary
> @vPWS varchar(5)
<< Stored Proc deleted >>

You can, do this (... save the resultset into a temp table...) but it's more
complicated or you can run the whole thing each time and have PHP decide
which rows to show as you page through the results.

If the stored proc is quick (low impact on the DB) I'd suggest doing the
latter - return all of the rows to PHP, have PHP display the first 10 rows
and have the "Next 10" button be the submit button of a form with all the
same fields you need to re-run the same query (all hidden) and an off-set
field (which in this example would have 10 in it now).  Run the query again
and don't print the first 10 rows - then print 10 - then repeat the trick
with the Next 10 button (which now has the offset of 20)...

If the stored Proc is something you don't want to run over and over again do
as you suggest.  But you can't use a temp table though because it will get
confused and if you connect to the DB on a different connection those
results will not be there.  (temp tables are pre user and get blown away
when you disconnect).  It might work in development but if you scale this to
two web servers you could break this.  So here's how I'd do it:

Create a regular table with the columns you expect from the SP, and an
identity column, a web_user (int) and a datetime column.

Have the Stored proc:
- delete from this table where your new datetime column is over 6 hours old
(all the old data), you need to do this or the table will overflow.

- figure a random number that is not in this table (this is your web_user

- load this table set the web_user to the random number and the datetime
column to now in MS SQL getdate().

- Have your Stored Proc return only the web_user value to PHP.

Now run a new query on the table where web_user = the value you just got.
Your "Next 10" button only needs to store the web_user value and the number
of the row you're on now.  If you need some sample code I can send you some
but really it's pretty straight forward in the  PHP side.

Good Luck,

Frank Flynn
Poet, Artist & Mystic

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to