mySQL table row count not working when joining tables

Hi,
Using Dreamweaver Developer Toolbox, I added a Horizontal recordset to get two columns across for the city. Next to this is a number value of how many records are in the particular city.

But I have gone wrong somewhere, as I get the same value for all my 102 cities.

I have two records in the 'jobs' table and it shows '2' in the repeat region for all 102 cities.

Can anyone point me in the right direction?

Thank you.

Attach Code

Query for cities ------
mysql_select_db($database_db, $db);
$query_jobs_locations = "SELECT * FROM city";
$jobs_locations = mysql_query($query_jobs_locations, $db) or die(mysql_error());
$row_jobs_locations = mysql_fetch_assoc($jobs_locations);
$totalRows_jobs_locations = mysql_num_rows($jobs_locations);

Query for counts ------

mysql_select_db($database_db, $db);
$query_job_counts = "SELECT * FROM jobs, restaurants, city WHERE jobs.rest_id = restaurants.rest_id AND restaurants.rest_city = city_name AND job_status = 'ON'";
$job_counts = mysql_query($query_job_counts, $db) or die(mysql_error());

-----

Loop -----
<?php
  do { // horizontal looper version 3
?>
          <td><table border="0" class="smallText">
                <tr>
                  <td><a href="search_by_location.php?=<?php echo $row_jobs_locations['city_name']; ?>" onMouseOver="mouseOver()" onMouseOut="mouseOut()"><?php echo $row_jobs_locations['city_name']; ?></a></td>
                  <td>&nbsp;<?php echo $totalRows_job_counts ?> </td>
                </tr>
            </table></td>
          <?php
    $row_jobs_locations = mysql_fetch_assoc($jobs_locations);
    if (!isset($nested_jobs_locations)) {
      $nested_jobs_locations= 1;
    }
    if (isset($row_jobs_locations) && is_array($row_jobs_locations) && $nested_jobs_locations++ % 2==0) {
      echo "</tr><tr>";
    }
  } while ($row_jobs_locations); //end horizontal looper version 3
?>

----

How can I load the values for the specific city?

Also when I hover over the city name, I want to be able to show the point on a map of the UK.

I have figured out a way of creating the map effect but not as nice as the site above.

Using Javascript I am loading the image on rollover state:

Heres my code:
<script type="text/javascript">
function mouseOver()
{
document.map.src ="images/map7.jpg";
}
function mouseOut()
{
document.map.src ="images/map.jpg";
}
</script>

<td><a href="search_by_location.php" onMouseOver="mouseOver()" onMouseOut="mouseOut()">mouse</a> </td>
<td><div align="right"><img src="images/map.jpg" border="0" usemap="#Map" id="map" name="map"/></div></td>

But the problem I have is how do I add the correct map when the user clicks on the town/city name?

The town/city name will be taken from the database and setup as a link to go to search_by_location.php which will have SQL to show all for specific location(town/city)

Im sure someone once told me you cant add php to javascript :-s is this correct? Start Free Trial