creating a search form with multiple checkboxes

Iain

Hi there,

I'm still getting to grips with coding, using PHP and mySQL, and wonder if anyone can help with something I'm trying to do.

At the moment I have...

a table Candidates :

CandidateID (primary key, autonumber)
FirstName
LastName
etc

A table Profiles :

ProfileID (primary key, autonumber)
CandidateID (integer)

And an interlinking table CandidateProfiles :

CandidateID (integer)
ProfileID (integer)

So if the CandidateProfiles table looks like :

1, 2
1, 4
1, 6
2, 2
2, 5
2, 6
3, 1
3, 6
4, 2
4, 3
4, 5
4, 6

I'd want to have a search form with a checkbox for each Profile, and if checkboxes 2, 5, and 6 were checked to return a results page listing Candidates 2 and 4, because they have matching Profiles 2, 5 and 6.

Hope that makes sense and maybe someone can point me in the right diection with it...

Cheers,
Iain

devinemke

<?php
if (!isset($_POST['submit']))
{
	echo '<form action="" method="POST">';

for ($i = 1; $i <= 10; $i++)
{
	echo '<input type="checkbox" name="profiles[]" value="' . $i . '"> profile #' . $i . '<br>';
}

echo '
<input type="submit" name="submit" value="submit">
</form>
';
}
else
{
	if (isset($_POST['profiles']))
	{
		$sql = 'SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $_POST['profiles']) . ')';
		echo $sql;
	}
	else
	{
		echo 'you did not check any profiles';
	}
}
?>

Iain

Thanks for that - I can see that it looks exactly right - from that code, and hitting submit it displays the SQL query as :

SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(2,9)

but I'm not quite sure about where the results need to go - on the same page, or on another page - ie change :

echo '<form action="" method="POST">';

echo '<form action="RESULTSPAGE.PHP" method="POST">';

And if I have a results page, what does the recordset need to be, as it's coded on the search page?

TobyRT

according to Devine's answer, it's going on the same page.

The if(!isset) line tells php what to do if the form hasn't been submitted - ie display the form

If it HAS been submitted, it ignores the form and just displays the results.

You can use the action you've used (resultspage.php) or you could use $_SERVER['PHP_SELF'] - that way it doesn't matter if you decide to change your filenames or directory structure

Iain

I think half the trouble might be that I'm so used to using Dreamweaver - so creating recordsets / queries using that - and having the recordset code looking something like :

mysql_select_db($database_connSearchTechUK, $connSearchTechUK);
$query_Candidates = "SELECT * FROM Candidates ORDER BY CandidateID ASC";
$query_limit_Candidates = sprintf("%s LIMIT %d, %d", $query_Candidates, $startRow_Candidates, $maxRows_Candidates);
$Candidates = mysql_query($query_limit_Candidates, $connSearchTechUK) or die(mysql_error());
$row_Candidates = mysql_fetch_assoc($Candidates);

..rather than more embedded form in the code above.

So I'm not sure where to display the actual results, after hitting submit.

link

My starting point was a search page looking like this.

So to get from there to a results page like this.

If I could get this sussed it would be great, as it's the last major hurdle in the project I'm currently working on.

Iain

So if I have a page just with the code exactly as above it produces what looks like the correct SQL:

link, but just displays the SQL rather than the actual results.

In this second page, I've added a form action of "resultspage.php", which currently just displays all the records - I'm unsure of how to pass the generated SQL query through to the results page so that only those with matching profiles are displayed.

link

Must be nearly there tho'...?

TobyRT

I use DW too - no shame in that (to me at least!) - i find the syntax colouring very hand for spotting those missing quote marks 🙂

You simply have to work in code view and code by hand.

The reason it's just showing the SQL, I would wager, is that you haven't put in the opening PHP tags.

Have you connected to the database correctly?

Iain

D'oh! You might be right actually - I think I was too busy wondering about displaying the results in a repeat region table, with the fields displaying using :

<?php echo <'fieldname1> ?> etc ( < scuse the code which i know is wrong, but it's from memory and is still new to me).

Whereas presumably the code above is doing all that using a loop kind of thing?

So I guess my next question would be how to change the layout to something more tabular and fitting in with the site design as in the links I posted above, with the search and results pages?

