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

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

MySQL PHP joining tables and querying both for checkbox aray

Post 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
Last edited by simonmlewis on Sat Oct 11, 2008 2:18 pm, edited 1 time in total.
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 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);
 
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 »

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

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 :D
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 »

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

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

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);
?>  
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 »

Do you have ids for intradepartment and 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 »

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

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

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.
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 ,
I hope this helps.
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 »

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

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" />';
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 »

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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply