MySQL PHP joining tables and querying both for checkbox aray
Moderator: General Moderators
Re: MySQL PHP joining tables and querying both for checkbox aray
<?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
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
Hey I'm all for bizarre code if it works!
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
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.
All the best from the United Kingdom.
- 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
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
Ok.....?
Do I use a different type of join then?
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.
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
Ok... tried this:
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
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";
}
?>
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.
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
I'm now trying a Multiple Selection method, though the problem remains:
This lists everything from intradept, but nothing is 'selected'.
Help!!!!!

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";
}
?>Help!!!!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: MySQL PHP joining tables and querying both for checkbox aray
Don't quote me on this, but I'm pretty sure that ... ... is the same as ...
I think you might be after a LEFT JOIN.
Code: Select all
SELECT * FROM intradept, intrastaffCode: Select all
SELECT * FROM intradept INNER JOIN intrastaffRe: MySQL PHP joining tables and querying both for checkbox aray
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 ...
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.
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"'; ?>>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.
- 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
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).
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
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......
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.
All the best from the United Kingdom.
- 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
From the blow code you posted earlier, it looks like you trying to set a "select" drop down list.
What are you trying to "check" or "select", checkboxes or select lists?
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";
}
?>
-
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
Selection lists as it's easier to control the height.
Show everything from intradept.dept, and only 'select' those in the resulting list where there is a match among the text in intrastaff.OtherDept.
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>";
?>Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
- 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
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
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.
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.
All the best from the United Kingdom.
- 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
Ok, apologies for not being specific.
This code should help:
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="";
}