Hi, John... no problem with the reply.
I've been very busy, too!
Anyway...
I see from your screen shot that your "article_id" column
is set to "auto-increment" whenever an article is added, correct?
That will be essential to dynamically referencing your articles.
Next, let's address the "data-time stamp". In MySQL, my database,
"datetimestamp" is a datatype that I can select for that field when
I construct the database. MySQL automatically inserts the current
date-time value whenever an article is added or updated. (You can
set it in MySQL not to update the date-time automatically when the
article itself is updated.) Realize that different databases use
variations of SQL, so I'll try to keep the query SQL to very common
variations. (What is your database, anyway? MySQL? MSSQL?)
To make links on your page for your articles you'll need to do a couple
of things...
First, run a query to select your articles and the information you need
to create the links.
For MySQL, I would write:
<cfquery name="get_articles" datasource="articles">
Select article_id, article_title from articles
</cfquery>
Now this query has no "order by" part, so it just going to get the id and title
of all 300 articles in their order they've been put into the database.
Next, on your web page your can output all the article titles with a link to
a page with the complete article information.
So, we would put this on the page:
<cfoutput query="get_articles">
<p><a
href="complete_article_page.cfm?article_id=#article_id#">#article_title#</a></p>
</cfoutput>
That should output all your article titles with a link to a page called
"complete_article_page.cfm".
They key to making it dynamic is using the "?article_id=#article_id#" part.
The question mark (?)
is just a separator that is used to separate the page name from the "query
string", or the
part that comes after the page name. "article_id" is a variable (in this case,
a URL variable
because
the value is being sent in the URL address). With the "=#article_id#" part,
the variable,
"article_id",
is being set to the value of #article_id#, which gets its value from the
outputting of the query.
Each time the query outputs a row of information from your database, the
#article_id# will have the
value contained in that article row's article_id field. So, instead of having
"where article_id =
3"
in your query, which will only select the one article whose article_id = 3, now
you have links on
all
your article titles and can click on any title to be taken to the
"complete_article_page.cfm" page
to view
the article there. You can see the value of #article_id# as a real number when
you mouseover your
links. Look in the status bar (if it's enabled in your toolbar settings)at the
bottom of your
browser and you'll see something
like "http://mywebsite.com/complete_article_page.cfm?article_id=2"; The
"article_id=2" part is going
to identify the
specific article we want to get from the database and display on the complete
article page.
The other part of this is the "complete_article_page.cfm" page.
You'd set it up like this:
First you need another query to get just the article information based on the
article_id value
passed
in the URL variable that we set up above.
<cfquery name="get_article" datasource="articles">
select article_title, article_text from articles where article_id =
'#article_id#'
</cfquery>
In the query above, I just selected the title and text of the article to keep
things relatively
simple
for now. And you may or may not need the single quotes around #article_id#,
depending on your
database.
If we then use this code on your page...
<p><cfoutput>#get_article.title#</cfoutput></p>
<p> </p>
<p><cfoutput>#get_article.text#</cfoutput></p>
....you should get your article's title followed by a blank line (created by
the <p> </p> html),
and the your complete article's text.
Let me know if you get this working, then we'll add some of the other info in
there, like the byline
and
author info.
Hope this helps!
Rick
> -----Original Message-----
> From: John Barrett [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 27, 2007 2:15 AM
> To: CF-Newbie
> Subject: Re: ColdFusion query
>
> Hi Rick,
>
> I am sorry to get back to you so late, but I was playing with this, and I
> think that I am
> starting to understand.
>
> Thanks so much for your mail, it has made a world of a difference!
> I set the db like you said, and here is a sceenshot:
> http://cfhawaii.com/articles_db.jpg
>
>
> I used the code below, which allows me to pick which article I would like to
> display.
>
>
> <!--- Query The Database For health options articles --->
> <cfquery name="getarticle" datasource="articles">
> select article_title, article_text from articles where article_id=3
>
> </cfquery>
>
>
> (where article=3 displays the 3 entry for the articles, I did not know about
> this very cool!)
>
> <!--- display article title & main text --->
> <cfoutput>
> #getarticle.article_title#<p>
> #getarticle.article_text#
> </cfoutput>
>
>
> What I am hoping to do is to have a page with links, and when somebody goes
> to that link they
> can view the article, which is called from the database. There are over 300
> articles, and so
> this is a great start to load the articles from the db. Can I ask about the
> timestamp? I have
> not figured that out yet, so I guess that when somebody fills out a form,
> which gets written
> to the db it gets a time written to the db, then you can tell CF to get that
> information from
> the db?
>
>
> I am also trying to create it so the pages are created on the fly, but for
> that I think that
> I will need url parameters. For now I am just trying to load the article into
> a template,
> which I think that I can now do. I wonder if about the style, as that does
> not seem to be
> working.
>
>
> You can see the project at:
> http://www.nutritionatc.hawaii.edu/HO
>
>
> This is the static version.
>
> Thanks so much for all your help`-`
> John
>
>
>
>
>
> ----- Original Message ----
> From: Rick Faircloth <[EMAIL PROTECTED]>
> To: CF-Newbie <[email protected]>
> Sent: Sunday, November 25, 2007 4:11:55 AM
> Subject: Re: ColdFusion query
>
>
> Hi, John... I had to forward my last message below because
> I'm getting the "digest" version of the mailing list for
> cf-newbie and it didn't go through. Read the following post
> and let me know if you have any questions.
>
> Rick
>
>
> > -----Original Message-----
> > From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> > Sent: Sunday, November 25, 2007 8:48 AM
> > To: '[email protected]'
> > Subject: RE: ColdFusion Newbie (CF-Newbie): Digest every hour
> >
> > Hi, John.
> >
> > This is definitely worth learning! You'll never want to build
> > another static rather than dynamic website after your first, so keep
> at it!
> >
> > I'm not sure I understand what happened when you ran the code:
> > <cfoutput>
> > #GetArticle.Text#
> > </cfoutput>
> >
> > That should have only output one article's text. On the other hand,
> > if you run this code:
> >
> > <cfoutput query="GetArticle">
> > #Text#
> > </cfoutput>
> >
> > You'll get the text for every article, as the statement
> > <cfoutput query="GetArticle"> loops through the entire group of
> records
> > returned by the query "GetArticle".
> >
> > If you're just trying to always gets the first article in your
> database, which
> > would be the first row, then you could set up your query like this:
> >
> > <cfquery name="getarticle" database"(yourdatabasename)">
> >
> > select text from articles limit 1
> >
> > </cfquery>
> >
> > Now, that works with MySQL database, which I've used for years.
> >
> > My table structure would be (and is when I do this):
> >
> > article_id
> > article_title
> > article_text
> > article_byline
> > article_author
> > author_name
> > author_title
> > author_photo
> > date_entered
> >
> > That gives me everything I need for a nice article display. It's not
> necessary
> > to have all that; it just works well for my websites.
> >
> > I get the feeling that you're wanting to be able to select specific
> > articles, rather than just the first one you entered all the time.
> Would that be correct?
> >
> > Are you wanting to get the *last* one entered or *last one updated*
> > in the database? I do this a lot for articles which clients enter.
> > When they enter it a timestamp gets put in the table to show it was
> the
> > last one entered. If I want that article I use this query:
> >
> > <cfquery name="getarticle" database="articles">
> > select title, text from articles order by date_entered desc limit 1
> > </cfquery>
> >
> > Now, there's a lot more in that query, but let's break it down...
> >
> > First, this part "select title, text from articles" is just selecting
> > the title and text from the target article. I usually use those when
> > putting the article on a website.
> >
> > Next, the "order by date_entered"... "date_entered" is the row in my
> > database table which has the timestamp for the article. The
> timestamp
> > is updated automatically whenever the article is entered or updated.
> That
> > way, the last article entered or updated always has the latest date.
> > The "order by" by tell the query to return the records from the
> database
> > ordered from first to last by whatever criteria I tell it to use; in
> this
> > case it's "date_entered".
> >
> > The next little part of the query that makes this work is the "desc"
> part.
> > That tells the query not only to return the records in "descending
> order",
> > in this case descending order by date_entered because of the the
> "order by
> > date_entered" part.
> >
> > And finally, the last part that makes this query return only 1 result
> from
> > the query is the "limit 1" part. Now, this works I MySQL databases,
> but my
> > guess is, it would probably work in Access, if that's what you're
> using, too.
> >
> > Let me know if this helps and if you need more help, tell me
> specifically,
> > what database you're using, such as Access or MySQL, and how your
> database
> > table is structured, meaning what columns of information you have in
> your
> > database, tell me exactly what you want to accomplish with your query
> and
> > display on your website, then finally, show me all the code you're
> using.
> >
> > With all that information, we can get you the results you want.
> >
> > I remember being exactly where you are about 10 years ago, so I know
> what
> > you're going through. It can all be very confusing at first. But
> stick with
> > it, it's worth it! And ColdFusion is by far the easiest way to use
> basic
> > functions all the way up to the most powerful!
> >
> > Hope this helps!
> >
> > Rick
> >
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: cf-newbie [mailto:[EMAIL PROTECTED]
> > > Sent: Sunday, November 25, 2007 4:00 AM
> > > To: cf-newbie
> > > Subject: ColdFusion Newbie (CF-Newbie): Digest every hour
> > >
> > >
> > > ColdFusion Newbie (CF-Newbie) 25-Nov-07
> Issue:618
> > > In this issue:
> > > cf query
> > > cf query
> > >
> > >
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion is delivering applications solutions at at top companies
around the world in government. Find out how and where now
http://www.adobe.com/cfusion/showcase/index.cfm?event=finder&productID=1522&loc=en_us
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:3166
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
