Help on MySQL searchform

Search MySQL

[Dustin]

Hi,

I'm doing a search form, but I still don't get what else is missing from my code to make it work.

The Query statement is:

$query = "SELECT * from Resident WHERE LP BETWEEN " . $_POST['minPrice'] . " AND " . $_POST['MaxPrice']" AND BR = " . $_POST['br']" AND BTH = " . $_POST['bth']" AND YBT BETWEEN " . $_POST['ybtmin'] . " AND " . $_POST['ybtmax']" AND SFF BETWEEN " . $_POST['minsqft'] . " AND " . $_POST['maxsqft'];

And the code for the Search Form:

<form name="searchListingForm" method="post" action="search3.php">
<input type="submit" name="Submit" value="search" border="0">

Price Range, Bedrooms &amp; Bathrooms:

Min Price:
<select name="minPrice">
<option value="0" selected>No Min&nbsp;</option>
<option value="10000" >$10,000</option>
<option value="20000" >$20,000</option>
<option value="30000" >$30,000</option>
<option value="40000" >$40,000</option>
<option value="50000" >$50,000</option>
</select>

Max Price:
<select name="MaxPrice">
<option value="100000000" >No Max</option>
<option value="10000" >$10,000</option>
<option value="20000" >$20,000</option>
<option value="30000" >$30,000</option>
<option value="40000" >$40,000</option>
<option value="50000" >$50,000</option>
<option value="60000" >$60,000</option>
<option value="70000" >$70,000</option>
<option value="80000" >$80,000</option>
<option value="90000" >$90,000</option>
<option value="100000" >$100,000</option>
<option value="100000000" >No Max</option>
</select> </td> <td> <b>Bedrooms:</b><br>
<select name="br" size="1">
<option value="" >No Min&nbsp;&nbsp;&nbsp;</option>
<option value="1" >1+</option>
<option value="2" >2+</option>
<option value="3" >3+</option>
</select>

Bathrooms:
<select name="bth" size="1">
<option value="" >No Min&nbsp;&nbsp;&nbsp;</option>
<option value="1" >1+</option>
<option value="1.5" >1.5+</option>
<option value="1.75" >1.75+</option>
<option value="2" >2+</option>
</select>

<input type="submit" name="Submit" value="search" border="0">

Property Type(s):

Property Features:
<input type="Checkbox" name="ptyp" value="RESI" checked>Residential

Year Built:
<input type="text" name="ybtmin" value="" size="4">
<input type="text" name="ybtmax" value="" size="4">

Square Feet:
<input type="text" name="minsqft" value="" size="4">
<input type="text" name="maxsqft" value="" size="4">

____________________________

Can someone please help me out with this? Any help will be much appreciated! Thanks

[edited by: jatar_k at 5:39 pm (utc) on April 22, 2005]
[edit reason] tore out all the table code [/edit]

[KingMacro]

to begin with...

$query = "SELECT * from Resident WHERE LP BETWEEN " . $_POST['minPrice'] . " AND " . $_POST['MaxPrice']" AND BR = " . $_POST['br']" AND BTH = " . $_POST['bth']" AND YBT BETWEEN " . $_POST['ybtmin'] . " AND " . $_POST['ybtmax']" AND SFF BETWEEN " . $_POST['minsqft'] . " AND " . $_POST['maxsqft'];

missing a . :-D

also a word of advice, dont use $_POST variables directly in the query, make them safe first to prevent someone posting the text ";DROP DATABASE Whatever;" in one of the fields...

for numbers just use ($_POST['whatever']+0) and for text use mysql_escape_string... although its not a problem with the script as such its a potential major security issue

[Dustin]

Thanks for your help. But still it doesn't work.

I need some help, please :)

[jatar_k]

Welcome to WebmasterWorld Dustin,

what is happening? I don't understand what it is, and isn't, doing. Did you fix that syntax error that KingMacro mentioned? It should read

AND BTH = " . $_POST['bth'] . " AND YBT BETWEEN " .

[Dustin]

Hi Jatar,

Thanks for your welcome message.

I changed it to:

_________________________
mysql_connect("localhost","####","*****");

mysql_select_db("mydatabase") or die("Unable to select database");

