Can anyone point me in the right direction for doing simple record paging with Prototype? I have a MySQL database with a table named "tesimony" which has three feilds (id [int 11 autonumber], content [text], name [varchar 75]. How can I display one row at a time with a link to rotate out the current row with the next row? Thanks in advance, Aaron Roberson --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Spinoffs" group. To post to this group, send email to rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-spinoffs?hl=en -~----------~----~----~----~------~----~------~--~---
Hey Aaron, Aaron Roberson a écrit :> I have a MySQL database with a table named "tesimony" which has three > feilds (id [int 11 autonumber], content [text], name [varchar 75]. How > can I display one row at a time with a link to rotate out the current > row with the next row?Well, first, what about the loading strategy? In your situation, do you think it is best to do one XHR per record (with possible UI latency, then), or to fetch larger resultsets (e.g. 10 records at a time)? -- Christophe Porteneuve a.k.a. TDD "[They] did not know it was impossible, so they did it." --Mark Twain Email: tdd-x+CfDp/qHev2eFz/2MeuCQ@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Spinoffs" group. To post to this group, send email to rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-spinoffs?hl=en -~----------~----~----~----~------~----~------~--~---
MySQL has a function similar to T-SQL (microsoft) TOP which is actually more thought out. It is called LIMIT. It takes 2 comma seperated numbers, a start record, and an end record (though that could be length, i dont use mysql much) So in your "getdata" page, you''ll want to run your SQL query and use LIMIT X,1 (or X,X+1 if it''s end row, rather than length) Where X is a value passed into getdata. So passing it on the query string you''d go : getdata.[aspx|php|asp|*] ? key=value and then ensure it is numeric data (sql injection prevention) and send it into your query to get your one record. Since you''ve only got 3 columns of data, i''d be tempted to just return a hash-style string, eg: Id=<value>&Name=<value> etc and then when you get your response using a Ajax.Request(url,...) in your handler for the onSuccess, you can use the string-to-hash function (sorry can''t remember the name) to convert the string of key=value pairs into something you can reference using hash[key]) You would probably also need to return the count of records that are available, so you can hide the next link when you are on the last record. The next link would just make a new call to get new data with X + 1 Anyway, very brief overview, i''m not going to attempt to go into any more detail. On 3/7/07, Christophe Porteneuve <tdd-x+CfDp/qHev2eFz/2MeuCQ@public.gmane.org> wrote:> > > Hey Aaron, > > Aaron Roberson a écrit : > > I have a MySQL database with a table named "tesimony" which has three > > feilds (id [int 11 autonumber], content [text], name [varchar 75]. How > > can I display one row at a time with a link to rotate out the current > > row with the next row? > > Well, first, what about the loading strategy? In your situation, do you > think it is best to do one XHR per record (with possible UI latency, > then), or to fetch larger resultsets (e.g. 10 records at a time)? > > -- > Christophe Porteneuve a.k.a. TDD > "[They] did not know it was impossible, so they did it." --Mark Twain > Email: tdd-x+CfDp/qHev2eFz/2MeuCQ@public.gmane.org > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Spinoffs" group. To post to this group, send email to rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-spinoffs?hl=en -~----------~----~----~----~------~----~------~--~---
Well, this example probably has a little more than what you want, but if you can handle it, it''s the way to go. I would highly recommend this approach (provided its inline with your requirements). http://particletree.com/features/preloading-data-with-ajax-and-json/ I hope you are aware of JSON? If not, don''t worry, just shoot an email to the group and your questions will get answered. Best, Mandy. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Spinoffs" group. To post to this group, send email to rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-spinoffs?hl=en -~----------~----~----~----~------~----~------~--~---
@Christophe - I don''t know if it wold be faster to load ten rows at a
time into some XML and then display each pair of nodes one at a time
or to just fetch each record from MySQL on every instance (XML is
pretty cludgy, and MySQL is pretty fast). At this point I would just
like to make it work, whether I put ten rows into XML or JSON or
fetched each row from MySQL.
@Gareth - I am very familiar with the LIMIT parameter in MySQL and
would prefer this technique, but I don''t know how to keep track of the
last record retrieved so that I can increment the counter and then
retrieve the following record when the link/button is clicked again.
Here is my query (in ColdFusion):
<cfquery name="getTestimony" datasource="berrettdb">
SELECT id, content, name
FROM testimony LIMIT #url.count#, 1;
</cfquery>
<cfoutput><p>#qTestimony.content#</p><cite>#qTestiomony.name#</cite></cfoutput>
What that does is get the record starting at the number passed in and
limiting it to just that one record. Then I output that one record''s
content in paragraph tags and the name in cite tags. When this is
returned, it will replace everything between <blockquote
id="testimony"></blockquote>.
I also have a query that returns the total number of records in the
table. Here it is in ColdFusion:
<cfquery name="getTestimonies" datasource="berrettdb">
SELECT id
FROM testimony
</cfquery>
Before I output the content from the table record I can check if the
number passed in is greater than (using GT in ColdFusion) the record
count. If it is, I can set the count variable back to one so that the
first testimony record will be output again (effectively rotating
through the testimonies instead of displaying a previous and next link
and worrying about hiding one or the other if we are at the first or
last record). Here is how that is done:
<cfif url.count GT getTestimonies.recordCount>
url.count = 1
</cfif>
THE ONLY PROBLEM is that I don''t know how to pass number of count back
to the calling page with a value of count + 1 and then take that and
pass it back to my getData page so I can get the next record when the
link is clicked again. How do I "maintain state" between my getData
page and my view page?
@Maninder - I have poured over that article on particletree.com and I
cannot seem to parse it all out and apply the principles to my
situation.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Spinoffs" group.
To post to this group, send email to
rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-spinoffs?hl=en
-~----------~----~----~----~------~----~------~--~---
Put the record number into a hidden form element, then read it using $F() to send it to your request... Gareth On 3/8/07, Aaron Roberson <iisitedesign-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > @Christophe - I don''t know if it wold be faster to load ten rows at a > time into some XML and then display each pair of nodes one at a time > or to just fetch each record from MySQL on every instance (XML is > pretty cludgy, and MySQL is pretty fast). At this point I would just > like to make it work, whether I put ten rows into XML or JSON or > fetched each row from MySQL. > > @Gareth - I am very familiar with the LIMIT parameter in MySQL and > would prefer this technique, but I don''t know how to keep track of the > last record retrieved so that I can increment the counter and then > retrieve the following record when the link/button is clicked again. > > Here is my query (in ColdFusion): > > <cfquery name="getTestimony" datasource="berrettdb"> > SELECT id, content, name > FROM testimony LIMIT #url.count#, 1; > </cfquery> > > <cfoutput><p>#qTestimony.content#</p><cite>#qTestiomony.name#</cite></cfoutput> > > What that does is get the record starting at the number passed in and > limiting it to just that one record. Then I output that one record''s > content in paragraph tags and the name in cite tags. When this is > returned, it will replace everything between <blockquote > id="testimony"></blockquote>. > > I also have a query that returns the total number of records in the > table. Here it is in ColdFusion: > > <cfquery name="getTestimonies" datasource="berrettdb"> > SELECT id > FROM testimony > </cfquery> > > Before I output the content from the table record I can check if the > number passed in is greater than (using GT in ColdFusion) the record > count. If it is, I can set the count variable back to one so that the > first testimony record will be output again (effectively rotating > through the testimonies instead of displaying a previous and next link > and worrying about hiding one or the other if we are at the first or > last record). Here is how that is done: > > <cfif url.count GT getTestimonies.recordCount> > url.count = 1 > </cfif> > > THE ONLY PROBLEM is that I don''t know how to pass number of count back > to the calling page with a value of count + 1 and then take that and > pass it back to my getData page so I can get the next record when the > link is clicked again. How do I "maintain state" between my getData > page and my view page? > > @Maninder - I have poured over that article on particletree.com and I > cannot seem to parse it all out and apply the principles to my > situation. > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Spinoffs" group. To post to this group, send email to rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-spinoffs?hl=en -~----------~----~----~----~------~----~------~--~---
I got it figured out folks!
Here is my JavaScript (using Script.aculo.us):
var currentRecord = 1;
function showMoreLink(obj){
//obj = document.getElementById(obj);
window.setTimeout(''Effect.Appear(\''view-more\'')'',700);
}
Event.observe(window, ''load'', init, false);
function init() {
getCount();
showTestimony();
Effect.Appear(''testimony'');
};
function getCount(){
new
Ajax.Request(''tags/nextTestimony.cfm?action=getCount&count=''+currentRecord,
{
method:''get'',
onComplete: function(response) {
currentRecord = (response.responseText);
}
});
}
function showTestimony(){
new
Ajax.Updater(''testimony'',''tags/nextTestimony.cfm?action=showNext&count=''+currentRecord,{method:''get''});
}
function showNextTestimony(obj){
getCount();
showTestimony();
Effect.BlindDown(''testimony'');
}
Here is my ColdFuison:
<cfscript>
switch(url.action){
case "getCount":
getCount(url.count);
break;
case "showNext":
showNext(url.count);
break;
}
</cfscript>
<cffunction name="getCount" output="true"
access="public">
<cfargument name="count" required="yes">
<cfset var getTestimonies = "" />
<!--- do a query to get max record id --->
<cfquery name="getLastRecord" datasource="berrettdb"
cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
SELECT id
FROM testimony
ORDER BY id DESC
LIMIT 1
</cfquery>
<!--- check if count variable is greater than last record id [if yes
then set count to 1 to start over, otherwise increment count by 1]
--->
<cfif arguments.count GTE getLastRecord.id>
<cfset arguments.count = 1 />
<cfelse>
<cfset arguments.count = arguments.count + 1 />
</cfif>
<cfoutput>#arguments.count#</cfoutput>
</cffunction>
<cffunction name="showNext">
<cfargument name="count" required="yes">
<cfset var getTestimony = "" />
<!--- get single testimony starting at record number passed in and
limit to just that record --->
<cfquery name="getTestimony" datasource="berrettdb">
SELECT id, content, name
FROM testimony
WHERE id = <cfqueryparam cfsqltype="cf_sql_numeric"
value="#arguments.count#" />
LIMIT 1;
</cfquery>
<!--- output testimony and citation name for blockquote innerHTML --->
<cfoutput><p>#getTestimony.content#</p><cite>#getTestimony.name#</cite></cfoutput>
</cffunction>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Spinoffs" group.
To post to this group, send email to
rubyonrails-spinoffs-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-spinoffs-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-spinoffs?hl=en
-~----------~----~----~----~------~----~------~--~---