With DW - I definitely agree with the colour coding, so even I can see when something isn't right. I'm fine with working with the HTML in code view, and OKish at fixing PHP where it's obviously different from something else doing pretty much the same thing that I can see is working or doing something - but not up to speed with coding it from scratch yet.

Cheers,
Iain

TobyRT

Let's look at the code and let me clarify what's going on for you...

//Start by opening the PHP tags :P
<?php
//These 2 lines say "if the form is not submitted, do this..."
if (!isset($_POST['submit'])) 
{

What are we told to do? Start the form - as I said in my earlier post, this action=" " can either be a specific file name, or as Devine seems to suggest, the same file itself, you can call this using $_SERVER['PHP_SELF']

 echo '<form action="" method="POST">';

Here comes the loop. I prefer while() loops myself, but this does it too - Devine could explain why he chose this particular method. This basically creates 10 checkboxes, each with a name (profiles) which puts stuff in the profiles[] array. I assume you'll be pulling this from the database? In which case, you'll want to put some db connection stuff and a query before you start the table. But anyway, this loop creates the contents of the form 🙂

  

  for ($i = 1; $i <= 10; $i++) 
    { 
        echo '<input type="checkbox" name="profiles[]" value="' . $i . '"> profile #' . $i . '<br>'; 
    }

OK, now we've got the contents, lets close the form and give it a 'submit' button

     

    echo ' 
    <input type="submit" name="submit" value="submit"> 
    </form> 
    '; 
} //NB - this curly bracket is closing the if(!isset) statement from the top

So essentially, this page (called whatever you like) has two actions - what to do it the form is NOT submitted - ie what it should do when it's initially loaded(above) and what to do after, if the form HAS been submitted (below).

else 
{ //start the 'else' part of the if-else construction

So, the form has been submitted - check that there's some content in the submission:

    if (isset($_POST['profiles'])) 
    {

ok there is content - let's use it in the query:

        $sql = 'SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $_POST['profiles']) . ')';

I didn't look at the code properly before - this is why you're seeing the SQL

        echo $sql;

So to display the results, you just need to say something along the lines of:

//start the table BEFORE the while loop, else you'll end up with a lot of tables :)
<table>
while $row=mysql_fetch_assoc($sql)
{
echo '<tr><td>'.$row['candidateid'].'</td><td>'.$row['profileid'].'</td></tr>';
} //this ends the while() loop

So now we've said what to do if there ARE items in the submitted form, let's end the if() statement, and say what to do if the user hasn't checked anything:


} 
else 
{ 
    echo 'you did not check any profiles'; 
} 
} 
?>

Hope this all makes sense, and apologies if I appear to be teaching my granny to suck eggs 🙂

Iain

Not at all - thanks for that. 🙂 I kind of follow what's doing what, but just wasn't sure about where the data should be displaying.

So if check I've got the PHP line to open the database connection, followed by :

<?php
if (!isset($_POST['submit']))
{
echo '<form action="" method="POST">';

//start the table BEFORE the while loop, else you'll end up with a lot of tables 🙂
<table>
while $row=mysql_fetch_assoc($sql)
{
echo '<tr><td>'.$row['candidateid'].'</td><td>'.$row['profileid'].'</td></tr>';
} //this ends the while() loop

for ($i = 1; $i <= 10; $i++) 
{ 
    echo '<input type="checkbox" name="profiles[]" value="' . $i . '"> profile #' . $i . '<br>'; 
} 

echo ' 
<input type="submit" name="submit" value="submit"> 
</form> 
';

}
else
{
if (isset($POST['profiles']))
{
$sql = 'SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $POST['profiles']) . ')';
echo $sql;
}
else
{
echo 'you did not check any profiles';
}
}
?>

Should I get some output?

And if I make the form action point to a results page, I can just use that table code which should bring through the SQL recordset from the search page and place it wherever in the page, again with the open connection line at the top of the page?

TobyRT

you need the SQL on the results page, but what I'm saying is that with this code, the search page is ALSO the results page.

If you want to have a separate results page, take out the 'if !isset and else' statementsand just display the form on one page, and the SQL on the other.

