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.

<?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!!!

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.

Thanks. I have pasted the SQL below. I generated the SQL from dreamweaver:

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 

sounds to me like you’ll want to use OR, then :slight_smile:

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.

You aren’t getting an error, so that’s a start.
After this line in your code, add an echo of $query_rsplan.


$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.

@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:


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