Link to home
Start Free TrialLog in
Avatar of arigelap
arigelapFlag for India

asked on

Another checkbox search with php and MySQL problem

Hello Experts,

This problem has been posted quite a few times but since my knowledge of php and MySQL is pretty bad, I couldn't get a solution.

So for my problem: We have a simple html form with 9 checkboxes. The form needs to communicate with a MySQL database and return a post depending on the search done.

The MySQL database is a simple one with a single table.Each entry has the name and description as well as the 9 charasteristics from the form. These are set to 'enum' with an option of yes or no. If the client selects 4 characteristics, the form needs to get all the entries that have these 4 characteristics.

You can see also see an example of the form at the following link:

http://locationlasterrenas.com/Age_Films/film_picker.html

The php script that is there right now is a simple one that I used to show how I'd like the results displayed (on the right side).

I need to know if the structure of my database is correct and help for the php.

Thanks.

<form action="filmpicker.php" method="post"> 
            <table width="300"  border="0" align="left" cellpadding="3" cellspacing="1"> 
              <tr align="center"> 
                <td width="200"><strong>Features</strong></td> 
                <td><strong>Importance</strong></td> 
              </tr> 
              <tr> 
                <td width="200">1. Heat Protection</td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="HP" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">2. Fade Reduction </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="FR" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">3. Energy / Utility Savings </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="ES" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">4. Safety &amp; Security</td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="SS" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">5. Glare Reduction </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="GR" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">6. Privacy </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="PR" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">7. Clear View </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="CV" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">8. Excellent  View at Night</td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="NV" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">9. One-Way Mirror </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="OW" >Yes</td> 
              </tr> 
              <tr align="center"> 
                <td colspan="2"><input type="submit" name="Submit" value="Find Film" class="Button"> 
                  <input type="reset" name="Reset" value="Reset" class="Button" onClick="clearSelection();"></td> 
                </tr> 
            </table> 
		    </form>

Open in new window

Avatar of Lukasz Chmielewski
Lukasz Chmielewski
Flag of Poland image

right, and what is the problem ? the form works, to see the structure of a db you need to post it
Avatar of arigelap

ASKER

Hi,

Thanks for your quick reply. Sorry for not posting it earlier. I hope this is what you need:

Table structure for table window_films
Field      Type      Null      Default      Comments
Name      varchar(255)      No            
Description      text      No            
HP      enum('y', 'n')      No      y      
FR      enum('y', 'n')      No      y      
ES      enum('y', 'n')      No      y      
SS      enum('y', 'n')      No      y      
GR      enum('y', 'n')      No      y      
PR      enum('y', 'n')      No      y      
CV      enum('y', 'n')      No      y      
NV      enum('y', 'n')      No      y      
OW      enum('y', 'n')      No      y      

Yes, the form does work right now but I need one that connects with the database and posts the reply on the right side.

Thanks again.
could you post the form and the "right side" code ?
you can do this to receive the submitted data ... and replace echo line with whatever you like


if you need more just tell me

<?php
//reciving the post and put it in easy named variable
$res = $_POST['formfilm'];
 
//do loop
for ($i=0;$i<count($res);$i++) {
 
//echo results
echo $i." = ".$res[$i]."<br>";
}
?>

Open in new window

OK, here you go. It's part of the filmpicker.php file. I put the same html page with the form with the small php code in the 'right side'

But, as I had mentioned earlier this code is not what I need. It was just a simple trial with php.

