Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jun 2006
    Posts
    46
    Thanks
    3
    Thanked 0 Times in 0 Posts

    PHP show select price and everything below that price

    Hi,

    I have a search form with 4 search fields and one of them is a price field. What I want to do is when say $200 is selected from the Select List that it shows the results for $200 and everything below $200.

    I have attached the code below and wondering if anyone can help as I'm relatively new to PHP and MySQL and I'm pulling my hair out abit here lol.

    Any help would be much appreciated

    The field thats the price field is: myfield_06

    Code:
    <?php require_once('Connections/Fmtomysql.php'); ?>
    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
    {
      if (PHP_VERSION < 6) {
        $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
      }
    
      $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
    
      switch ($theType) {
        case "text":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;    
        case "long":
        case "int":
          $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case "double":
          $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
          break;
        case "date":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;
        case "defined":
          $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
          break;
      }
      return $theValue;
    }
    }
    
    mysql_select_db($database_Fmtomysql, $Fmtomysql);
    $query_Recordset1 = "SELECT * FROM `search`";
    $Recordset1 = mysql_query($query_Recordset1, $Fmtomysql) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 = mysql_num_rows($Recordset1);
    
    mysql_select_db($database_Fmtomysql, $Fmtomysql);
    $query_FmtoMysql = "SELECT `online`.id, `online`.name, `online`.details, `online`.hide, info_1.r01_1, info_1.r01_2, info_1.r02_1, info_1.r02_2, info_2.r03_1, info_2.r03_2, info_2.r04_1, info_2.r04_2, info_3.r05_1, info_3.r05_2, info_3.r06_1, info_3.r06_2, info_4.r01_header, info_4.r01_footer, images.image1, images.image2, images.image3, images.image4, images.image5, images.image6, images.image7, images.image8, images.image9, images.image10, images.image11, images.image12, banner.banner_text, banner.banner_hide, bannersmall.bannersmall_hide, bannersmall.bannersmall_text, misc.status, website.background_color, website.font_color, website.border_color, website.ref_color
    FROM banner, website, online JOIN info_1 ON info_1.id_info_1 = online.id JOIN info_2 ON info_2.id_info_2 = online.id JOIN info_3 ON info_3.id_info_3 = online.id JOIN misc ON misc.id_misc = online.id JOIN images ON images.id_images = online.id JOIN info_4 ON info_4.id_info_4 = online.id JOIN search ON search.id_search = online.id , bannersmall
    WHERE `online`.id=id_info_1 AND id_info_2 AND id_info_3 AND id_images AND id_misc ORDER BY `online`.id ";
    $FmtoMysql = mysql_query($query_FmtoMysql, $Fmtomysql) or die(mysql_error());
    $row_FmtoMysql = mysql_fetch_assoc($FmtoMysql);
    $totalRows_FmtoMysql = mysql_num_rows($FmtoMysql);
    
    ?>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Search</title>
    <link href="css/search.css" rel="stylesheet" type="text/css" />
    <script type='text/javascript' src='http://code.jquery.com/jquery-1.6.2.js'></script>
    <script src="js/search_residential_forsale.js" type="text/javascript"></script>
    
    </head>
    <body onload="DropDups1(); DropDups2(); DropDups3(); DropDups4();">
    <div id="content">
      <div style="background:#666; padding:5px; border-radius:5px">
        <form action="<?=$PHP_SELF?>" method="post" name="search" id="search">
          <input name="myfield_01" value="Residential" hidden="yes" />
          <input name="myfield_02" value="For Sale" hidden="yes" />
          <input name="Search" type="submit" class="showallbutton" id="Search" value=" " style="margin-right:4px" />
          <select name="myfield_03">
            <option value="~">County - Any</option>
            <?php
    do {  
    ?>
            <option value="<?php echo $row_Recordset1['myfield_03']?>"><?php echo $row_Recordset1['myfield_03']?></option>
            <?php
    } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
      $rows = mysql_num_rows($Recordset1);
      if($rows > 0) {
          mysql_data_seek($Recordset1, 0);
    
    	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
      }
    ?>
          </select>
          <select name="myfield_04">
            <option value="~">Area - Any</option>
            <?php
    do {  
    ?>
            <option value="<?php echo $row_Recordset1['myfield_04']?>"><?php echo $row_Recordset1['myfield_04']?></option>
            <?php
    } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
      $rows = mysql_num_rows($Recordset1);
      if($rows > 0) {
          mysql_data_seek($Recordset1, 0);
    	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
      }
    ?>
          </select>
          <select name="myfield_05">
            <option value="~">Bedrooms - Any</option>
            <?php
    do {  
    ?>
            <option value="<?php echo $row_Recordset1['myfield_05']?>"><?php echo $row_Recordset1['myfield_05']?></option>
            <?php
    } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
      $rows = mysql_num_rows($Recordset1);
      if($rows > 0) {
          mysql_data_seek($Recordset1, 0);
    	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
      }
    ?>
          </select>
          <select name="myfield_06" style="margin-right:0px">
            <option value="~">Price Limit - Any</option>
            <?php
    do {  
    ?>
            <option value="<?php echo $row_Recordset1['myfield_06']?>"><?php echo $row_Recordset1['myfield_06']?></option>
            <?php
    } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
      $rows = mysql_num_rows($Recordset1);
      if($rows > 0) {
          mysql_data_seek($Recordset1, 0);
    	  $row_Recordset1 = mysql_fetch_assoc($Recordset1);
      }
    ?>
          </select>
          <input type="hidden" name="searching" value="yes" />
          <input name="Search" type="submit" class="submitbutton" id="Search" value=" " style="margin-left:4px"/>
        </form>
    
      </div>
      <? 
     //This is only displayed if they have submitted the form 
     if ($searching =="yes") 
     { 
     echo ""; 
     
     //If they did not enter a search term we give them an error 
     if ($myfield_01 == "") 
     { 
     echo ""; 
     exit; 
     } 
     
     if ($myfield_02 == "") 
     { 
     echo ""; 
     exit; 
     } 
    
     if ($myfield_03 == "") 
     { 
     echo "<p>You need to enter a County"; 
     exit; 
     } 
      
     if ($myfield_04 == "") 
     { 
     echo "<p>You need to enter Area"; 
     exit; 
     } 
       
     if ($myfield_05 == "") 
     { 
     echo "<p>You need to enter a Bedrooms"; 
     exit; 
     } 
        
     if ($myfield_06 == "") 
     { 
     echo "<p>You need to enter a Price Limit"; 
     exit; 
     } 
    
     
     // We preform a bit of filtering 
     $find = strtoupper($find); 
     $find = strip_tags($find); 
     $find = trim ($find); 
      
     
     //Now we search for our search term, in the field the user specified 
     $data = mysql_query("SELECT * FROM website, online JOIN info_1 ON info_1.id_info_1 = online.id JOIN info_2 ON info_2.id_info_2 = online.id JOIN info_3 ON info_3.id_info_3 = online.id JOIN misc ON misc.id_misc = online.id JOIN images ON images.id_images = online.id JOIN info_4 ON info_4.id_info_4 = online.id JOIN search ON search.id_search = online.id WHERE myfield_01 LIKE'%$myfield_01%' AND myfield_02 LIKE'%$myfield_02%' AND myfield_03 LIKE'%$myfield_03%' AND myfield_04 LIKE'%$myfield_04%' AND myfield_05 LIKE'%$myfield_05%' AND  myfield_06 LIKE'%$myfield_06%'"); 
     
      //And we remind them what they searched for 
     echo "<br>"; 
     } 
     
     //And we display the results 
     while($result = mysql_fetch_array( $data )) 
     { 
     echo "<div class='tablespacer' style='background:";
     echo $result['border_color'];
     echo "; display:";
     echo $result['hide'];
     echo "'><div class='contentbackground' style='background:";
     echo $result['background_color'];
     echo "'><div class='image' style='color:";
     echo $result['ref_color'];
     echo "'> <span> Reference ";
     echo $result['id']; 
     echo "</span> <br />";
     echo "<a href='page.php?id=";
     echo $result['id'];
     echo "'><img class='imagesize' src='"; 
     echo $result['image1'];
     echo "' /></a><div class='iconoverlay'><div class='iconoverlayinner'>";
     echo "<a href='page.php?id=";
     echo $result['id'];
     echo "'> <img src='images/Magnifying.png' alt='Click Image For More Info' title='Click Image For More Info' border='0' /> </a> </div></div><div class='imageoverlay'>";
     echo "<div class='imageoverlayinner' style='width:300px'>";   
     echo $result['status']; 
     echo "</div></div></div><div class='title' style='color:";
     echo $result['font_color']; 
     echo "'><a href='page.php?id=";
     echo $result['id']; 
     echo "' style='text-decoration:none'>";
     echo $result['name']; 
     echo "</a></div><br /><div class='content' style='color:";
     echo $result['font_color']; 
     echo "'>";       
     echo $result['details'];
     echo "</div></div></div>";       
     } 
     
     
     //This counts the number or results - and if there wasn't any it gives them a little message explaining that 
     $anymatches=mysql_num_rows($data); 
     if ($anymatches == 0) 
     { 
     echo ""; 
     } 
     
    
     ?>
    </div>
    </body>
    </html>
    <?php
    mysql_free_result($Recordset1);
    ?>
    Many Thanks
    Joe

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,122
    Thanks
    2
    Thanked 327 Times in 319 Posts
    instead of using -
    Code:
    myfield_06 LIKE'%$myfield_06%'
    you would use -
    Code:
    myfield_06 <= $myfield_06
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    Gibson (10-31-2013)

  • #3
    New Coder
    Join Date
    Jun 2006
    Posts
    46
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi!

    Thanks alot for the reply. I have tried that before and it doesn't work. It displays no results and stops all the drop down lists from displaying results.

    I am replacing:

    myfield_06 LIKE'%$myfield_06%'

    with

    myfield_06 <= $myfield_06

    just to make sure I'm replacing the right part.

    Many thanks
    Joe

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,122
    Thanks
    2
    Thanked 327 Times in 319 Posts
    I'm going to guess that your database column is a character type and it has things like the leading $ in it?

    What sort of things are actually stored in that column? An example of a value you are picking in the drop-down and an example of a value in the table you expect the <= comparison to match would be helpful.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    Gibson (10-31-2013)

  • #5
    New Coder
    Join Date
    Jun 2006
    Posts
    46
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Sorry for the slow reply my account got disabled for some reason lol!

    I'm going to guess that your database column is a character type and it has things like the leading $ in it?
    Yes that is correct

    What sort of things are actually stored in that column? An example of a value you are picking in the drop-down and an example of a value in the table you expect the <= comparison to match would be helpful.
    The values are grabbed from the myfield_06 column which is in the same database as the search is being done on. And example of the value is:

    Code:
    ~ 100,000
    Is that what you required?

    Many thanks
    Joe

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,122
    Thanks
    2
    Thanked 327 Times in 319 Posts
    The thousands comma separator is a human convention to make numbers easier for humans to read.

    For a greater-than/less-than comparison to work in php/mysql, you must store the number with only the numerical digits and you must store it in a column with a numerical data type (strings are compared left to right and the string 1000 is less than the string 2.)

    In programming, you would only add things like a $ or the thousands comma separate when displaying the number.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    Gibson (10-31-2013)

  • #7
    New Coder
    Join Date
    Jun 2006
    Posts
    46
    Thanks
    3
    Thanked 0 Times in 0 Posts
    You are fantastic! Thanks so much

    Now is there away to set the <select> options to automatically add the comma's in the right place?

    Or is that a javascript section question?

    Thanks so much again

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,122
    Thanks
    2
    Thanked 327 Times in 319 Posts
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #9
    New Coder
    Join Date
    Jun 2006
    Posts
    46
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Excellent ill check that out

    I have problem though. That script you gave me for some reason prevents the other search select lists from working

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,122
    Thanks
    2
    Thanked 327 Times in 319 Posts
    Your conditions using LIKE in the query will match everything when the search value is empty due to the wild-card characters around the search value. For the price comparison, this is no longer true.

    Normally, for what you are doing, you would dynamically build the WHERE clause in the query using only the terms that have values, so that your query executes as efficiently as possible. If a price hasn't been selected, the AND myfield_06 <= $myfield_06 term should be left out of the query.

    Another reason to be dynamically building the WHERE clause is so you can have more complicated search terms. How about a minimum/maximum price range or a maximum/minimum range or just a maximum or a minimum for any of the fields?
    Last edited by CFMaBiSmAd; 11-01-2013 at 04:32 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •