[RESOLVED] Help passing checked checkboxes to a new page

Iain

This is really the same problem I'm trying to resolve in this thread - but to avoid confusion that one is using the same page, whereas this one is about how to pass the results through to a separate results page.

As before, I currently 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.

So I have a search page with various checkboxes :

<!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>SearchTechUK - technical recruitment consultancy specialising in the aviation aerospace industry - current vacancies</title>


</head>

<body>
<div id="wrapper">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td><img src="Images/bannerTop.png" alt="Aviation vacancies"></td>
</tr>
<tr>
<td><div id="navcontainer">
<ul id="navlist">
<li id="active"><a href="homepage.htm" id="current">Home</a></li>
<li><a href="client.htm">Client Services</a></li>
<li><a href="candidate.htm">Candidate Services</a></li>
<li><a href="vacancies.htm">Vacancies</a></li>
<li><a href="psychometric.htm">Psychometric Services</a></li>
<li><a href="contact.htm">Contact Us</a></li>
</ul>
</div></td>
</tr>
<tr>
<td><img src="Images/bannerBottom.png" alt="Technical aviation vacancies" border="0"></td>
</tr>
</table>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" valign="top">Search for a candidate by selecting from the profiles below. </td>
</tr>
<tr>
<td width="59%" valign="top">&nbsp;</td>
<td width="41%" valign="top">&nbsp;</td>
</tr>
<tr>
  <td valign="top">&nbsp;</td>
  <td valign="top">&nbsp;</td>
</tr>
<tr>
<td colspan="2" valign="top"><form name="form1" method="POST" action="profilesearchresults.php">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="3%" valign="middle"><input name="profiles[1]" type="checkbox" class="tickbox"></td>
<td width="22%">A1</td>
<td width="3%" valign="middle"><input name="profiles[5]" type="checkbox" class="tickbox"></td>
<td width="22%">757</td>
<td width="3%" valign="middle"><input name="profiles[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="profiles[2]" type="checkbox" class="tickbox"></td>
<td>B1</td>
<td valign="middle"><input name="profiles[6]" type="checkbox" class="tickbox"></td>
<td>767</td>
<td valign="middle"><input name="profiles[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="profiles[3]" type="checkbox" class="tickbox"></td>
<td>737 Classic </td>
<td valign="middle"><input name="profiles[7]" type="checkbox" class="tickbox"></td>
<td>TA319</td>
<td valign="middle"><input name="profiles[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="profiles[4]" type="checkbox" class="tickbox"></td>
<td>737 NG </td>
<td valign="middle"><input name="profiles[8]" type="checkbox" class="tickbox"></td>
<td>TA330</td>
<td valign="middle"><input name="profiles[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="Search">
  <input type="reset" name="Reset" value="Reset form"></td>
</tr>
</table>

</form></td>
</tr>
<tr>
<td colspan="2" valign="top">&nbsp;</td>
</tr>
</table>


</div>
</body>
</html>

But what I'm not sure about is how to pass through the selected checkboxes and then what the query should be in the results page.

Someone did post in the other thread a query of :

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

But it was within some PHP code - I tried it in my results page, but it wasn't liking it somewhere.

This is the very last bit of functonality I'm trying to add to this project, so if anyone could point me in the right direction here it would be hugely appreciated.

Cheers,
Iain

bogu

And the problem is?

Iain

The problem at the moment is that I'm trying to have a search page with various checkboxes, that returns Candidates that have the same profiles attached to them, based on the tables above.

So the search page is as above, and the results page currently has the following PHP at the start :

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

$currentPage = $_SERVER["PHP_SELF"];

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



$CandidateID = intval($_GET['CandidateID']);

$maxRows_Candidates = 5;
$pageNum_Candidates = 0;
if (isset($_GET['pageNum_Candidates'])) {
  $pageNum_Candidates = $_GET['pageNum_Candidates'];
}
$startRow_Candidates = $pageNum_Candidates * $maxRows_Candidates;

mysql_select_db($database_connection1, $connection1);
$query_Candidates = "SELECT * FROM Candidates";
$query_limit_Candidates = sprintf("%s LIMIT %d, %d", $query_Candidates, $startRow_Candidates, $maxRows_Candidates);
$Candidates = mysql_query($query_limit_Candidates, $connection1) or die(mysql_error());
$row_Candidates = mysql_fetch_assoc($Candidates);

