Solved

PHP MYSQL Advanced search code problem - dynamic form and checkboxes

Posted on 2004-09-25
7
322 Views
Last Modified: 2013-12-12
I have a php page which has a form on it, on the form there is checkboxes which are drawn dynamically from a database.

The Code for the check boxes is

<?php
  while (!$RsPropertyFeatures->EOF) { // horizontal looper
?>
                                        <td><table width="100" border="0" cellpadding="0" cellspacing="0">
                                            <tr>
                                              <td><table width="200"  border="0" cellspacing="2" cellpadding="2">
                                                  <tr>
                                                    <td width="36"><img src="PortfolioFiles/PropertyFeatures/<?php echo $RsPropertyFeatures->Fields('PicFile'); ?>"> </td>
                                                    <td width="124"><?php echo $RsPropertyFeatures->Fields('PropertyFeatureRef'); ?></td>
                                                    <td width="20"><input name="propertyfeature" type="checkbox" id="propertyfeature" value="checkbox"></td>
                                                  </tr>
                                              </table></td>
                                              <?php
   
?>
                                            </tr>
                                        </table></td>
                                        <?php
    $RsPropertyFeatures->MoveNext();
    if (!isset($nested_RsPropertyFeatures)) {
      $nested_RsPropertyFeatures= 1;
    }
    if (!$RsPropertyFeatures->EOF && $nested_RsPropertyFeatures++%2==0) {
      echo "</tr><tr>";
    }
    //end horizontal looper
  }
?>

Now I have a search function which constructs a mysql query for the options that are chosen on the html form.

This is the function so far

<?php
//Connection statement
require_once('../Connections/RondaConnections.php');

// begin Recordset
$query_RsProperties = "SELECT * FROM   rcproperty   LEFT OUTER JOIN rctowns ON (rcproperty.Location = rctowns.idLocation)   LEFT OUTER JOIN rclocale ON (rcproperty.Locale = rclocale.idLocale) WHERE rcproperty.idAccount = 6";
$RsProperties = $RondaConnections->SelectLimit($query_RsProperties) or die($RondaConnections->ErrorMsg());
$totalRows_RsProperties = $RsProperties->RecordCount();
// end Recordset
 //PHP ADODB document - made with PHAkt 2.7.3?>
<?php

//Declare Form Variables

$Ref=$_POST['Ref'];
$HouseType=$_POST['PropertyType'];
$Region=$_POST['Region'];
$Town=$_POST['Town'];
//$LPrice=$_POST['LPrice'];
//$HPrice=$_POST['HPrice'];
$Bedrooms=$_POST['Bedrooms'];
$Bathrooms=$_POST['Bathrooms'];
$Locale=$_POST['Locale'];



// SQL Query
$SQLQUERY = " ";
// Declare optional SQL Statement
// Ref No
if ($Ref != "") {
          $SQLQUERY .= "AND RefNo = '$Ref'";
          }
// Property Type
if ( !empty($HouseType) && $HouseType == 'all' ) {
;
}
else {
          $SQLQUERY .= "AND rcproperty.idPropertyType = '$HouseType'";
          }
// Region
if ( !empty($Region) && $Region == 'all' ) {
;
}
else {
       $SQLQUERY .= "AND Location = '$Region'";
          }
// Town
if ( !empty($Town) && $Town == 'all' ) {
;
}
else {
        $SQLQUERY .= "AND TownName = '$Town'";
          }
// Bedrooms
if ( !empty($Bedrooms) && $Bedrooms == 'all' ) {
;
}
else {
         $SQLQUERY .= "AND rcproperty.Bedrooms = '$Bedrooms'";
          }
           
// Bathrooms
if ( !empty($Bathrooms) && $Bathrooms == 'all' ) {
;
}
else {
          $SQLQUERY .= "AND Bathrooms = '$Bathrooms'";
         }
// Locale
if ( !empty($Locale) && $Locale == 'all' ) {
;
}
else {
          $SQLQUERY .= "AND Locale = '$Locale'";
         }

