Link to home
Start Free TrialLog in
Avatar of cataleptic_state
cataleptic_stateFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Avatar of Roger Baklund
Roger Baklund
Flag of Norway image

Your $query_job_counts query does not specify what city you are counting. Also, you don't need to join the city table:

$query_job_counts = "SELECT * FROM jobs, restaurants WHERE jobs.rest_id = restaurants.rest_id AND restaurants.rest_city = {$row_jobs_locations[city_name]} AND job_status = 'ON'";

You also ask how to add the correct map when the user clicks on the town/city name. You can fetch it from the query string, change this line:

<a href="search_by_location.php?=<?php echo $row_jobs_locations['city_name']; ?>"

...into this:

<a href="search_by_location.php?city=<?php echo $row_jobs_locations['city_name']; ?>"

... then $_GET['city'] will contain the name of the city when search_by_location.php executes.

Finally you ask if you cant add php to javascript. Not sure what you mean by this... PHP is run server side, and javascript is run in the client, so you can not execute PHP code from javascript, but you can of course output javascript code from your PHP script.
Avatar of cataleptic_state

ASKER

Ok I tried your SQL statement, but nothing comes up on the page I have attached the code I have.

I know how to get the map I have used OnMouseOver on the hyperlink and Javascript to load the map on same page.

When I was asking about adding PHP to Javascript. I was having a problem with

<script type="text/javascript">
function Aberdeen()
{
document.map.src ="images/Aberdeen.jpg";
}

I wanted function Aberdeen() to load dynamically

e.g function <?php echo $cityname[cityname'];?>()

But I have found a way around this problem.

The only thing I cannot work out is why my row count is not coming up for specific city.

I bet its staring me in the face :'(

And the reason why I have $query_jobs_locations and $query_job_counts is that
$query_jobs_locations lists the cities in order and is static.
$query_job_counts should load the number of jobs in the specific city.

Hope this helps


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);
 
mysql_select_db($database_db, $db);
$query_job_counts = "SELECT * FROM jobs, restaurants, city WHERE restaurants.rest_id = jobs.rest_id AND restaurants.rest_city = city.city_name AND job_status = 'ON'";
$job_counts = mysql_query($query_job_counts, $db) or die(mysql_error());
$row_job_counts = mysql_fetch_assoc($job_counts);
$totalRows_job_counts = mysql_num_rows($job_counts);
 