if (isset($_GET['totalRows_Candidates'])) {
  $totalRows_Candidates = $_GET['totalRows_Candidates'];
} else {
  $all_Candidates = mysql_query($query_Candidates);
  $totalRows_Candidates = mysql_num_rows($all_Candidates);
}
$totalPages_Candidates = ceil($totalRows_Candidates/$maxRows_Candidates)-1;

$queryString_Candidates = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_Candidates") == false && 
        stristr($param, "totalRows_Candidates") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_Candidates = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_Candidates = sprintf("&totalRows_Candidates=%d%s", $totalRows_Candidates, $queryString_Candidates);

$TFM_LimitLinksEndCount = 5;
$TFM_temp = $pageNum_Candidates + 1;
$TFM_startLink = max(1,$TFM_temp - intval($TFM_LimitLinksEndCount/2));
$TFM_temp = $TFM_startLink + $TFM_LimitLinksEndCount - 1;
$TFM_endLink = min($TFM_temp, $totalPages_Candidates + 1);
if($TFM_endLink != $TFM_temp) $TFM_startLink = max(1,$TFM_endLink - $TFM_LimitLinksEndCount + 1);
?>

and the section displaying the results looks like :

<?php do { ?>
<tr>
<td class="idcell"><?php echo $row_Candidates['CandidateID']; ?></td>
<td class="fieldcell"><a href="admin/candidateDetails.php?CandidateID=<?php echo $row_Candidates['CandidateID']; ?>"><?php echo $row_Candidates['FirstName']; ?> <?php echo $row_Candidates['LastName']; ?></a></td>
<td class="fieldcell"><?php echo $row_Candidates['Telephone']; ?></td>
<td class="fieldcell"><?php echo $row_Candidates['Email']; ?></td>
<td class="fieldcell"><div align="center"><?php echo $row_Candidates['nb']; ?></div></td>
</tr>
<?php } while ($row_Candidates = mysql_fetch_assoc($Candidates)); ?>
<?php 
if ($totalRows_Candidates==0){
echo "<tr><td colspan='5'>*No Matching Candidates Found*</td></tr>"; 
}
?>

But the results page is displaying the actual SQL at the top of the page like :

SELECT * FROM Candidates, CandidateProfiles WHERE Candidates.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(on,on)

Rather than returning the matching candidates.

Roger_Ramjet

That is because you are telling it to echo the sql - not run it 😕

Iain

Oh - what needs changing?

I was working with the code posted in my other thread here :

here

In the second post.

Do I just need to change 'echo' to 'run'?

Iain

bogu

Shouldnt be for checkboxes values the candidates ID?
So your select should look something like this:

SELECT * FROM Candidates, CandidateProfiles WHERE Candidates.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(1,2,3,..,n)

Roger_Ramjet

What you have at present is

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

Which is why the query string is being printed at the top of the page.

You need to reun that query string somewhere using mysql_query($sql) for it retrieve the results.

Iain

I have code in other pages 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);

So is this the sort of thing?

Although I'd be unsure of the exact order / syntax, as presumably the SELECT has already been defined in the above?

Or could I have something like :

mysql_select_db($database_connSearchTechUK, $connSearchTechUK);
$query_Candidates = "SELECT * FROM Candidates, CandidateProfiles WHERE Candidates.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(1,2,3,..,n) 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);

In a results page?

Although with that I'm still unsure how the checked checkbox values are being passed through? Is it literally just in the POST command of the search form, so 1, 2, 3,..etc above will simply return matches on the checkboxes checked with the same numbers?

TobyRT

Hi Iain,