ah - and put square brackets around code when you post in here, then it's easier to read 🙂

Iain

I always wondered how you did that!

Not quite there tho' - I did have the database connection line.

Wherever I put the table in, I'm getting an error :

Parse error: parse error, unexpected '<' in /home/sites/searchtechuk.com/public_html/profilesearch.php on line 4

<table>

At the moment I have :

<?php require_once('Connections/connSearchTechUK.php'); ?>
<?php 
//start the table BEFORE the while loop, else you'll end up with a lot of tables  

<table> 
while $row=mysql_fetch_assoc($sql) 
{ 
echo '<tr><td>'.$row['candidateid'].'</td><td>'.$row['profileid'].'</td></tr>'; 
} //this ends the while() loop
if (!isset($_POST['submit'])) 
{ 
    echo '<form action="" method="POST">'; 

for ($i = 1; $i <= 10; $i++) 
{ 
    echo '<input type="checkbox" name="profiles[]" value="' . $i . '"> profile #' . $i . '<br>'; 
} 

echo ' 
<input type="submit" name="submit" value="submit"> 
</form> 
'; 
} 
else 
{ 
    if (isset($_POST['profiles'])) 
    { 
        $sql = 'SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $_POST['profiles']) . ')'; 
        echo $sql; 
    } 
    else 
    { 
        echo 'you did not check any profiles'; 
    } 
} 
?>

TobyRT

my fault - whenever you put html tags (like <table>) in php tags (<?php ?>) you have to echo them correctly:

you'll need to say

echo '<table>';

Iain

OK - thanks for all this.

I've got other pages that pull through a CandidateID to add in Profiles to that Candidate - so they must have code similar to what I need here.

What was stumping me mostly was how to record which checkboxes were ticked and incorporate that into the SQL query - which is clearly working with the code devine posted.

I'll sit down and have a closer look at it tonight.

(but will probably run into some problem and post back here later! 😃 )

Thanks again.

TobyRT

good luck - have fun

Iain

Still don't think I've got the table in the right place, and am getting this error message :

Parse error: parse error, unexpected T_WHILE, expecting ',' or ';' in /home/sites/searchtechuk.com/public_html/profilesearch.php on line 9

Where line 9 is

while $row=mysql_fetch_assoc($sql)

In :

<?php require_once('Connections/connSearchTechUK.php'); ?>
<?php 

if (!isset($_POST['submit'])) 
{ 
    echo '<form action="" method="POST">'; 
//start the table BEFORE the while loop, else you'll end up with a lot of tables  

echo '<table>' 
while $row=mysql_fetch_assoc($sql)
{ 
echo '<tr><td>'.$row['candidateid'].'</td><td>'.$row['profileid'].'</td></tr>'; 
} //this ends the while() loop


for ($i = 1; $i <= 10; $i++) 
{ 
    echo '<input type="checkbox" name="profiles[]" value="' . $i . '"> profile #' . $i . '<br>'; 
} 

echo ' 
<input type="submit" name="submit" value="submit"> 
</form> 
'; 
} 
else 
{ 
    if (isset($_POST['profiles'])) 
    { 
        $sql = 'SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $_POST['profiles']) . ')'; 
        echo $sql; 
    } 
    else 
    { 
        echo 'you did not check any profiles'; 
    } 
} 
?>

I've tried it in a few places, and with or without a ; at the end, but no joy I'm afraid...

Iain

If I have a table like this, in a search page :

