Jump to content


Photo

Php And Mssql Queries From 3 Tables At Once


  • Please log in to reply
5 replies to this topic

#1 jamina1

jamina1
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 28 June 2006 - 01:28 PM

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!

$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>";
}


#2 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 28 June 2006 - 01:37 PM

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

#3 jamina1

jamina1
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 28 June 2006 - 07:37 PM

[!--quoteo(post=388821:date=Jun 28 2006, 09:37 AM:name=craygo)--][div class=\'quotetop\']QUOTE(craygo @ Jun 28 2006, 09:37 AM) View Post[/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!

#4 Buyocat

Buyocat
  • Members
  • PipPipPip
  • Advanced Member
  • 267 posts

Posted 28 June 2006 - 07:43 PM

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
Looking for some easy-to-use tools?  Try these, https://sourceforge....jects/utils-php -- I made them myself.  They're distinct tools which are easy to understand and use.  See some examples uses at http://www.anotherearlymorning.com

#5 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 29 June 2006 - 01:40 PM

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

<?
// 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";
}
?>

Good Luck

Ray

#6 jamina1

jamina1
  • Members
  • PipPipPip
  • Advanced Member
  • 32 posts

Posted 01 July 2006 - 12:24 AM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users