?
Solved

PHP MYSQL Advanced search code problem - dynamic form and checkboxes

Posted on 2004-09-25
7
Medium Priority
?
334 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 160 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 160 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

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

771 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