Thanks.
<DIV id="columna_interna_1b">
	
 
<P>Every film is designed to have different characteristics and performance standards in order to meet different requirements. If you are not sure on what to buy, use the following form to choose what you are looking for and we'll recommend what is best for you. </P>
<p><form action="filmpicker.php" method="post"> 
            <table width="300"  border="0" align="left" cellpadding="3" cellspacing="1"> 
              <tr align="center"> 
                <td width="200"><strong>Features</strong></td> 
                <td><strong>Importance</strong></td> 
              </tr> 
              <tr> 
                <td width="200">1. Heat Protection</td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="HP" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">2. Fade Reduction </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="FR" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">3. Energy / Utility Savings </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="ES" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">4. Safety &amp; Security</td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="SS" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">5. Glare Reduction </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="GR" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">6. Privacy </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="PR" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">7. Clear View </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="CV" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">8. Excellent  View at Night</td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="NV" >Yes</td> 
              </tr> 
              <tr> 
                <td width="200">9. One-Way Mirror </td> 
                <td align="center"><input name="formfilm[]" type="checkbox" value="OW" >Yes</td> 
              </tr> 
              <tr align="center"> 
                <td colspan="2"><input type="submit" name="Submit" value="Find Film" class="Button"> 
                  <input type="reset" name="Reset" value="Reset" class="Button" onClick="clearSelection();"></td> 
                </tr> 
            </table> 
		    </form>&nbsp;</p>
<P>&nbsp;</P>
</DIV>
<DIV id="columna_interna_2b">
  <?php
# 
$checkboxes = $_POST['formfilm']; #this is the array from the form (only selected values) 
echo '<pre>'; 
print_r($checkboxes); 
echo '</pre>'; 
# 
?>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
</DIV>

Open in new window

is that means you solve it or what ??

you can receive the fields ... what you need to do next :D
Hi agamal,

Not solved yet but am trying your solution. Will post a reply as soon as I try it.

Thanks.
Hi agamal,

I tried your script. I put in the db connection before the form and put your code on the right side where I wanted he answer . Then I checked 'heat protection' and sent the form.

The reply I got was HP=0 even though I have 2 entries which have heat protection as 'yes'.

What I need is the form to check which of the database entries have the characteristics that have been checked and then echo the name and description on the right side.

Thanks.
do you have any update on the script .. if yes post it complete .. if no just till and i will work on the last one
Agamal,

I had not seen your post. No, no other updates. Your script lists the boxes checked but thats not what I need.

What I need is the form to check which of the database entries have the characteristics that have been checked and then echo the name and description on the right side.

Thanks.
working on it .....
i need a dump of window_films to test the code before posting it
Hey agamal,

Here's the dump. I hope it works. Thanks for all your efforts

I'm not being able to upload the zip file so I dumped it as normal SQL, then copied and pasted in the code area.
-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: 10.6.171.222
-- Generation Time: Feb 18, 2009 at 01:36 PM
-- Server version: 4.1.22
-- PHP Version: 5.2.8
 
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: `agefilm`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `window_films`
--
 
