SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php search problem using "AND" in the query

    I need help here and I have been racking my brain over this.
    I am actually using Dreamweaver Cs3 to do the search over two tables in MYSQL . The search works if I am using "OR" in the query and it gives me the exact results but it does not show any result if I used "AND" in the SQL statement. I have tried everything to no avail. I will be very glad if anyone can help with this. I am not too good with PHP (The reason why I use dreamweaver recordset).
    I have pasted the whole page below for your perusal. I can also post the search for if it is needed.



    Code:
    <?php require_once('Connections/conCom.php'); ?>
    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
    {
      $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;
    }
    
    ?>
    <?php
    $currentPage = $_SERVER["PHP_SELF"];
    
    $maxRows_rsplan = 20;
    $pageNum_rsplan = 0;
    if (isset($_GET['pageNum_rsplan'])) {
      $pageNum_rsplan = $_GET['pageNum_rsplan'];
    }
    
    $KTColParam1_rsplan = "-1";
    if (isset($_GET["bedrooms"])) {
      $KTColParam1_rsplan = (get_magic_quotes_gpc()) ? $_GET["bedrooms"] : addslashes($_GET["bedrooms"]);
    }
    $KTColParam2_rsplan = "-1";
    if (isset($_GET["bathrooms"])) {
      $KTColParam2_rsplan = (get_magic_quotes_gpc()) ? $_GET["bathrooms"] : addslashes($_GET["bathrooms"]);
    }
    $KTColParam3_rsplan = "-1";
    if (isset($_GET["stories"])) {
      $KTColParam3_rsplan = (get_magic_quotes_gpc()) ? $_GET["stories"] : addslashes($_GET["stories"]);
    }
    $KTColParam4_rsplan = "-1";
    if (isset($_GET["sqft"])) {
      $KTColParam4_rsplan = (get_magic_quotes_gpc()) ? $_GET["sqft"] : addslashes($_GET["sqft"]);
    }
    $KTColParam5_rsplan = "-1";
    if (isset($_GET["floorplan"])) {
      $KTColParam5_rsplan = (get_magic_quotes_gpc()) ? $_GET["floorplan"] : addslashes($_GET["floorplan"]);
    }
    $KTColParam6_rsplan = "-1";
    if (isset($_GET["garage"])) {
      $KTColParam6_rsplan = (get_magic_quotes_gpc()) ? $_GET["garage"] : addslashes($_GET["garage"]);
    }
    $KTColParam7_rsplan = "-1";
    if (isset($_GET["community"])) {
      $KTColParam7_rsplan = (get_magic_quotes_gpc()) ? $_GET["community"] : addslashes($_GET["community"]);
    }
    $KTColParam8_rsplan = "-1";
    if (isset($_GET["price_range"])) {
      $KTColParam8_rsplan = (get_magic_quotes_gpc()) ? $_GET["price_range"] : addslashes($_GET["price_range"]);
    }
    mysql_select_db($database_conCom, $conCom);
    $query_rsplan = sprintf("SELECT communities_subdivisions.id AS id_1, communities_subdivisions.name AS name_2, plan.bedrooms, plan.baths, plan.stories, plan.base_sqft, plan.name, plan.garage, plan.id, plan.community_id, plan.base_price FROM (plan LEFT JOIN communities_subdivisions ON communities_subdivisions.id=plan.community_id) WHERE plan.bedrooms=%s  AND plan.baths=%s  AND plan.stories=%s  AND plan.base_sqft>=%s  AND plan.name=%s  AND plan.garage>=%s  AND plan.community_id=%s  AND plan.base_price=%s ", GetSQLValueString($KTColParam1_rsplan, "int"),GetSQLValueString($KTColParam2_rsplan, "int"),GetSQLValueString($KTColParam3_rsplan, "int"),GetSQLValueString($KTColParam4_rsplan, "int"),GetSQLValueString($KTColParam5_rsplan, "text"),GetSQLValueString($KTColParam6_rsplan, "int"),GetSQLValueString($KTColParam7_rsplan, "int"),GetSQLValueString($KTColParam8_rsplan, "int"));
    $rsplan = mysql_query($query_rsplan, $conCom) or die(mysql_error());
    $row_rsplan = mysql_fetch_assoc($rsplan);
    $totalRows_rsplan = mysql_num_rows($rsplan);
    
    $queryString_rsplan = "";
    if (!empty($_SERVER['QUERY_STRING'])) {
      $params = explode("&", $_SERVER['QUERY_STRING']);
      $newParams = array();
      foreach ($params as $param) {
        if (stristr($param, "pageNum_rsplan") == false && 
            stristr($param, "totalRows_rsplan") == false) {
          array_push($newParams, $param);
        }
      }
      if (count($newParams) != 0) {
        $queryString_rsplan = "&" . htmlentities(implode("&", $newParams));
      }
    }
    $queryString_rsplan = sprintf("&totalRows_rsplan=%d%s", $totalRows_rsplan, $queryString_rsplan);
    ?><!DOCTYPE html> 
    <html> 
    <head> 
    <title>Plans </title> 
    </head> 
    <body> 
     
    <div data-role="page" id="home">
      <div data-role="content">
        <h3>Search Now</h3>
     <?php 
    // Show IF Conditional region1 
    if (@$row_rsplan['archive'] == 0) {
    ?>
        <ul data-role="listview" data-inset="true">
              <?php do { ?>
                <li><a href="plandetail.php?id=<?php echo $row_rsplan['id']; ?>"><strong><?php echo $row_rsplan['name']; ?></strong> - <?php echo $row_rsplan['name_2']; ?><br />
                  <span style="color:#CC3300; font-size:12px; margin-top:5px; font-family:Arial, Helvetica, sans-serif;">$<?php echo number_format($row_rsplan['base_price'],0,'.',','); ?>, <?php echo $row_rsplan['base_sqft']; ?> SF, <?php echo $row_rsplan['stories']; ?> Story, <?php echo $row_rsplan['bedrooms']; ?>/<?php echo $row_rsplan['baths']; ?>/<?php echo $row_rsplan['garage']; ?></span></a></li>
                <?php } while ($row_rsplan = mysql_fetch_assoc($rsplan)); ?>
            </ul>
            <?php //$TFM_navLinks
    if ($pageNum_rsplan > 0) {
      printf('<a href="'."%s?pageNum_rsplan=%d%s", $currentPage, 0, $queryString_rsplan.'">First</a>');
    }else{
      echo "First";
    }
    echo " | ";
    if ($pageNum_rsplan > 0) {
      printf('<a href="'."%s?pageNum_rsplan=%d%s", $currentPage, max(0, $pageNum_rsplan - 1), $queryString_rsplan.'">Previous</a>');
    }else{
      echo "Previous";
    }
    echo " | ";
    if ($pageNum_rsplan < $totalPages_rsplan) { 
      printf('<a href="'."%s?pageNum_rsplan=%d%s", $currentPage, $pageNum_rsplan + 1, $queryString_rsplan.'">Next</a>');
    }else{
      echo "Next";
    }
    echo " | ";
    if ($pageNum_rsplan < $totalPages_rsplan) { 
      printf('<a href="'."%s?pageNum_rsplan=%d%s", $currentPage, $totalPages_rsplan, $queryString_rsplan.'">Last</a>');
    }else{
      echo "Last";
    }
    ?>
    <?php } 
    // endif Conditional region1
    ?>
      </div><!-- /content --><!-- /footer-->
    </div><!-- /page -->
    </body>
    </html>
    <?php
    mysql_free_result($rsplan);
    ?>
    Help!!!

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    please post the query alone, the rest of the code is irrelevant to the problem and makes finding the query awkward.

    Also you are most likely running into the order of prescedence problem.

    (4 + 5) * 2 = 40
    4 + 5 * 2 = 14

    the ANDs and ORs in your query would need similar treatment, AND takes prescedence over OR.

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I have pasted the SQL below. I generated the SQL from dreamweaver:

    Code:
    SELECT communities_subdivisions.id AS id_1, communities_subdivisions.name AS name_2, plan.bedrooms, plan.baths, plan.stories, plan.base_sqft, plan.name, plan.garage, plan.id, plan.community_id, plan.base_price FROM (plan LEFT JOIN communities_subdivisions ON communities_subdivisions.id=plan.community_id) WHERE plan.bedrooms=%s  AND plan.baths=%s  AND plan.stories=%s  AND plan.base_sqft>=%s  AND plan.name=%s  AND plan.garage>=%s  AND plan.community_id=%s  AND plan.base_price=%s

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eni360 View Post
    The search works if I am using "OR" in the query and it gives me the exact results but it does not show any result if I used "AND" in the SQL statement.
    sounds to me like you'll want to use OR, then

    note: if plan.community_id has to have a value (whether you're using OR or AND) then you'll want an inner join instead of a left join, but this isn't a critical issue and if you don't know how to change it, don't worry about it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sounds to me like you'll want to use OR, then

    note: if plan.community_id has to have a value (whether you're using OR or AND) then you'll want an inner join instead of a left join, but this isn't a critical issue and if you don't know how to change it, don't worry about it
    I have used inner join as well with no luck.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You aren't getting an error, so that's a start.
    After this line in your code, add an echo of $query_rsplan.
    PHP Code:
    $query_rsplan sprintf("SELECT communities_subdivisions.id AS id_1, communities_subdivisions.name AS name_2, plan.bedrooms, plan.baths, plan.stories, plan.base_sqft, plan.name, plan.garage, plan.id, plan.community_id, plan.base_price FROM (plan LEFT JOIN communities_subdivisions ON communities_subdivisions.id=plan.community_id) WHERE plan.bedrooms=%s  AND plan.baths=%s  AND plan.stories=%s  AND plan.base_sqft>=%s  AND plan.name=%s  AND plan.garage>=%s  AND plan.community_id=%s  AND plan.base_price=%s "GetSQLValueString($KTColParam1_rsplan"int"),GetSQLValueString($KTColParam2_rsplan"int"),GetSQLValueString($KTColParam3_rsplan"int"),GetSQLValueString($KTColParam4_rsplan"int"),GetSQLValueString($KTColParam5_rsplan"text"),GetSQLValueString($KTColParam6_rsplan"int"),GetSQLValueString($KTColParam7_rsplan"int"),GetSQLValueString($KTColParam8_rsplan"int"));

    echo 
    'query : ' $query_rsplan
    Then copy and paste the query that shows up in your browser, and test it in phpMyAdmin.
    Do remember (like has been said before) that all those AND's mean that each and every column in the plan table has to have the value as specified in the WHERE clause. One different value, and the row isn't selected. So if you want to get all rows that correspond to at least one of the criteria (even if not all), then use OR's instead of AND's.

  7. #7
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,882
    Mentioned
    74 Post(s)
    Tagged
    6 Thread(s)
    @eni360,

    Welcome to the forum.

    I have never used DreamWeaver but to test queries try copying and pasting your SQL into

    http://localhost/phpmyadmin/index.php

    Here is your expanded query:
    PHP Code:
    SELECT 
        communities_subdivisions
    .id   AS id_1
        
    communities_subdivisions.name AS name_2
        
    plan.bedrooms
        
    plan.baths
        
    plan.stories
        
    plan.base_sqft
        
    plan.name
        
    plan.garage
        
    plan.id
        
    plan.community_id
        
    plan.base_price 
    FROM 
        
    (
          
    plan 
          LEFT JOIN   communities_subdivisions 
          ON          communities_subdivisions
    .id=plan.community_id
        

        
    WHERE         plan.bedrooms=%s  
        
    AND           plan.baths=%s
        
    AND           plan.stories=%s
        
    AND           plan.base_sqft>=%s
        
    AND           plan.name=%s
        
    AND           plan.garage>=%s
        
    AND           plan.community_id=%s
        
    AND           plan.base_price=%
    Learn how to be ready for The New Move to Discourse

    How to make Make Money Now with a *NEW* look

    Be sure to congratulate Wolfshade on earning Member of the Month for August 2014


Bookmarks

Posting Permissions

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