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

[kalenj]

post your sql along with your error message.

[guitarromantic]

Here's the SQL:

$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());

That's called in the code for the dropdown here:
echo "<select name='Reviewer_ Name' id='Reviewer_Na me'
value=\"$Staff_ id\">";
while($row2 = mysql_fetch_arr ay($result, MYSQL_NUM))
{
list($Staff_id, $Staff_Name) = $row2;
echo "<option value=\"$Staff_ id\">$Staff_Nam e</option>";
}
echo "</select>";

There's no error message, but currently that code is not loading the
correct value into the dropdown, it just loads the default one.

[Geoff Berrow]

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

list($Staff_id, $Staff_Name) = $row2;

Your select query is fetching 9 columns but you're only listing two. If
it is returning a row at all. Have you tried print_r on $row2?
--
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]

Geoff:

Sorry, I should have specified: As well as those two columns, there's
also some others being displayed.

This might make a little more sense:
http://www.scenepointblank.com/matt/...edit.php?id=26

This is the page I'm working with. I made some (bad) changes to the
code so currently the dropdown box is displaying the wrong value
(ordinarily it should be displaying a staff list) but basically, the
code you quoted is just for the dropdown. The other parts of the form
are supposed to be filled by the other 7 columns.

Also, I'm quite new to php/sql.. could you elaborate on what print_r
does?

Thanks again,
Matt

[Geoff Berrow]

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

This might make a little more sense:
http://www.scenepointblank.com/matt/...edit.php?id=26
Working perfectly. It's fetching the first two columns Band_Name,
Record_Name and putting them in the select box
This is the page I'm working with. I made some (bad) changes to the
code so currently the dropdown box is displaying the wrong value
(ordinarily it should be displaying a staff list) but basically, the
code you quoted is just for the dropdown. The other parts of the form
are supposed to be filled by the other 7 columns.
Whoa. If only one row is returned from this query, why do you need a
select box?
Also, I'm quite new to php/sql.. could you elaborate on what print_r
does?

It prints the contents of the array. It will show you what you've got.
Right now it looks like your Staff_Name will be contained in $row2[3]
and Staff_id in $row2[4]

So you could lose the list and do:

echo "<option value=\"".$row2[4]."\">".$row2[3]."</option>";

However, if you want all the reviewers in a drop down box, you'll have
to query the staff table separately and create an array from which you
can generate the drop down box. Each time round the loop check if the
value matches the value returned from the first query. If it is, print
selected in the option tag.

--
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]

Geoff:
I need a select box just so we have the ability to edit this value
easily if neccessary. I guess it just makes it easier than typing in
the ID for each staff member, when we have around 30..

As for the code: I used the $row2[3] and $row2[4] values, they worked
great, displaying the $author value for the correct review. However,
when I uncommented a part of my code, they became blank. The code I
uncommented? That which loads the other values into the form.

For example:
http://www.scenepointblank.com/matt/...dit.php?id=240

If I comment out those lines, we get this:
http://www.scenepointblank.com/matt/...t_2.php?id=240

This is the code I'm commenting out:

$i=0;
while ($i < $num) {

/*$Band_Name=mys ql_result($matt result,$i,"Band _Name");
$Record_Name=my sql_result($mat tresult,$i,"Rec ord_Name");
$Review=mysql_r esult($mattresu lt,$i,"Review") ;
$Staff_Name=mys ql_result($matt result,$i,"Staf f_Name");
$Staff_id=mysql _result($mattre sult,$i,"Staff_ id");
$Score=mysql_re sult($mattresul t,$i,"Score");
$Cover_Image=my sql_result($mat tresult,$i,"Cov er_Image");
$Record_Label=m ysql_result($ma ttresult,$i,"Re cord_Label");
$Band_Site_URL= mysql_result($m attresult,$i,"B and_Site_URL");
*/

$i++;
}

Without that, none of the $variables load in the form. What am I doing
wrong?

Thanks for all your help so far.
Matt

[Geoff Berrow]

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

Geoff:
I need a select box just so we have the ability to edit this value
easily if neccessary. I guess it just makes it easier than typing in
the ID for each staff member, when we have around 30..
Read my reply again. The query you are running seems to return one row
only. To get a drop down box of all staff members you'll have to run
/another/ query on the staff members table.
As for the code: I used the $row2[3] and $row2[4] values, they worked
great, displaying the $author value for the correct review. However,
when I uncommented a part of my code, they became blank. The code I
uncommented? That which loads the other values into the form.

For example:
http://www.scenepointblank.com/matt/...dit.php?id=240

If I comment out those lines, we get this:
http://www.scenepointblank.com/matt/...t_2.php?id=240

This is the code I'm commenting out:

$i=0;
Why do we have a loop here? It's still only one record isn't it?while ($i < $num) {

/*$Band_Name=mys ql_result($matt result,$i,"Band _Name");
$Record_Name=m ysql_result($ma ttresult,$i,"Re cord_Name");
$Review=mysql_ result($mattres ult,$i,"Review" );
$Staff_Name=my sql_result($mat tresult,$i,"Sta ff_Name");
$Staff_id=mysq l_result($mattr esult,$i,"Staff _id");
$Score=mysql_r esult($mattresu lt,$i,"Score");
$Cover_Image=m ysql_result($ma ttresult,$i,"Co ver_Image");
$Record_Label= mysql_result($m attresult,$i,"R ecord_Label");
$Band_Site_URL =mysql_result($ mattresult,$i," Band_Site_URL") ;
*/

$i++;
}

Without that, none of the $variables load in the form. Why do you think they would?
What am I doing
wrong?

It seems a very complicated way of doing it
$myrow=mysql_fe tch_array($resu lt) will fetch an associative array of
variables in the form $myrow['Band_Name'], $myrow['Record_Name'] and so
on. You can then use these to populate your form
--
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, I seem to have managed to do all of this, thanks a ton.
http://www.scenepointblank.com/matt/...view.php?id=99

See the extra dropdown at the end? That's a test I'm adding. It's
displaying all the staff from a seperate query, but I can't get it to
load the correct value.

You said "Each time round the loop check if the value matches the value
returned from the first query. If it is, print selected in the option
tag.".

My code:

$query2 = "SELECT Staff_id, Staff_Name FROM staff";
$result2 = mysql_query($qu ery2) or die('Error : ' . mysql_error());

...

echo "<select name=\"Reviewer _Name\" id=\"Reviewer_N ame\"
value=\"Staff_i d\">";
while($row2 = mysql_fetch_arr ay($result2, MYSQL_NUM))
{
list($Staff_id, $Staff_Name) = $row2;
echo "<option value=\"$Staff_ id\">$Staff_Nam e</option>";
}
echo "</select>";

How can I do what you suggested?

Just to clarify, I really appreciate this help. I'm only a few weeks
into php (does it show?!) and this is really helping me out.

Matt

[Geoff Berrow]

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

How can I do what you suggested?
Let's assume you have defined a variable $Staff_id_db from your first
query

while($row2 = mysql_fetch_arr ay($result2, MYSQL_NUM))
{
list($Staff_id, $Staff_Name) = $row2;
//check if this option should be selected
if($Staff_id==$ Staff_id_db){
$selected=" selected";
}
else{
$selected="";
}

echo "<option
value=\"$Staff_ id\"$selected>$ Staff_Name</option>";
}
Just to clarify, I really appreciate this help. I'm only a few weeks
into php (does it show?!) and this is really helping me out.

Cool. :-) (But I'm going to bed now...)

--
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/