Please help with code

[nelquintin]

What i want to do is match my clients(stored in a datbase) to a property ,i pulled up as a result.Here is my code.The thing is it doesnt match the client and the property properly.Here is my code.Your help is appricated.
<?php
mysql_connect("localhost","root","pass123");
mysql_select_db("vap");
$search = $_POST["search"];
$query = "SELECT * FROM propertys WHERE ref LIKE '%".$_POST['search']."'";
$result = mysql_query($query);

if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_array($result)) {
        $picture = $row["picture"];
        $minprice = $row["minprice"];
    $maxprice = $row["maxprice"];
$price = $row["price"];
        $ref = $row["ref"];
        $type = $row["type"];
        $erf = $row["erf"];
        $size = $row["size"];
        $bed = $row["bed"];
        $bath = $row["bath"];
        $gar = $row["gar"];

echo "<image src=\"$picture ","\" style=\"border: 2px solid black;\"><br><br>";
echo "<br> $ref <br> $price <br> $type <br> $erf <br> $size <br> $bed <br> $bath <br> $gar <br>";
}
}
?>
<form method="POST" action="matchclient.php">
<input type="Submit" name="Submit" value="Match Client">
</form>

And here is matchclient.php
<?php
mysql_connect("localhost","root","pass123");
mysql_select_db("vap");
$search = $_POST["search"];
$price = $_POST["price"];
$query = "SELECT * FROM client WHERE price LIKE '%".$_POST['search']."'";
$result = mysql_query($query);

if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_array($result)) {
        $name = $row["name"];
        $surname = $row["surname"];
    $price = $row["price"];
    $mobile = $row["mobile"];
$office = $row["office"];
        $home = $row["home"];
        $email = $row["email"];
   

echo "<br> Name: $name <br> Surname: $surname <br> Price: $price <br> Mobile: $mobile <br> Office: $office <br> Home: $home <br> Email: $email<br>" ;

}
}
?>

[Jenk]

you might want to remove your DB credentials.. and you'll also want to escape your input for the SQL query.

As for the original question.. can you clarify please?

you want to select clients based on property? If so, you'll need a foreign key in either table to link them together, then select based on that.

[code]SELECT * FROM `clients` WHERE `property_id` = '$foo'[/code]

[-Mike-]

A few things i thought, but may not be right:

you are searching to match price field to $_POST['search'] and not $_POST['price'] or something similiar?

Your while loops should specify mysql_fetch_array($result, MYSQL_ASSOC) - because you then use the name of the fields in the database to reference your fields. My memory doesn't remember if it does this automatically or not though...
[code]
hile ($row = mysql_fetch_array($result)) {
        $picture = $row["picture"];
        $minprice = $row["minprice"];
      $maxprice = $row["maxprice"];
      $price = $row["price"];
        $ref = $row["ref"];
        $type = $row["type"];
        $erf = $row["erf"];
        $size = $row["size"];
        $bed = $row["bed"];
        $bath = $row["bath"];
        $gar = $row["gar"]; [/code]

Your image:
[code]echo "<image src=\"$picture ","\" style=\"border: 2px solid black;\">[/code]
is incorrectly escaped for quotations:
[code]echo "<image src=\"$picture \" style=\"border: 2px solid black;\">[/code]

[code]
$query = "SELECT * FROM propertys WHERE ref LIKE '%".$_POST['search']."'";[/code]

Doesn't need to escaping there - [code]
$query = "SELECT * FROM propertys WHERE ref LIKE '%$_POST['search']%' ";[/code]
I added an extra % sign in, otherwise you are searching for things ending in the value of $_POST['search']

Finally, just for testing, i'd make that if(mysql_num_rows($result)) into if(mysql_num_rows($result) > 0){
instead... i seem to remember it giving me problems at one point :/

[Jenk]

It most definately does need escaping.

[-Mike-]

[quote author=Jenk link=topic=104636.msg417474#msg417474 date=1155828804]
It most definately does need escaping.
[/quote]

Remarkably clear on your correction - WHICH part :P
I be assuming it's:

[code]
%".$_POST['search']."'";[/code]

I was going off the fact that it's acceptable to have put in:
'$var'
to your sql coding without any hiccup, and simply went with that. As it was, I still question whether he was wanting to search only the end of their fields for the search value, or anywhere in there..

[Jenk]

the part you refer to as "Doesn't need to escaping there -"

[nelquintin]

My testing box is nt connexted to any server so it doesnt make a dirrence.
If i pull up a property i want to match all my clients in another table with that property the foreign key would be price i think?Should i have a min and max price for my clients?

[Jenk]

price is a bad choice for foreign key. what if different properties have the same price?

create a new field on your clients table for the id of the property, which matches the property id from the properties table that said client is affiliated with.


as for not escaping.. well, not going to argue but it needs escaping. what happens when you move it to production? Can you absolutely 100% guarantee you won't forget to change it? It's also bad habits to not escape even when you think it's safe. :p

[-Mike-]

[quote author=nelquintin link=topic=104636.msg417495#msg417495 date=1155830195]
My testing box is nt connexted to any server so it doesnt make a dirrence.
If i pull up a property i want to match all my clients in another table with that property the foreign key would be price i think?Should i have a min and max price for my clients?
[/quote]

In that case, once you've found your property, I don't quite see where the value of "price" comes from when passing it onto the next php page? You only have a submit button and nothing else (really), but nothing that actually passes the needed variables of price and search onto the next page for processing your next query.

[code]
<form method="POST" action="matchclient.php">
<input type="Submit" name="Submit" value="Match Client">
</form>[/code]

In which case, if you add a hidden field:
[code]
<form method="POST" action="matchclient.php">
<input type="hidden" name="price" value="<?php echo $price; ?>" />
<input type="hidden" name="search" value="<?php echo $search;?>" />
<input type="Submit" name="Submit" value="Match Client">
</form>
[/code]
Then when you hit the submit button to pull up your clients, both price and search will have values...
Otherwise it looks like your next search (to find the clients based upon the property you've found) are attempting to match, well, no value as far as i can see - as it doesn't currently exist as a posted variable on that submit button.

[nelquintin]

Thanks but if i push submit it brings up all the clients in the database instead of just the clients matching the propertys price range....

[-Mike-]

[quote][code]$price = $_POST["price"];
$query = "SELECT * FROM client WHERE price LIKE '%".$_POST['search']."'";[/code][/quote]

Maybe do a slight change:
[code]
$price = $_POST["price"];
$query = "SELECT * FROM client WHERE price BETWEEN '$price'-1000 AND '$price' + 1000";
[/code]

The above shows selecting price from the search criteria - but isn't the criteria the price, at which point shouldn't it be the price that is matching the value in the price column?

An alternative, apart from a fixed price, is to have a range for each customer... or a percentage variable of the price instead (ie 200,000 +- 10%, so 180-220,000).
I just added (not sure about syntax on it) the ability to check between two prices based upon the clients stated price, plus or minus 1000.

As I said, don't quite get why you match their price column with your search result... shouldn't it be your search result brings up a price, which customers are then matched against that price?