Query within a query whilst splitting results across two columns

[Khmer42]

Hi guys,

I've been working on this script and just can't see why it doesn't work, am I missing something?

I want the script to search a database by city, the script will then place all hotels whose city is the same as the city of the variable and list them in two columns under the name of the city.

This is how the results should look e.g.:

Phnom Penh

Phnom Penh hotel 1 Phnom Penh Hotel 2

Siem Reap

Siem Reap hotel 1 Siem Reap Hotel 2

Sihanouk Ville

Sihanouk Ville Hotel 1 Sihanouk Ville Hotel 2

This is how it does look:

Phnom Penh

Phnom Penh Hotel 1 Siem Reap Hotel 1

Siem Reap

Phnom Penh Hotel 1 Siem Reap Hotel 1

Sihanouk Ville

Phnom Penh Hotel 1 Siem Reap Hotel 1

Doesn't sound so complicated right? Here's the full script:

<?php $query_locations = "SELECT city, COUNT(*) AS cat_num
			FROM cambodia
			GROUP BY city";
$results_locations = mysql_query($query_locations) or die(mysql_error());
while ($row_locations = mysql_fetch_array($results_locations))
 {
     extract ($row_locations);



          echo "<table  width='625' border='0' cellpadding='0' cellspacing='0'>";
            echo "<tr class='contentmiddle'>";
        //The variable $city works here
              echo "<td class='leftHeading'>$city</td>";

            echo "</tr></table>";


$columns = 2;


$query = "SELECT name, price, stars FROM cambodia WHERE city='$city' AND level=1";
$result = mysql_query($query);

$num_rows = mysql_num_rows($result);


$rows = ceil($num_rows / $columns);

while($row = mysql_fetch_array($result)) {
    $data[] = $row['name'];

//store the other field into an array
$data2[] = $row['stars'];

//store the other field into an array
$data3[] = $row['price'];
}

echo "<TABLE class='middlecontent'  cellpadding='0' cellspacing='0' width='620' BORDER=\"0\">\n";

for($i = 0; $i < $rows; $i++) {

echo "<TR>\n";

for($j = 0; $j < $columns; $j++) {
    if(isset($data[$i + ($j * $rows)])) {

		echo "<td width='12'><img src='includes/img/blank.gif' width='12' height='20'></td>";
//The $city variable works here inside the link showing the relevent city name, but the hotel name is from a different city to the variable

		echo "<TD width='175' class='contentleft'><a href='hotel_details.php?name=" . $data[$i + ($j * $rows)] . "&city=$city' title='$name'>" . $data[$i + ($j * $rows)] . "</a></TD>\n";

        echo "<TD width='99' class='contentleft'><img src='/images/php/" . $data2[$i + ($j * $rows)] . ".gif'></TD>\n";

        echo "<TD width='37'><strong>$" . $data3[$i + ($j * $rows)] . "</strong></TD>\n";

		echo "<td width='12'><img src='includes/img/blank.gif' width='12' height='20'></td>";

		echo "<Td width='0' bgcolor='#FFFFFF'><img src='includes/img/blank.gif' width='5' height='20'></td>";
    }
}
echo "</TR>\n";
}
echo "</TABLE>\n";

		  }?>	

Can anybody see where I'm going wrong, because I can't.