Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

PHP MYSQL Advanced search code problem - dynamic form and checkboxes

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

610 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