[Resolved] Using results from one table to query another table?

PM_Hopper

Hello everyone, once again I'm trying to fix-up a job and don't have the slightest clue how to begin.

In a backend administration, the site checks for jobs in the table raw_jobs and returns the jobname, personel name and company id. The company id is directly associated with the company name in another table called raw_clients.

What my query is doing is returning the number of the company (Company ID) how do I then take that number and access the raw_clients table and get the company name from the number?

Any help would be greatly appreciated.

PastorDoug

try this:

select t2.company_name, t1.jobname, t1.personel_name from raw_jobs as t1, raw_clients as t2 where t1.company_id = t2.company_id;

This works for me in a similar situation.

🙂

PM_Hopper

Thanks a lot Pastor Doug however my php skills are non existant! Please find the existing code below that was already on the page. I just need to change the job_comany from a number to the name from the table raw_clients in the same database.

The job_company number reflects the id field in the raw_clients table and the true company name is in the client_company field from the same database.

Existing Code
<?
$query = "SELECT * FROM raw_jobs ";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
echo "<a href=\"jobdetails.php?job_id=".$row['job_id']."\">".$row['job_name']."</a>".$row['job_company']."".$row['job_addedby']."<br>
";
}
?>

I tired to mess around with what you posted but had no luck at all.

Thanks again for your help.

PastorDoug

OK ...
I'm sure I can help you get what you want done, but I need to know a little more about the tools you have available.

Are you running under windows or *nix?

Can you get access to the database using a mysql client?

The query that php is passing to the mysql daemon needs to be changed so that it includes the data from the two tables in each row of results. The present query selects ALL columns and ALL records from the raw_jobs table.

If you can use a mysql client in interactive mode, then you could play around with the AS feature (or a STRAIGHT JOIN) query until the results are what you want. When the query works, then modify the php code accordingly.

If none of this makes any sense, that's ok, let me know what tools you have and what you know about mysql and we can go from there.

I have not turned on the private email feature, but if that will help I would consider doing it.

DON'T GIVE UP 🙂

PM_Hopper

Thanks Pastor doug, I'm using a Unix server, with a MySQL database. I have php MyAdmin installed so I can browse and edit the data easily. I've edited my code a bit (trial and error) so that I coiuld ask for help easier.

<?
$query = "SELECT * FROM raw_jobs, raw_clients ";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
$job_id = $row['job_id'];
$job_name = $row['job_name'];
$job_company = $row['job_company'];
$job_addedby = $row['job_addedby'];
$id = $row['id'];
$client_company = $row['client_company'];

						echo "<table width=\"396\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\"><tr>
          <td width=\"198\"><font color=\"#FFFFFF\" size=\"1\" face=\"Verdana, Arial, Helvetica, sans-serif\"><a href=\"jobdetails.php?job_id=$job_id\">$job_name</a></font></td>
            <td width=\"114\"><font color=\"#FFFFFF\" size=\"1\" face=\"Verdana, Arial, Helvetica, sans-serif\">$job_company</font></td>
            <td width=\"84\"><font color=\"#FFFFFF\" size=\"1\" face=\"Verdana, Arial, Helvetica, sans-serif\">$job_addedby</font></td>
          </tr>
        </table>";
						}
						?>[/COLOR]

That was the full code on the page. That currently works. I'm having a side problem of the info just repeating itself but that is another issue.

What I need is to take the $id and the $job_company numbers which should match and get the coresponding $client_company

Does this make any sense?

the raw_jobs table has information on the jobs as well as the job_company. The job-company variable corresponds with the variable id in the raw_clients table. Getting the number is simple but I want to take that number and get the $client_company on the same row.

PastorDoug

😃

If that code is working now, then all you need to do is modify the query to the following:

$query = "SELECT * FROM raw_jobs, raw_clients ";
$query .= "WHERE id = job_company";

(The period before the '=' causes the where clause to be appended to the string in $query). This will have the effect of requesting a row of data for every record in the raw_jobs table to have the entire contents of one raw_clients record inserted at the end of the row. If the raw_jobs represent unique entities in each record, and assuming that some clients have more than one job, but (again) each job is somehow unique, then the addition of the WHERE clause will solve ALL of the problems.

What you will be doing is joining two tables based upon the relationship of the id and job_company fields. I doubt that you will have a problem, but it the query fails with something about ambiguous id's then change the WHERE to WHERE raw_jobs.id = raw_clients.job_company.

PM_Hopper

Thank you for your help PastorDoug. Everything is working exactly like I wanted it too!

PastorDoug

You are welcome! I learned something too!