3 Replies Latest reply on Dec 24, 2008 8:38 AM by siriiven

    Sql Select Problem

      I am building a blog and I'm trying to have a"Read More" button to allow someone to write an article description, and place a read more link wherever they want. The read more link will have an id = systemreadmore. When the whole article gets inserted into a database it is one block of html code in a memo field.

      How can I do a select statement that gets everything before the id=systemreadmore link?

      Something in theory like

      Select * from articles
      where left(articlebody, <a href="" id=systemreadmore>Read More</a> )

      Any ideas?
        • 1. Re: Sql Select Problem
          Dan Bracuk Level 5
          Check your db documentation for functions that sound like substring or position.
          • 2. Re: Sql Select Problem
            craigkaminsky Level 3
            Because of the memo field you note in your summary, is it safe to assume this is an Access DB? If this is an Access DB, is there a particular benefit to running this sort of filter at the DB level?

            In my previous experience, MS Access was not only a slower database (than SQL Server or MySQL, for example). but it isn't a true database server and easily locks up when experiencing higher loads.

            It might be easier, and without any significant a performance, to manipulate the article body content/string in CF.

            <cfquery name="rs" datasource="#dsn#>
            select article_id, article_body
            from articles
            where article_id = #id#

            Once you get the record you want, you can use a variety of ColdFusion's string functions (see live docs for more options) to pull the section you want from the article_body.

            // quick and dirty -- certainly more effective ways
            readMorePos = ReFindNoCase("id=systemreadmore",rs.article_body);
            readMoreBody = RemoveChars(rs.article_body,1,readMorePos-1);

            • 3. Re: Sql Select Problem
              siriiven Level 1
              Thanks a lot guys for the ideas. I will have to look into these and see what I can come up with. I don't really need to do this processing on the database side, I can do it through CF logic also.