Following on from our previous discussions, I think you need to look at the logic of this, and understand how the process works, but basically, when you hit 'post' it sends the checked values as an array to SQL (this is why you're using the 'IN' part of the query)

You're essentially passing information from a source (the checkboxes) to a database to be searched. If the checkboxes are static (i.e. hardcoded) and aren't going to change, you only need the SQL in the results page and to then display it therein. This is a 'two page' scenario:
Page 1 - show the checkboxes
Page 2 - get relevant information and display it

IF, however, your checkboxes first need to be generated by a query (the different choices are also stored in a database) you'll need some SQL in that page too - essentially a 'three page' scenario. The only difference reallly is the way you retrieve the checkboxes.

In your other thread, we're doing basically the same thing as you're asking in here, except that instead of using two pages, you're using two 'clauses'.

Your SQL is fine (I think - only you can tell me that when you get the results) - it's just your overall logic and syntax that needs tweaking.

1) show checkboxes, put them in a form, give things values
2) post the form, with the action pointing to your results page
3) your results page then performs the SQL to pick up any results. If there are results, you display them, if not, give an error message

Iain

OK - cheers.

I think I'm getting there now. I have a search page with the checkboxes, although not sure which of three versions I should have (I'm currently getting the same results with all three).

checkboxes 1 :

<tr>
<td width="3%" valign="middle"><input name="profiles[]" value="1" type="checkbox" class="tickbox"></td>
<td width="22%">A1</td>
<td width="3%" valign="middle"><input name="profiles[]" value="5" type="checkbox" class="tickbox"></td>
<td width="22%">757</td>
<td width="3%" valign="middle"><input name="profiles[]" value="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>

etc

checkboxes 2 :

<tr>
<td width="3%" valign="middle"><input name="profiles[1]" type="checkbox" class="tickbox"></td>
<td width="22%">A1</td>
<td width="3%" valign="middle"><input name="profiles[5]" type="checkbox" class="tickbox"></td>
<td width="22%">757</td>
<td width="3%" valign="middle"><input name="profiles[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>

etc

checkboxes 3 :

<tr>
<td width="3%" valign="middle"><input name="profiles" value="1" type="checkbox" class="tickbox"></td>
<td width="22%">A1</td>
<td width="3%" valign="middle"><input name="profiles" value="5" type="checkbox" class="tickbox"></td>
<td width="22%">757</td>
<td width="3%" valign="middle"><input name="profiles" value="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>

etc

And in my results page I have the php at the top of the page for the recordset :

mysql_select_db($database_connSearchTechUK, $connSearchTechUK);
$query_Candidates = "SELECT DISTINCT * FROM Candidates, CandidateProfiles WHERE Candidates.CandidateID = CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(1,2,3,4,5,6,7,8,9,10,11,12)";
$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);

This is bringing up a record for each entry in CandidateProfiles table - so one candidate 3 times for each profile attached, one candidate 4 times etc.

So presumably it just needs something else in the PHP to do with the array and query it properly, so as to only return the records that match the checked checkboxes - which is where I get stumped...

Iain

OK - seem to have got it working where a candidate matches any of the selected checkboxes, with the SQL :

SELECT *, Count(*) As rank 

FROM Candidates, CandidateProfiles 

WHERE Candidates.CandidateID = CandidateProfiles.CandidateID 

AND CandidateProfiles.ProfileID IN(' . implode(',', $profiles).') 

GROUP BY Candidates.CandidateID 

ORDER BY rank DESC, Candidates.CandidateID

I just hadn't had the code that calls up / sets the array :

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

$profiles = array_keys($_POST['profiles']);

The absolute, very last thing that would be great to have would be to return exact matches tho', don't think this is quite right, but something along the lines of this...?

SELECT *, Count(*) As nb 

(SELECT Count(CandidateProfiles.ProfileID) 

FROM CandidateProfiles 

WHERE CandidateProfiles.CandidateID = $CandidateID) As na, 

FROM Candidates, CandidateProfiles 

WHERE Candidates.CandidateID = CandidateProfiles.CandidateID 

AND CandidateProfiles.ProfileID IN(' . implode(',', $profiles).') 

GROUP BY Candidates.CandidateID 

HAVING na = nb

TobyRT

This code inherently DOES return exact matches, as you're searching for what I assume is a unique ID? You're using the = operator, and the ID (not something LIKE the ID) HAS to be IN the array in $profiles

Iain

Not sure at all what's wrong with it - but the error message is :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT Count(CandidateProfiles.ProfileID) FROM CandidateProfiles WHERE Cand' at line 3

Iain

Think I'm giving up on this one - it's pretty much OK as it is, as it ranks the results - so includes in the reault page how many profiles are matched, and lists them in order of number of profiles matched.