Select/Join query help (dropdown box?)

**kalenj@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

post your sql along with your error message.

**guitarromantic@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

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** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

I noticed that Message-ID:
<1120843279.866 122.23310@z14g2 000cwz.googlegr oups.com> from
guitarromantic@ gmail.com contained the following:
[color=blue]
> list($Staff_id, $Staff_Name) = $row2;[/color]

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@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

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:

Error 404 | Scene Point Blank

http://www.scenepointblank.com/matt/dev/reviews/reviewedit.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** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

I noticed that Message-ID:
<1120846329.363 902.109070@o13g 2000cwo.googleg roups.com> from
guitarromantic@ gmail.com contained the following:
[color=blue]
>This might make a little more sense:
>http://www.scenepointblank.com/matt/...edit.php?id=26[/color]

Working perfectly. It's fetching the first two columns Band_Name,
Record_Name and putting them in the select box[color=blue]
>
>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.[/color]

Whoa. If only one row is returned from this query, why do you need a
select box?
[color=blue]
>Also, I'm quite new to php/sql.. could you elaborate on what print_r
>does?[/color]

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@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

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:

Error 404 | Scene Point Blank

http://www.scenepointblank.com/matt/dev/reviews/reviewedit.php?id=240

If I comment out those lines, we get this:

Error 404 | Scene Point Blank

http://www.scenepointblank.com/matt/dev/reviews/reviewedit_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** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

I noticed that Message-ID:
<1120851439.968 980.54140@g47g2 000cwa.googlegr oups.com> from
guitarromantic@ gmail.com contained the following:
[color=blue]
>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..[/color]

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.[color=blue]
>
>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;[/color]

Why do we have a loop here? It's still only one record isn't it?[color=blue]
>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.[/color]
Why do you think they would?
[color=blue]
>What am I doing
>wrong?[/color]

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@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

Okay, I seem to have managed to do all of this, thanks a ton.

Error 404 | Scene Point Blank

http://www.scenepointblank.com/matt/dev/reviews/editreview.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** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

I noticed that Message-ID:
<1120866465.469 190.296750@z14g 2000cwz.googleg roups.com> from
guitarromantic@ gmail.com contained the following:
[color=blue]
>How can I do what you suggested?[/color]

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>";
}[color=blue]
>
>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.[/color]

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/

**guitarromantic@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

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** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

I noticed that Message-ID:
<1120918876.965 985.276430@f14g 2000cwb.googleg roups.com> from
guitarromantic@ gmail.com contained the following:
[color=blue]
>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"?[/color]

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@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

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** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

I noticed that Message-ID:
<1120928569.894 009.183330@o13g 2000cwo.googleg roups.com> from
guitarromantic@ gmail.com contained the following:

Echo these variables to the screen to see if you are getting what you
think you are getting
[color=blue]
> if($Staff_id==$ myrow['Staff_id']){[/color]

[color=blue]
> $selected="sele cted";
> }
> else{
> $selected="";
> }
> echo "<option value=\"$Staff_ id\"
>selected=\"$se lected\">$Staff _Name</option>";[/color]

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@gmail.com** · Jul 17 '05, 01:49 PM

Re: Select/Join query help (dropdown box?)

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.

Select/Join query help (dropdown box?)

Select/Join query help (dropdown box?)

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment