filter results with checkbox multi

nbbcj · May 11, 2012

Hi there

I have a DB of design work all the work is categorise as Brand Identity, print or online

Some are a mix so project 1 is categorised as brand identity and online

Project 2 is print and online

Im trying to filter my results by using checkbox so when brand identity and online are checked and the filter button is clicked there val is sent to the display page, i am passing the val ok as i get this in the url bar (index1.php?cid=bi&cid2=online) but is not displaying only entry's with cat brand identity and online. its not displaying any thing but error msg

In my DB I have 3 columns cid, cid1, cid2.

cid= bi

cid1= print

cid2= online

I have 2 problems if anyone can help please.

1, I get no results on the display page(index1.php) when no filter is slected ie show all

i think this part needs a IF empty run this query if not empty ie filters have been passed to the page run this query

#######################

i get this error There was a problem with the SQL query: 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 '''' at line 1

#######################

2, I cant get the filters to filter so no display again (cant seam to get the sql select command to work right and fetch the results i need i just get the above error

Thank you for any help im still noobish but getting there slowly very slowly lol

Anymore info needed let me know

nav code (iv used method get to see what is being passed to index1.php the display page)

<form id="form1" name="filter" method="get" action="index1.php">
<ul>
    	<li><a href="/work/"title="Show All"<?php if ($_SESSION['section'] == 'all') { echo "class=\"selected\""; }?>>Show All</a></li>
        <li>Brand Identity <input name="cid" type="checkbox" value="bi" /></li>
        <li> Print <input name="cid1" type="checkbox" value="print" /></li>
<li>Online<input name="cid2" type="checkbox" value="online" /></li>
</ul>
  <input type="submit" name="" id="filter" value="Filter" />
</form>

display page code


<?php
error_reporting(E_ALL);
include ("includes/db_config.php");
mysql_connect($db_hostname,$db_username,$db_password);
@mysql_select_db($db_database) or die( "Unable to select database");

$cid= $_GET['cid'];
$cid1= $_GET['cid1'];
$cid2 = $_GET['cid2'];

$query="SELECT * from `$db_table` WHERE `cid`='".$cid."' or `cid1`='".$cid1."' or `cid2`'".$cid2."'";

$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error());
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 4;
    while($row = mysql_fetch_array($result))       
   {
      
       extract($row);

     
       if($i == 0)
          echo "<tr>";

      
       if($pro_name != "" && $pro_name != null)
       echo "<td class=\"results\"><a href=\"details.php?id=$row[id]\"><img src=\"/work/thumbnails/$row[thumbnail]\" alt=\"$row[thumbnail]\" height=\"155\" width=\"155\" /><br><h2>$row[pro_name]<br></a></h2><p>$row[short_details]</p></td>";
   
if(++$i == $max_columns)
       {
           echo "</tr>";
           $i=0;
       } 
   } 
} 


if($i > 0){    for($j=$i; $j<$max_columns;$j++) echo "<td> </td>";   echo '</tr>';} ?></table></td>
   </tr>
</table>

Psycho · May 11, 2012

Your structure makes no sense. Don't create columns such as cid, cid1, & cid2 where you will set the same value. Instead create columns called bi, print & online then use a 0 or 1 to indicate if that record belongs to that type or not. Then do the same for your checkboxes: name them bi, print & online and just give them a value of 1. Then the URL will look something like this: index1.php?bi=1&online=1

Now, in your processing script you could do something such as this

$whereClauses = array();
if(isset($_GET['bi'])) { $whereClauses = "bi=1"; }
if(isset($_GET['print'])) { $whereClauses = "print=1"; }
if(isset($_GET['online'])) { $whereClauses = "online=1"; }

$query = "SELECT * FROM `$db_table`";
if(count($whereClauses))
{
    $query .= " WHERE " . implode(" AND ", $whereClauses);
}

nbbcj · May 11, 2012

Hay Psycho thank you for your reply,

i have tried what you said but Im running in to a problem i now get a display with the index1.php page but a error mas when i try to filter.

here is the error msg.

There was a problem with the SQL query: 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 '' at line 1

i have changed my DB now and added the bi,prit,online as you suggested and i was thinking that would be a better way to do it, so when i add new cats it will be easy

thank you once again if you could take a look at the code and let me no what iv done wrong please.

And can you offer any advice on sql inject protection like a link to a good noobies how to or just more info about it please

thank you thank you thank you

<div id="right_column">
    <table class="table" id="home_index_tb">
   <tr class="results_bg">
    <?php
error_reporting(E_ALL);

include ("includes/db_config.php");





mysql_connect($db_hostname,$db_username,$db_password);
@mysql_select_db($db_database) or die( "Unable to select database");

$whereClauses = array();
if(isset($_GET['bi'])) { $whereClauses = "bi=1"; }
if(isset($_GET['print'])) { $whereClauses = "print=1"; }
if(isset($_GET['online'])) { $whereClauses = "online=1"; }

$query = "SELECT * FROM `$db_table`";
if(count($whereClauses))
{
    $query .= " WHERE " . implode(" AND ", $whereClauses);
}


$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error());
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 4;
    while($row = mysql_fetch_array($result))       
   {
      
       extract($row);

     
       if($i == 0)
          echo "<tr>";

      
       if($pro_name != "" && $pro_name != null)
       echo "<td class=\"results\"><a href=\"details.php?id=$row[id]\"><img src=\"/work/thumbnails/$row[thumbnail]\" alt=\"$row[thumbnail]\" height=\"155\" width=\"155\" /><br><h2>$row[pro_name]<br></a></h2><p>$row[short_details]</p></td>";
   
if(++$i == $max_columns)
       {
           echo "</tr>";
           $i=0;
       } 
   } 
} 


if($i > 0){    for($j=$i; $j<$max_columns;$j++) echo "<td> </td>";   echo '</tr>';} ?></table></td>
   </tr>
</table>
</div><!--END RIGHT_COLUMN -->

and the filter box code

<ul>

<li>Brand Identity<input name="bi" type="checkbox" value="1" /></li>

<li>Print <input name="print" type="checkbox" value="1" /></li>

<li>Online<input name="online" type="checkbox" value="1" /></li>

</ul>

</form>

Drummin · May 12, 2012

Update these lines

if(isset($_GET['bi'])) { $whereClauses[] = "bi=1"; }
if(isset($_GET['print'])) { $whereClauses[] = "print=1"; }
if(isset($_GET['online'])) { $whereClauses[] = "online=1"; }

Psycho · May 12, 2012

Update these lines

if(isset($_GET['bi'])) { $whereClauses[] = "bi=1"; }
if(isset($_GET['print'])) { $whereClauses[] = "print=1"; }
if(isset($_GET['online'])) { $whereClauses[] = "online=1"; }

Good catch

nbbcj · May 12, 2012

cool thank you so much ppl ill give it a go now and report back

EDIT

its all running good when one filter is selected but with more than one filter i get no display any idea

url is /index1.php?print=1&online=1 no display

but

/index1.php?print=1 or bi=1 or online=1 all fine

any help please iv looked at the code but i cant work out whats up.

thanks CJ

nbbcj · May 12, 2012

cool thank you so much ppl ill give it a go now and report back

EDIT

its all running good when one filter is selected but with more than one filter i get no display any idea

url is /index1.php?print=1&online=1 no display

but

/index1.php?print=1 or bi=1 or online=1 all fine

any help please iv looked at the code but i cant work out whats up.

thanks CJ

EDIT again

with error reporting on i get this notice

Notice: Undefined variable: i in /home/sites/loveidee.com/public_html/work/index1.php on line 145

line 145 is this

if($i > 0){ for($j=$i; $j<$max_columns;$j++) echo "<td> </td>"; echo '</tr>';} ?></table></td>

im going to have a play with the code c if i can work fix it

but any help would be good thanks

nbbcj · May 12, 2012

my dum ass lol

its all working i just didnt have the 1 and 0 set right in the data base :S

here is the full working code it filters and displays the results in a 4x6 grid

filter links code (clean code so anyone can use ) SIDE note i now use POST not GET as it does not show in the url

<form id="form1" name="filter" method="GET" action="index1.php">
<ul>
    	<li><a href="/work/"title="Show All">Show All</a></li>
        <li>Brand Identity:<input name="bi" type="checkbox" value="1" /></li>
        <li>Print:<input name="print" type="checkbox" value="1" /></li>
<li>Online:<input name="online" type="checkbox" value="1" /></li>
</ul> 
  <input type="submit" name="" id="filter" value="Filter" />
</form>

and the display code

<table class="table" id="home_index_tb">
   <tr class="results_bg">
    <?php
error_reporting(E_ALL);

$db_hostname = 'localhost';				// database host (usually 'localhost')
$db_username = '';		// database username
$db_password = '';		// database password
$db_database = ''; 		//database name
$db_table = '';                 //table name

mysql_connect($db_hostname,$db_username,$db_password);
@mysql_select_db($db_database) or die( "Unable to select database");

$whereClauses = array();
if(isset($_GET['bi'])) { $whereClauses[] = "bi=1"; }
if(isset($_GET['print'])) { $whereClauses[] = "print=1"; }
if(isset($_GET['online'])) { $whereClauses[] = "online=1"; }

$query = "SELECT * FROM `$db_table`";
if(count($whereClauses))
{
    $query .= " WHERE " . implode(" AND ", $whereClauses);
}


$result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error());
if($result && mysql_num_rows($result) > 0)
{
    $i = 0;
    $max_columns = 4; // SET THE AMOUNT OF column
    while($row = mysql_fetch_array($result))       
   {
      
       extract($row);

     
       if($i == 0)
          echo "<tr>";

       // change pro_name to the name of the column you want to count
       if($pro_name != "" && $pro_name != null) // change pro_name to the name of the column you want to count  
       echo "<td class=\"YOUR CLASS\"><a href=\"details.php?id=$row[id]\"><img src=\"THUMBNAIL DIR/$row[thumbnail]\" alt=\"$row[thumbnail]\" height=\"155\" width=\"155\" /><br><h2>$row[THE COLUMN YOU WANT TO DISPLAY]<br></a></h2><p>$row[THE COLUMN YOU WANT TO DISPLAY]</p></td>";
   
if(++$i == $max_columns)
       {
           echo "</tr>";
           $i=0;
       } 
   } 
} 


if($i = 0){    for($j=$i; $j<$max_columns;$j++) echo "<td> </td>";   echo '</tr>';} ?></table></td>
   </tr>
</table>

i hope all that is understandable and im no way a DEV just some one trying to get on in this big bad world.

ALSO there is no sql inject protection as im now learning this and i be leave this page dont need it ?????? hum

comments any one

thanks CJ

Psycho · May 13, 2012

In this particular case you do not need to worry about SQL injection because you are not using any user defined values in the query. You are only using the user defined values to set the additional WHERE conditions which are hard-coded and safe (as they are currently written).

Sign In

filter results with checkbox multi

Recommended Posts

nbbcj

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

nbbcj

Link to comment

Share on other sites

Drummin

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

nbbcj

Link to comment

Share on other sites

nbbcj

Link to comment

Share on other sites

nbbcj

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information