Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Luke Johnson 61 posts 80 karma points
    Aug 06, 2011 @ 01:09
    Luke Johnson
    0

    Access specific SQL data with Query String?

    I am builidng a news area, and want to be able to send visitors to a specific news story loaded from a SQL database. Is it best to use a query string to access the news story data?

    On a landing page, I have set up the links like this:

    <xsl:attribute name = "href">/bcast/news/article.aspx?id=<xsl:value-of select = "id" />

    With article.aspx?id=<xsl:value-of select="id" /> I am hoping to send visitors to a specific story by referring to the id of the story from the SQL database.

    However, my article.aspx page must be missing something. Here is the code thus far:

    <xsl:param name="currentPage"/>

    <!-- SQL Here -->
      <xsl:variable name = "newsStory" select="SQL:GetDataSet('DBname', 'select id, date, title, description, thumbnail, articletype from tableName', 'newsStory')"/>
     
      <xsl:template match="/">

        <h1><xsl:value-of select = "title" /></h1>
        <p><xsl:value-of select = "date" /></p>
        <div><xsl:value-of select = "description" /></div>
        <xsl:value-of select="umbraco.library:RequestServerVariables('QUERY_STRING')"/>

        </xsl:template>

    The query string populates to the page using:

    <xsl:value-of select="umbraco.library:RequestServerVariables('QUERY_STRING')"/>

    But I must need to add something to:

    <h1><xsl:value-of select = "title" /></h1>
    <p><xsl:value-of select = "date" /></p>
    <div><xsl:value-of select = "description" /></div>

    Is there some sort of "by ID" property I can add that will fetch the data from the specific story? What needs to be added? Do I need parameters to point to the story by ID number?

     

  • Bo Damgaard Mortensen 709 posts 1186 karma points
    Aug 08, 2011 @ 23:02
    Bo Damgaard Mortensen
    0

    Hi Luke,

    Correct me if I'm wrong here, but to me it seems like you need the WHERE clause in the database query? If you want to get hold of a specific newitem, that is.

    So your query would look something like this:

    name="newsStory"select="SQL:GetDataSet('DBname', 'select id, date, title, description, thumbnail, articletype from tableName WHERE id = YourQueryStringId', 'newsStory')"/>

    Also, as a sidenote, you should be able to get hold of the querystring from XSLT by calling the RequestQueryString() method from umbraco.library:

    <xsl:value-of select="umbraco.library:RequestQueryString('id')"/>

    Hopefully this makes sense :-)

    All the best,

    Bo

  • Luke Johnson 61 posts 80 karma points
    Aug 09, 2011 @ 19:47
    Luke Johnson
    0

     

    The addition of "WHERE id =" worked when I input a hardcoded value, e.g., WHERE id = 1037

    <xsl:variable name = "storyID" select = "umbraco.library:RequestQueryString('id')" />
    <xsl:variable name = "newsStoryContent" select="SQL:GetDataSet('Database', 'SELECT content from webcontent_news WHERE id = 1037', 'newsStoryContent')"/>

    <xsl:template match="/">

    <xsl:value-of select = "$newsStoryContent" disable-output-escaping="yes" /><!-- content -->

    But it fails when I try using a variable instead of a hardcoded value:

     

     

    ....'SELECT content from webcontent_news WHERE id = $storyID', 'newsStoryContent')"/>

    <xsl:template match="/">

    <xsl:value-of select = "$newsStoryContent" disable-output-escaping="yes" /><!-- content -->

     

     

    Is there something wrong with my syntax? I need to be able to pick up the incoming ID numbers dynamically. I have several thousand media items (news stories, photos, videos, podcast mp3's, etc...) that I want to retrieve from SQL tables.

     

  • Chriztian Steinmeier 2698 posts 8107 karma points MVP 3x admin c-trib
    Aug 09, 2011 @ 22:11
    Chriztian Steinmeier
    0

    Hi Luke,

    You just need to remember that you're actually still in XSLT, though you're building a string that's to be interpreted as SQL - so, in order to have the value of $storyID inserted, you need to use XSLT/XPath syntax:

    <xsl:variable name = "newsStoryContent" select="SQL:GetDataSet('Database', concat('SELECT content from webcontent_news WHERE id = ', $storyID), 'newsStoryContent')"/>

    /Chriztian

  • Luke Johnson 61 posts 80 karma points
    Aug 10, 2011 @ 00:01
    Luke Johnson
    0

    That did it! Thank-you.

  • ramanathan 1 post 71 karma points
    Oct 03, 2016 @ 11:40
    ramanathan
    0

    i need to access the SQLdatabase by xslt code, i searched in web tutorials but unable to found . if anybody having idea about this plz share it .

Please Sign in or register to post replies

Write your reply to:

Draft