SOLVED 2 Repeat Regions

[ltoto]

i designed the tables myself

Would i need to add something to one of the region or country tables then i assume

[ltoto]

[code]<?php

if($row_rsPages['Id']== "15"  )
{
$row_rsCountry_Q = mysql_query("SELECT * FROM tabCountry  ORDER BY 'countryName' ") or die(mysql_error());
while($row_rsCountry = mysql_fetch_array($row_rsCountry_Q))

{
echo '<h2>'.  $row_rsCountry['countryName'].'</h2>';
}
$row_rsRegion_Q = mysql_query("SELECT * FROM tabRegion WHERE 'regionName' = '".$row_rsCountry['Id']."' ORDER BY 'regionName' ") or die(mysql_error());
while($row_rsRegion = mysql_fetch_array($row_rsRegion_Q))
{
echo '<br />' . $row_rsRegion['regionName'] . '<br />';
}
}
?>[/code]

right now this is the code, which shows the Countrys Fine now, but not the regions, i am assuming i must add something to the region table , so that the same regions dont list in every country, but they only show in the country that they are from.

alothough i have been looking and am a little confused of what needs adding to the table.

hope this makes sense

[HuggieBear]

Id say you needed them as follows:

[color=green][b]tabCountry[/b] - countryID, countryName
[b]tabRegion[/b] - regionID, countryID, regionName
[b]tabHotel[/b] - hotelID, regionID, hotelName, hotelImage, hotelDescription, hotelRating[/color]

This way you can cascade down.

Regards
Rich

[ltoto]

so basically just add the country name to the tabRegion, and set the drop down menu up in the database, so i can choose the country for the region, and then would the code work in the front end or would it need changing

[HuggieBear]

Your code in the front-end would possibly need changing too, but with the tables setup as they are, you can get the following:

All Countries,
All Regions in a specific country,
All Hotels in a specific country,
All Regions,
All Hotels in a specific region,
All Hotels.

Regards
Rich

[ltoto]

so just to clarify, without changing the tables, I could do that with what i have got at the moment, which is what I am looking to do?

if so, I am assuming this bit needs changin in the code:

