[SOLVED] array help

[twsowerby]

Hi all,

 

I've got a form which passes its data to an array and is then processed to see if there are matches in the database. It was working fine when I was retrieving data from one table, but as soon as I tried to match a value in one table to another table it didn't work properly.

 

The code is:

 

<?php
require_once('sessionAndDB.php');
$resortID=$_POST['resortID'];
$where = array();
if($_POST['resortID'])
  $where[] = "villas.resortID = $resortID";
if($_POST['pool'])
  $where[] = "villas.pool = '1'";
if($_POST['hot_tub'])
  $where[] = "villas.hottub = '1'";
if($_POST['tv'])
  $where[] = "villas.tv = '1'";
if($_POST['sauna'])
  $where[] = "villas.sauna = '1'";
if($_POST['dwasher'])
  $where[] = "villas.dWasher = '1'";
if($_POST['washer'])
  $where[] = "villas.washer = '1'";
if($_POST['beach'])
  $where[] = "villas.beach = '1'";
if($_POST['shops'])
  $where[] = "villas.shops = '1'";
if($_POST['bars'])
  $where[] = "villas.bars = '1'";
$sql = "SELECT villas.*,resorts.resortName FROM villas, resorts";
if(count($where))
  $sql .= " WHERE ".implode(' AND ',$where);
$query = mysql_query($sql);
$count = mysql_num_rows($query);
while ($row = mysql_fetch_array($query))
{
$Name=$row["Name"];
$Resort=$row["resortName"];
$Price=$row["price"];
$ID=$row["ID"];
echo "<a href=\"./villadesc.php?id=$ID\">$Name</a>, $Resort, £$Price";
}

if(($count)==0) {
echo "<p>We're sorry, your search didn't return any results.</p><p>Please try again with a different query, or you can <a href='search_script.php'>click here</a> to see a list of all our villas</p>";
}

?>

 

The script does return the right row (ie the correct villa name and price), but instead of just displaying the selected data once, it displays the data once for each value that is in the resortName field in the resorts table.

 

Been staring at it for a long long time now and can't fix it, so if anyone can spot the bug I would really appreciate it!

 

Regards,

 

Tom

[Barand]

And the query is what?

[twsowerby]

query is in that block of code...

 

$sql = "SELECT villas.*,resorts.resortName FROM villas, resorts";
if(count($where))
  $sql .= " WHERE ".implode(' AND ',$where);

 

Have I missed something?

 

Tom

 

 

[AP81]

Looks like you aren't joining the tables correctly.

 

Take a look at your code, I can't see anywhere where you are joining the villas table to the resorts table.  You'll need to edit your query to something like this:

 

<?php
  $sql = "SELECT villas.*,resorts.resortName FROM villas, resorts WHERE villas.resortID =  resorts.resortID;
?>

 

This takes into account that resortID is the Key you will be joining on.

[twsowerby]

Thanks AP81,

 

I had tried the join earlier on to no effect, but your post made me realise that when I was joining them I was using the posted variable, so:

 

WHERE villas.resortID = resorts.$resortID

 

That passes in the resort ID as the joined table field, which of course is not what I wanted. Guess I was just having a dim moment. (in my defense it was late!)

 

Anyway, sorted it now, thanks for replying.

 

Regards,

 

Tom