sw9 Posted November 21, 2007 Share Posted November 21, 2007 I have two mysql statements I'm running, and then I am sending out PHP mail with the output. The issue is that I have some 1 to many data types that are causing too much output if I try to combine my statements. Let me try to explain. First, I am trying to send an email digest of videos that have been added to my website. Each video has some singular qualities like Length, Description, Video Name. Beyond that, I have categories set up on my site where a user adding a video can pick Producers, Subjects, etc. For these, each video can have multiple values. However, in my email digest I would want it to look like this: Video Name: My Brilliant Video Length : 2 hours Description: In this video, you'll learn how to be brilliant. Producers: Jim Smith, Joe Smith Subjects: Math, Science, Gardening, Intelligence So right now I have two mySQL statements. 1 that pulls all the initial singular data. And another that pulls the Category Name (Producers, Subjects) and then lines them up with the results (Jim Smith, Joe Smith, Math, Science, etc). But I don't know how I can combine all of this info to make my digest look like it does above. Here is my code: <?php $myDate = date("l \\t\h\e jS \of F Y"); $sql = "SELECT DISTINCT a.nid, a.type, a.title, a.uid, a.status, a.created, a.changed, b.nid, b.field_length_value, b.field_description_value, c.uid, c.name FROM node AS a LEFT JOIN content_type_vmx_program AS b ON (a.nid = b.nid) LEFT JOIN users AS c ON (a.uid = c.uid) WHERE a.type = 'vmx_program' AND a.status = '1' AND a.changed >= '".strtotime("1 October 2007 08:20:36")."' limit 50"; $rval = mysql_query($sql); $rows = mysql_num_rows($rval); while ($data = mysql_fetch_assoc($rval)) { $message = "<html><body><br><br><b>There were ".$rows." new submissions on ".$myDate."</b>."; $gtitle = strtoupper($data["title"]); $Games[$gtitle] = "<br><br> Submitted By: ".strtoupper($data["name"])."<br> Program Title: ".$data["title"]."<br>Length: ".$data["field_length_value"]." <br>Program Description: ".$data["field_description_value"]."<br>"; } $message .= implode ("<br><br><hr>", $Games ); // This next section is my second sql statement -- I am not sure how to get it to associate correctly. But the below d.nid would // need to be equal to the first statement's a.nid while also not printing out redundant data... $sql2 = "SELECT d.nid, d.tid, e.vid, e.tid, e.name as termname, e.vid, f.vid, f.name AS category, g.nid, g.type, g.title FROM term_node AS d LEFT JOIN term_data AS e ON (d.tid = e.tid) LEFT JOIN vocabulary AS f ON (e.vid = f.vid) LEFT JOIN node AS g ON (d.nid = g.nid) WHERE g.type = 'vmx_program' AND f.vid = e.vid ORDER BY g.title"; $rval2 = mysql_query($sql2); while ($catdata = mysql_fetch_assoc($rval2)) { // This prints something like "Producer: Joe Smith" echo $catdata["category"] . ": ". $catdata["termname"]."<br>"; } $to = "me@me.com"; $from = "you@you.net"; $subject = "New Videos!"; $headers = "From: $from\r\n"; $headers .= "Content-type: text/html\r\n"; $message .= "</body></html>"; mail($to, $subject, $message, $headers); ?> I would greatly appreciate any help you can give! Thanks. Quote Link to comment Share on other sites More sharing options...
sw9 Posted November 26, 2007 Author Share Posted November 26, 2007 perhaps this has gotten lost in all the holiday festivities....i'm really stumped on this; any suggestions would make my day! thanks. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 26, 2007 Share Posted November 26, 2007 Well, I gave it about 30 mins trying to construct your schema and decide which of around 20 columns you select map to the five you want to show. That's before I start constructing tables and test data. Sorry, timed out. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.