Array problem (ignore other post)

[zang8027]

I have a database thats set up like this:

 

 

citiesSmall:

 

PK | City name

--------------

1  | Butler

2  | Cranberry

 

 

hotels:

 

PK  |  hotel name

----------------

1  | holiday inn

2  | hampton inn

 

 

hotelsToCity:

 

city | hotel

------------------

1  |  1

1  |  2

 

 

Now, i want to use PHP to grab all the hotels that are in city 1, and display the name of those hotels from the hotel table.  Here is what I have, and is not working:

 

        
<?php
   //Make a connection to the server... connect("servername","username","password")
   $link=mysql_connect("localhost","root") or die("No server connection".mysql_error());
   //connect to our database
   $db=mysql_select_db("dineonline_db") or die("No Database Connection ".mysql_error());
   //construct a SQL query that shows all records from products table
   $query="SELECT * FROM hotelsToCity WHERE city_ID = 1";
   //run the query
   $result=mysql_query($query);
   //for each row returned by our query, do what is in curly braces
   while($row = mysql_fetch_array($result)){
   
   //store into a variable the ID # for the current row 
   $ID=$row['hotel_ID'];
   //store into a variable the Name for the current row
   $name=$row['city_ID'];
   
   $hotelFK = array();
   array_push($hotelFK,$ID);
   
    echo "$hotelFK[0] , hotelFK[1]";  // I get 1, 2
   }
   
   //run a second query that uses the hotel table to retrieve the hotel name that is being asked for from the custom variable
   for($i=0; $i< count($hotelFK); $i++){
   $query = "SELECT * FROM hotels WHERE hotel_ID = $hotelFK[$i]";
   }
   
   $result = mysql_query($query);
   //for each row returned by our query, do what is in curly braces
   while($row = mysql_fetch_array($result)){
   //Set up the rows from the table
   $IDHotel=$row['hotel_ID'];
   $nameHotel = $row['hotel_name'];
   
   //Print out the name from the hotel (from the hotel table)
   print $nameHotel;
   
   }   
   

 

 

How can i do this to achieve this on screen:

 

"Holiday inn and hampton inn"

[premiso]

"and is not working"

 

Very vague. How is it not working?

 

   for($i=0; $i< count($hotelFK); $i++){
       $query = "SELECT * FROM hotels WHERE hotel_ID = $hotelFK[$i]";
   }

 

This makes no sense. You overwrite the query each time so it will only pull the last query.

 

Try replacing it with:

 

   $hotelIds = implode(", ", $hotelFK);
   $query = "SELECT * FROM hotels WHERE hotel_ID IN(" . $hotelIds . ")";

 

Should yield the proper results.

[Mark Baker]

It's always more efficient to keep the number of queries that you execute to a minimum.

Far better to just use:

SELECT H.* 
  FROM hotels H,
       hotelsToCity C
WHERE C.city_ID = 1
   AND H.PK = C.hotel

[zang8027]

ok thanks, ill try it out. That is the problem is that it keeps rewriting over. Ill let ya know

[zang8027]

ok so i tried it out and it works! Thank you! Now the next problem I can't figure out is why my values are not being put into an array when i try to use the array push. Here is the small snippet of code:

 

<?php
...

$query="SELECT * FROM hotelsToCity WHERE city_ID = 1";
//run the query
$result=mysql_query($query);
//for each row returned by our query, do what is in curly braces
while($row = mysql_fetch_array($result)){

//store into a variable the ID # for the current row 
$ID=$row['hotel_ID'];
//store into a variable the Name for the current row
$name=$row['city_ID'];

//Create an empty array to store the hotels
$myArray = array();

	//add values into the array
	for($i=0;$i<3;$i++)
	{
		array_push($myArray,$ID);
	}
}

?>

 

 

as you see, i tried running a for loop to put them in and they don't go in. The whole things works if i add values into the array when i define it. But now it wont add values into the array.. any ideas?

Btw.. the values that are being brought in are 1, 2, and 7. So thats three values I wish to put into the array.