Page 1 of 1

help filtering data with checkboxes

Posted: Sun Jun 26, 2005 11:12 pm
by darkcrimson
Hello,

I first will say that I am pretty new to PHP. I am working on a project for a website that has records that are entered in to a DB the records are then displayed in a page that shows a preview. IE, you see the address, value, # of bedrooms etc.. You can click a link and it will display details..nothing tough there..

Here is the hard part.. I need to find how to filter these records with checkboxes (next to each DB record ie. 1 main street, 2 main street etc..) when the user clicks a form button so that the other non-selected records disappear. I've figured out how to put the form button and make a new check box on each line but I cannot figure out a way to sort the data.

I have tried several ways including making the form go to a 2nd page which would only show the data that was checked but I cannot figure out how to make that work nor can I figure out how to do it with just one page.

I am really deserpate for an answer because I am really stuck on this and would like to get it done as soon as possible.

Below is the code for the site, the way I have it now it goes to a 2nd page but that is not required, I just assume that will be easiest.

Any suggestions on what to do? Thanks in advance!

Code: Select all

<title>Portfolio Listings</title>
    
<table width="690" cellspacing="0" cellpadding="3" border="0" style="font-family: sans-serif; font-size: 9pt" align="center">
  <!--DWLayoutTable-->
  <tr> 
    <td width="31" height="21" valign="top"><div align="center"><strong>View</strong></div>
    </td>
    <td width="139" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=1" class="content">Address</a></div>
    </td>
    <td width="111" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=2" class="content">City</a></div>
    </td>
    <td width="28" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=3" class="content">State</a></div>
    </td>
    <td width="100" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=4" class="content">County</a></div>
    </td>
    <td width="35" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=5" class="content">Units</a></div>
    </td>
    <td width="58" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=6" class="content">Bedrooms</a></div>
    </td>
    <td width="32" align="center" valign="top" nowrap><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=7" class="content">Baths</a></div>
    </td>
  <td width="102" valign="top"><div align="center"><a href="../../portfolio/list.php?contract_num=<?echo $contract_num?>&sortby=8" class="content">Assessed
        Value</a></div>
  </td>
  <td width="102" valign="top"> 
  </form>
  <form name='filter' action='checked.php' method='post'>
    <input type='submit' name='submit' value='Filter' ></td> </tr>

That is the HTML part with the form.. below is the coding..

Code: Select all

<?// execute the statement

if (!$result = mysql_query($stmt, $dblink)) {
        print("There was and error in the query ".mysql_error());
        print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
        exit();
} else {
while ($row = mysql_fetch_array($result)) {
				$row_color = altBgColor();
			    print ("<tr bgcolor=\"".$row_color."\">\n");
				print "\n<td align=\"left\" nowrap>
				<a href=\"show_property.php?id=
				".$row["ID"]."\">Details</a></td>";
				print "\n<td align=\"left\">".$row["address"]."</td>";
				print "\n<td align=\"left\">".$row["city"]."</td>";
				print "\n<td align=\"left\">".$row["state"]."</td>";
				print "\n<td align=\"center\">".$row["county"]."</td>";
                print "\n<td align=\"left\">".$row["units"]."</td>";
                print "\n<td align=\"left\">".$row["bedrooms"]."</td>";
				print "\n<td align=\"left\"nowrap>".$row["baths"]."</td>";
				print "\n<td align=\"left\"nowrap>".$row["assessed_value"]."</td>";
				print "\n<td align=\"left\"nowrap><input type='checkbox' name='kc' value='ck' /></td>";
				print "</tr>";
        }
		}
?>
</table>



Once again, any help would be great, thanks!

d11wtq | Like the man himself says - we have

Code: Select all

tags for PHP code [/color]

Posted: Sun Jun 26, 2005 11:51 pm
by Burrito
use the PHP tags for you php code...

so if I understand you correctly, you want a checkbox next to something that will make certain records NOT display? If so, try the following:

Code: Select all

&lt;input type=&quote;checkbox&quote; name=&quote;checkboxone&quote; value=&quote;col1&quote;&gt;check here

Code: Select all

