How to make a single row from many.

Frunkie · #1 (**permalink**) 10-25-09, 17:03

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

Frunkie · #2 (**permalink**) 10-25-09, 17:20

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??

r937 · #3 (**permalink**) 10-25-09, 19:02

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

sco08y · #4 (**permalink**) 10-25-09, 22:14

Quote:

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.

r937 · #5 (**permalink**) 10-25-09, 22:37

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

Frunkie · #6 (**permalink**) 10-26-09, 03:53

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?

Frunkie · #7 (**permalink**) 10-26-09, 05:17

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.

gicimpspi · #8 (**permalink**) 10-26-09, 05:51

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);
}

r937 · #9 (**permalink**) 10-26-09, 07:13

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

Frunkie · #10 (**permalink**) 10-27-09, 08:03

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.

r937 · #11 (**permalink**) 10-27-09, 08:08

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

Frunkie · #12 (**permalink**) 10-27-09, 16:56

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.

sco08y · #13 (**permalink**) 11-14-09, 13:55

Quote:

Originally Posted by Frunkie

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech