Select/Join query help (dropdown box?)

[guitarromantic]

Hey everyone.

I'm re-writing a php Content Management System (based loosely on
phpNuke but specific to my site). My first major change was attempting
to normalise the Review section database structure. I've created a
Staff table with staff email addresses and names (plus id), and a
Reviews table with the text of the reviews. The Reviews table contains
an 'author' field with a value that corresponds to the Staff table.
When I display a review, I successfully used an Inner Join to link the
records and display the correct author name/email for the relevant
review.

My problem now is when editing the reviews. I originally had a basic
php page that simply selected the values and loaded them into a form,
but I can't get this to work now. Specifically, In the Submit form for
Reviews, I created a dropdown box which loaded the values from the
Staff table. When storing a review, it correctly stores the value for
the selected Author's ID in the Review table. My problem is when
editing the review, I cannot get php to lookup the author ID in the
Reviews table, then display in the dropdown the correct Author Name
from the Staff table for that specific review.

Could anyone help? The MySQL manual for joins may as well be written in
Greek.

Matt

[guitarromantic]

I'm sorry, I didn't follow. What did you mean by:

"Let's assume you have defined a variable $Staff_id_db from your first
query"?

[Geoff Berrow]

I noticed that Message-ID:
<11************ **********@f14g 2000cwb.googleg roups.com> from
gu************@ gmail.com contained the following:

I'm sorry, I didn't follow. What did you mean by:

"Let's assume you have defined a variable $Staff_id_db from your first
query"?

Your first query:

$query = "SELECT Band_Name, Record_Name, Review, Staff_Name, Staff_id,
Score, Cover_Image, Record_Label, Band_Site_URL
FROM review_test INNER JOIN staff ON Reviewer_Name = staff.Staff_id
WHERE id='$id'";
$result = mysql_query($qu ery) or die('Error : ' . mysql_error());

if you do:
$myrow=mysql_fe tch assoc($result);

then $myrow['Staff_id'] will contain the staff id of the person who
wrote the review, no?

Compare that with the results from the query of the staff table to
determine which should be selected in the select box

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[guitarromantic]

Sorry to be a constant pain; I'm really doing my best to understand
this, but it's a hot day.. etc. I still can't get this to work; all the
following code is doing is selecting the final value in the dropdown
instead.

This is what I have:

echo "<select name=\"Reviewer _Name\" id=\"Reviewer_N ame\"
value=\"Staff_i d\">";

$myrow="mysql_f etch assoc($mattresu lt)";

while($row3 = mysql_fetch_arr ay($result2, MYSQL_NUM))
{
list($Staff_id, $Staff_Name) = $row3;

//check if this option should be selected
if($Staff_id==$ myrow['Staff_id']){
$selected="sele cted";
}
else{
$selected="";
}
echo "<option value=\"$Staff_ id\"
selected=\"$sel ected\">$Staff_ Name</option>";
}
echo "</select>";

$mattresult is the result of the query you just posted.
$result2 is the second query that looks up all the values in the staff
table.

[Geoff Berrow]

I noticed that Message-ID:
<11************ **********@o13g 2000cwo.googleg roups.com> from
gu************@ gmail.com contained the following:

Echo these variables to the screen to see if you are getting what you
think you are getting

if($Staff_id==$ myrow['Staff_id']){
$selected="sele cted";
}
else{
$selected="";
}
echo "<option value=\"$Staff_ id\"
selected=\"$se lected\">$Staff _Name</option>";

Incorrect html. It should be

echo "<option value=\"$Staff_ id\"$selected>$ Staff_Name</option>";

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[guitarromantic]

Okay, $myrow['Staff_id'] is returning 'm'. Uh..

As for $Staff_id, it is now displaying the same value (46) no matter
what review I display (which obviously have different authors).

As for the html, I just (wrongly?) presumed that the if statement would
simply input the text 'selected' into the code, so I thought making the
attribute="$sel ected" was the proper way to achieve this.

[Geoff Berrow]

I noticed that Message-ID:
<11************ *********@g43g2 000cwa.googlegr oups.com> from
gu************@ gmail.com contained the following:

Okay, $myrow['Staff_id'] is returning 'm'. Uh..
Then that's what it contains <shrug> Have you checked the data?
As for $Staff_id, it is now displaying the same value (46) no matter
what review I display (which obviously have different authors).
So it should. $Staff_id is coming from your query of all reviewers and
changes each time you go round the loop. I assume you are echoing it
when the loop ends
As for the html, I just (wrongly?) presumed that the if statement would
simply input the text 'selected' into the code, so I thought making the
attribute="$se lected" was the proper way to achieve this.

Nope. :-)

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[guitarromantic]

Argh, I give up. The value for $Staff_id is definitely NOT m, anywhere.
The ID field is a auto_incrementi ng primary key, so that doesn't make
sense. I have the page looking up the reviewer entered, I just can't
let the editor (easily) modify the reviewer. I guess I'll just put a
message on the page saying to make sure they select the right reviewer
when posting.

[Geoff Berrow]

I noticed that Message-ID:
<11************ **********@g49g 2000cwa.googleg roups.com> from
gu************@ gmail.com contained the following:

Argh, I give up. The value for $Staff_id is definitely NOT m, anywhere.
The ID field is a auto_incrementi ng primary key, so that doesn't make
sense. I have the page looking up the reviewer entered, I just can't
let the editor (easily) modify the reviewer. I guess I'll just put a
message on the page saying to make sure they select the right reviewer
when posting.

In that case why do they need to edit the reviewer at all? Just echo
the name of the reviewer to the screen and leave it out of the update
query so it is not changed.

I think you need to look carefully at where your variables are coming
from.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/