Page 2 of 4

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

Posted: Sat Oct 11, 2008 5:22 am
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

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

Posted: Sat Oct 11, 2008 6:18 am
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

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

Posted: Sat Oct 11, 2008 6:24 am
by aceconcepts
An INNER JOIN will only show data if a match exists in both tables.

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

Posted: Sat Oct 11, 2008 6:30 am
by simonmlewis
Ok.....?

Do I use a different type of join then?

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

Posted: Sat Oct 11, 2008 2:11 pm
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

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

Posted: Wed Oct 15, 2008 6:31 am
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:

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

Posted: Wed Oct 15, 2008 7:42 am
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.

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

Posted: Wed Oct 15, 2008 7:55 am
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.

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

Posted: Wed Oct 15, 2008 8:18 am
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).

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

Posted: Wed Oct 15, 2008 8:22 am
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......

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

Posted: Wed Oct 15, 2008 8:28 am
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?

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

Posted: Wed Oct 15, 2008 8:33 am
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.

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

Posted: Wed Oct 15, 2008 8:41 am
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="";
}
 

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

Posted: Wed Oct 15, 2008 8:51 am
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.

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

Posted: Wed Oct 15, 2008 8:59 am
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="";
}