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

mukunthan
Forum Newbie
Posts: 12
Joined: Sat Sep 13, 2008 12:52 am

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

Post by mukunthan »

<?php $result = mysql_query ("SELECT * FROM intradepartment INNER JOIN intrastaff WHERE intradepartment.dept=intrastaff.OtherDept AND intrastaff.id = '$id'");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_array($result)){?>
<input type='checkbox' name='dept[]' value='<?php echo $row["dept"]?>' <?php if($row["dept"] == $row["OtherDept"]) echo "checked";?> >
<?php echo $row["dept"]."<br />";
}
}else{
echo "No matching records";
}
?>

Hi Simon,
Let it be a bizarre piece of code. But try this. I have just added a semicolon after the "<br />";
rectified code line : echo $row["dept"]."<br />";

All php statements should end with semicolon. If this works fine may be this was not so bizarre as u thought. Lets hope for the best. I could have written everything in php without embedding pure HTML but i thought it might confuse you. You had previously used 2 if statements but we now only use one for checking the checkbox.

If this sill doesnot work then paste the table structure for intradepartment & intrastaff
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 »

Hey I'm all for bizarre code if it works! :P
Trust me, I've used some in the past that I didn't think would work, but did.

This could now produces no errors, which is a fabulous result, however what it is is to only show the 'dept' where there is a match in intrastaff. It doesn't show all departments in intradept (note correction: not intradepartment but intradept - my bad!).

So right now I only see "Company and Commercial" with a ticked check box.

Maybe the join is the wrong way around....? I'm not familiar with joins so still learning.

Cheers for the code to date.

Simon
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 »

An INNER JOIN will only show data if a match exists in both tables.
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 »

Ok.....?

Do I use a different type of join then?
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 »

Ok... tried this:

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id'");
if (mysql_num_rows($result) > 0) 
{
while ($row = mysql_fetch_array($result))
{ ?>
<input type='checkbox' name='dept[]' value='<?php echo $row["dept"]?>' 
<?php if($row["dept"] == $row["OtherDept"]) { echo "checked";}
elseif($row["dept"] != $row["OtherDept"]) { echo "";}
?> >
<?php echo $row["dept"]."<br />";
}
}else{
echo "No matching records";
}
?> 
 
It half works. It will list all 'dept' from intradept, and it will tick the one box where OtherDept matches - however, OtherDept is going to have more than one 'dept' entered in its field - hence having checkboxes to add to that record.

If I change "Company Commercial" to (for example) "Company Commercial, Finance, Accident", it ticks nothing. So there is a problem.

I did try the STRIPOS code which has worked for me before, but I used it before with a $row[name'], "Bob Builder" style script. This has to look at both tables rather that hardcoded department names.

I'm nearly there....but not quite.

Simon
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'm now trying a Multiple Selection method, though the problem remains:

Code: Select all

$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY dept 
 
ASC");
if (mysql_num_rows($result) > 0) 
{
while ($row = mysql_fetch_array($result))
{ ?>
<option class='bodytext' value='<?php echo $row["dept"]?>' 
<?php 
if (stripos($row["dept"], $row["OtherDept"])!=false) { echo "selected";}
elseif($row["dept"] != $row["OtherDept"]) { echo "";}
?> >
<?php echo $row["dept"]."<br />";
}
}else{
echo "No matching records";
}
?>
This lists everything from intradept, but nothing is 'selected'.

Help!!!!!
:crazy:
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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 »

Don't quote me on this, but I'm pretty sure that ...

Code: Select all

SELECT * FROM intradept, intrastaff
... is the same as ...

Code: Select all

SELECT * FROM intradept INNER JOIN intrastaff
I think you might be after a LEFT JOIN.
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 »

Also, I'd go back to the earlier version of checking with the id's match. That stripos method is pretty pointless. It assumes that there may be some slight difference between the two (which there should never be) and further assumes that one is more reliable than the other.

Something like the following should suffice ...

Code: Select all

<input type="checkbox" name="dept[]" value="<?php echo $row["dept"]?>"<?php if($row['dept'] == $row['OtherDept']) echo ' checked="checked"'; ?>>
You'll notice I took out the elsif section. Firstly, if it failed the if, you could just use else seeing as you just reversed the if arguments anyhow, and seeing as how you don't want it to output anything anyhow, it's kinda moot.

Also, for maximum cross-browser compatibility, you should use checked="checked" instead of checked, and there should be a space beforehand (as per the sample above).

Hope you find this useful.
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 »

Yes, like I stated at the begining, a LEFT JOIN is most logical.

Read up on JOINs via the link I sent you. Once you get your head round them, they are most useful.

However, I get the feeling that you are not using a relational database to it's proper function. Tables should ideally be link by IDs (primary and foreign).
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 »

Thank you for your response.

Problem is, the field it's checking in could contain more than one word:

..."finance, company commercial"

... for example. So an "==" will fail. As the field doesn't equal "finance", it just contains it.

So 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 »

From the blow code you posted earlier, it looks like you trying to set a "select" drop down list.

Code: Select all

 
$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY dept
 
ASC");
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_array($result))
{ ?>
<option class='bodytext' value='<?php echo $row["dept"]?>'
<?php
if (stripos($row["dept"], $row["OtherDept"])!=false) { echo "selected";}
elseif($row["dept"] != $row["OtherDept"]) { echo "";}
?> >
<?php echo $row["dept"]."<br />";
}
}else{
echo "No matching records";
}
?> 
 
What are you trying to "check" or "select", checkboxes or select lists?
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 »

Selection lists as it's easier to control the height.

Code: Select all

<select size="4" name=otherdepts" multiple="multiple" style="height:100px;">
<?php
** connection code remove **
$result = mysql_query ("SELECT * FROM intradept, intrastaff WHERE intrastaff.id = '$id' ORDER BY 
 
dept");
while ($row = mysql_fetch_object($result)) {
echo "<option class='bodytext' value='$row->dept'>$row->dept</option>";
 
}
mysql_free_result($result);
mysql_close($sqlconn);
echo "</select>";
?>
Show everything from intradept.dept, and only 'select' those in the resulting list where there is a match among the text in intrastaff.OtherDept.
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 »

I usually set "selected" items in list in the following way:

Code: Select all

 
while ($row = mysql_fetch_object($result)) {
   if($row['field']==$var1_wotever)
   {
      $selected='select="selected"';
   }
      echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
 
   $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 »

But you aren't seeing a vital flaw here - I need to check that the contents of one $row->variable in one table, is **SIMILAR** or **CONTAINS** same data in another $row->variable in another table.

Each code I read here is "=="...... is equal to. The field containing "finance, commercial", won't be "equal to" a variable containing "finance". Similar, yes. But not equal to.
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, apologies for not being specific.

This code should help:

Code: Select all

 
while ($row = mysql_fetch_object($result)) {
   if(stristr($field_to_search, $what_to_search_for))
   {
      $selected='select="selected"';
   }
      echo '<option class="bodytext" value="'.$row->dept.'" '.$selected.'>'.$row->dept.'</option>';
 
   $selected="";
}
 
Post Reply