$query = "SELECT * FROM Resident WHERE LP BETWEEN " . $_POST['minPrice'] . " AND " . $_POST['MaxPrice'] . " AND BR = " . $_POST['br'] . " AND BTH = " . $_POST['bth'] . " AND YBT BETWEEN " . $_POST['ybtmin'] . " AND " . $_POST['ybtmax'] . " AND SFF BETWEEN " . $_POST['minsqft'] . " AND " . $_POST['maxsqft'] ."";

$result = mysql_query($query) or die("Couldn't execute query");
___________________________________

and now I've got message "Couldn't execute Query"

I really don't know why.....please help :(

[jatar_k]

first let's try to change this

$result = mysql_query($query) or die("Couldn't execute query");

to

$result = mysql_query($query) or die("Couldn't execute query: " . mysql_error());

that will enable you to see the true error from mysql and provide some more information.

[Dustin]

Ok..when I added that code in, this is what I've got

Couldn't execute query: 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 'AND BTH = AND YBT BETWEEN Min Year Built must be a number AND

I did check and change from VARCHAR to INT for those, but it still gives me errors

[Dustin]

Ok..after I added the '' before and after the table name 'Resident' this is what I've got:

Couldn't execute queryYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''Resident' WHERE LP BETWEEN 40000 AND 350000 AND BR = 1 AND BTH

The numbers: 40000, 350000, ect...are the one I input in a search form for testing.

Thanks a lot for your help!

[KingMacro]

if you change it to

$result = mysql_query($query) or die("Couldn't execute query\"$query\" Error:" . mysql_error());

then it will show you the full query it is trying to execute - makes for much easier debugging (dont forget to remove it once the problem is fixed for security reasons)

[Dustin]

I've got this message

Couldn't execute query"SELECT * FROM Resident WHERE LP BETWEEN 10000 AND 600000 AND BR = 2 AND BTH = 1 AND YBT BETWEEN AND AND SFF BETWEEN AND limit ," Error: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 'AND AND SFF BETWEEN AND limit ,' at line 6

I'm going crazy...please help. :(

[jatar_k]

sorry Dustin, I misunderstood in your last post, I kinda thought you fixed it

well from what that error is telling you it doesn't like the

AND AND

I assume something is missing there or you just need to remove one of them

[Dustin]

Hi Jatar,

I remove all, except for this:

$query = "SELECT * FROM Resident WHERE LP BETWEEN ".$_POST['minPrice']." AND ".$_POST['MaxPrice']."";

And I've got this:

Couldn't execute query"SELECT * FROM Resident WHERE LP BETWEEN 225000 AND 350000 limit ," Error: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 '' at line 3

[jatar_k]

you are missing values for limit also

>> AND 350000 limit ,"

should be some numbers there, such as

limit 10
or
limit 0,25

look for limit in this page
[dev.mysql.com...]

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)

[Dustin]

Hi KingMacro and Jatar,

Thanks for all your help. Now everything work just FINE! I really appreciate your help pointing me to the RIGHT direction!

Now if I may, I'd like to ask another question, please.
__________
The results return hundred of records. How can I make a Previous 10, or Next 10 link?
I did use the LIMIT 10, but then it only return 10 and that's it.

Thanks again,

[jatar_k]

when you look at limit in your sql queries, remember

[offset] , [number of results]

so, if we decide to put 10 per page then

[number of results] should always be 10, this gives us this as our standard limit then

LIMIT [offset, ] 10

we don't need to use the offset, which means we shouldn't use the comma if we don't use the offset. Though we do want the offset in this case, we just don't know what it will be for each request, aaahhhh, we need a variable and then we can use php to figure out what number to use for the offset.

So what do we know?
to return from the beginning of the results we use 0
we are using 10 results
- so the first page would be result 0 - 9
- every page after the first should be +10 from our offset
- every page before should be -10 from our offset

so we need a default, I think we should use 0 as that is the first result, then we need to pass a value in our links for what the new offset would be.

what about a basic little check based on the above criteria

<?
$start = 0;
if (isset($_GET['start'])) $start = $_GET['start'];
$sql = "SELECT * FROM Resident WHERE LP BETWEEN 225000 AND 350000 limit " . $start . ",10";
echo '<p>',$sql;
$next = $start + 10;
$previous = $start - 10;
if ($previous < 0) $previous = 0;
echo '<p><a href="http://www.example.com/page.php?start=' . $previous . '">previous 10</a> : <a href="http://www.example.com/page.php?start=' . $next . '">next 10</a>';
?>

something like that