// Store Results in Variable
$result = mysql_query($query_RsProperties . $SQLQUERY) or die(mysql_error() . " - $query_RsProperties" . $SQLQUERY);
?>
<?php while($row = mysql_fetch_assoc($result)) {
echo "" .$row['PropertyName']. "" . "<BR>";
}
 ?>

Which works Fine as far as the drop down boxes and text fields are concerned.
Now the problem is the checkboxes.  the checkboxes are property features which are drawn from another table in the database.  The propertytofeatures table has this structure

CREATE TABLE `rcpropertytofeatures` (
  `idPropertyToFeature` tinyint(1) NOT NULL auto_increment,
  `IdProperty` tinyint(4) default NULL,
  `idPropertyFeature` tinyint(4) default NULL,
  PRIMARY KEY  (`idPropertyToFeature`),
  UNIQUE KEY `idPropertyToFeature` (`idPropertyToFeature`)
) TYPE=MyISAM

and related to that table is is the actual propertfeatures table which has this structure

CREATE TABLE `rcpropertyfeatures` (
  `idPropertyFeature` int(1) NOT NULL auto_increment,
  `PropertyFeatureRef` varchar(32) default NULL,
  `idAccount` int(11) default NULL,
  `Description` varchar(128) default NULL,
  `PicFile` varchar(100) default NULL,
  `idLanguage` tinyint(4) default NULL,
  PRIMARY KEY  (`idPropertyFeature`),
  UNIQUE KEY `idPropertyFeatures` (`idPropertyFeature`),
  UNIQUE KEY `idPropertyFeature` (`idPropertyFeature`),
  KEY `idProperty` (`idAccount`)
) TYPE=MyISAM

Now what i need to do (I think) Is query the $result again to find out which properties have which property features if they have selected any of them.

How do i do this????
ps this was the original question I asked before i realised it wouldnt work!!!
http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_21143846.html
0
Comment
Question by:awd2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
7 Comments
 
LVL 1

Accepted Solution

by:
x-tinct earned 40 total points
ID: 12151076
Again, can you write the SQL query like you want it? Then it will be easier to explain the php code.

Try to output the SQL at the end:
echo $query_RsProperties . $SQLQUERY;

Then try to connect to the database manually and see if it works. If there are any error, try to correct if you can. Then post the resulting query.

I see that you haven't changed the code for the checkbox in the above code. Have you tried it in your code?
You need to insert brackets [] in the name field and change the value to $RsPropertyFeatures->Fields('idPropertyFeature') or other unique ID. Like this:

<input name="propertyfeature[]" type="checkbox" id="propertyfeature" value="<?php echo $RsPropertyFeatures->Fields('idPropertyFeature'); ?>">

If I'm still wrong. Maybe you can explain a little more about you want to do and what these features are (just boolean values or not)? Is this form used to search for houses with spesific features? Etc...
0
 
LVL 10

Assisted Solution

by:frugle
frugle earned 40 total points
ID: 12152450
after changing:

<input name="propertyfeature" type="checkbox" id="propertyfeature" value="checkbox">

to:

<input name="propertyfeature[]" type="checkbox" id="propertyfeature" value="<?php echo $RsPropertyFeatures->Fields('idPropertyFeature'); ?>">

it should leave you with an array propertyfeature() containing the ID's - e.g. '4','8','9','15'

You can then implode the array with commas:

$featuredProps = implode(",", $propertyfeature);

and use in your SQL statement:

SELECT * FROM gubbins WHERE id IN($featuredProps);

Mike
0
 
LVL 10

Expert Comment

by:frugle
ID: 12565105
I'd think a split would be the best way forward, as we both solve bits of the question - x-tinct shows how to get the input into an array and I show how to use that information in the new page.

Mike
0
 
LVL 1

Expert Comment

by:x-tinct
ID: 12567179
I agree
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wordpress and Wufoo 1 39
MySqli Real Escape String and SQL Injection 1 47
xml with php question 5 34
Why is my $_POST not going to results page 10 37
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to implement server side field validation and display customized error messages to the client.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question