MySQL PHP joining tables and querying both for checkbox aray

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by Stryks »

Sorry .. just wanted to point something out.

Code: Select all

while ($row = mysql_fetch_object($result)) {
   $selected="";   
   if(stristr($field_to_search, $what_to_search_for))
   {
      $selected='select="selected"';
   }
      echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
}
Otherwise, if the first iteration returns false the echo will try to display an unset $selected.

But more importantly than that little nitpick, I'm still not entirely sure that the correct data is being returned. I think you should find out exactly what that query is returning before getting too bogged down in the display side of things (though aceconcept's code should do it nicely if the data IS correct).
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Ohhh that looks good, though I am receiving this error:
Parse error: syntax error, unexpected $end
Code in full is this:

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_object($result)) {
if(stristr($row->dept, $row->OtherDept))
{
$selected='select="selected"';
}
echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
  
$selected="";
}
?>
</select>
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_object($result)) {
if(stristr($row->dept, $row->OtherDept))
{
$selected='select="selected"';
}
echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
  
$selected="";
}}
?>
</select>
I've fixed the error, but this isn't highlighting anything, and the 'id' entry I am looking for has three entries in OtherDept that should be highlighting in this list.

So close I'm clenches my fists.....
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by aceconcepts »

Line 9 of your code previously posted should read:

Code: Select all

 
$selected='selected="selected"';
 
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Mmmmmm it's still not highlight three names in the list that it should be, based on the value in OtherDept for that ID.

This is all the code:

Code: Select all

<select size="4" name=otherdepts" multiple="multiple" style="height:100px;">
<?php
 
$sqlconn=@mysql_connect("localhost","user","pass");
$rs=@mysql_select_db("dbname",$sqlconn);
 
$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_object($result)) {
if(stristr($row->dept, $row->OtherDept))
{
$selected='selected="selected"';
}
echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
 
$selected="";  
}}
?>
</select>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by aceconcepts »

Ok, lets be really specific:

Change line 13 to:

Code: Select all

 
if(stristr($row->dept, $row->OtherDept)===TRUE)
 
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

I might cry..... it's not working my friend....

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_object($result)) {
if(stristr($row->dept, $row->OtherDept)===TRUE)
{
$selected='selected="selected"';
}
echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
 
$selected="";  
}}
?>
</select>
It's not erroring, it's just not highlighting anything.

I've seen some stristr's with != etc, so not sure if that should be in play here, but you seem to know your stuff.

gggrrr...so bloomin close.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by aceconcepts »

Humour me and try this:


Code: Select all

 
while ($row = mysql_fetch_array($result)) {
if(stristr($row['dept'], $row['OtherDept'])===TRUE)
 
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_array($result)) {
if(stristr($row['dept'], $row['OtherDept'])===TRUE)
{
$selected='selected="selected"';
}
echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
 
$selected="";  
}}
?>
</select>
I'm getting an empty list of nothing now.
I think I can see that you've used ['asdf'] as you are using "array". I did try it with $row->asdf but it didn't like it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by aceconcepts »

Try testing it with values you know exists and remove "===TRUE".

e.g.

Code: Select all

 
if($row['dept']=="finance, something")
 
This will tell you if everything else is working.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Code: Select all

while ($row = mysql_fetch_array($result)) {
if($row['dept']=="Finance, Accident")
{
$selected='selected="selected"';
}
echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
 
$selected="";  
This produces nothing in the menu. But I'm not sure I have coded it correctly.
I'm not sure also if you meant to put the stristr in the code.

It's rather infuriating....
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by aceconcepts »

No, you did it how i suggested.

The code is right for indicating a "selected" list item.

The text you compare must be exactly correct "Finance, Accident", even trailing blank spaces " ".

Also, you need to change the value and output of the "option" to $row['...'] as opposed to $row->
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_array($result)) {
if($row['dept']=="Finance, Accident")
{
$selected='selected="selected"';
}
echo '<option class="bodytext" value="'.$row["dept"].'" '.$selected.'>'.$row["dept"].'</option>';
  
$selected="";  
}}
?>
</select>
Ok...this code is producing the list from intradept.dept but nothing is highlighted.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

I was just wondering if this would work, as the stristr might be the wrong way around.
As I am looking in intrastaff.OtherDept for the content of intradept.dept.

However, this too only renders a full list from intradept.dept with nothing selected.

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
if (mysql_num_rows($result) > 0)
{
    while ($row = mysql_fetch_array($result))
        {
        if(stristr($row['OtherDept'], $row['dept'])===TRUE)
            {
            $selected='selected="selected"';
            }
        echo '<option class="bodytext" value="'.$row["dept"].'"         
 
'.$selected.'>'.$row["dept"].'</option>';
    $selected="";  
        }
}
?>
</select>
:banghead:
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MySQL PHP joining tables and querying both for checkbox aray

Post by simonmlewis »

Hold the phone........I might have something:

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY dept");
if (mysql_num_rows($result) > 0)
{
    while ($row = mysql_fetch_array($result))
        {
        if(stristr($row['dept'], $row['OtherDept'])===TRUE)
            {
            $selected='selected="selected"';
            }
        echo '<option class="bodytext" value="'.$row["dept"].'"'.$selected.'>'.$row["dept"].'</option>';
    $selected="";  
        }
}
?>
</select>
This is highlighting "Accident". There are three other 'dept' in the field that should be highlighted, so I am now even closer.
Any idea why it's only highlighting that one? fyi The field is written like so:
Company Commercial, Finance, Accident
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply