Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: How to make a single row from many.

    I have a table that simply holds links. With this table I want to build an unordered list of these links. These links should be 100% dynamic in that I don't want to hard code any of the headers (Technical, Fun Links etc) into the application code. "Header" and "TXT" will be what the user will see. All of the other data in this table will be used in building the links.

    Here is some sample data:

    Code:
    HEADER		TXT		TITLE			ALT		LINK
    Favorites	Google		Search Google		Google		google.com
    Favorites	Yahoo		Search Yahoo		Yahoo		yahoo.com
    Fun		You Tube	YouTube			YouTube		youtube.com
    Technical	DB Forums	DB Forums		DB Forums	dbforums.com

    Code:
    Technical	<----A single header
        DBForums	<----A single link
    
    Fun		<----A single header
        YouTube	<----A single link
    
    Favorites	<----A single header
        Yahoo	<----The first link
        Google	<----The second link
    Building a result like this with application code is not a problem until I get into multiple links per header. I'm wondering if there is a way to have SQL query this table and return results like this or if I would need to do this with application code. Any help would be greatly appreciated.



    Thanks,
    Frank

  2. #2
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I originally thought that it would be best if I could get all headers with the corresponding links on a single line but I'm starting to think that isn't such a good idea anymore. Maybe a different design? Maybe EAV??

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    Maybe EAV??
    you go right now and wash your mouth out with soap, you naughty boy

    the query that you want to run is the most basic, simple SELECT you can imagine, but it has to include an ORDER BY clause

    then grouping the links by header, and indenting them, is a job for the application layer

    your table design is fine

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Maybe a different design? Maybe EAV??
    Your table already *is* an EAV table, more or less. What you're trying to do, "make a single row from many" is precisely what people ask when they are stuck with EAV and want to extract a regular table from it.

    In this case, I think it makes sense to keep it the way you have it. Becuase you don't want a table, you want output. Is the output supposed to be HTML? It's such a straightforward transformation from the table to the HTML there's no real reason to try and do this in SQL. Just sort by the headers and iterate through. When the header changes, spit out the tag for the header.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sco08y
    Your table already *is* an EAV table, more or less.
    no it isn't

    if it were an EAV table, it would look like this --
    Code:
    link          attribute  value
    google.com    header     Favorites
    google.com    txt        Google
    google.com    title      Search Google
    google.com    alt        Google
    yahoo.com     header     Favorites
    yahoo.com     txt        Yahoo
    yahoo.com     title      Search Yahoo
    yahoo.com     alt        Yahoo
    youtube.com   header     Fun
    youtube.com   txt        You Tube
    youtube.com   title      Youtube
    youtube.com   alt        Youtube
    dbforums.com  header     Technical
    dbforums.com  txt        DB Forums
    dbforums.com  title      DB Forums
    dbforums.com  alt        DB Forums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    lol.. Not to worry Rudy, I just couldn't bring myself to model such a thing.

    As far as this query is concerned, I have no idea why I can't seem to get this data to show the way I want it to. What I consistently get back from the database, and is the correct result set is:

    Code:
    HEADER		TXT		TITLE			ALT		LINK
    Favorites	Google		Search Google		Google		google.com
    Favorites	Yahoo		Search Yahoo		Yahoo		yahoo.com
    Fun		You Tube	YouTube			YouTube		youtube.com
    Technical	DB Forums	DB Forums		DB Forums	dbforums.com
    If I had a WHERE clause in my SQL, where I could request the headers I wanted, this would be a piece of cake but because the link headers are "dynamic", that is, they can be created "on the fly", it complicates things a bit; at least in the application code.

    I thought about doing two separate queries. One to get the distinct headers, return those headers back to my scripts then use those values in a second query to get the associated links. This is less than optimal though.

    I know you have some programming experience. How might you solve this?

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Well, I got it. I just reorganized my array of values once it was returned from the database. Now, each header has the correct "sub" array beneath it. Here is what I was after:

    Code:
                [0] => Array
                    (
                        [Favorites] => Array
                            (
                                [0] => Array
                                    (
                                        [txt] => Google
                                        [title] => Google
                                        [alt] => Google
                                        [link] => google.com
                                    )
                                [1] => Array
                                    (
                                        [txt] => Yahoo
                                        [title] => Search Yahoo
                                        [alt] => Yahoo
                                        [link] => yahoo.com
                                    )
                            )
                        [Fun] => Array
                            (
                                [0] => Array
                                    (
                                        [txt] => You Tube
                                        [title] => Broadcast Yourself
                                        [alt] => YouTube
                                        [link] => youtube.com
                                    )
                            )
                        [Technical] => Array
                            (
                                [0] => Array
                                    (
                                        [txt] => DBForums
                                        [title] => DBForums
                                        [alt] => DBForums
                                        [link] => DBForums
                                    )
                            )
                    )
    Thank you Rudy and sco08y.

  8. #8
    Join Date
    Oct 2009
    Posts
    1
    Here is, in my opinion, the most interesting part:
    MySQL Code:

    //If first time key in date run statement
    if(first == 0){
    ++first;//Track if is the first record key in
    items[i] = new Item(itemNumber, itemDesc, sellPrice, quantity);
    }
    //Else continue key in record
    else{
    entries = items[0].getEntries();//Get number of entries entered
    for(j=0; j<entries; ++j){

    duplicate = items[j].equals(itemNumber, itemDesc, sellPrice, quantity);//Check for duplication of entries

    if(duplicate == true) {
    Print.duplicateError();
    --i;
    --looping;
    break;
    }
    }
    if(duplicate == false)//else save record
    items[entries] = new Item(itemNumber, itemDesc, sellPrice, quantity);
    }

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    I know you have some programming experience. How might you solve this?
    i don't do php, i do coldfusion

    in coldfusion, you execute the query like this --
    Code:
    <CFQUERY NAME="getlinks" DATASOURCE="#dsn#">
    SELECT header
         , txt
         , title
         , alt
         , link
      FROM links
    ORDER
        BY header
         , link
    </CFQUERY>
    then to display the results in the nice indented list style, let's use an html h2 tag for the headers and unordered lists for the links --
    Code:
    <CFOUTPUT QUERY="getlinks" GROUP="header">
    <h2>#header#</h2>
    <ul>
      <CFOUTPUT>
      <li>#link# -- #txt# #title# #alt#</li>
      </CFOUTPUT>
    </ul>
    </CFOUTPUT>
    simple, yes?

    compare the cleanliness, simplicity, and elegance of coldfusion to the cluttered, detailed, verbose php solution as posted by gicimpspi

    gicimpspi, it might have helped your cause a bit if you had actually used frank's data model rather than your own items and descriptions and prices
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    simple, yes?

    compare the cleanliness, simplicity, and elegance of coldfusion to the cluttered, detailed, verbose php solution as posted by gicimpspi

    gicimpspi, it might have helped your cause a bit if you had actually used frank's data model rather than your own items and descriptions and prices
    ColdFusion is amazingly straightforward. It almost looks like a templating system that I can use without even having to dive into a manual. I think that gicimpspi's code is VB or something else but it isn't PHP. I think he may be plugging his sig.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    I think he may be plugging his sig.
    sig spamming is beginning to happen with increasing frequency on this site

    i'm on other forum sites as well as this one, and i have seen the deleterious effects of allowing it to go unchecked
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    sig spamming is beginning to happen with increasing frequency on this site

    i'm on other forum sites as well as this one, and i have seen the deleterious effects of allowing it to go unchecked
    Your right Rudy. It's the result of what happens when "good" moderators leave here and go to other sites.

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Frunkie View Post
    ColdFusion is amazingly straightforward. It almost looks like a templating system that I can use without even having to dive into a manual. I think that gicimpspi's code is VB or something else but it isn't PHP. I think he may be plugging his sig.
    Nah, not VB, VB doesn't do braces. The syntax could be C#, Java, or C++. The preincrement operators (++j and --i) are often used in C++ because they avoid an extra copy over postincrement, i++ and j++. OTOH, you see the .equals method a lot in Java. Without seeing any function declarations it's hard to say for sure. Whatever language in, it's crap code; each of those languages has proper container classes that they're not using.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •