Searching a DB

Anon

I am fairly new to PHP and MySQL, so excuase me for being thick with this.

I am writing a database to hold info for a letting agency by two tables (properties and landlords).

I wish to perform a user search on the database for clients to find a property with an option for location, rent and landlord type (private or agency), but cant' seem to get the proper sql to check all three as a whole or individually. Any help?

Anon

i presume you got a landlordID in your properties and landlords tables (so one landlord can have many properties). in this case, your first step is to pull all the data into one table using a join...

SELECT * FROM Properties LEFT JOIN Landlords ON Properties.LandlordID = Landlords.LandlordID

This will then be the basis of the query, you then need to produce a WHERE clause for the SQL statement, refining this table to suit the criteria specified, assuming you've got location, rent and type as arrays (if they we're chosen using a multiple selection list) then you assemble the WHERE clause by going through the arrays to produce a string such as..

(Location = "Location1" OR Location = "Location2") AND (Rent = "200-400") AND (Type = "Private")

then bung it on the end of the SQL you had before... SELECT * F ... ordID WHERE (Locat ... )

you get the idea...

if you need any help with the PHP to do the assembling of the WHERE clause, just reply back...

Oh, some advice when having trouble with SQL stuff... always assemble the SQL statement first, then run it... this gives you a chance to debug easily by printing out the contents of a variable

$DEBUG = 1

... some code ...

$query = "SELECT * FROM Landlords";
if($DEBUG) print "<P>".$query."</P>\n";
$result = mysql_query($query);

hope the ramblings are of some help

dom
🙂

Anon

Ok here is what ive got

If i search for - say "location" of london with "rent" of 1-£50 and no landlord type, i comes back saying no result even though i know there is one in the database with that critera.

case "searchpage":

        echo "<FORM method=\"post\" action=\"$PHP_SELF?command=search\">";
        echo "<H3>Search For A Property</H3>";
        echo "<table width=400 cellpadding=0 cellspacing=0 border=0>";
        echo "<TR>";
        echo "<TD width=200>Rent:</TD>";
        echo "<TD><select name=\"rent\"><option value=\"\">No Selection</option><option value=\"1-50\">£1-£50</option><option value=\"51-100\">£51-£100</option><option value=\"101-150\">£101-£150</option><option value=\"151\">£150</option></select</TD></TR>\n";
        echo "<TR>";
        echo "<TD width=200>location:</TD>";
        echo "<TD><input type=\"text\" name=\"location\"></TD></TR>\n";
        echo "<TR>";
        echo "<TD width=200>Type Of Landlord:</TD>";
        echo "<TD>Private Landlord <input type=\"radio\" name=\"type\" value=\"Private Landlord\"> Letting Agency <input type=\"radio\" name=\"type\" value=\"Letting Agency\">  ";
        echo "<TR><TD colspan=\"2\" align=\"center\"><INPUT type=\"submit\" value=\"Search\"> <INPUT type=\"reset\" value=\"Reset\"></TD></TR>";
        echo "</table>";

        break;

case "search":

  $sql = "select landlords.company_name, landlords.type, properties.ID, properties.landlord, properties.address, properties.location, properties.rent, properties.period from properties left join landlords on properties.landlord=landlords.ID WHERE(location = '$location') AND (rent = '$rent') AND (landlord = '$type')order by ID";

    $result = mysql_query($sql,$db);		
    $numrows = mysql_num_rows($result);

Anon

hi,

if you're searching with no specified landlord type, then the last bit of your WHERE clause will read landlord=''. i imagine this will produce no records in the result as no landlords = ''

also, what type is properties.landlord? you seem to be searching for results with this set to $type, but you're also using it in the join statement, suggesting its an id...

look at the sql statement that gets produced by printing it out between $result... & $numrows... this will tell you what it's doing, then run this query in mysql itself and see what happens.

hope you have a bit more luck now

dom
🙂