<form name="form1" method="POST" action="profileresults.php">
	<table width="100%" border="0" cellspacing="0" cellpadding="0">
		<tr>
			<td width="3%" valign="middle"><input name="ckbox[1]" type="checkbox" class="tickbox"></td>
			<td width="22%">A1</td>
			<td width="3%" valign="middle"><input name="ckbox[5]" type="checkbox" class="tickbox"></td>
			<td width="22%">757</td>
			<td width="3%" valign="middle"><input name="ckbox[9]" type="checkbox" class="tickbox" ></td>
			<td width="22%">Sales Engineer </td>
			<td width="3%" valign="middle">&nbsp;</td>
			<td width="22%">&nbsp;</td>
		</tr>
		<tr>
			<td valign="middle"><input name="ckbox[2]" type="checkbox" class="tickbox"></td>
			<td>B1</td>
			<td valign="middle"><input name="ckbox[6]" type="checkbox" class="tickbox"></td>
			<td>767</td>
			<td valign="middle"><input name="ckbox[10]" type="checkbox" class="tickbox"></td>
			<td>Simulator Engineer </td>
			<td valign="middle">&nbsp;</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
		<td valign="middle"><input name="ckbox[3]" type="checkbox" class="tickbox"></td>
			<td>737 Classic </td>
			<td valign="middle"><input name="ckbox[7]" type="checkbox" class="tickbox"></td>
			<td>TA319</td>
			<td valign="middle"><input name="ckbox[11]" type="checkbox" class="tickbox"></td>
			<td>Technical Instructor </td>
			<td valign="middle">&nbsp;</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td valign="middle"><input name="ckbox[4]" type="checkbox" class="tickbox"></td>
			<td>737 NG </td>
			<td valign="middle"><input name="ckbox[8]" type="checkbox" class="tickbox"></td>
			<td>TA330</td>
			<td valign="middle"><input name="ckbox[12]" type="checkbox" class="tickbox"></td>
			<td>Technical Support</td>
			<td valign="middle">&nbsp;</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td colspan="8"><input type="submit" name="Submit" value="Finish">
  			<input type="reset" name="Reset" value="Reset form"></td>
		</tr>
	</table>
</form>

What would I need in the results page, as that isn't using a loop to create the checkboxes?

Iain

This is the sort of thing I'm thinking it must be something like, but not getting all the parts in the right place :

<?php require_once('Connections/connSearchTechUK.php'); ?>
<?php mysql_select_db($database_connSearchTechUK, $connSearchTechUK);
$query_Candidates = "SELECT * FROM Candidates ORDER BY CandidateID ASC";
$query_limit_Candidates = sprintf("%s LIMIT %d, %d", $query_Candidates, $startRow_Candidates, $maxRows_Candidates);
$Candidates = mysql_query($query_limit_Candidates, $connSearchTechUK) or die(mysql_error());
$row_Candidates = mysql_fetch_assoc($Candidates);
?>
<?php

$ck = $_POST['ckbox'];

foreach ($ck As $GetIndex => $GetValue){
if ($GetValue=='on'){
$sql .= "('{'$GetIndex'), ";
}
}
{ 
    if (isset($_POST['profiles'])) 
    { 
        $sql = 'SELECT * FROM Candidates, CandidateProfiles WHERE Candidate.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $_POST['ckbox']) . ')'; 
        echo $sql; 
    } 
    else 
    { 
        echo 'you did not check any profiles'; 
    } 
} 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<table width="600" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <?php do { ?>
  <tr>
    <td><?php echo $row_Recordset1['CandidateID']; ?></td>
    <td><?php echo $row_Recordset1['FirstName']; ?></td>
    <td><?php echo $row_Recordset1['LastName']; ?></td>
    <td>&nbsp;</td>
  </tr>
  <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

Any suggestions for either this, or where I was going wrong with devine's original code still?

Cheers,
Iain

TobyRT

First point - read the error message. It's expecting something, which means it isn't there 😛

the error is occuring on line 9 because there's no ';' terminating line 8.

There are clues in the errors ya know!

Looking at your subsequent posts, are you saying your checkboxes and options are hardcoded, rather than being pulled from a database?

Iain

Cheers - just back to this as I was away for a few days.

Re - the clue in the error - the error mentioned line 9, so that made me think the ';' was missing from line 9. But when I added it, it didn't help. Like I say, I'm only just startting out with PHP really.

Anyway, I added the missing ';' on line 8, but then there was a missing '(' on line 9.

So I changed :

while $row=mysql_fetch_assoc($sql)

to :

while ($row=mysql_fetch_assoc($sql))

And that at least is displaying the checkboxes :

link

But the page displays the error :

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/sites/searchtechuk.com/public_html/test3.php on line 9

As for whether they're hardcoded or not - from a functionality point of view it doesn't matter really - whatever would be easiest to generate the code for the comparison to bring up theCandidates with the matching Profiles to those checked on the search page.

But there is a Profiles table, so might that probably be the way to go?