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    | [email protected] |
| 823491     | Quora        | [email protected]     |
| 784930     | Facebook     | [email protected]  |
+------------+--------------+---------------------+

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
– Ramki, Commented May 17, 2014 at 8:22

Lee S · Accepted Answer · 2014-05-17 08:23:01Z

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, 2014 at 8:23

Lee S

3431 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3288852 Over a year ago

I wish I could mark everyones question as correct.. Yours was the simplest, just tested it and worked perfectly, thanks!

Lee S Over a year ago

As you say, everyone was right in this question :) Glad it worked for you!

Community · Accepted Answer · 2017-05-23 10:32:23Z

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, 2017 at 10:32

CommunityBot

11 silver badge

answered May 17, 2014 at 8:20

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Comments

Markus Kottländer · Accepted Answer · 2014-05-17 08:32:47Z

1

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.

edited May 17, 2014 at 8:32

answered May 17, 2014 at 8:25

Markus Kottländer

8,2844 gold badges42 silver badges61 bronze badges

Collectives™ on Stack Overflow

PHP & MySQL: Query from 2 tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related