Jump to content

Search with multiple criterion in MySQL database

hybmg57

By hybmg57
in MySQL Help

 Share

Recommended Posts

hybmg57 Member

hybmg57

Posted
Posted

Hi,

 

I'm getting this error,

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\MLS Search Form\Search Results.php  on line 116

 

I have done search under Search form which has correct values but it seems like when I enter WHEN and AND functions in mysql query I get this error.

 

Please help me!!!

 

Below is my code...

 

Thank you.

 

Jae

 

 

<?php

if (is_numeric($_GET['pg'])) {

        $pgnum = ((int) $_GET['pg']);

}

 

else {

        $pgnum = 0;

        $errorMessage = "Invalid value for page number";

 

}

 

$startnum = 0;

$nextpg = ($pgnum + 1);

$prevpg = ($pgnum-1);

 

if ($pgnum > 0) {

        $offset = ($pgnum * 19);

}

 

else {

        $offset = 0;

}

 

$limit = 20;

?>

 

<?php

 

$Property_Type=$_GET['property_type'];

$City=$_GET['city'];

$Beds=$_GET['beds'];

$Baths=$_GET['baths'];

$Lowest_Price=$_GET['lowest_price'];

$Highest_Price=$_GET['highest_price'];

 

echo $Property_Type.", ";

echo $City.", ";

echo $Beds.", ";

echo $Baths.", ";

echo $Lowest_Price.", ";

echo $Highest_Price.", ";

 

include("db.php");

 

 

?>

<body id="www-rogers-healy-com" class="our-listings our-listings-landing" text>

  <div id="page">

      <div id="content-container">

        <div id="content" class="clearfix">

            <div id="container">

              <div id="listings-container"> <div id="listings">

 

<?php

$rets_login_url = "http://ntreisrets.mls.ntreis.net:80/rets/login";

$rets_username = "(Username entered)";

$rets_password = "(Password entered)";

$rets_agent = "(Agent)";

 

require_once('phRets.php');

 

$rets = new phRets;

 

$connect = $rets->Connect($rets_login_url, $rets_username, $rets_password);

if (!$connect) {

        print_r($rets->Error());

}

 

 

 

