Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

mySQL table row count not working when joining tables

Posted on 2008-10-09
7
861 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article discusses how to create an extensible mechanism for linked drop downs.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

789 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