PHP & MySQL: Query from 2 tables

Question

I have the following tables:

JOBS:

+--------+------------+------------------+
| job_id | company_id |  job_title       |
+--------+------------+------------------+
| 123456 | 342186     | MySQL Dev needed |
| 549201 | 175123     | PHP Dev needed   |
| 784930 | 823491     | UI Designer pls  |
+--------+------------+------------------+

COMPANIES:

+------------+--------------+---------------------+
| company_id | company_name |  company_email      |
+------------+--------------+---------------------+
| 342186     | Microsoft    | microsoft@email.com |
| 823491     | Quora        | quora@email.com     |
| 784930     | Facebook     | facebook@email.com  |
+------------+--------------+---------------------+

This is my current Query for getting all jobs from the JOBS table:

// get all jobs from jobs table
$result = mysql_query("SELECT * FROM jobs") or die(mysql_error());

// check for empty result
if (mysql_num_rows($result) > 0) {
    // looping through all results
    // jobs node
    $response["jobs"] = array();

    while ($row = mysql_fetch_array($result)) {
        // temp user array
        $job = array();
        $job["job_id"] = $row["job_id"];
        $job["company_id"] = $row["company_id"];
        $job["company_name"] = $row["company_name"]; //<<<-------THIS
        $job["job_title"] = $row["job_title"];

        // push single product into final response array
        array_push($response["jobs"], $product);
    }
else { //Error }

With the above code I get all jobs, now, How can I modify my query/code so that "company_name" belongs to the respective job? Example:

Since the job "Mysql Dev needed" was posted by company_id = 342186 then it belongs to Microsoft

I was thinking in making another while loop inside the actual while loop so that I can get the exact company info, but I do not believe is the best way.

Use join you can get the answer if you join set foreign and primary key reference set primary for COMPANIES table company_id and foreign key reference is JOBS table company_id if you any doubts means ask me i will clear about this. — Ramki, May 17 '14 at 08:22

score 3 · Answer 1 · edited May 23 '17 at 10:32

3

You simply use a join in your query like this:

select
    co.company_name,
    jo.company_id,
    jo.job_id,
    jo.job_title
from
    jobs jo
        join companies co
            on jo.company_id=co.companyid

Additionally, I wrote a question and answer a while back to help with questions like yours - so that you can get a better understanding of queries.

edited May 23 '17 at 10:32

Community

1
1

answered May 17 '14 at 08:20

Fluffeh

33,228
16
67
80

score 3 · Accepted Answer · answered May 17 '14 at 08:23

3

SELECT * FROM jobs
LEFT JOIN companies ON jobs.company_id = companies.company_id

This will 'join' the two queries together and give you the information in one row, 'joined' by the common identifier of both tables (company_id)

answered May 17 '14 at 08:23

Lee S

343
1
8

I wish I could mark everyones question as correct.. Yours was the simplest, just tested it and worked perfectly, thanks! – user3288852 May 17 '14 at 08:52
As you say, everyone was right in this question :) Glad it worked for you! – Lee S May 17 '14 at 09:02

Markus Kottländer · Answer 3 · 2014-05-17T08:32:47.097

Instead of a join, you can also use a query like this:

SELECT
    c.company_name,
    j.company_id,
    j.job_id,
    j.job_title
FROM
    jobs j,
    companies c
WHERE
    c.id = j.company_id

Two additional notes:

For performance reasons you should probably select only the needed columns in the query. And it is heavily recommended to use PDO or MySQLi instead of the mysql_-functions as they are deprecated as of PHP 5.5.

php.net:

The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the MySQLi or PDO_MySQL extensions.

PHP & MySQL: Query from 2 tables

3 Answers3