filter the results of a database search based on choices from multiple select list

maxwell_

I have a select list that allows me to filter the results of a database search.
The user chooses from a dropdown menu then clicks submit.
The target page shows records filtered according to what was chosen from the list.
What I would like is to allow the user to choose multiple items from the
list while holding down the ctrl key, then filter the search results accordingly.

I know I need the change the select tag to from

softwareLevel to softwareLevel[]

I think my problem is mysql syntax.

This is the code for the currrent select tag

<select name="softwareLevel" id="softwareLevel">
         <?php
  do {  

  ?>
          <option value="<?php echo $row_rs_level['level_id']?>"><?php echo $row_rs_level['level']?></option>
          <?php
 } while ($row_rs_level = mysql_fetch_assoc($rs_level));
    $rows = mysql_num_rows($rs_level);
    if($rows > 0) {
       mysql_data_seek($rs_level, 0);
        $row_rs_level = mysql_fetch_assoc($rs_level);
  }
 ?>
      </select>

This is the code on the target page

$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.level_id = ". GetSQLValueString($_POST['softwareLevel'], "int") ." ORDER BY id ASC";
  }

Regards Maxwell

tomhath

It sounds like you need WHERE...IN...

Your query would look something like this, shouldn't be hard to build the comma separated string:

select ... WHERE titles.level_id in (1, 2, 3, 4, 5)

maxwell_

When I add var dump to the target page and submit the form using

<select name="softwareLevel"

the output is -

postarray(2) {
["softwareLevel"]=>
string(4) "5000"
["button"]=>
string(6) "Submit"
}

And the page filters according to the chosen option, no errors

If I try submitting the form while holding down ctrl and selecting 3
options using the following

<select name="softwareLevel[]"

the output is -

postarray(2) {
["softwareLevel"]=>
array(3) {
[0]=>
string(4) "5000"
[1]=>
string(4) "6000"
[2]=>
string(4) "8000"
}
["button"]=>
string(6) "Submit"
}

along with this error, also the page shows no results

Warning: stripslashes() expects parameter 1 to be string, array given in C:\apache\htdocs\tutor\public\software\list.php on line 54

in both tables titles and level level_id is a varchar, so why is php happy
when a single option is chosen,
then shows an error when set to multiple

this is the relevant code that makes up the target page list.php

$colname_rsTitles = "-1";
if (isset($_GET['id'])) {
  $colname_rsTitles = $_GET['id'];
}
mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = "-1";
if (isset($_GET['id'])) {
  $totalRows_rsTitles = $_GET['id'];
}
$colname_rs_comments = "-1";
if (isset($_GET['software_id'])) {
  $colname_rs_comments = $_GET['software_id'];
}


mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);

$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.level_id = ". GetSQLValueString($_POST['softwareLevel'], "int") ." ORDER BY id ASC";
}

tomhath

It looks like you have two things to deal with.

First, you need to determine whether $_GET['software_id'] is returning a string or an array.

http://php.net/manual/en/function.is-array.php

Once you know that, you need to build the Sql query correctly. You have the right code if single item was selected (a string); but if you have an array you need to join the elements together and use IN rather than =

http://php.net/manual/en/function.join.php

and make the query something like:

WHERE titles.level_id in (" . $joined_array . ") ORDER BY ..."

maxwell_

I used the is_array function

$colname_rs_comments = "-1";
if (isset($GET['software_id'])) {
$colname_rs_comments = $GET['software_id'];
}

$colname_rs_comments = array('this', 'is', 'an array');

echo is_array($colname_rs_comments) ? 'Array' : 'not an Array';
echo "\n";

$no = 'this is a string';

echo is_array($no) ? 'Array' : 'not an Array';

The output says not an array!

getarray(0) {
}

postarray(2) {
["softwareLevel"]=>
array(2) {
[0]=>
string(4) "6000"
[1]=>
string(4) "8000"
}
["button"]=>
string(6) "Submit"
}

Array not an Array

tomhath

The browser swallowed your newline. The is_array function worked:

First test returned:
Array

Second test returned:
not an Array

<?php

$colname_rs_comments = array('this', 'is', 'an array');
echo "First test: ";
echo is_array($colname_rs_comments) ? 'Array' : 'not an Array';
echo "\n<p/>";

$no = 'this is a string';
echo "Second test: ";
echo is_array($no) ? 'Array' : 'not an Array';
echo "\n<p/>";

?>

First test: Array
<p/>Second test: not an Array
<p/>

maxwell_