?>
<table width="100%" border="0">
  <tr>
    <td valign="top"><table border="0">
      <tr>
        <?php
  do { // horizontal looper version 3
?>
<script type="text/javascript">
function Aberdeen()
{
document.map.src ="images/Aberdeen.jpg";
}
function AyreKilmarnockIrvine()
{
document.map.src ="images/AyreKilmarnockIrvine.jpg";
}
function BarnsleyDoncasterRotherham()
{
document.map.src ="images/BarnsleyDoncasterRotherham.jpg";
}
function BasingstokeFleet()
{
document.map.src ="images/BasingstokeFleet.jpg";
}
function Bath()
{
document.map.src ="images/Bath.jpg";
}
function BedfordMiltonKeynes()
{
document.map.src ="images/BedfordMiltonKeynes.jpg";
}
function BirminghamCentral()
{
document.map.src ="images/BirminghamCentral.jpg";
}
function Blackburn()
{
document.map.src ="images/Blackburn.jpg";
}
function BoltonWigan()
{
document.map.src ="images/BoltonWigan.jpg";
}
function Bournmouth()
{
document.map.src ="images/Bournmouth.jpg";
}
function BradfordHalifaxSkipton()
{
document.map.src ="images/BradfordHalifaxSkipton.jpg";
}
function Brighton()
{
document.map.src ="images/Brighton.jpg";
}
function Bristol()
{
document.map.src ="images/Bristol.jpg";
}
function BromleyBexley()
{
document.map.src ="images/BromleyBexley.jpg";
}
function Cambridge()
{
document.map.src ="images/Cambridge.jpg";
}
function Canterbury()
{
document.map.src ="images/Canterbury.jpg";
}
function CardiffWestValleys()
{
document.map.src ="images/CardiffWestValleys.jpg";
}
function CarlisleNorthCumbria()
{
document.map.src ="images/CarlisleNorthCumbria.jpg";
}
function Chelmsford()
{
document.map.src ="images/Chelmsford.jpg";
}
function ChesterNorthWales()
{
document.map.src ="images/ChesterNorthWales.jpg";
}
function ChesterfieldMansfield()
{
document.map.src ="images/ChesterfieldMansfield.jpg";
}
function Colchester()
{
document.map.src ="images/Colchester.jpg";
}
function Aberdeen()
{
document.map.src ="images/Aberdeen.jpg";
}
function Aberdeen()
{
document.map.src ="images/Aberdeen.jpg";
}
function mouseOut()
{
document.map.src ="images/map_big1.jpg";
}
</script>
          <td><table border="0" class="smallText">
                <tr>
                  <td><a href="search_by_location.php?=<?php echo $row_jobs_locations['city_name']; ?>" onMouseOver="<?php echo trim(preg_replace('#[^\p{L}\p{N}]+#u', '', $row_jobs_locations['city_name'])); ?>()" 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
?>
 
---- Restaurant table ---
 CREATE TABLE `joyrul`.`restaurants` (
`rest_id` INT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`rest_name` VARCHAR( 25 ) NOT NULL ,
`rest_address` TEXT NOT NULL ,
`rest_city` VARCHAR( 25 ) NOT NULL ,
`rest_postcode` VARCHAR( 8 ) NOT NULL ,
`rest_teleno` VARCHAR( 12 ) NOT NULL ,
`rest_email` VARCHAR( 80 ) NOT NULL ,
`rest_website` VARCHAR( 80 ) NULL ,
`rest_opentimes` VARCHAR( 255 ) NOT NULL ,
`rest_description` LONGTEXT NOT NULL ,
`rest_photo` VARCHAR( 255 ) NOT NULL ,
`rest_map` LONGTEXT NOT NULL ,
`rest_contact` VARCHAR( 50 ) NOT NULL ,
`rest_status` VARCHAR( 10 ) NOT NULL ,
FULLTEXT (
`rest_name` ,
`rest_address` ,
`rest_city` ,
`rest_postcode` ,
`rest_teleno` ,
`rest_email` ,
`rest_website` ,
`rest_opentimes` ,
`rest_description` ,
`rest_photo` ,
`rest_map` ,
`rest_contact` ,
`rest_status`
)
) ENGINE = MYISAM 
 
--- Jobs Table ---
 CREATE TABLE `joyrul`.`jobs` (
`job_id` INT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`job_title` VARCHAR( 50 ) NOT NULL ,
`job_category` VARCHAR( 20 ) NOT NULL ,
`job_description` LONGTEXT NOT NULL ,
`job_start_date` VARCHAR( 8 ) NOT NULL ,
`job_status` VARCHAR( 10 ) NOT NULL ,
`rest_id` INT( 6 ) NOT NULL ,
INDEX ( `rest_id` ) ,
FULLTEXT (
`job_title` ,
`job_category` ,
`job_description` ,
`job_start_date` ,
`job_status`
)
) ENGINE = MYISAM 
 
 
 
ALTER TABLE `jobs` ADD `job_wages` VARCHAR( 10 ) NOT NULL AFTER `job_start_date` ,
ADD `job_accom` VARCHAR( 8 ) NOT NULL AFTER `job_wages` ,
ADD `job_travel` VARCHAR( 20 ) NOT NULL AFTER `job_accom` ,
ADD `job_drvlicense` VARCHAR( 10 ) NOT NULL AFTER `job_travel` ,
ADD `job_holpay` VARCHAR( 25 ) NOT NULL AFTER `job_drvlicense` ;
 
 
---- City Table ----
 CREATE TABLE `joyrul`.`city` (
`city_id` INT( 6 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`city_code` VARCHAR( 4 ) NOT NULL ,
`city_name` VARCHAR( 80 ) NOT NULL
) ENGINE = MYISAM 

Open in new window

$totalRows_job_counts is calculated once at the start of the script, but you expect it to "magically" change for each iteration of your loop? That's not how it works. You would have to calculate this value WITHIN the loop, to get a new $totalRows_job_counts for each city.

However, it would be more efficient to calculate it in the first query, i.e. combining both your queries. (See code below).

Then remove your second query, and replace <?php echo $totalRows_job_counts ?> with  <?php echo $row_jobs_locations['job_count'] ?>

PS: function Aberdeen() is defined three times, and all the city-functions are within your loop, re-defining the functions for each iteration of the loop. Move these functions out of the loop. (I would have done this different, without the regexp and city-functions, but if it's working for you it's no need to change it.)
$query_jobs_locations = "SELECT city_name, count(jobs.job_id) as job_count 
  FROM city, jobs, restaurants 
  WHERE 
    restaurants.rest_id = jobs.rest_id AND 
    restaurants.rest_city = city.city_name AND 
    job_status = 'ON'";

Open in new window

Sorry ,I forgot a group clause in the query... it should be like this:
$query_jobs_locations = "SELECT city_name, count(jobs.job_id) as job_count 
  FROM city, jobs, restaurants 
  WHERE 
    restaurants.rest_id = jobs.rest_id AND 
    restaurants.rest_city = city.city_name AND 
    job_status = 'ON'
  GROUP BY city_name";

Open in new window

Hi,
Thats great, thank you for the explanation. I have not touched this subject in a long time.

I would however like to award you points but I still have one problem, I need all the cities listed.
Your code only lists the cities that have jobs in.
ASKER CERTIFIED SOLUTION
Avatar of Roger Baklund
Roger Baklund
Flag of Norway image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
Thank you so much for getting me out of a sticky situation and explaining the solution in a clear and professional manner.