StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODisplay data of one table based on Id of other using SQL, PHP (RSS Style)
primarykey
Id
7797755
data
AcceptedAnswerId
7882899
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-10-17T18:10:43.840
FavoriteCount
0
LastActivityDate
2011-10-26T15:37:28.387
LastEditDate
2011-10-26T15:37:28.387
LastEditorUserId
988093
OwnerUserId
988093
ParentId
0
PostTypeId
1
Score
0
ViewCount
1460
LastEditorDisplayName
text
Body
probably a straightforward to the trained eye but I have agonised over this for too long now.. I have two db tables designed to generate RSS feeds to the browser: 'Channel' and 'Item': <hr> <pre><code>**Channel Table** Primary Key: Id Id Title Desc Link 1 News latest www... 2 Sport latest www... 3 Gossip latest www.. </code></pre> As you can see there is more than one channel, which is central to my query.. <pre><code>**Item Table** Primary Key: Id, Index Key: Chan_Id Id Title Desc Link Chan_Id 1 Footie Liv-ManU1 www... 2 2 Cricket India-Eng.. www... 2 3 G5 Summit G5 Talks.. www... 1 4 X-Factor Simon Cowell.www... 3 5 Iraq MOD says... www... 1 6 M Jackson Court trial..www... 3 </code></pre> <hr> Now I have seen scripts where an RSS feed can be displayed thru PHP and it works great - the main problem is all examples I have seen assumes you have only One channel to attach your items to.. <pre><code>News G5 Summit G5 Talks...... Iraq MOD says...... </code></pre> seeing as I have more than one channel, what I am looking for is to first print out multiple channels with related content: <pre><code>News G5 Summit G5 Talks...... Iraq MOD says...... Sport Footie Liv-ManU1..... Cricket India-Eng..... Gossip X-Factor Simon Cowell.. M Jackson Court trial.. </code></pre> then ultimately dump this data into separate xml files, depending on subject matter (Footie.xml, Cricket.xml, etc.) This part just needs a little fine tuning, as I have a formula for this using PHP DOM to construct and save: <pre><code>$doc->save(row['chan_title']) </code></pre> not the exact syntax but method I use works, but shows only channels NOT items. The central issue is two-fold: <ol> <li>SQL Query: how would I go about selecting the data from the db, as a join? If so, an Inner Join similar to this: SELECT Channel.Id, Channel.Title, Item.Title, Item.Desc, Item.Chan_Id FROM Channel INNER JOIN Item ON Channel.Id=Item.Chan_Id ORDER BY Channel.Id</li> </ol> or maybe use a sub-query? <ol> <li>How do I then get the data from two tables and display it with PHP? Would it be easier to wrap up methods in classes and do it that way? I've tried to use a <code>while( channel query)</code> inside <code>while( item query)</code> inside <code>foreach( item)</code> but only displayed channel info. Possibly the wrong permutation but I've been experimenting with a view of finding a solution but need some expert help so I can move on!!</li> </ol> Your help will be appreciated, I will try and respond as soon as I can. EDIT - Wow, 2 days and no response, can't be that hard!! Well actually, it isn't. I didn't need it to be so complicated, the main point was more about the correct syntax. Still not found a true solution but getting close - I decided to use two queries, with two <code>while</code> statements, one inside the other: <pre><code>$detQry = sprintf("SELECT * FROM ".$detailsTable); $itQry = sprintf("SELECT id, title, description FROM ".$itemsTable." WHERE chan_id = ".$detailsTable.".id ORDER BY chan_id ASC LIMIT 0, 30 "); $result = mysql_query($detQry) OR die ('Could not execute query: ' . mysql_error()); $run = mysql_query($itQry) OR die ('Could not execute query: ' . mysql_error()); while($row = mysql_fetch_assoc($result)) { echo $row['title'].' '; echo $row['description'].' '; while(list($ttl, $dsc, $pbd) = mysql_fetch_array($run)) { echo $ttl.' '; echo $dsc.' '; echo $pbd.' '; } } </code></pre> Problem is, when I run this script only the Channel 'title ' and 'description' print.. what am I not doing to make this work? Could it be to do with the <code>list()</code> statement? Any help welcomed to shorten the agony... EDIT 2 - Resolved, thanks to a suggestion from Woody. It took a long time, but finally got the specific result I was looking for after I tested the formula given by Woody. There are a few One Channel to 'n' Items scripts, but I needed a 'n' Channel to 'n' Items - this is perfect and flexible. With my current level of experience it would've taken a long time to come up with the answer like this - thanks again. <pre><code>(some db_connect stuff..) $query = "SELECT * FROM webref_rss_items, webref_rss_details WHERE webref_rss_details.id = webref_rss_items.chan_id ORDER BY webref_rss_details.id"; $t_result = mysql_query( $query ) OR die ('Could not execute query: ' . mysql_error()); $rowCount = mysql_num_rows( $t_result ); $cat = -1; // last category for($r = 0; $r < $rowCount; $r++) { $row = mysql_fetch_array( $t_result ); if($row['id'] != $cat) { $cat = $row['id']; //create doctype $doc = new DOMDocument('1.0','UTF-8'); header('content-type: text/xml'); //create rss root with values $root = $doc->createElement('rss'); $root->setAttribute('version', '2.0'); $doc->appendChild($root); //create channel element $channel = $doc->createElement("channel"); $root->appendChild($channel); // add node for each record, create the text nodes for element and add text $title = $doc->createElement('title', $row['title']); $channel->appendChild($title); $link = $doc->createElement('link', $row['link']); $channel->appendChild($link); $desc = $doc->createElement('description', $row['description']); $channel->appendChild($desc); $lang = $doc->createElement('language', $row['lang']); $channel->appendChild($lang); $image = $doc->createElement('image'); $image = $channel->appendChild($image); $imttl = $doc->createElement('title', $row['image_title']); $image->appendChild($imttl); $imlink = $doc->createElement('link', $row['image_link']); $image->appendChild($imlink); $imdesc = $doc->createElement('description', $row['image_desc']); $image->appendChild($imdesc); $imwidth = $doc->createElement('width', $row['image_width']); $image->appendChild($imwidth); $imheight = $doc->createElement('height', $row['image_height']); $image->appendChild($imheight); $imurl = $doc->createElement('url', $row['image_url']); $image->appendChild($imurl); $manEdit = $doc->createElement('managingEditor', $row['man_edit']); $channel->appendChild($manEdit); $webmaster = $doc->createElement('webmaster', $row['webmaster']); $channel->appendChild($webmaster); $copyright = $doc->createElement('copyright', $row['copyright']); $channel->appendChild($copyright); $pubDate = $doc->createElement('pubDate', $row['ch_pubDate']); $channel->appendChild($pubDate); $lastBuild = $doc->createElement('lastBuildDate', $row['lastBuild']); $channel->appendChild($lastBuild); $category = $doc->createElement('category', $row['category']); $channel->appendChild($category); $generator = $doc->createElement('generator', $row['generator']); $channel->appendChild($generator); $docs = $doc->createElement('docs', $row['docs']); $channel->appendChild($docs); $cloud = $doc->createElement('cloud', $row['cloud']); $channel->appendChild($cloud); $ttl = $doc->createElement('ttl', $row['ttl']); $channel->appendChild($ttl); $rating = $doc->createElement('rating', $row['rating']); $channel->appendChild($rating); $textInput = $doc->createElement('textInput', $row['textInput']); $channel->appendChild($textInput); $skipHours = $doc->createElement('skipHours', $row['skipHours']); $channel->appendChild($skipHours); $skipDays = $doc->createElement('skipDays', $row['skipDays']); $channel->appendChild($skipDays); } //Dynamically Generated Items $item = $doc->createElement('item'); $item = $channel->appendChild($item); $it_ttl = $doc->createElement('title', $line['title']); $item->appendChild($it_ttl); $it_desc = $doc->createElement('description', $line['desc']); $item->appendChild($it_desc); $it_link = $doc->createElement('link, $line['link']'); $item->appendChild($it_link); $it_guid = $doc->createElement('guid', $line['guid']); $item->appendChild($it_guid); $it_pubDt = $doc->createElement('pubDate', $line['pubDate']); $item->appendChild($it_pubDt); $it_auth = $doc->createElement('author', $line['author']); $item->appendChild($it_auth); $it_ctg = $doc->createElement('category', $line['category']); $item->appendChild($it_ctg); $it_cmnt = $doc->createElement('comments', $line['comments']); $item->appendChild($it_cmnt); $it_encl = $doc->createElement('enclosure', $line['enclosure']); $item->appendChild($it_encl); $it_src = $doc->createElement('source', $line['source']); $item->appendChild($it_src); $mast = $row['ch_title']; $saw = explode(" ", $mast); $chip = $saw[0].'-'.$saw[1]; // saw1-saw2 $beam = "../../feed/".$chip.".xml"; echo 'Wrote: ' . $doc->save($beam) . ' bytes. '; } } ?> </code></pre> Works perfectly!
Tags
<php><mysql><rss>
Title
Display data of one table based on Id of other using SQL, PHP (RSS Style)
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USDan Braun
UserOwnerUserId
1. USDan Braun
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. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

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.