StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL virtual fields for duplicate results
primarykey
Id
19554109
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2013-10-23T23:20:19.670
FavoriteCount
0
LastActivityDate
2013-11-02T20:29:23.950
LastEditDate
2013-11-02T20:29:23.950
LastEditorUserId
1162844
OwnerUserId
1162844
ParentId
0
PostTypeId
1
Score
0
ViewCount
88
LastEditorDisplayName
text
Body
I apologize in advance that I don't know the terminology of the tools I'm trying to use. I have a table of events with a startdate field (among others) and a related repeats table with a reference to the event id. The repeats table stores the days of the week on which the event repeats and whether it's monthly, weekly, etc. What I'm hoping to do is duplicate the repeating events within the SQL query so my final result will have the the same event in different places when ordered on start date, so I can limit the results for proper pagination. I'm looking at creating virtual tables and cloning tables documentation, but I'm having trouble applying the examples to my situation. Update: Hopefully I can elaborate on this. The basics of what I have now is <code>SELECT * FROM 'events' WHERE 'start_date' >= TODAY() ORDER BY 'start_date LIMIT 20</code> which gets me every event from today on, but I'm paginating the results so only 20 are displayed at a time. What I would like to do is create a temporary 'virtual' table with the events which have an associated repeat entry, on which I will change the <code>start_date</code> based on the repeat information. So if it's a weekly repeat, this second table would be filled with identical events except that each <code>start_date</code> would be 7 days from the last. Then I could do a join on these two tables, limit those results to the 20 pagination limit I want, and have a query result with the events in the correct place and easy to perform pagination on. I understand that creating a function in mySQL might be on the right track as I imagine I would have to loop through some information for adding to dates. I only know the level of SQL one picks up by writing in PHP, so functions are a bit out of my scope, though it doesn't seem that it'll be too hard to pick up with a little reading. I'm more confused about how I would create a fake table, add entries to it in a loop and then use a join on it to merge it with the first query. I'm also beginning to wonder about the overhead for doing this in mySQL and, should I be successful in getting this to work, how I might cache these results, though it's only an afterthought right now. Thanks to those who are trying to help me, I'm having trouble getting this question into words for some reason.
Tags
<mysql>
Title
MySQL virtual fields for duplicate results
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USaviemet
UserOwnerUserId
1. USaviemet
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.