So now I know that it not an array

postarray(2) {
["softwareLevel"]=>
array(2) {
[0]=>
string(4) "6000"
[1]=>
string(4) "8000"
}
["button"]=>
string(6) "Submit"
}

First test: Array

Second test: not an Array

is it enough to simply change the query to

WHERE titles.level_id in (" . $colname_rs_comments . ") ORDER BY id ASC";

$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.level_id in (" . $colname_rs_comments . ") ORDER BY id ASC";

When I ran the above the output incorrectly showed no results.
No errors displayed.

johanafm

maxwell@;10985804 wrote:

<select name="softwareLevel"

postarray(2) {
  ["softwareLevel"]=>
  string(4) "5000"
  ["button"]=>
  string(6) "Submit"
}

$_POST['softwareLevel] is a string

<select name="softwareLevel[]"

postarray(2) {
  ["softwareLevel"]=>
  array(3) {
    [0]=>
    string(4) "5000"
    [1]=>
    string(4) "6000"
    [2]=>
    string(4) "8000"
  }
  ["button"]=>
  string(6) "Submit"
}

And now it's an array, i.e.
$POST['softwareLevel'][0] = 5000
$POST['softwareLevel'][1] = 6000
$_POST['softwareLevel'][2] = 8000

maxwell@;10985804 wrote:
Warning: stripslashes() expects parameter 1 to be string, array given in C:\apache\htdocs\tutor\public\software\list.php on line 54

Since you are now passing an array instead of a string

maxwell@;10985804 wrote:
in both tables titles and level level_id is a varchar, so why is php happy
when a single option is chosen,
then shows an error when set to multiple

It has nothing to do with if one or many options are selected! It has to do with wether you name your select (or other type of) element "softwareLevel" or "softwareLevel[]".

maxwell@;10985804 wrote:
GetSQLValueString($_POST['softwareLevel'], "int");

This only works if the above element is a string instead of an array of string, in which case you need to do call the above function for every element of $_POST['softwareLevel'].

Personally, I'd do something like

$where = '';
# You only need the where clause IF any options have been selected
if (count($_POST['softwareLevels']))
{
	# Create an array containing as many elements of 'int' as there are
	# elements in $_POST['softwareLevel']
	$types = array_pad(array(), count($_POST['softwareLevel']), 'int');

# Use array map to call GetSQLValueString() for each combination of elements
# in these two arrays, i.e. apply GetSQLValueString using 'int' for all of the input data
$_POST['softwareLevel'] = array_map('GetSQLValueString', $_POST['softwareLevel'], $types);

$where = sprintf(' WHERE titles.level_id IN (%s) ',
	implode(', ', $_POST['softwareLevel']));
}

$query = sprintf('SELECT stuff FROM table %s ORDER BY stuff', $where)<;

johanafm

maxwell@;10985804 wrote:
Warning: stripslashes() expects parameter 1 to be string, array given in C:\apache\htdocs\tutor\public\software\list.php on line 54

Why are you calling stripslashes? If you havn't called addslashes, you should not need to call stripslashes, at least not unless the php.ini setting [man]magic_quotes_gpc[/man] is turned on, and and as you can see by following this link

PHP documentation wrote:
This feature has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.

And as always, if you are not using PHP 5.3+ it's time to upgrade a year ago.

maxwell_

I have removed stripslashes as you said it is no longer needed.

I tried the array

$POST['softwareLevel'][0] = 5000;
$POST['softwareLevel'][1] = 6000;
$POST['softwareLevel'][2] = 7000;
$POST['softwareLevel'][3] = 8000;
$_POST['softwareLevel'][4] = 9000;

then run this part

$where = '';

You only need the where clause IF any options have been selected

if (count($_POST['softwareLevel']))
{

Create an array containing as many elements of 'int' as there are

elements in $_POST['softwareLevel']

$types = array_pad(array(), count($_POST['softwareLevel']), 'int'); 

# Use array map to call GetSQLValueString() for each combination of elements 
# in these two arrays, i.e. apply GetSQLValueString using 'int' for all of the input data 
$_POST['softwareLevel'] = array_map('GetSQLValueString', $_POST['softwareLevel'], $types); 

$where = sprintf(' WHERE titles.level_id IN (%s) ', 
    implode(', ', $_POST['softwareLevel']));

}

$query = sprintf('SELECT id, title, company, description, resources, location, url, image, keyword, copies FROM titles %s ORDER BY id ASC', $where);
}

Still no results filtered through,
this is the vardump even though I only select 2 options

