Table one:both cols, table two: 1 col depending on?

[typomaniac]

I have 2 tables, one named branch, and one named branches, each with two columns.

Structure is:

| branch | branches |

|======================|==================================|

| cid int(9) | bid tinyint(2) | bid tinyint(2) | branch_name varchar(25) |

|-----------------+-----------------+-----------------+-----------------------------------|

| 111111111 | 3 | 1 | U.S. Army |

|-----------------+-----------------+-----------------+-----------------------------------|

| 222222222 | 1 | 2 | U.S. Air Force |

|-----------------+-----------------+-----------------+-----------------------------------|

| 333333333 | 6 | 3 | U.S. Marine Corps |

|-----------------+-----------------+-----------------+-----------------------------------|

| 444444444 | 2 | 4 | U.S. Navy |

|-----------------+-----------------+-----------------+-----------------------------------|

| 555555555 | 4 | 5 | U.S. Coast Guard |

|-----------------+-----------------+-----------------+-----------------------------------|

| 666666666 | 5 | 6 | Merchant Marines |

+----------------+-----------------+-----------------+-----------------------------------+

The branch table stores the branch(es) veterens served in:

branch.cid: a client id number (unique--the one to match up with $var[a fictitious id]).

branch.bid: the number to reference the individual branches listed in the branches table

The branches table holds the name of the service and an id for each one.

branches.bid: an id for branch_name

branches.branch_name: Branches of the military

This is for the purpose of editing an individual's profile. What I'm trying to do is produce the list of checkboxes using the database with bid as the value from the branches table which gives me a checkbox listing for everybranch which is followed by branch_name. Getting this far is no problem but when I try to add the checks to the boxes. I'm either getting only a checkbox for the ones from previous input or they're all checked or I'm getting a list a mile long. I could write the checkboxes out one by one but the format is used for other details of the profile and sometimes new listings(such as those in the branches column) are added via a different script(which is already working) and the person filling out the form has no way of controlling what is in .

The script that has me spinning in circles is as follows:

$var=222222222; //$var is a user input to match up against cid column in branch table

$result223 = mysql_query ("SELECT branches.branch_name, branches.bid as branchid,branch.bid, branch.cid FROM branches INNER JOIN branch ON branches.bid = branch.bid where branch.cid=$var ") or die(mysql_error());

echo"<br><br><table border='2px'>";

while($row223 = mysql_fetch_array( $result223 )) {

$x223=$row223['branch_name'];

$x223a=$row223['branchid'];

$x223b=$row223['cid'];

$x223c=$row223['bid'];

echo"<tr><td>";

echo"<input type='checkbox'name='branch[]'value='$x223a'";if($x223b==$var){echo"checked";}echo"> $x223";

}

I used 'group by branchid' and it showed a box for each branch which is what I'm wanting but it only uses the amount of listings from branch as is in the branches table and there are many entries in the branch table and if the $var entry was past the first six entries it didn't appear as checked. I removed group by and replaced it with 'where branch.cid=$var' and it would only show one checkbox per entry using that number.

What am I doing wrong and what can I do to fix this? I need to display all from the branches table checked accordingly to the branch table. It is for doing updates. Please let me know if anything needs explained. This is my first run with php and it has me stumped.

[justsomeguy]

Use a left join or right join instead of inner join. If you select from branches LEFT JOIN branch then you will get all records from branches, and only the matching columns from branch. So, records that have a value for the CID are selected, and if the CID is null then there is not a record in the other table and the checkbox should not be selected.

[typomaniac]

Thank you sir, that worked like a charm. It took a little bit of finangling the statement to get it to do the proper results but then it hit. Tested with other $var numbers to verify and all check-go. I went with:

$var=111111111;

$result223 = mysql_query ("SELECT branches.branch_name, branches.bid as branchid,branch.bid as braid ,branch.cid FROM branches LEFT JOIN branch ON branches.bid=branch.bid and cid=$var order by branches.bid") or die(mysql_error());
echo"<br><br><table border='2px'>";
while($row223 = mysql_fetch_array( $result223 )) {
$x223=$row223['branch_name'];
$x223a=$row223['branchid'];
$x223b=$row223['cid'];
$x223c=$row223['braid'];
echo"<tr><td>";
echo"<input type='checkbox'name='branch[]'value='$x223a'";if($x223b==$var){echo"checked";}echo"> $x223";
}

Now, to study it and figure out what's happening there and continue the project. Hopefully I can figure the rest out. Thanx a million

Edited November 26, 2015 by typomaniac