Php And Mssql Queries From 3 Tables At Once

[jamina1]

Hi guys. I'm trying to write out a script that will show certain information based on the id numbers fed to it using Get. Url looks like this blah.php?id=106,105,255 (or something similar)
Here's the basic database structure I'm working with - unfortunately it can't be changed or combined into one table.

Table: Project_author
projectId
authorId

Table: Faculty
facultyid (which is the same as authorId)
fname (first name)
lname (last name)

Table: Project
id (same as projectId)
Title (title of project)

My query would be simple if all of these tables were in one! What I'm trying to do is list out the projects by certain authors. I've accomplished this with the following code: HOWEVER because I have to get each entry by its ID number from a different table one at a time, I can't alphabetize them using ORDER BY lname. I tried doing various joins but it doesn't work. The id numbers are fed to the query using $_GET.

Here's what works to list them out - but I need it to be alphabetized. Help!

[code]
$id = $pieces = explode(",",$_GET['id']);
echo "<div align='center' class='title'><strong>Projects by Author</strong></div>";
foreach($id as $k => $authorId) {
  #Get Author Heading
  $name = mssql_query("SELECT fname,lname,facultyid FROM Directory.dbo.Faculty
  WHERE facultyId=$authorId");
  while($result = mssql_fetch_assoc($name)) {
    echo "<ul class='heading'><li>".$result['fname']." ".$result['lname']."</li>";
    #Get projects by this AuthorId
    echo "<ul class='materia'>";
    $projects = mssql_query("SELECT DISTINCT projectId FROM Project_author
    WHERE authorId = $authorId");
    while($these = mssql_fetch_assoc($projects)) {
       $info = mssql_query("SELECT title,id FROM Project
       WHERE id =".$these['projectId']."ORDER BY title");
       while($display = mssql_fetch_assoc($info)) {
        echo "<li><a href='display.php?project=".$display['id']."'>".$display['title']."</a></li>";
       }
    } echo "</ul>";
  } echo "</ul>";
}
[/code]

[craygo]

damn I got just the thing you need. I am at a class right now I will be returning back to the office at 2pm EST will give you code then. Sorry for the wait. Someone may help in the meantime though.

Ray

[jamina1]

[!--quoteo(post=388821:date=Jun 28 2006, 09:37 AM:name=craygo)--][div class=\'quotetop\']QUOTE(craygo @ Jun 28 2006, 09:37 AM) [snapback]388821[/snapback][/div][div class=\'quotemain\'][!--quotec--]
damn I got just the thing you need. I am at a class right now I will be returning back to the office at 2pm EST will give you code then. Sorry for the wait. Someone may help in the meantime though.

Ray
[/quote]

Ya still there? Please! I need help!

[Buyocat]

Sorry if I misunderstood your needs, but something like this would grab data from multiple tables...

SELECT a.row1, a.row2, a.row3, b.row2, b.row3, c.row4
FROM table1 a, table2 b, table3 c

So that would return the specified rows from the tables. You can add where clauses too, like:
WHERE c.row1 = a.row1 AND b.row1 = a.row1 etc

[craygo]

No need to implode your id's. Looks like they are being sent in the proper format anyway. You would only need to format them if they come thru as an array. That being said here is the code

[code]<?
// Set id's
$ids = $_GET['id'];
// Set initial values
$lastauthor = "";
// Query Database
$sql = "SELECT fname, lname, Title, authorid, id
        FROM (project_author INNER JOIN project ON projectid = id) INNER JOIN faculty ON authorid = facultyid
        GROUP BY authorid, Title
        HAVING authorid IN ($ids)";
  $res = mysql_query($sql) or die (mysql_error());
// Count number of results
    $num_rows = mysql_num_rows($res);
// If results Greater than 1 then echo results
    if($num_rows > 1){
// Start loop
      while($r = mysql_fetch_array($res)){
// Start grouping
if($r['authorid'] != $lastauthor){
// Print group footer in this case end of bullet.
    if($lastauthor != ''){
    echo "</ul>";
    }
// Print authors Name(start of group)
echo "<li>".$r['fname']." ".$r['lname']."</li><ul class='materia'>";
}
// Print details in the group
echo "<li><a href='display.php?project=".$r['id']."'>".$r['Title']."</a></li>";
// Assign author id to lastgroup variable
$lastauthor = $r['authorid'];
}
// Complete group(end bullet)
echo "</ul>";
} else {
// If no results found echo this
print "nothing to display";
}
?>[/code]

Good Luck

Ray

[jamina1]

WOW! Thank you so much.

Doing some research I figured I needed to use Joines somehow, but I didn't know how to make it work with looping and stuff. THANKS!