postarray(2) {
["softwareLevel"]=>
array(5) {
[0]=>
int(5000)
[1]=>
int(6000)
[2]=>
int(7000)
[3]=>
int(8000)
[4]=>
int(9000)
}
["button"]=>
string(6) "Submit"
}

$colname_rsTitles = "-1";
if (isset($_GET['id'])) {
  $colname_rsTitles = $_GET['id'];
}
mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = "-1";
if (isset($_GET['id'])) {
  $totalRows_rsTitles = $_GET['id'];
}
$colname_rs_comments = "-1";
if (isset($_GET['software_id'])) {
  $colname_rs_comments = $_GET['software_id'];
}


mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);

mysql_select_db($database_abe, $abe);
$query_rs_comments = "SELECT * FROM guest";
$rs_comments = mysql_query($query_rs_comments, $abe) or die(mysql_error());
$row_rs_comments = mysql_fetch_assoc($rs_comments);
$totalRows_rs_comments = mysql_num_rows($rs_comments);

mysql_select_db($database_abe, $abe);
$query_rs_users = "SELECT * FROM users";
$rs_users = mysql_query($query_rs_users, $abe) or die(mysql_error());
$row_rs_users = mysql_fetch_assoc($rs_users);
$totalRows_rs_users = mysql_num_rows($rs_users);

mysql_select_db($database_abe, $abe);
$query_Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY titles.id";
$Recordset1 = mysql_query($query_Recordset1) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

if(!isset($_POST['softwareLevel'])){
if (!isset($_GET['class_id'])) {
//show all software titles
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles ORDER BY id ASC";

}else{
//show software titles filtered by Literacy of Numeracy (using URL GET variable)
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.class_id = ". GetSQLValueString($_GET['class_id'], "int") ." ORDER BY id ASC";
}
}else{

//show software titles filtered by Level

$_POST['softwareLevel'][0] = 5000;
$_POST['softwareLevel'][1] = 6000;
$_POST['softwareLevel'][2] = 7000;
$_POST['softwareLevel'][3] = 8000;
$_POST['softwareLevel'][4] = 9000;

$where = ''; 

# You only need the where clause IF any options have been selected

if (count($_POST['softwareLevel'])) 
{ 
    # Create an array containing as many elements of 'int' as there are 
    # elements in $_POST['softwareLevel'] 

$types = array_pad(array(), count($_POST['softwareLevel']), 'int'); 

# Use array map to call GetSQLValueString() for each combination of elements 
# in these two arrays, i.e. apply GetSQLValueString using 'int' for all of the input data 
$_POST['softwareLevel'] = array_map('GetSQLValueString', $_POST['softwareLevel'], $types); 

$where = sprintf(' WHERE titles.level_id IN (%s) ', 
    implode(', ', $_POST['softwareLevel'])); 
} 

$query = sprintf('SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles %s ORDER BY id ASC', $where);
}

$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);

echo "<pre>get";
var_dump($_GET);
echo "</pre>"; 

echo "<pre>post";
var_dump($_POST);
echo "</pre>";


$colname_rs_comments = array('this', 'is', 'an array'); 
echo "First test: "; 
echo is_array($colname_rs_comments) ? 'Array' : 'not an Array'; 
echo "\n<p/>"; 

$no = 'this is a string'; 
echo "Second test: "; 
echo is_array($no) ? 'Array' : 'not an Array'; 
echo "\n<p/>";

johanafm

maxwell@;10985960 wrote:
$_POST['softwareLevel'][0] = 5000;
$_POST['softwareLevel'][1] = 6000;
$_POST['softwareLevel'][2] = 7000;
$_POST['softwareLevel'][3] = 8000;
$_POST['softwareLevel'][4] = 9000;
this is the vardump even though I only select 2 options [/B]
postarray(2) {
["softwareLevel"]=>
array(5) {
[0]=>
int(5000)
[1]=>
int(6000)
[2]=>
int(7000)
[3]=>
int(8000)
[4]=>
int(9000)
}
["button"]=>
string(6) "Submit"
}

If you overwrite $_POST with new values in your code, it will not matter what you select in the form.

$query = sprintf('SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles %s ORDER BY id ASC', $where);

# error_log or echo the query if you don't get the expected results
printf('<pre>%s</pre>', htmlentities($query, ENT_QUOTES, 'utf-8'));
}

And of course post the actual query here if you need more help.

maxwell_

I tried

printf('<pre>%s</pre>', htmlentities($query, ENT_QUOTES, 'utf-8'));

