Jump to content

Php And Mssql Queries From 3 Tables At Once


jamina1

Recommended Posts

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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!
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.