DROP TABLE IF EXISTS `window_films`;
CREATE TABLE IF NOT EXISTS `window_films` (
  `Name` varchar(255) NOT NULL default '',
  `Description` text NOT NULL,
  `HP` enum('y','n') NOT NULL default 'y',
  `FR` enum('y','n') NOT NULL default 'y',
  `ES` enum('y','n') NOT NULL default 'y',
  `SS` enum('y','n') NOT NULL default 'y',
  `GR` enum('y','n') NOT NULL default 'y',
  `PR` enum('y','n') NOT NULL default 'y',
  `CV` enum('y','n') NOT NULL default 'y',
  `NV` enum('y','n') NOT NULL default 'y',
  `OW` enum('y','n') NOT NULL default 'y',
  UNIQUE KEY `Main` (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
--
-- Dumping data for table `window_films`
--
 
INSERT INTO `window_films` VALUES('83', '84', 'y', 'y', 'y', 'n', 'y', 'y', 'n', 'n', 'y');
INSERT INTO `window_films` VALUES('78', '84', 'y', 'y', 'y', 'n', 'y', 'y', 'n', 'n', 'n');
INSERT INTO `window_films` VALUES('Neutral 35 (RE35NEARL)', 'The most popular of the neutral series due to the increased amount of light transmitted and overall film performance. This sun control film offers a unique combination of low reflectivity and high performance. It has a neutral/gray color is due to the metal coating and will not fade or change color. This film offers 3M''s unique abrasion resistant coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'n', 'n', 'n');
INSERT INTO `window_films` VALUES('Neutral 50 (RE50NEARL)', 'This lighter neutral film provides very high light transmission while providing good performance. It has a neutral/gray color is due to the metal coating and will not fade or change color. This film offers 3M''s unique abrasion resistant coating for added durability.', 'y', 'y', 'n', 'n', 'n', 'n', 'y', 'n', 'n');
INSERT INTO `window_films` VALUES('Neutral 70 (RE70NEARL)', 'This neutral film provides the highest light transmission while providing good performance. It has a neutral/gray color is due to the metal coating and will not fade or change color. This film offers 3M''s unique abrasion resistant coating for added durability.', 'y', 'y', 'n', 'n', 'n', 'n', 'y', 'n', 'n');
INSERT INTO `window_films` VALUES('Prestige 70', 'This film provides the highest light transmission while providing excellent performance. As 3Ms top-of-the-line residential/ commercial film, Prestige rejects 97% of the suns infrared radiation and 99.9% of the suns UV rays. In addition, the films provide lower reflectivity than glass, as well as being a good method of reducing glare and eye discomfort. The film offers 3Ms unique abrasion coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'y', 'n', 'n');
INSERT INTO `window_films` VALUES('Prestige 60', 'This film provides the high light transmission while providing excellent performance. As 3Ms top-of-the-line residential/ commercial film, Prestige rejects 97% of the suns infrared radiation and 99.9% of the suns UV rays. In addition, the films provide lower reflectivity than glass, as well as being a good method of reducing glare and eye discomfort. The film offers 3Ms unique abrasion coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'y', 'n', 'n');
INSERT INTO `window_films` VALUES('Prestige 50', 'The most popular of the Prestige line of films. As 3Ms top-of-the-line residential/ commercial film, Prestige rejects 97% of the suns infrared radiation and 99.9% of the suns UV rays. In addition, the films provide lower reflectivity than glass, as well as being a good method of reducing glare and eye discomfort. The film offers 3Ms unique abrasion coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'y', 'n', 'n');
INSERT INTO `window_films` VALUES('Prestige 40', 'The darkest of the Prestige line of films. As 3Ms top-of-the-line residential/ commercial film, Prestige rejects 97% of the suns infrared radiation and 99.9% of the suns UV rays. In addition, the films provide lower reflectivity than glass, as well as being a good method of reducing glare and eye discomfort. The film offers 3Ms unique abrasion coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'n', 'n', 'n');
INSERT INTO `window_films` VALUES('Night Vision 15', 'The darkest of our Night Vision series, this sun control film offers a unique combination of low reflectivity, warm beauty and high performance. Night Vision films have a low interior reflectivity that leaves your view clearer than ever, especially at night. This film offers 3M''s unique abrasion resistant coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'y', 'n', 'y', 'n');
INSERT INTO `window_films` VALUES('Night Vision 25', 'The most popular of the Night Vision series due to the increased amount of light transmitted and film performance. This sun control film offers a combination of low reflectivity, warm beauty, and high performance. Night Vision films have a low interior reflectivity that leaves your view clear, especially at night. This film offers 3M''s unique abrasion resistant coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'y', 'n', 'y', 'n');
INSERT INTO `window_films` VALUES('Night Vision 35', 'The most popular of the Night Vision series due to the increased amount of light transmitted and film performance. This sun control film offers a combination of low reflectivity, warm beauty, and high performance. Night Vision films have a low interior reflectivity that leaves your view clear, especially at night. This film offers 3M''s unique abrasion resistant coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'n', 'y', 'n');
INSERT INTO `window_films` VALUES('Night Vision 45', 'This light Night Vision film provides higher light transmission while providing strong performance. This film is commonly used in residential applications where both visible light and control of heat are equally important. Night Vision films have a low interior reflectivity that leaves your view clear, especially at night. This film offers 3M''s abrasion resistant coating for added durability.', 'y', 'y', 'y', 'n', 'y', 'n', 'n', 'y', 'n');

Open in new window

test it now .... and tell me if you need anything else




<DIV id="columna_interna_1b">
 
 
<P>Every film is designed to have different characteristics and performance standards in order to meet different requirements. If you are not sure on what to buy, use the following form to choose what you are looking for and we'll recommend what is best for you. </P>
<p><form action="checkbox_search.php" method="post">
            <table width="300"  border="0" align="left" cellpadding="3" cellspacing="1">
              <tr align="center">
                <td width="200"><strong>Features</strong></td>
                <td><strong>Importance</strong></td>
              </tr>
              <tr>
                <td width="200">1. Heat Protection</td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="HP" >Yes</td>
              </tr>
              <tr>
                <td width="200">2. Fade Reduction </td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="FR" >Yes</td>
              </tr>
              <tr>
                <td width="200">3. Energy / Utility Savings </td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="ES" >Yes</td>
              </tr>
              <tr>
                <td width="200">4. Safety &amp; Security</td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="SS" >Yes</td>
              </tr>
              <tr>
                <td width="200">5. Glare Reduction </td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="GR" >Yes</td>
              </tr>
              <tr>
                <td width="200">6. Privacy </td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="PR" >Yes</td>
              </tr>
              <tr>
                <td width="200">7. Clear View </td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="CV" >Yes</td>
              </tr>
              <tr>
                <td width="200">8. Excellent  View at Night</td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="NV" >Yes</td>
              </tr>
              <tr>
                <td width="200">9. One-Way Mirror </td>
                <td align="center"><input name="formfilm[]" type="checkbox" value="OW" >Yes</td>
              </tr>
              <tr align="center">
                <td colspan="2"><input type="submit" name="Submit" value="Find Film" class="Button">
                  <input type="reset" name="Reset" value="Reset" class="Button" onClick="clearSelection();"></td>
                </tr>
            </table>
                    </form> </p>
<P> </P>
</DIV>
<DIV id="columna_interna_2b">
  <?php
#
$checkboxes = $_POST['formfilm']; #this is the array from the form (only selected values)
 
echo '<pre>';
//print_r($checkboxes);
echo '</pre>';
 
//connect to DB
        $connection = mysql_connect('localhost', 'root', 'root') or die (mysql_error());
        $db = mysql_select_db('test', $connection) or die (mysql_error());
 
//query =
$query  = "SELECT `Name` , `Description` FROM `window_films` WHERE ";
$and = "AND ";
        for ($i=0;$i < count($checkboxes);$i++) {
            if (($i+1) == count($checkboxes) ) { $and = ""; }
        $query = $query."`".$checkboxes[$i]."`= 'y' ".$and;
                                          }
$result = mysql_query($query);
while($rs = mysql_fetch_array($result)) {
echo $rs[0]."<br>";
}
?>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
  <p> </p>
</DIV>

Open in new window

Hi agamal,

Thanks man, I hope you got some sleep. ;)

Almost there, I think.

After doing a search, in the results, only the names are echoed and not the description.

Is it possible to add a line which says 'no results match your search when a person does a search for something that does not exist.

Also, do you think it's possible to put the name in bold and put a space after each result (if its not too much to ask).

Thanks a lot
do you mean by "something that does not exist" no results found
and i will put name in bold and description in normal
ASKER CERTIFIED SOLUTION
Avatar of agamal
agamal
Flag of United Arab Emirates image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey,

Yes, I meant no results found.

It'll be cool if you can put name in bold and description normally.

Also, like I had mentioned before, the description does not show at present.

Thanks.
did you tested the code above ...... the lat one

ID:23681047
Hi agamal,

It works! I had posted the above comment just as you were posting the code.

Thanks a lot. I'll accept the code as a solution.

Hey, by the way I have another problem and maybe you can take a look (it's a problem with CMSMS - an opensource php based CMS):

https://www.experts-exchange.com/questions/24152946/How-can-I-generate-perfect-PDF-documents-from-HTML-in-CMS-made-simple.html?anchorAnswerId=23668428#a23668428

Again, thanks a ton.