Multi-Select Listbox Problems

[klycette]

I have several problems regarding multi-select listboxes, but I think they're all fairly simple and related. One problem I have is with a search page that allows people to make multiple selections from a listbox. This page passes all of the selections through to the search results page, so I don't think that's the problem. I also think that my SQL is fine. What I think is the problem is that I need some kind of loop to go through the selections being passed forward from the previous page. Here's what I have so far:

$varProvince_rsSearchResults = "1";
if (isset($HTTP_GET_VARS['province'])) {
$varProvince_rsSearchResults = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['province'] : addslashes($HTTP_GET_VARS['province']);

If I make multiple selections, the search results page brings back the results that contain the last province in the list that was selected, but not the results that contain all of the selected provinces. As I said, I have other problems with multi-select listboxes, but the problem with multiple choices seems to be recurring. If someone could re-write this bit od code or point me to a tutorial or something, I'd greatly appreciate it.

[kevinsequeira]

Hi,
could you post the form page and the search results page. i just went thru the earlier post of urs and i guess this is an extension of what you wanted to do then.
hopefully we should get this solved this time

reg
kevin

[klycette]

The form page is pretty basic...just a category and province listbox that people can choose from...no php involved, just using "GET" to forward the info on to the search results page. The php on the search results page runs like this:

<?php
$maxRows_rsSearchResults = 10;
$pageNum_rsSearchResults = 0;
if (isset($HTTP_GET_VARS['pageNum_rsSearchResults'])) {
$pageNum_rsSearchResults = $HTTP_GET_VARS['pageNum_rsSearchResults'];
}
$startRow_rsSearchResults = $pageNum_rsSearchResults * $maxRows_rsSearchResults;

$varProvince_rsSearchResults = "1";
if (isset($HTTP_GET_VARS['province'])) {
$varProvince_rsSearchResults = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['province'] : addslashes($HTTP_GET_VARS['province']);
}
$varCategory_rsSearchResults = "1";
if (isset($HTTP_GET_VARS['category'])) {
$varCategory_rsSearchResults = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['category'] : addslashes($HTTP_GET_VARS['category']);
}
mysql_select_db($database_connJobFerret, $connJobFerret);
$query_rsSearchResults = sprintf("SELECT jobs.job_title, jobs.province, jobs.city, jobs.posting_date, employers.employer_name FROM jobs, employers, job_categories WHERE employers.employer_id = jobs.employer_id AND jobs.job_id = job_categories.job_id AND jobs.province = '%s' AND job_categories.category_id = '%s' ORDER BY jobs.posting_date DESC", $varProvince_rsSearchResults,$varCategory_rsSearchResults);
$query_limit_rsSearchResults = sprintf("%s LIMIT %d, %d", $query_rsSearchResults, $startRow_rsSearchResults, $maxRows_rsSearchResults);
$rsSearchResults = mysql_query($query_limit_rsSearchResults, $connJobFerret) or die(mysql_error());
$row_rsSearchResults = mysql_fetch_assoc($rsSearchResults);

if (isset($HTTP_GET_VARS['totalRows_rsSearchResults'])) {
$totalRows_rsSearchResults = $HTTP_GET_VARS['totalRows_rsSearchResults'];
} else {
$all_rsSearchResults = mysql_query($query_rsSearchResults);
$totalRows_rsSearchResults = mysql_num_rows($all_rsSearchResults);
}
$totalPages_rsSearchResults = ceil($totalRows_rsSearchResults/$maxRows_rsSearchResults)-1;
?>

Most of it was genertated by Dreamweaver, but it does work fine with single selections from the listboxes on the previous page...just not with multiple selections. Please let me know if you need anything else...I'm just on my way to work, so this was a rushed response. Thanks in advance.

[kevinsequeira]

i hate code thats been auto-generated :mad: :mad:
well here is the problem in the query

<?php
if (isset($HTTP_GET_VARS['province'])) {
$varProvince_rsSearchResults = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['province'] : addslashes($HTTP_GET_VARS['province']);
}
$varCategory_rsSearchResults = "1";
if (isset($HTTP_GET_VARS['category'])) {
$varCategory_rsSearchResults = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['category'] : addslashes($HTTP_GET_VARS['category']);
} 
 $query_rsSearchResults = sprintf("SELECT jobs.job_title, jobs.province, jobs.city, jobs.posting_date, employers.employer_name FROM jobs, employers, job_categories WHERE employers.employer_id = jobs.employer_id AND jobs.job_id = job_categories.job_id AND jobs.province = '%s' AND job_categories.category_id = '%s' ORDER BY jobs.posting_date DESC", $varProvince_rsSearchResults,$varCategory_rsSearch
Results); 
?>

the query is checking for only one entry of the province and category array. you would have to rewrite it to check for all

see this post on how to get the array variables ..
http://www.phpbuilder.com/board/showthread.php?threadid=10240248

reg
kevin

[klycette]

So if I rewrite the code, where would I fit this code in what I already have?

<SELECT NAME="choices[]" MULTIPLE="MULTIPLE">
<OPTION VALUE="1">1</OPTION>
<OPTION VALUE="2">2</OPTION>
.
.
.
</SELECT>

on the page where you submit to access the listbox like this
<?php
for ($i = 0; $i < sizeof($choices); $i++) {
echo $choices[$i];
}

// or
print_r $choices;
?>

This was from the other post that you directed me to. Sorry, I'm just not sure how to incorporate it into what I have.

[kevinsequeira]

<?php
if (isset($HTTP_GET_VARS['province'])) {
		$varProvince_rsSearchResults = "";
  for ($i = 0; $i < sizeof($province); $i++) {
  	$varProvince_rsSearchResults .= " AND province = '$province[$i]'";
  }
}

// to debug echo $varProvince_rsSearchResults
echo $varProvince_rsSearchResults."<BR>";

if (isset($HTTP_GET_VARS['category'])) {
		$varCategory_rsSearchResults = "";
  for ($i = 0; $i < sizeof($category); $i++) {
  	$varCategory_rsSearchResults .= " AND category = '$category[$i]'";
  }
}

//to debug echo $varCategory_rsSearchResults
echo $varCategory_rsSearchResults."<BR>";

mysql_select_db($database_connJobFerret, $connJobFerret);
$query_rsSearchResults = "SELECT jobs.job_title, jobs.province, jobs.city, jobs.posting_date, employers.employer_name FROM jobs, employers, job_categories WHERE employers.employer_id = jobs.employer_id AND jobs.job_id = job_categories.job_id";
$query_rsSearchResults .= $varProvince_rsSearchResults.$varCategory_rsSearchResults; 
$query_rsSearchResults .=" ORDER BY jobs.posting_date DESC";

// to debug echo  $query_rsSearchResults
echo $query_rsSearchResults."<BR>";
?>

reg
kevin

[klycette]

I'm going to have to look into this a little more. I tried different combinations of what you gave me with what I had and even just yours alone, but I had no luck. I have some other server behaviours that depend on the recordset created by Dreamweaver so I'm going to have to learn how to work with them or get rid of them entirely. Thanks for your input.

[klycette]

I've been looking at this again recently and it's making more sense now that I'm hand-coding instead of using Dreamweaver. Essentially what's happening is you're looping through both array and building strings which are then concatenated onto the query, correct?

Since I want to look for any record in the database that corresponds to any of the selected provinces, I'll have to use OR instead of AND, so how do I incorporate that into what is here already? It's obviously going to have to be something like:

AND province = "this" OR province = "that" OR province = "theother"

in order to find all the records. How would I build this loop, since the first time I want AND and then OR for others in the loop?