Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 24th, 2004, 04:46 PM
Registered User
 
Join Date: Apr 2004
Location: Knoxville, TN, .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Is there ANY way to do this???

I have a database with three tables. They contain the following relevant fields:

stories:
sid (unique story ID#)

story_author:
sid (relates to the stories table - will not be unique)
uid

authors:
uid (unique Author ID, relates to story_author table)
aname (text for the name of the author)

A story may have more than one author, such that story #1 may have two entries in the story_author table.

Sample information:
stories
sid sname
1 Our First Story
2 My Loner Story

story_author
sid uid
1 1
1 2
2 2

authors:
uid aname
1 Joe
2 Bob

My Problem: I wish to produce, in a single row, both authors of the multi-author story, such that the data might look like this:
sid sname aname
1 Our First Story Joe, Bob

Is this even possible? If so, HOW???

Thanks for any help,
James

Reply With Quote
  #2 (permalink)  
Old April 25th, 2004, 06:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I don't think you can do that in MySql as it does not support functions as MSSQL does.

Anyways you can take a look at this post to have an idea.
http://p2p.wrox.com/topic.asp?TOPIC_ID=1787
I made you table structure look similar to what it shows in the above post. But unable to proceed from there as the post deals with Functions there on, which is not supported in MySql(AFAIK)

SELECT a.sid, b.sname, c.aname
FROM story_author a, stories b, authors c
where a.sid=b.sid and a.uid=c.uid

Sid Sname aname
------------------------------
1 Our First joe
1 Our First Bob
2 Lone Story Bob


Also you can take a look at the cross-tab report at MS site, to see if that suits you. I tried this, but didnot work as I expected, may be I am doing something wrong.

http://msdn.microsoft.com/library/de...qd_14_04j7.asp

Let us know if you get it solved.

Cheers!

-Vijay G
Reply With Quote
  #3 (permalink)  
Old April 25th, 2004, 06:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I would suggest you to do this from your client application/front end, which would be easier and better way to get that result.

-Vijay G
Reply With Quote
  #4 (permalink)  
Old April 27th, 2004, 04:39 PM
Registered User
 
Join Date: Apr 2004
Location: Knoxville, TN, .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, turned out doing it from the front end (which happens to be PHP in this case) turned out to be the simplest way to get the desired result (not that getting that result was by any means simple!)

For the curious, I ended up defining a class to hold story information and then filled in each object's information with the query results, using object functions to return the desired output formatting.

Thanks for the assist... at least I didn't waste more time looking for a solution that wasn't there.

James


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off




All times are GMT -4. The time now is 10:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.