$where = &quote;&quote;;
if(isset($_POST&#1111;'checkboxone']))
  $where .= &quote;where col1 &lt;&gt; '&quote;.$_POST&#1111;'checkboxone'].&quote;'&quote;;
$query = &quote;select * from myTable &quote;.$where;
$result = mysql_query($query)
  or die(mysql_error());
//etc

Posted: Mon Jun 27, 2005 3:12 am
by phpScott
change your checkbox name into an array like

Code: Select all

&lt;input type='checkbox' name='kc&#1111;]' id='kc' value='ck' /&gt;
that way when the form gets submitted you will get an array of values called kc.

Code: Select all

print_r($_GET&#1111;'kc']);
you will also noticed that I put an id element in incase you need to refrence the list of checkboxes using js.

Posted: Tue Jun 28, 2005 11:35 am
by darkcrimson
hmm..

no luck guys :(

I think the array idea is a good one but I am not sure of the coding, you mentioned

Code: Select all

print_r($_GET['kc']);
but I do not quite understand how to use this. Would this go on a 2nd page called checked.php and I remove the other print values and replace with that? I tried something similar but nothing showed up on the 2nd page.

As for the first idea, I am not sure where to put this code either, it seems to give me a blank white page no matter where I try to put it.

any ideas?

Posted: Tue Jun 28, 2005 11:42 am
by Burrito
print_r just prints out the array (keys and values).

what Scott was suggesting was to just create an array of your checkboxes all with the same name:

ex:

Code: Select all

&lt;input type=&quote;checkbox&quote; name=&quote;mycheck&#1111;]&quote; value=&quote;bob&quote;&gt;
&lt;input type=&quote;checkbox&quote; name=&quote;mycheck&#1111;]&quote; value=&quote;larry&quote;&gt;
&lt;input type=&quote;checkbox&quote; name=&quote;mycheck&#1111;]&quote; value=&quote;billy&quote;&gt;

Code: Select all

print_r($_POST&#1111;'mycheck']);
// will output your array with the keys and values
you would just have to loop over the array to build your query to exclude the options you don't want on your query...probably easiest (and best) to use a foreach loop

Posted: Tue Jun 28, 2005 11:53 am
by darkcrimson
that would be easiest,but the problem is where the last check box is, i am not manually updating the check boxes, as there could be thousands of records.

Code: Select all

<?// execute the statement

if (!$result = mysql_query($stmt, $dblink)) {
        print("There was and error in the query ".mysql_error());
        print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
        exit();
} else {
while ($row = mysql_fetch_array($result)) {
				$row_color = altBgColor();
			    print ("<tr bgcolor=\"".$row_color."\">\n");
				print "\n<td align=\"left\" nowrap>
				<a href=\"show_property.php?id=
				".$row["ID"]."\">Details</a></td>";
				print "\n<td align=\"left\">".$row["address"]."</td>";
				print "\n<td align=\"left\">".$row["city"]."</td>";
				print "\n<td align=\"left\">".$row["state"]."</td>";
				print "\n<td align=\"center\">".$row["county"]."</td>";
                print "\n<td align=\"left\">".$row["units"]."</td>";
                print "\n<td align=\"left\">".$row["bedrooms"]."</td>";
				print "\n<td align=\"left\"nowrap>".$row["baths"]."</td>";
				print "\n<td align=\"left\"nowrap>".$row["assessed_value"]."</td>";
				print "\n<td align=\"left\"nowrap><input type='checkbox' name='id' value='ID'></td>"; 
				print "</tr>";
        }
		}

?>
that is my main issue.. making unique check boxes for each automatically and then displaying ONLY the records that were selected when the form button is clicked... the way the code is set up as you can see if each record is printed on a new line so making value=bob value=jane etc is not an option, pretty much an array is the only way as far as I can tell but I cannot seem to get that to work the way I need it to.

Posted: Tue Jun 28, 2005 12:05 pm
by Burrito
so now I'm confused as to what your'e trying to do. I thought the goal was to refine your search by allowing users to check boxes to "remove" fields from the search query.

if that's the case, you could just put a checkbox next to every one of the fields you have listed in your while loop and name those checkboxes the id of the row with their value being the field name. Then just loop over the checkbox array to strip out the fields you don't want.

am I missing something else?

Posted: Tue Jun 28, 2005 12:51 pm
by darkcrimson
well the real purpose of the check boxes will be implimented later on..

I'll try to explain further:

first before the php page is a HTML page, it has links to portfolio 1,2, 3 etc.. the user clicks one of those links and then the records corrisponding to this particular portfolio (known as contract_number) are displayed. There could be 2 or 2000 items here. On this page you just see address, price etc.. just basic info. There is a details link for each record which when clicked sends the user to a page for details on that particular entry.

There is also a check for for each record, now assumming there are 2000 entries, if the user only wants to see entries 1,5,25 and 999 they just click the box next to that entry and click the form button. At this point the non checked records disappear.

Later on there will be an exel spreadsheet generated with info on only those selected, but I will worry about that later, right now I just need to filter out nonchecked entries.

hope that helps

Posted: Tue Jun 28, 2005 12:56 pm
by Burrito
ok that makes it easier.

just use the array we've been discussing, then implode() it separating the values by commas and in your select use "where id IN ($impodedvalue)"

that will only return rows that have been checked.

Posted: Tue Jun 28, 2005 2:29 pm
by darkcrimson
ok i've changed the code a bit and almost have it working correctly.


first of all, here is my new code:

Code: Select all

$stmt = "SELECT ID,address,city,state,county,units,bedrooms,baths,assessed_value,contract_num FROM portfolio WHERE contract_num = '".$contract_num."'";

$list = '';
if(!empty($_POST['kc']) && is_array($_POST['kc'])) {
  foreach($_POST['kc'] as $kc) {
    $kc = (int)$kc;
    if($kc > 0) {
      $list .= "$kc";
    }
  }
  $stmt .= " AND ID IN ($list)";
}

if ($sortby==1) 
$stmt .= " ORDER BY address";

elseif ($sortby==2) 

$stmt .= " ORDER BY city";
....................


how it works currently is if you only select 1 record and click the button, the other data disappears, however if you select more than 1 record, you get a blank table.

the code that changes this is:

Code: Select all

$list .= "$kc";
    }
  }

if I change it to

Code: Select all

$list .= "$kc,$kc,$kc,$kc";
for example, I can show 4 records, if I ake out 2 of those, I can show 2 but the fact is I will be working with an unknown amount that could be 10 or 10,000 and copying and pasting $kc is not the best solution.

Is there a way I can display all the checked records no matter the number with only one line of code?

ALSO just for reference: this is what I have for the check boxes

Code: Select all

print "\n<td align=\"left\"nowrap><input type='checkbox' name='kc[]' value='{$row['ID']}' /></td>";