Using check boxes as part of filter in PHP/SQL

[WAMFT1]

I am trying to get my php form to pull data from an sql database but I want to use the check boxes to filter the information it brings back. Can anybody help me on how to get this coded correctly.

checkbox filter.php

[Barand]

You give the checkboxes the same values that are stored in the table

 

(This code assumes the type columns in the database table are INT)

 

$typeList = join(',', array_map('intval', $_POST['Type']));

$sql = "SELECT * FROM APS WHERE Paycode='ANM' AND type IN ($typeList) ORDER BY `Date` DESC";

[WAMFT1]

The Type column in SQL is text, would it still work?

[Barand]

if text then the values need to be escaped with real_escape_string

 

eg

 

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$arr = array (
   'Jones',
   "O'Neill",
   'Smith'
);
$list  = "'" . join("','", array_map(array($db,'real_escape_string'), $arr)) . "'";
echo $list;    //--> 'Jones','O\'Neill','Smith'

[WAMFT1]

Thanks, I will give that a try

[WAMFT1]

Sorry to be a nuisance Barand, Still can't get the coding to work properly. I have been at it all day and about to pull my hair out. I can't get any data to display at all. Not sure what is going on. Even if I change checkbox selections noting happens.

comms.php

[Barand]

Your form method is GET

Your checkboxes are now called SelType

You haven't submitted the query

 

Apart from that ...

 

try

 

$SelType = join(',', array_map('intval', $_GET['SelType']));

$sql = "SELECT * FROM APS WHERE Paycode='ANM' AND Type IN ($SelType) ORDER BY `Date` DESC";  
$result = mysql_query($sql);

  

[WAMFT1]

Awesome!! Thanks heaps. It is working and just how I want it to.

[Barand]

The Type column in SQL is text, would it still work?

What happened to "text" type

[WAMFT1]

I added another field into the SQL to use the int value for querying and print the text on the display. I then wrote into the export query my end to assign 1 to commission, 2 to wealthtrac and 3 to rebate to insert into the online SQL. That way I would have the best of both worlds. 

[Barand]

Better to create a lookup table

 

 

TABLE type
 +------+------------+
 | type | typeDesc   |
 +------+------------+
 |  1   | commission |
 |  2   | wealthtrac |
 |  3   | rebate     |
 +------+------------+

 

and use a join to get the description

[WAMFT1]

That is what I have done internally before I export to SQL, but I guess it makes more sense to reduce the size of the file and server space.