Multiple tables, at my wit's end

[fwapah]

I'm trying to create an application where one logs in to input grant scores. I have a table for the reviewers, with their name, id (unique) and username (for login), and a table for the grants with the grant number (unique), applicant name, reviewer1's score, reviewer2's score and reviewer1's comments and reviewer2's comments.

 

I'm trying to make it so when reviewers log in, the url has their ID in the URL (eg, score?rid=5) and then based upon that rid, the information for the two grants they are in charge of will be displayed.

 

I've tried seriously about 10 different codes, to no avail. This is probably cake for you guys, but I've been on this for two days now.

 

Thanks in advance for your help.

[PHP Monkeh]

Which field in your grants or users table determines which grants they have access to?

[fwapah]

That's where I get a little confused.. each grant has two reviewers but the reviewers have two separate grants; ie, reviewer 1 has grants 35 and 47, while reviewer 2 has 35 and 48. I'm not sure if it would be easier to setup a script that pulls the grants on score.php?rid=1 by grant1 OR grant2 in the reviewer table, or to have it pull by rid1 OR rid2 = $rid in the grant table and have them pulled there.

[craygo]

just give aliases to the tables. you would also have to add a field in the grant table to reference the reviewer.

 

"SELECT * FROM grants

JOIN reviewers as r1 ON grants.reviewer1id = r1.id

JOIN reviewers as r2 ON grants.reviewer2id = r2.id

WHERE grant id = 'xx'";

[fwapah]

ugh bear with me, sorry. i really appreciate everyone's quick response.

 

once they log in, the url is going to have the reviewer's id in it, so i (think i) it would be easiest to pull based on that and basically impossible to pull from a single grant id. but my mind is fried right now, so hopefully you can explain what's easier/harder, worse/better.

 

thanks again for your help. i really appreciate it.

[craygo]

no biggy

 

"SELECT * FROM grants

JOIN reviewers as r1 ON grants.reviewer1id = r1.id

JOIN reviewers as r2 ON grants.reviewer2id = r2.id

WHERE r1.reviewer1id = '".$_GET['rid']."' OR r2.reviewer2id = '".$_GET['rid']."'";

[fwapah]

Brilliant! Thanks so much. I'm now trying to make a table that displays that information, but it's not working either (not showing up on score.php?rid=1, I'm left w/a blank page). I'm sorry to make you guys hold my hand, but I've been researching for days now and can't figure out what's going wrong. Here's my pieced together code, from the beginning:

 

<?php
  $rid = $_GET["rid"];

mysql_connect("xxx", "xxx", "xxx") or die(mysql_error());
mysql_select_db("xxx") or die(mysql_error());

$query="SELECT * FROM grants
JOIN reviewers as r1 ON grants.reviewer1id = r1.id
JOIN reviewers as r2 ON grants.reviewer2id = r2.id
WHERE r1.reviewer1id = '".$_GET['rid']."' OR r2.reviewer2id = '".$_GET['rid']."'";
$rt=mysql_query($query); 
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4">Grants for your review</td>
</tr>
<tr>
<td align="center"><strong>First</strong></td>
<td align="center"><strong>Last</strong></td>
<td align="center"><strong>Grant #</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($rt)){
?>
<tr>
<td><? echo $rows['gfirst']; ?></td>
<td><? echo $rows['glast']; ?></td>
<td><? echo $rows['gnum']; ?></td>


// link to update.php and send value of id
<td align="center"><a href="update.php?id=<? echo $rows['gnum']; ?>">update</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

[craygo]

May have to change the field to search on the rid. just in case there are no id's there

<?php
  $rid = $_GET["rid"];

mysql_connect("xxx", "xxx", "xxx") or die(mysql_error());
mysql_select_db("xxx") or die(mysql_error());

$query="SELECT * FROM grants
JOIN reviewers as r1 ON grants.reviewer1id = r1.id
JOIN reviewers as r2 ON grants.reviewer2id = r2.id
WHERE grants.reviewer1id = '$rid' OR grants.reviewer2id = '$rid'";
$rt=mysql_query($query) or die(mysql_erreor()); 
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="4">Grants for your review</td>
</tr>
<tr>
<td align="center"><strong>First</strong></td>
<td align="center"><strong>Last</strong></td>
<td align="center"><strong>Grant #</strong></td>
<td align="center"><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($rt)){
extract $rows;
echo "
<tr>
<td>$gfirst</td>
<td>$glast</td>
<td>$gnum</td>
// link to update.php and send value of id
<td align=\"center\"><a href=\"update.php?id=$gnum\">update</a></td>
</tr>\n";
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

 

Ray