while selecting 2 of the 5 options

there were no errors

this following printed to the screen

SELECT id, title, company, description, resources, location, url, image, keyword, copies FROM titles WHERE titles.level_id IN (5000, 8000) ORDER BY id ASC

Also the repeat region listing all items did not show at all
When I try any of the other options View all items ETC the repeat region works as it should

this is more or less the full page


$colname_rsTitles = "-1";
if (isset($_GET['id'])) {
  $colname_rsTitles = $_GET['id'];
}
mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = "-1";
if (isset($_GET['id'])) {
  $totalRows_rsTitles = $_GET['id'];
}
$colname_rs_comments = "-1";
if (isset($_GET['software_id'])) {
  $colname_rs_comments = $_GET['software_id'];
}


mysql_select_db($database_abe, $abe);
$query_rsTitles = sprintf("SELECT title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE id = %s ORDER BY id ASC", GetSQLValueString($colname_rsTitles, "int"));
$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);

mysql_select_db($database_abe, $abe);
$query_rs_comments = "SELECT * FROM guest";
$rs_comments = mysql_query($query_rs_comments, $abe) or die(mysql_error());
$row_rs_comments = mysql_fetch_assoc($rs_comments);
$totalRows_rs_comments = mysql_num_rows($rs_comments);

mysql_select_db($database_abe, $abe);
$query_rs_users = "SELECT * FROM users";
$rs_users = mysql_query($query_rs_users, $abe) or die(mysql_error());
$row_rs_users = mysql_fetch_assoc($rs_users);
$totalRows_rs_users = mysql_num_rows($rs_users);

mysql_select_db($database_abe, $abe);
$query_Recordset1 = "SELECT COUNT(guest.software_id) as COUNT, titles.id FROM titles LEFT JOIN guest ON titles.id = guest.software_id GROUP BY titles.id";
$Recordset1 = mysql_query($query_Recordset1, $abe) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


if(!isset($_POST['softwareLevel'])){
if (!isset($_GET['class_id'])) {
//show all software titles
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles ORDER BY id ASC";

}else{
//show software titles filtered by Literacy of Numeracy (using URL GET variable)
$query_rsTitles = "SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles WHERE titles.class_id = ". GetSQLValueString($_GET['class_id'], "int") ." ORDER BY id ASC";
}
}else{
//show software titles filtered by Level (using Form POST variable)

$where = ''; 
# You only need the where clause IF any options have been selected 
if (count($_POST['softwareLevel'])) 
{ 
    # Create an array containing as many elements of 'int' as there are 
    # elements in $_POST['softwareLevel'] 
    $types = array_pad(array(), count($_POST['softwareLevel']), 'int'); 

# Use array map to call GetSQLValueString() for each combination of elements 
# in these two arrays, i.e. apply GetSQLValueString using 'int' for all of the input data 
$_POST['softwareLevel'] = array_map('GetSQLValueString', $_POST['softwareLevel'], $types); 

$where = sprintf(' WHERE titles.level_id IN (%s) ', 
    implode(', ', $_POST['softwareLevel'])); 
} 

$query = sprintf('SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles %s ORDER BY id ASC', $where);

printf('<pre>%s</pre>', htmlentities($query, ENT_QUOTES, 'utf-8')); 

}

$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);
echo "<pre>get";
var_dump($_GET);
echo "</pre>"; 

echo "<pre>post";
var_dump($_POST);
echo "</pre>";


$colname_rs_comments = array('this', 'is', 'an array'); 
echo "First test: "; 
echo is_array($colname_rs_comments) ? 'Array' : 'not an Array'; 
echo "\n<p/>"; 

$no = 'this is a string'; 
echo "Second test: "; 
echo is_array($no) ? 'Array' : 'not an Array'; 
echo "\n<p/>";

Best regards Maxwell

johanafm

$query = sprintf('SELECT id, title, company, `description`, resources, location, url, image, keyword, copies FROM titles %s ORDER BY id ASC', $where);

printf('<pre>%s</pre>', htmlentities($query, ENT_QUOTES, 'utf-8'));

}

$rsTitles = mysql_query($query_rsTitles, $abe) or die(mysql_error());

1. Indent your code so you see what code blocks actually belong together and can see what code is executed under what circumetances.

Check what variables you use in different places. In this case you store your query in the variable $query, and then execute mysql_query using the (most likely) undefined variable $query_rsTitles. Should that variable be defined, it will at least not contain what you want it to.