How to set up a search that uses checkboxes?

Question

tc1967uk 0 Newbie Poster

15 Years Ago

Hi

I'm a total newb at php and mysql but using Dreamweaver I've managed to create a php webpage containing a series of checkboxes (I think of them as tags) and a text field (for URLs) that allows me to enter data into a database. I've done a test post, and it works.

The problem is, I don't know how to create a search function that will allow me to tick the same boxes and do a search that will bring up the URLs in the text field.

Can someone suggest how to edit my page to add a search option that brings up the results a user defines, with options to choose between showing the result that meet ALL defined criteria, or just SOME criteria? The results should open in a new page.

How the search should work - E.g. if we have links on the database that have the following tags:

AB
A
AC
B
C
BC

and a user ticks the boxes marked A & C, and also the button marked 'Show results that meet ANY criteria'. He would then see the following results:

AB
A
C
BC

However, if he had clicked 'Show results that meet ALL criteria' he would have gotten this instead:

AC

At this moment, I THINK I only need simple script that I could build on, so if someone can suggest a basic script to my homepage, I would be grateful.

I'll include my homepage script and also my database script, in case that helps. Thanks for reading this.

================================================

<?php require_once('Connections/mine_timbo.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : 

mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO timbo (apple, strawberry, liverpool, manchesterunited, chelsea, 

arsenal, everton, circle, square, triangle, hexagon, red, orange, yellow, green, algeria, brazil, 

chile, denmark, egypt, finland, url) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 

%s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString(isset($_POST['apple']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['strawberry']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['liverpool']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['manchesterunited']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['chelsea']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['arsenal']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['everton']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['circle']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['square']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['triangle']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['hexagon']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['red']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['orange']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['yellow']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['green']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['algeria']) ? "true" : "", 

"defined","'Y'","'N'"),
					   GetSQLValueString(isset($_POST['brazil']) ? "true" : "", 

"defined","'Y'","'N'"),
					   GetSQLValueString(isset($_POST['chile']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['denmark']) ? "true" : "", 

"defined","'Y'","'N'"),
					   GetSQLValueString(isset($_POST['egypt']) ? "true" : "", 

"defined","'Y'","'N'"),
					   GetSQLValueString(isset($_POST['finland']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString($_POST['url'], "text"));

  mysql_select_db($database_mine_timbo, $mine_timbo);
  $Result1 = mysql_query($insertSQL, $mine_timbo) or die(mysql_error());

  $insertGoTo = "thanks.html";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Index.me !</title>
<style type="text/css">
<!--
.style3 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: x-small; }
.style4 {font-size: x-small}
.style6 {color: #FF0000}
.style8 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: x-small; font-weight: bold; }
-->
</style>
<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
</head>

<body>
<table width="800" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td><img src="logo2.png" alt="logo" width="798" height="87" /></td>
  </tr>
  
  <tr>
    <td><form action="<?php echo $editFormAction; ?>" method="post" name="form1" id="form1">
      <table border="0" align="left" bgcolor="#FFFFFF">
          <tr valign="baseline">
            <td width="57" align="left" nowrap="nowrap" bgcolor="#FFFFFF"><span 

class="style3">Fruit</span></td>
            <td colspan="2" bgcolor="#FFFFFF"><span class="style3">Apple:
              <input name="apple" type="checkbox" id="apple" value="" />
            Strawberry:
              <input name="strawberry" type="checkbox" id="strawberry" value="" />
</span></td>
          </tr>
          <tr valign="baseline">
            <td align="left" nowrap="nowrap" bgcolor="#DDE3F4"><span class="style3">Team:</span></td>
            <td colspan="2" bgcolor="#EBEFF9"><span class="style3">Liverpool:
                <input name="liverpool" type="checkbox" id="liverpool" value="" />             
              Manchester United: 
              <input name="manchesterunited" type="checkbox" id="manchesterunited" value="" />
            Chelsea:
            <input name="chelsea" type="checkbox" id="chelsea" value="" />             
            Arsenal:
            <input name="arsenal" type="checkbox" id="arsenal" value="" />             
            Everton:
            <input name="everton" type="checkbox" id="everton" value="" />
            </span></td>
          </tr>
          <tr valign="baseline">
            <td align="left" nowrap="nowrap" bgcolor="#FFFFFF"><span 

class="style3">Shapes:</span></td>
            <td colspan="2" bgcolor="#FFFFFF"><span class="style3">Square:
                <input name="square" type="checkbox" id="square" value="" />
            Circle: 
            <input name="circle" type="checkbox" id="circle" value="" />             
            Triangle:
            <input name="triangle" type="checkbox" id="triangle" value="" />             
            Hexagon:
            <input name="hexagon" type="checkbox" id="hexagon" value="" />
            </span></td>
          </tr>
          <tr valign="baseline">
            <td align="left" nowrap="nowrap" bgcolor="#DDE3F4"><span 

class="style3">Colours:</span></td>
            <td colspan="2" bgcolor="#EBEFF9"><span class="style3">Red:
                <input name="red" type="checkbox" id="red" value="" />             
              Orange:
              <input name="orange" type="checkbox" id="orange" value="" />
            Yellow:
            <input name="yellow" type="checkbox" id="yellow" value="" />
            Green:
            <input name="green" type="checkbox" id="green" value="" />
            </span></td>
          </tr>
          <tr valign="baseline">
            <td align="left" nowrap="nowrap" bgcolor="#FFFFFF"><span 

class="style3">Countries:</span></td>
            <td colspan="2" bgcolor="#FFFFFF"><span class="style3">Algeria:
                <input name="algeria" type="checkbox" id="algeria" value="" />            
             Brazil:
             <input name="brazil" type="checkbox" id="brazil" value="" />              
             Chile:
             <input name="chile" type="checkbox" id="chile" value="" />              
             Denmark:
             <input name="denmark" type="checkbox" id="denmark" value="" />              
             Egypt:
             <input name="egypt" type="checkbox" id="egypt" value="" />              
             Finland:
             <input type="checkbox" name="finland" value="" />
            </span></td>
          </tr>
          
          
          <tr valign="baseline">
            <td align="left" valign="bottom" nowrap="nowrap" bgcolor="#FFFFFF">&nbsp;</td>
            <td width="621" valign="bottom" bgcolor="#FFFFFF"><span class="style8">Url:</span>
              <input name="url" type="text" value="" size="32" />
              <input name="submit" type="submit" value="Insert record" />
            <input name="Reset" type="reset" id="button" value="Reset" /></td>
            <td width="108" align="right" valign="bottom" bgcolor="#FFFFFF">&nbsp;</td>
          </tr>
        </table>
        <input type="hidden" name="MM_insert" value="form1" />
      </form>
    <p>&nbsp;</p></td>
  </tr>
</table>
</body>
</html>

============================

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- [url]http://www.phpmyadmin.net[/url]
--
-- Host: localhost
-- Generation Time: Nov 24, 2008 at 10:37 AM
-- Server version: 4.1.22
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `blah_blah`
--

-- --------------------------------------------------------

--
-- Table structure for table `timbo`
--

CREATE TABLE IF NOT EXISTS `timbo` (
  `timbo_id` int(11) NOT NULL auto_increment,
  `apple` varchar(10) NOT NULL default '',
  `strawberry` varchar(10) NOT NULL default '',
  `liverpool` varchar(10) NOT NULL default '',
  `manchesterunited` varchar(10) NOT NULL default '',
  `chelsea` varchar(10) NOT NULL default '',
  `arsenal` varchar(10) NOT NULL default '',
  `everton` varchar(10) NOT NULL default '',
  `square` varchar(10) NOT NULL default '',
  `circle` varchar(10) NOT NULL default '',
  `triangle` varchar(10) NOT NULL default '',
  `hexagon` varchar(10) NOT NULL default '',
  `red` varchar(10) NOT NULL default '',
  `orange` varchar(10) NOT NULL default '',
  `yellow` varchar(10) NOT NULL default '',
  `green` varchar(10) NOT NULL default '',
  `algeria` varchar(10) NOT NULL default '',
  `brazil` varchar(10) NOT NULL default '',
  `chile` varchar(10) NOT NULL default '',
  `denmark` varchar(10) NOT NULL default '',
  `egypt` varchar(10) NOT NULL default '',
  `finland` varchar(10) NOT NULL default '',
  `url` text NOT NULL,
  PRIMARY KEY  (`timbo_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

--
-- Dumping data for table `timbo`
--

INSERT INTO `timbo` (`timbo_id`, `apple`, `strawberry`, `liverpool`, `manchesterunited`, `chelsea`, `arsenal`, `everton`, `square`, `circle`, `triangle`, `hexagon`, `red`, `orange`, `yellow`, `green`, `algeria`, `brazil`, `chile`, `denmark`, `egypt`, `finland`, `url`) VALUES
(34, 'Y', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'www.test01.com');

php

2 Contributors
1 Reply
61 Views
2 Days Discussion Span
Latest Post 15 Years Ago Latest Post by digital-ether

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

digital-ether 399 Nearly a Posting Virtuoso Team Colleague · Answer 1 · 2008-11-29T07:00:23+00:00

PHP allows you to use Array notation in your form field names.

eg:

<input type="checkbox" name="checked[check1]" />
<input type="checkbox" name="checked[check2]" />

When received in PHP, you get an Array.

eg:

$checked = $_GET['checked'];

If both the fields were checked, then $checked would be:

$checked; // array('check1'=>1, 'check2'=>1);

The array should easier to use, since it is indexed. Being indexed allows you to iterate through the Array, and thus group your code into a loop instead of having to write out each variable name in a linear (hard-coded) fashion.

eg:

GetSQLValueString(isset($_POST['apple']) ? "true" : "", "defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['strawberry']) ? "true" : "", 

"defined","'Y'","'N'"),
                       GetSQLValueString(isset($_POST['liverpool']) ? "true" : "",

and the following 20 or so lines would be written as:

foreach($checked as $name=>$value) {
  // build the sql query here
}

Or even just a single implode() if possible.

This would allow you to create your search Query dynamically.

As for the search query itself:

it would look something like:

SELECT * FROM tablename where column1 = 'yes' AND column2 = 'no' ... etc.

If you break this down into pieces you can construct in a loop you get:

SELECT * FROM tablename // doesn't change
column1 = 'yes' // condition is dynamic
AND // connects each condition (boolean AND or OR)

so PHP would look like:

For the static part:

$sql = "SELECT * FROM tablename";

dynamic conditions

$conditions = array(); 
foreach($checked as $name=>$value) {
  $conditions[] = $name." = '".$value."'";
}

then you put the conditions together with AND or OR based on whether you want to match all or any.

$match_type = $_GET['match'] == 'all' ? ' AND ' : ' OR ';
$sql .= implode($match_type, $conditions);

This is just a basic template you could use.