Page 1 of 4
MySQL PHP joining tables and querying both for checkbox aray
Posted: Wed Oct 08, 2008 3:44 pm
by simonmlewis
$sqlconn=@mysql_connect("localhost","user","pass");
$rs=@mysql_select_db("dbname",$sqlconn);
$query = ("SELECT OtherDept FROM intrastaff WHERE id = '$id'");
$result = mysql_query($query);
while($row = mysql_fetch_object($result))
if (stripos($row['OtherDept'],"Company Commercial")!==false)
{
echo "<input type='checkbox' name='dept[]' value='$row->OtherDept' checked>$row->OtherDept<br/>";
}
else
{
echo "<input type='checkbox' name='dept[]' value='$row->OtherDept'>$row->OtherDept<br/>";
}
mysql_close($sqlconn);
What I am trying to do here is say this:
Render all Departments from intradepartment (table) and place a tick in the checkbox where there is a matching entry in intrastaff.
I know intradepartment isn't shown here now as I was trying something else, but I hope this will give u the idea.
In the end, this will all render as one form with two other connections, and be able to submit the data to a MySQL database.
I just can't get it to produce all boxes, and then tick those where both match.
Hope someone can help.
Simon
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 5:02 am
by aceconcepts
I think i understand your problem.
This is how i might approach it:
Code: Select all
$sqlconn=@mysql_connect("localhost","royth","simonmlewis579");
$rs=@mysql_select_db("roythorne",$sqlconn);
$query = ("SELECT OtherDept FROM intrastaff WHERE id = '$id'");
$result = mysql_query($query);
echo'<form method="post">';
while($row = mysql_fetch_array($result)) //array rather than object
if (stripos($row['OtherDept'],"Company Commercial")!=false) //only use 1 equal sign
{
echo '<input type="checkbox" name="dept[]" value="what_value_here" checked="checked" /><br/>';
}
else
{
echo '<input type="checkbox" name="dept[]" value='what_value_here' /><br/>';
}
mysql_close($sqlconn);
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 5:32 am
by simonmlewis
Hi Mark
Now sure you do understand it - I'm trying to connect two tables. My SELECT query didn't include it, but was meant to say that I am trying:
select * FROM intradepartment, intrastaff
But this fails for me.
Need to select two tables, list check boxes for each entry in intradepartment, and only tick the box if there is a matching entry in the 'OtherDept' field in intrastaff.
Simon
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 5:44 am
by aceconcepts
Oh ok.
What you need to use is a LEFT JOIN for more info on joins take a look at:
http://www.w3schools.com/Sql/sql_join.asp
Code: Select all
$query = "SELECT * FROM intradepartment LEFT JOIN intrastaff ON intradepartment.id=intrastaff.id WHERE intrastaff.id = '$id'";
Using JOINS, you need a field from both tables that have a relationship e.g. patients and patient's dentists. A patient ID will exist in patient table and also a table that links patient with dentist e.g. patient_id -> dentist_id
Hope it makes sense. Check out the link i've supplied - pretty useful for understanding JOINS

Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 6:09 am
by simonmlewis
Code: Select all
<?php
$sqlconn=@mysql_connect("localhost","user","pass");
$rs=@mysql_select_db("dbname",$sqlconn);
$result = mysql_query ("SELECT * FROM intradepartment LEFT JOIN intrastaff ON
intradepartment.dept=intrastaff.OtherDept WHERE intrastaff.id = '$id'");
while ($row = mysql_fetch_object($result))
if (stripos($row['intradepartment.dept'],$row['intrastaff.OtherDept'])!=false)
{
echo "<input type='checkbox' name='dept[]' value='$row->dept'>$row->dept<br/>";
}
mysql_close($sqlconn);
?>
This fails too, in the fetch_object. I tried the fetch_array which also failed.
Simon
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 6:13 am
by aceconcepts
You may need to play around with it a little becuause i don't know what your database table fields are.
I used intrastaff.id etc... under the assumption that you used "id" as a field.
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 7:48 am
by simonmlewis
In the intradepartment table, I am looking in the 'dept' field.
In the intrastaff table, I'm checking for 'id' field, and then comparing the 'OtherDept' field with intradepartment.
I think it's also the IF statement that is causing problems.
This is the latest code that fails:
Code: Select all
$result = mysql_query ("SELECT * FROM intradepartment JOIN intrastaff ON
intradepartment.dept=intrastaff.OtherDept WHERE intrastaff.id = '$id'");
while ($row = mysql_fetch_array($result))
if ($row['intradepartment.dept']==$row['intrastaff.OtherDept'])
{
echo "<input type='checkbox' name='dept[]' value='$row->intradepartment.dept'
checked>$row->intradepartment.dept<br/>";
}
else
{
echo "<input type='checkbox' name='dept[]' value='$row->intradepartment.dept'>$row-
>intradepartment.dept<br/>";
}
mysql_close($sqlconn);
?>
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 7:51 am
by aceconcepts
Do you have ids for intradepartment and intrastaff?
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Thu Oct 09, 2008 7:56 am
by simonmlewis
Yes, however it is only querying the id in intrastaff.
A button is pressed on a previous page that posts the id of the staff member to this 'edit' page.
It then queries that id, finds the person and gathers the data from intrastaff.
It then needs to gather all the department names from intradepartment and render them on screen, but the IF statements needs to check if there is a match of department names. If there is, it renders it with the department name from intradepartment, and puts a check in it. If there is no match, it renders it with the department name from intradepartment, with NO check in it.
Make sense?
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Fri Oct 10, 2008 10:16 am
by simonmlewis
This is the latest code for this problem:
Code: Select all
$result = mysql_query ("SELECT * FROM intradepartment JOIN intrastaff ON intradepartment.dept=intrastaff.OtherDept WHERE
intrastaff.id = '$id'");
if (mysql_num_rows($result)==0) { echo "Error."; }
else {
while ($row = mysql_fetch_array($result))
if ($row->dept != $row->OtherDept)
{
echo "<input type='checkbox' name='dept[]' value='$row->dept'>$row->dept<br/>";
}
else
{
echo "<input type='checkbox' name='dept[]' value='$row->dept' checked>$row->dept<br/>";
}}
It produces:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource
Is anyone a whiz with connecting tables and querying both in PHP?
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Fri Oct 10, 2008 12:14 pm
by aceconcepts
That error basically means that the query isn't executing correctly.
Add "or die(mysql_error())" at the end of your query.
The most efficient way of getting info from more than one table is to use a JOIN - whether its a left, right or inner join.
You need to join your tables by IDs. IDs are typically unique identifiers for records in a table. Depending on how you build you database you would, again, typically have primary and foreign keys set as unique IDs.
Do some research on relation databases and tables keys - both primary and foreign.
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Fri Oct 10, 2008 1:31 pm
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 ,
I hope this helps.
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Fri Oct 10, 2008 3:07 pm
by simonmlewis
This is a bizarre piece of written code, as the PHP <? starts and ends throughout the code, so I am receiving various errors.
You have written a "while" and then ended it, which has - I think - confused the browser.
Should this not be a continuous query and IF statement?
Simon
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Fri Oct 10, 2008 4:37 pm
by aceconcepts
All "<?" and "?>" do is tell the browser the language you are using. He has ended php language using "?>" so that he can write plain HTML as opposed to embedding in PHP i.e.
Code: Select all
echo'<input name="blah" value="something" type="text" />';
Re: MySQL PHP joining tables and querying both for checkbox aray
Posted: Sat Oct 11, 2008 3:49 am
by simonmlewis
Oh ok, no offense intended to the fellow who wrote it - just not seen so many starts and ending, as I generally trying to keep it all in the query.
I get this error:
Parse error: syntax error, unexpected '}', expecting ',' or ';' in line 67
Line 67 in the file is this:
Code: Select all
<?php echo $row["dept"]."<br />"
***** }else{ *****
echo "No matching records";
... minus the **s of course.
There are equal number of {}s in there, but it's erroring.
Does it need a ; ?
Regards
Simon