twsowerby Posted April 14, 2008 Share Posted April 14, 2008 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 14, 2008 Share Posted April 14, 2008 And the query is what? Quote Link to comment Share on other sites More sharing options...
twsowerby Posted April 15, 2008 Author Share Posted April 15, 2008 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 Quote Link to comment Share on other sites More sharing options...
AP81 Posted April 15, 2008 Share Posted April 15, 2008 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. Quote Link to comment Share on other sites More sharing options...
twsowerby Posted April 15, 2008 Author Share Posted April 15, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.