[code]$row_rsRegion_Q = mysql_query("SELECT * FROM tabRegion WHERE 'regionName' = '".$row_rsCountry['Id']."' ORDER BY 'countryName' ") or die(mysql_error());
while($row_rsRegion = mysql_fetch_array($row_rsRegion_Q))
{
echo '<br />' . $row_rsRegion['regionName'] . '<br />';[/code]

again rich thanks for helping, I am just getting confised and struggling at the moment because this is the first time i have done something like this, and i am trying to get it finished asap, you have been a great help so far, as has everyone else who has replied...

[HuggieBear]

[quote]
so just to clarify, without changing the tables, I could do that with what i have got at the moment
[/quote]

No, as you have it now, with your current database structure, you have no way of linking the region to the country.  This means if you have three countries in your country list, and want to display the regions based on one particular country you can't, and this is exactly your problem.

[code=php:0]
SELECT * FROM tabRegion WHERE regionName = '".$row_rsCountry['Id']."'.
[/code]

Your code references a column that doesn't make sense I've put dummy examples in to illustrate...
[color=red]Select all columns from the table tabRegion where the region name (London) is equal to $row_rsCountry['Id'] (5)[/color]

Your regionName column is going to be a logical name (I'm assuming) but you're trying to link it to countryID which is going to be a number.  They're never going to match.

Can you see what I'm getting at here?

If you had a countryID column in tblRegion then you'd almost be there, but the above would need to look like this:

[code=php:0]
SELECT * FROM tabRegion WHERE countryID = '".$row_rsCountry['Id']."'.
[/code]

Regards
Rich

[ltoto]

ok , so i will now add into the tabRegion somewhere to add country name, then do the dropdown menu in the backend, and go from there

[HuggieBear]

Not country name, country ID.  Look at my previous structure....

[color=green][b]tabCountry[/b] - countryID, countryName
[b]tabRegion[/b] - regionID, countryID, regionName
[b]tabHotel[/b] - hotelID, regionID, hotelName, hotelImage, hotelDescription, hotelRating
[/color]

Rich

[ltoto]

quick question, with it being the ID , in the backend when they want to choose i hotel, would it just list the Id number instead,  though I am sure i did it before with the Id number and the name next to it

[HuggieBear]

No, you can set it up so that it shows the hotel name... Here's an example:

[code=php:0]
$sql = "SELECT hotelName FROM tblHotel where regionID = $row_rsRegion['id']";
[/code]

Regards
Rich

[ltoto]

http://212.161.124.21/hotellist.asp

i should of posted this at the start to help you, but this is how I am planning on it coming out when it is finished, something like this

[HuggieBear]

Yes, that's no problem, it's perfectly achievable with what you have.

I'm not sure what you're doing with the back-end, but yes, the front end can do that without issue.

Rich

[ltoto]

i will explain briefly about the backend to help you understand, hope it helps

basically, firstly the customer will be adding the Country Name, which is done.

After this the will go on to a separte section where they can add a region, which at the moment they can only add the region( but the way the tables are now I am guessing they can add the countrys, which will appear in a dynamic dropdown list, after they have sumbitted this, they will the go onto the hotel adding part where they can select the country the hotel is from , from a dynamic dropdown list, and they can do the same when slecting a region.

they will also add a hotel picture, description and hotel rating.

Hope this helps you understnad what i am tryin to do in the backend more

[HuggieBear]

Yes, it's all possible.

[color=blue][b]addhotel.php[/b] - They can add in new hotels, this page will contain the dynamically generated drop down menus for both country and region, along with the additional fields you're after (Image, description etc).  If the hotel's in a new region they can click a text link next to the region drop down to take them to a new page to add a region.

[b]addregion.php[/b] - Here they can add a new region by typing its name in a text box and selecting the country from a dynamically generated drop down.  If the country doesn't exist then they can click the text link next to the country drop down to take them to a new page to add a country.

[b]addcountry.php[/b] - Here they can add a new country in a text box.[/color]

It's important to note that they must assign the hotel to a region and the region to the country.

Regards
Rich

[ltoto]

wahay, that is how i have done the backend, atleast i have done something right now  ;D

[ltoto]

[quote author=HuggieBear link=topic=107882.msg433405#msg433405 date=1158154882]
Not country name, country ID.  Look at my previous structure....

[color=green][b]tabCountry[/b] - countryID, countryName
[b]tabRegion[/b] - regionID, countryID, regionName
[b]tabHotel[/b] - hotelID, regionID, hotelName, hotelImage, hotelDescription, hotelRating
[/color]

Rich
[/quote]

in country where you have put countryId, can that be left as Id or must I rename it as countryId

[HuggieBear]

It can be left as ID, so long as it's a unique field, preferably auto-incrementing.

Regards
Huggie

[ltoto]

and just to check, i then use this code for the drop down list is the regionadd page

[code]<select name="selectCountry" id="selectCountry">
                <?php
do { 
?>
                <option value="<?php echo $row_rsCountry['Id']?>"<?php if (!(strcmp($row_rsCountry['Id'], $row_rsRegionedit['countryId']))) {echo "SELECTED";} ?>><?php echo $row_rsCountry['Id']?></option>
                <?php
} while ($row_rsCountry = mysql_fetch_assoc($rsCountry));
  $rows = mysql_num_rows($rsCountry);
  if($rows > 0) {
      mysql_data_seek($rsCountry, 0);
  $row_rsCountry = mysql_fetch_assoc($rsCountry);
  }
?>
              </select>[/code]

which will be the same on the hotel page

[HuggieBear]

Assuming that [code=php:0]$row_rsRegionedit['countryId'][/code] came from a select on the tabRegion, then yes, it looks good to me.

Regards
Huggie

[ltoto]

right so this is now done, so now how do I change it so intead of it showing the Id of the region in the dropdown menu, it shows the names

[HuggieBear]

What's the select statement you're using to get the code for the dropdown?

Huggie

[ltoto]

it is only like this at the moment

[code]   <select name="selectCountry" id="selectCountry" title="<?php echo $row_rsAccomedit['hotelCountry']; ?>">
    <?php
do { 
?>
    <option value="<?php echo $row_rsRegion['Id']?>"<?php if (!(strcmp($row_rsRegion['Id'], $row_rsAccomedit['regionId']))) {echo "SELECTED";} ?>><?php echo $row_rsRegion['Id']?></option>
    <?php
} while ($row_rsRegion = mysql_fetch_assoc($rsRegion));
  $rows = mysql_num_rows($rsRegion);
  if($rows > 0) {
      mysql_data_seek($rsRegion, 0);
  $row_rsRegion = mysql_fetch_assoc($rsRegion);
  }
?>
             
     
              </select>[/code]

Edited

[HuggieBear]

Sorry, didn't explain myself there...

I mean what's your SQL select statement.. eg. SELECT countryId FROM... etc.

Huggie

[ltoto]

oh i see what you mean i think

[code]mysql_select_db($database_conTotal, $conTotal);
$query_rsRegion = "SELECT * FROM tabRegion";
$rsRegion = mysql_query($query_rsRegion, $conTotal) or die(mysql_error());
$row_rsRegion = mysql_fetch_assoc($rsRegion);
$totalRows_rsRegion = mysql_num_rows($rsRegion);

mysql_select_db($database_conTotal, $conTotal);
$query_rsCountry = "SELECT * FROM tabCountry";
$rsCountry = mysql_query($query_rsCountry, $conTotal) or die(mysql_error());
$row_rsCountry = mysql_fetch_assoc($rsCountry);
$totalRows_rsCountry = mysql_num_rows($rsCountry);
?>[/code]