Solved

mySQL table row count not working when joining tables

Posted on 2008-10-09
7
857 Views
Last Modified: 2013-12-12
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?
0
Comment
Question by:cataleptic_state
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22679168
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.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 22685661
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

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22686622
$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

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22687201
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

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 22687682
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.
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 125 total points
ID: 22688020
I see. Then you can use LEFT JOIN:
$query_jobs_locations = "SELECT city_name, count(jobs.job_id) as job_count 

  FROM city

  LEFT JOIN restaurants ON 

    restaurants.rest_city = city.city_name 

  LEFT JOIN jobs ON 

    restaurants.rest_id = jobs.rest_id AND

    job_status = 'ON'

  GROUP BY city_name";

Open in new window

0
 
LVL 4

Author Closing Comment

by:cataleptic_state
ID: 31504669
Hi,
Thank you so much for getting me out of a sticky situation and explaining the solution in a clear and professional manner.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
This article discusses how to create an extensible mechanism for linked drop downs.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now