What is the name of the third table and what columns does it have that are related to either tbl_tematics or tbl_hotels?
Well this is how it looks, and in honesty im a little worried as there are same name columns, but this is how it works.
This database was put together a long time ago for a different format website, but they dont want to change it at this point as there time constraints.
tbl_tematics - Id_Tem
thats is the start
then that relates to this tabel
tbl_hotntem - Id_Hot & Id_Tem
which then relates to content in the final table
tbl_hotels - Id_Hot, IdCntry_Hot, Act_Hot
Okay, so it looks like you could do this:
SELECT
DISTINCT Id_Tem
, Id_Hot, Id_Tem
, Id_Hot
, IdCntry_Hot
, IdRsrt_Hot
, Act_Hot
FROM tbl_tematics
LEFT JOIN tbl_hotntem ON (tbl_resorts.Id_Tem=tbl_hotntem.Id_Tem)
LEFT JOIN tbl_hotels ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot)
WHERE
(tbl_resorts.IdCntry_Rsrt='".$selectCountry."')
AND (tbl_resorts.Id_show='1')
AND (tbl_hotels.Act_Hot='1')
ORDER BY Nom_Rsrt
You may need to tweak the query (the ON statement specifically to make sure it matches on the appropriate columns)
Oh so its ok to have the same column names when doing a search using more than one table.
Oh thats not so bad then and thanks cpradio that looks just the ticket, will give it a go and see, and yes can see the tweeks to get it right.
Thanks mate
Its coming together nicely in honesty and Im very happy that I’m getting through the bulk of it myself, which wasnt the case ont he last project, so there def progress over the year, so thank you for that again.
This is I think how it needs to work for me:
<?
$x=mysql_query("SELECT DISTINCT Id_Tem, Desc_Tem, Id_Hot, Id_Tem, Id_Hot, IdCntry_Hot, IdRsrt_Hot, Act_Hot FROM tbl_tematics
LEFT JOIN tbl_hotntem ON (tbl_tematics.Id_Tem=tbl_hotntem.Id_Tem)
LEFT JOIN tbl_hotels ON (tbl_hotntem.Id_Hot=tbl_hotels.Id_Hot)
WHERE
(tbl_hotels.IdCntry_Hot='".$selectCountry."')
AND (tbl_hotels.Act_Hot='1')
ORDER BY Desc_Tem");
while($z=mysql_fetch_assoc($x)){ ?>
<input type="checkbox" name="category[]" value="<?=$z['tbl_tematics.Id_Tem']?>" onClick="javascript:checkRefresh()" <?=((in_array($z['tbl_tematics.Id_Tem'], $_REQUEST['category'])) ? "checked=\\"checked\\" " : "")?> class="inline" /> <?=$z['tbl_tematics.Desc_Tem']?><br/>
And here is the relationships and tabels.
tbl_tematics = Id_Tem, Desc_Tem
tbl_hotntem = Id_Hot, Id_Tem
tbl_hotels = Id_Hot, IdCntry_Hot, Act_Hot
Why I’m posting back is because I’m not getting the output on the page for categories
I suggest running your query in phpMyAdmin and see what you get back. You may be getting an error, in fact, I’m almost certain you are.
What I see is this:
SELECT DISTINCT Id_Tem, Desc_Tem, Id_Hot, Id_Tem, Id_Hot, IdCntry_Hot, IdRsrt_Hot, Act_Hot
Place the tablename in front of each column.
SELECT DISTINCT tbl_tematics.Id_Tem, tbl_tematics.Desc_Tem, tbl_tematics.Id_Hot, tbl_tematics.Id_Tem, tbl_hotels.IdCntry_Hot, tbl_hotels.IdRsrt_Hot, tbl_hotels.Act_Hot
no sorry its wrong, 2 secs, i thnk i can see the problem, its the final Id_Hot.
The others are created this one is the auto increment.
No again, I think its right.
Ok lets work it through as you have done.
Ok I is giving an output now using the code below, but its not outputting the name and it isnt being DISTINCT with its out put either:
<?
$x=mysql_query("SELECT DISTINCT tbl_tematics.Id_Tem, tbl_tematics.Desc_Tem, tbl_hotntem.Id_Hot, tbl_hotntem.Id_Tem, tbl_hotels.Id_Hot, tbl_hotels.IdCntry_Hot, tbl_hotels.IdRsrt_Hot, tbl_hotels.Act_Hot
FROM tbl_tematics
LEFT JOIN tbl_hotntem ON ( tbl_tematics.Id_Tem = tbl_hotntem.Id_Tem )
LEFT JOIN tbl_hotels ON ( tbl_hotntem.Id_Hot = tbl_hotels.Id_Hot )
WHERE (
tbl_hotels.IdCntry_Hot ='1'
)
AND (
tbl_hotels.Act_Hot ='1'
)
ORDER BY tbl_tematics.Desc_Tem");
while($z=mysql_fetch_assoc($x)){ ?>
<input type="checkbox" name="category[]" value="<?=$z['tbl_tematics.Id_Tem']?>" onClick="javascript:checkRefresh()" <?=((in_array($z['tbl_tematics.Id_Tem'], $_REQUEST['category'])) ? "checked=\\"checked\\" " : "")?> class="inline" /> <?=$z['tbl_tematics.Desc_Tem']?><br/>
<? } ?>
You’d have to play with that query in phpMyAdmin until you get it to output what you want. Unfortunately, I’d have to have the schemas, and data to be able to assist with that.
Ye I dont think its going to happen this way as the DISTINCT is working correctly, its just that I think its bringing out the DISTINCT hotels, when all i want is to bring out one DISTINCT category title thats contained within the country, if it does occur.
i thikn i need to use DISTINCT again somewhere, to only show one, so I suppose a query within a query
I’m having trouble getting the right data out for a count with the code below, its not right, so wondered if you can remind me how to see what is it the code below is doing.
<?
$queryNumber = mysql_query("select count(Id_Hot) as total from tbl_hotntem LEFT JOIN tbl_hotels ON (tbl_hotels.Id_Hot=tbl_hotntem.Id_Hot) where (IdRsrt_Hot='". $q['Id_Rsrt'] ."') and (tbl_hotntem.Id_Tem IN (". $categoryData ."))");
?>
Well if you want to see what it is sending to MySQL, just put this line above or below your line:
echo "select count(Id_Hot) as total from tbl_hotntem LEFT JOIN tbl_hotels ON (tbl_hotels.Id_Hot=tbl_hotntem.Id_Hot) where (IdRsrt_Hot='". $q['Id_Rsrt'] ."') and (tbl_hotntem.Id_Tem IN (". $categoryData ."))";
That should show you the query being executed, copy and paste that into phpMyAdmin (or whatever tool you use) and see what results it gives you.
Hi cpradio,
Can you see what is wrong with the line of code below.
<?
$sql.="select * FROM tbl_hotels ". if ($sqlcategory=="") { } else { $sqlcategory; } ." WHERE Act_Hot='1' ";
?>
An error keeps coming up in Dreamweaver to say its not right.
Yep, that is not valid syntax. 
You mean to do any of the following:
<?
$sql.="select * FROM tbl_hotels ". ((!empty($sqlcategory)) ? $sqlcategory : "") ." WHERE Act_Hot='1' ";
?>
<?
if (!empty($sqlcategory))
$sql.="select * FROM tbl_hotels ". $sqlcategory ." WHERE Act_Hot='1' ";
else
$sql.="select * FROM tbl_hotels WHERE Act_Hot='1' ";
?>
Ah right, thank you cpradio…
<?
$sql.="select * FROM tbl_hotels ". ((!empty($sqlcategory)) ? $sqlcategory : "") ." WHERE Act_Hot='1' ";
?>
Thats a lot different to mine isnt it, but thanks for the help again, always very appreciated.
Well, I made a slight improvement of using the [fphp]empty[/fphp] function instead of comparing to “”. It adds the benefit that if $sqlcategory is ever null it will behave how you’d expect too.
Yes as it would be, yours is a lot, lot neater than mine.
I’ve sort of nearly got the thing working, but for showing the hotels when a user clicks one of the options in the categories.
I’ve got the code showing and it seems right, but I’m missing something which i will find out im sure.
http://devchecksafetyfirst.csf.dcmanaged.com/index.php?regions[]=288&star[]=3&category[]=2&Country=7
Ah there you go I got it, I noticed that I didnt call the table before one of the Id_Hot, the order by one in fact.
<?
$sql.= " ORDER by Id_Hot ASC";
?>
needs to be
<?
$sql.= " ORDER by tbl_hotels.Id_Hot ASC";
?>
<?
$order = " ORDER by Id_Hot ASC";
$order2 = " ORDER by tbl_hotels.Id_Hot ASC";
$sql.= "". ((!empty($sqlcategory)) ? $order2 : "") ."";
?>
This works fine as long as $sqlcategory is not empty, so how do I have it so that $order works instead of $order2 if $sqlcategory is empty