[Resolved] Returning Results

furious5

Can anyone help me out, I'm struggling with returning some results from mysql

Basically I am doing a search for a villa, given specific parameters which is fine.

My problem comes when I try and return the matches. When the results are returned the search iterates through the correct villa ids but returns the same name for all the villas.

E.g:

17 - Ca’n Toni
50 - Ca’n Toni
94 - Ca’n Toni
95 - Ca’n Toni
259 - Ca’n Toni
265 - Ca’n Toni

Which should be something like:

17 - Ca’n Toni
50 - Villa Mosson
94 - Villa Camp del Bosch
95 - Poletas 2 Eva
259 - Villa Pedra Vista
265 - Villa C’al Perdiu

Here is the code I am using to run the search:

mysql_select_db($database_conVillas, $conVillas);
$sql = "SELECT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND pricing1.week = '03-01-04' AND pricing1.price != '0'";
$sql_result = mysql_query($sql) or die(mysql_error());
$num_rows = mysql_num_rows($sql_result);


if ($num_rows == 0){
echo 'Sorry your search did not return any results. Please return to the search page <a href=search.php>here</a>';
} else {


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

		echo $row["ID"] . " - " .$row["Name"]; 
		echo "<p>";

	}
}

Any ideas, as usual I am stumped?

furious5

I've just had a bit more of a play around with phpmyadmin running the SQL and noticed I get numerous records at the server.

The only way I could think to overcome this is to return the ID of the villa, and then pass this to the pricing1 table to join the query.

The only problem is I am not passing the villa_id in to the query.

The best way around it I can think of is to run 1 query to find out which villas have a resort which matches, and then run another query to find out if they are available on that date and have a price.

Could I run this as a sub query, or would I need to write an entirely seperate query, and pass the results from the first query in? 😕

jsim

You mistaken in sql query. In query you do not give any relations between two tables. So, if in one table fonded 1 matched row and in a second one - 3, then you get result with 3 rows where matched from from first table will be diblicated in all 3 rows.
You need something like this:

"select ... where ... and villas.id=pricing1.villa_id"

furious5

Yes, my problem is that I am not passing a ID in to the search, so need to somehow find the ID and then pass this to another query or sub-query.

I'm not at all sure how to do this though, any ideas anyone??

jsim

send your tables desing

furious5

Villas: ID, Resort, Name

Pricing: Price_ID, ID, Week, Price

jsim

Pricing.ID holds villa id i think?

furious5

Correct

jsim

So try this query:

$sql = "SELECT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND pricing1.week = '03-01-04' AND pricing1.price != '0' and villas.id=pricing1.id";

furious5

Perfect - Thank you