$result = mysql_query("SELECT *

                FROM residential

                WHERE  'PROPSUBTYPE'

                AND  'SUBDIVISION' LIKE '{$City}'

                AND  'BEDS' >={$Beds}

                AND  'BATHSTOTAL' >={$Baths}

                AND  'LISTPRICE' >={$Lowest_Price}

                AND  'LiSTPRICE' <={$Highest_Price}

                LIMIT {$offset}, {$limit}");

?>

<div id="rha-mls-pager-container">

 

<?php

if ($pgnum > 0) {

    echo("<a href='Search Results.php?pg=" . $prevpg . "'><< Previous

page</a>     ");

}

 

if ($pgnum > 0) {

    echo("<a href='Search Results.php?pg=" . $nextpg . "'>Next

page >></a>     ");

}

 

mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM residential LIMIT {$limit}");

$total_rows      =  mysql_query("SELECT FOUND_ROWS()");

$total_found  =    mysql_fetch_array($total_rows);

echo $total_found[0]." results found";

?>

</div><!--/#rha-mls-pager-container-->

<br/>

 

<?php

 

 

echo $found_results;

while ($row = mysql_fetch_array($result)){

      echo  "<div class=\"vcard listing\">";

      echo  "<div class=\"information\">";

      echo "<span style=\"size:20px\">\$".number_format($row['LISTPRICE'])."\n </span><br/>";

        echo "<p><span>{$row['STREETNUM']},{$row['STREETNAME']} ";

        echo "<BR/>{$row['CITY']}</span>";

        echo "{$row['STATE']}, {$row['ZIPCODE']}</p>";

      echo "<p><span>Beds: {$row['BEDS']}, Baths:{$row['BATHSTOTAL']}<br/>Square Feet: ".number_format($row['SQFTTOTAL'])." </span></p>"; 

      echo  "</div>";

 

        $photos = $rets->GetObject("Property", "Thumbnail", "{$row['MLSNUM']}", "0", 1);

            foreach ($photos as $photo) {

                  $listing = $photo['Content-ID'];

                  $number = $photo['Object-ID'];

           

                  if ($photo['Success'] == true) {

                        echo "<img src='{$photo['Location']}\n' alt=\"\" title=\"\"  class=\"listing-photo photo\" width=\"146\" height=\"107\" />";

                  }

                  else {

                        echo "<img src='images/no-image.png' alt=\"\" title=\"\"  class=\"listing-photo photo\" width=\"146\" height=\"107\" />";

                  }

            }

           

      echo "<a href=\"#\" class=\"button url\">See Property Details</a>";

      echo "</div>";

 

 

 

     

}

 

?>

Link to comment
Share on other sites
hybmg57 Member

hybmg57

Posted
  • Author
Posted

Sorry the line 116 ($found_results) returns nothing.

Below is the code...

 

Any ideas?

 

<?php
if (is_numeric($_GET['pg'])) {
        $pgnum = ((int) $_GET['pg']);
}

else {
        $pgnum = 0;
        $errorMessage = "Invalid value for page number";

}

$startnum = 0;
$nextpg = ($pgnum + 1);
$prevpg = ($pgnum-1);

if ($pgnum > 0) {
        $offset = ($pgnum * 19);
}

else {
        $offset = 0;
}

$limit = 20;
?>

<?php

$Property_Type=$_GET['property_type'];
$City=$_GET['city'];
$Beds=$_GET['beds'];
$Baths=$_GET['baths'];
$Lowest_Price=$_GET['lowest_price'];
$Highest_Price=$_GET['highest_price'];

echo $Property_Type.", ";
echo $City.", ";
echo $Beds.", ";
echo $Baths.", ";
echo $Lowest_Price.", ";
echo $Highest_Price.", ";

include("db.php");


?>
<body id="www-rogers-healy-com" class="our-listings our-listings-landing" text>
   <div id="page">
      <div id="content-container">
         <div id="content" class="clearfix">
            <div id="container">
               <div id="listings-container"> <div id="listings">

<?php
$rets_login_url = "http://ntreisrets.mls.ntreis.net:80/rets/login";
$rets_username = "(Username entered)";
$rets_password = "(Password entered)";
$rets_agent = "(Agent)";

require_once('phRets.php');

$rets = new phRets;

$connect = $rets->Connect($rets_login_url, $rets_username, $rets_password);
if (!$connect) {
        print_r($rets->Error());
}



$result = mysql_query("SELECT *
                 FROM residential
                 WHERE   'PROPSUBTYPE'
                 AND   'SUBDIVISION' LIKE '{$City}'
                 AND   'BEDS' >={$Beds}
                 AND   'BATHSTOTAL' >={$Baths}
                 AND   'LISTPRICE' >={$Lowest_Price}
                 AND   'LiSTPRICE' <={$Highest_Price}
                 LIMIT {$offset}, {$limit}");
?>
<div id="rha-mls-pager-container">

<?php
if ($pgnum > 0) {
     echo("<a href='Search Results.php?pg=" . $prevpg . "'><< Previous
page</a>     ");
}

if ($pgnum > 0) {
     echo("<a href='Search Results.php?pg=" . $nextpg . "'>Next
page >></a>     ");
}

mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM residential LIMIT {$limit}");
$total_rows      =   mysql_query("SELECT FOUND_ROWS()");
$total_found   =    mysql_fetch_array($total_rows);
echo $total_found[0]." results found";
?>
</div><!--/#rha-mls-pager-container-->
<br/>

<?php


echo $found_results;
while ($row = mysql_fetch_array($result)){
      echo   "<div class=\"vcard listing\">";
      echo   "<div class=\"information\">";
      echo "<span style=\"size:20px\">\$".number_format($row['LISTPRICE'])."\n </span><br/>";
        echo "<p><span>{$row['STREETNUM']},{$row['STREETNAME']} ";
        echo "<BR/>{$row['CITY']}</span>";
        echo "{$row['STATE']}, {$row['ZIPCODE']}</p>";
      echo "<p><span>Beds: {$row['BEDS']}, Baths:{$row['BATHSTOTAL']}<br/>Square Feet: ".number_format($row['SQFTTOTAL'])." </span></p>";   
      echo   "</div>";

         $photos = $rets->GetObject("Property", "Thumbnail", "{$row['MLSNUM']}", "0", 1);
            foreach ($photos as $photo) {
                  $listing = $photo['Content-ID'];
                  $number = $photo['Object-ID'];
           
                  if ($photo['Success'] == true) {
                        echo "<img src='{$photo['Location']}\n' alt=\"\" title=\"\"  class=\"listing-photo photo\" width=\"146\" height=\"107\" />";
                  }
                  else {
                        echo "<img src='images/no-image.png' alt=\"\" title=\"\"  class=\"listing-photo photo\" width=\"146\" height=\"107\" />";
                  }
            }
           
      echo "<a href=\"#\" class=\"button url\">See Property Details</a>";
      echo "</div>";



     
}

?>

Link to comment
Share on other sites
hybmg57 Member

hybmg57

Posted
  • Author
Posted

I seem to be getting this error when I include,

 

				  WHERE	'PROPSUBTYPE' 
				  AND	'SUBDIVISION' LIKE '{$City}'
				  AND	'BEDS' >={$Beds}
				  AND	'BATHSTOTAL' >={$Baths}
				  AND	'LISTPRICE' >={$Lowest_Price}
				  AND	'LiSTPRICE' <={$Highest_Price}

 

inside....

$result = mysql_query("SELECT * 
				  FROM residential 

				  LIMIT {$offset}, {$limit}");

 

Link to comment
Share on other sites
hybmg57 Member

hybmg57

Posted
  • Author
Posted

I tied that and below error comes up...

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IS NOT NULL AND 'BATHSTOTAL' >=1 AND 'LISTPRICE' >=1000 ' at line 4

Link to comment
Share on other sites
ejaboneta Regular Member

ejaboneta

Posted
Posted

Don't put single-quotes around column names. That makes them strings, not column names.

 

Hmm. How'd I miss that?... Well I was going to say, if you are still having a problem with it, can you post the whole query here? Judging from the 'IS NOT NULL' part of error, I'd say you are using a different one than the query in the code you are giving us because I don't see it in there.

Link to comment
Share on other sites
hybmg57 Member

hybmg57

Posted
  • Author
Posted

Thank you!!! It's worked!!! You are a legend!!!  :D

I have another question...

I am trying to get total number of returned results on the page with below query...

mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM residential LIMIT {$limit}");
$total_rows		=	mysql_query("SELECT FOUND_ROWS()");
$total_found	= 	mysql_fetch_array($total_rows);
echo $total_found[0]." results found";

 

This come out with 2000 results found (which is the total number of rows available in MySQL...)

Is there a way we can come up  with total number of search returned rows only?

Link to comment
Share on other sites
ejaboneta Regular Member

ejaboneta

Posted
Posted

The total returned maximum would be the limit you put on it. So if you want the total without the limit, you need another query.

 

You could have mysql count the results with a another query

SELECT count(*) AS total_found FROM table_name WHERE whatever = 'whatever'

 

or you can use mysql_num_rows

 

$query = mysql_query($sql);
$count = mysql_num_rows($query);

Link to comment
Share on other sites
hybmg57 Member

hybmg57

Posted
  • Author
Posted

I tried this and it has worked!!! Thanks you very much for all your help!!!

mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM residential 					  
		WHERE	AREA LIKE '{$City}'
		AND	BEDS >={$Beds}
		AND	BATHSTOTAL >={$Baths}
		AND	LISTPRICE >={$Lowest_Price}
		AND	LISTPRICE <={$Highest_Price}
		LIMIT {$offset}, {$limit}");
$total_rows		=	mysql_query("SELECT FOUND_ROWS()");
$total_found	= 	mysql_fetch_array($total_rows);
echo $total_found[0]." results found";

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.