Solved

PHP MYSQL Advanced search code problem - dynamic form and checkboxes

Posted on 2004-09-25
7
310 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
  • 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now