Having trouble calling data in other tables

[jezza52]

Hello Folks!

 

This is the first time ive posted for help with PHP/MySQL, I usually always find the answer to what im looking for on various forums but I think this problem is a bit to specific to solve third hand...

 

I'll try and explain the best I can...

 

I run a job board which uses RSS feeds to distribute jobs, we are migrating to a new platform so ive got to redo the rss feeds.

 

One of the feeds needs the job category, which is not located in the table where the job, salary, url, description and so forth. To find which category the job is relevant to there is a jobs_industries table but this also only contains a further reference;

 

 Full texts     job_id     company_id     industry_id
    Edit Edit     Copy Copy     Delete Delete     84578     10772     114
    Edit Edit     Copy Copy     Delete Delete     84580     10772     114
    Edit Edit     Copy Copy     Delete Delete     84740     10810     20

 

Then finally we have the table that has the job categories under 'Industries' and the data is as follows;

 

 Full texts     id     industry     industry_description     industry_url     parent     show_type     show_order
    Edit Edit     Copy Copy     Delete Delete     1     Accounting / Audit / Taxation     NULL    accounting-audit-taxation     1     parent     1
    Edit Edit     Copy Copy     Delete Delete     2     Accountant     NULL    accountant     1     child     1
    Edit Edit     Copy Copy     Delete Delete     3     Accounting Clerk / Supervisor     NULL    accounting-clerk-supervisor     1     child     1

 

So heres the bit I cant work out, Ideally I want to populate another colunm in the jobs database so the relevant job category is displayed (Also the pupose of this is because the RSS feed generator only works within one table.

 

I currently use this php script to populate the job_location column I added within jobs;

 

<?php
    
    $con=mysqli_connect("localhost","****","************,"*******");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
 
mysqli_query($con,"UPDATE jobs
INNER JOIN jobs_locations
    ON (jobs.id = jobs_locations.job_id)
   SET jobs.job_location = jobs_locations.display_name");

mysqli_close($con);

?>

 

Could anyone help me create a similar script for a new column in the jobs table whereby the job categories are inserted, the above code ensures the unique ids are matched so the new column data is inserted correctly, the thing is I can work out how to reference the job category through 2 tables..

 

Thanks in advance.

[DavidAM]

You would just write another UPDATE statement with an additional JOIN to the table containing the data you need.

 

But Do NOT do that and undo the location fix

 

You should not de-normalize your data for your output routines. You should use a SELECT in the feed generator with the JOINs you need to get your data. If you can not modify the generator and it will only accept a table name, then create a VIEW in the database and use the view name as the table name for the generator. The generator should not be able to tell the difference.

 

When you de-normalize your data like this, you run the risk of data getting out of sync. What if a user changes the location of a job -- maybe they fill the job, and then have a new one with the same requirements but a different location; or they spelled it wrong and call you and ask you to fix it. Now your data is out of sync. Maybe your front-end does not allow the user to change it, but what if you change the front-end later or someone changes it in the database without knowing that they have to change it in two places?

 

Unless you have triggers on the base table to update the de-normalized tables when the data changes, your data will get out of sync and you will be searching for a bug in the application when the problem is with the data. Use a VIEW and save yourself trouble down the road.

 

So, instead of the UPDATE statement you have, create a view:

CREATE VIEW Jobs_Location_View AS
SELECT *, jobs_locations.display_name as job_location
FROM jobs JOIN jobs_locations
ON (jobs.id = jobs_locations.job_id);