Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

moderately advanced SQL question

Old 09-12-05, 03:20 PM
  #1  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Feb 2000
Location: on the mountain
Posts: 7,849
moderately advanced SQL question

I am using mySQL and would like results returned in a certain way, but i can't figure it out besides doing a bunch of server side processing (which seems like what I may have to end up doing).

Basically i have 2 tables set up like this:
-------------------------------------

data_set
--------
id
set_name
timestamp

data
-----
id
data_set_id
data_type
data

basically a data set is any grouping of data. it could be for example (name, age, sex). so each time a data set is submitted, we get something like this inserted, with the actual data different:

data_set (1,'my_set', '#timestamp#')

and then 3 records would be inserted into data

(1, 1, text, 'josh')
(2, 1, int, '12')
(3, 1, text, 'yes')

So lets say I select some data_sets:

SELECT * FROM data_set

1, my_set, *timestamp*
2, other_set, *timestamp*

now i want to get the actual data that was submitted (there will be 3 rows for each set because there were 3 data itmes associated w/each set)

SELECT data FROM data WHERE ID in(1,2) ORDER BY my_set

returns something like this

josh
15
yes
samantha
27
allthetime


the problem is that I have 3 rows of results for each actual record, because i just throw one record for every data item because the data set is dynamic. what i would like to return is the records associated w/the same data_set in one row... just the data field is all i need, so it would be like

josh, 15, yes
samantha, 27, allthetime

basically combining all rows with the same parent into one record with a column for what used to be a row.

can SQL do this???? or do I need to dynamically build a query? i am thinking perhaps some type of nested queries? but i'm not sure.

that was extremely confusing i appologize.
moorehed is offline  
Old 09-12-05, 03:41 PM
  #2  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
You could probably do it with a union statement, selecting each record like:

select [desired fieldname] from [tablename] where ID = 1 and timestamp = [whatever]
union select [desired fieldname] from [tablename] where ID = 2 and timestamp = [whatever]
union select [desired fieldname] from [tablename] where ID = 2 and timestamp = [whatever]
X is offline  
Old 09-12-05, 04:01 PM
  #3  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Feb 2000
Location: on the mountain
Posts: 7,849
yeah i know i could do it like that... but the fields are dynamic, so i would have to build a new sql statement everytime... i was hoping there was some SQL keyword where i could have a row hold multiple rows basically.......
moorehed is offline  
Old 09-12-05, 04:04 PM
  #4  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
I guess I don't understand. What fields are dynamic?
X is offline  
Old 09-12-05, 04:06 PM
  #5  
Moderator
 
Groucho's Avatar
 
Join Date: Mar 2000
Location: Salt Lake City, Utah
Posts: 70,680
What exactly are you trying to do? This seems inefficient to me.
Groucho is offline  
Old 09-12-05, 04:08 PM
  #6  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,704
Yeah, I don't understand why he's creating three records. But if three or fewer identifiable records are always being created it shouldn't be difficult to union them.
X is offline  
Old 09-12-05, 04:12 PM
  #7  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Feb 2000
Location: on the mountain
Posts: 7,849
okay here is what is going on.

i have dynamic forms so that any user can make a form on his website with any fields he wants. when someone submits a form, it needs to store all the info in a database. because the form is dynamic, i can't have a database set up like this:

id, name, age, sex

because it needs to work no matter what form fields the user ads. so i just have a general table that holds data item (1 record = 1 data item). so it throws 3 separate data items in, and then gives them a parent and they all share the parents foreign key.

when i query them though, i would like it to actually appear as 1 record for one submission... but i can't do it. lol
moorehed is offline  
Old 09-12-05, 04:13 PM
  #8  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Feb 2000
Location: on the mountain
Posts: 7,849
if you can think of a better way to store information in a database that is submitted via form, but the form fields could be anything, let me know. i debated putting it in w/xml or something but that sounded like a PITA
moorehed is offline  
Old 09-12-05, 04:55 PM
  #9  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
So here's the problem: You want the returned record to have a variable size, depending (solely) on how many records exist for that data set ID. In other words, you want each record to have count(data_set_id) fields.

As far as I know, that's really not possible. Not in a clean way for a potentially unlimited width of data records. Consider what happens if one record has more fields than another - do you really want a jagged data set? (record 1 has 3 fields, record 2 has 6 fields, record 3 has 5 fields, etc.)

What you can do is assume a fixed maximum number of fields - say, 10. Just enumerate the fields from 1 to 10, and then assemble the result set with that number of attributes. Of course, setting this field higher imposes incrementally more work to generate the result query, so performance will drop - you'll have to tweak it.

- David Stein
sfsdfd is offline  
Old 09-12-05, 05:00 PM
  #10  
Moderator
 
Groucho's Avatar
 
Join Date: Mar 2000
Location: Salt Lake City, Utah
Posts: 70,680
It almost sounds like you're building a web-based GUI front-end for MySQL.

But if the user can customize the kinds of forms they make, why not use the "CREATE TABLE" functionality of MySQL to create the exact table they need for their data? Does that make sense?
Groucho is offline  
Old 09-12-05, 05:49 PM
  #11  
DVD Talk Legend
 
Mordred's Avatar
 
Join Date: Jan 2000
Location: Austin, TX
Posts: 12,208
Originally Posted by Groucho
It almost sounds like you're building a web-based GUI front-end for MySQL.

But if the user can customize the kinds of forms they make, why not use the "CREATE TABLE" functionality of MySQL to create the exact table they need for their data? Does that make sense?
That's a decent option and one I would use if I had to do it all in SQL. Of course doing it all in SQL would be silly... I'd just wrap the MySQL in some PHP code to format the data exactly how I want it which wouldn't take more than a few minutes time. I'm assuming moorehed has some reason for not just wrapping the SQL though, although I can't figure out what it would be.
Mordred is offline  
Old 09-13-05, 10:50 AM
  #12  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Feb 2000
Location: on the mountain
Posts: 7,849
Originally Posted by sfsdfd
So here's the problem: You want the returned record to have a variable size, depending (solely) on how many records exist for that data set ID. In other words, you want each record to have count(data_set_id) fields.

As far as I know, that's really not possible. Not in a clean way for a potentially unlimited width of data records. Consider what happens if one record has more fields than another - do you really want a jagged data set? (record 1 has 3 fields, record 2 has 6 fields, record 3 has 5 fields, etc.)

What you can do is assume a fixed maximum number of fields - say, 10. Just enumerate the fields from 1 to 10, and then assemble the result set with that number of attributes. Of course, setting this field higher imposes incrementally more work to generate the result query, so performance will drop - you'll have to tweak it.

- David Stein
thanks for the info guys... looks like i am just going to have PHP sort out the results into an array and then deal with it there... i just didn't want to do it that way if there was a quick easy way to do it with sql... but there's not
moorehed is offline  
Old 09-13-05, 12:47 PM
  #13  
DVD Talk Legend
 
Mordred's Avatar
 
Join Date: Jan 2000
Location: Austin, TX
Posts: 12,208
Your one issue is that you don't know how many fields there are for each data set. I would propose adding a num_fields field to data_set, otherwise you're going to have parsing problems.

Once that's done it's going to be really easy to do that in PHP.

Logically it'd look like:

SELECT data_set
open table
SELECT data WHERE data.id = data_set.id
While you have records
  add row
  for (i=0;i < num_fields;i++)
    add column with data.data
end while
close table
Mordred is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.