On 2/19/04 4:09 PM, "php-db-digest-help@xxxxxxxxxxxxx" <php-db-digest-help@xxxxxxxxxxxxx> wrote: > From: Karen Resplendo <karenresplendo@xxxxxxxxx> > Date: Thu, 19 Feb 2004 11:37:48 -0800 (PST) > To: php-db@xxxxxxxxxxxxx > 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 column). - 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 -- Frank Flynn Poet, Artist & Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
