CODEWITHSUNDEEP
phpmysqljson
user3288852
user3288852

Reputation: 205

PHP & MySQL: Query from 2 tables

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.

Upvotes: 0

Views: 62

Answers (3)

Markus Kottl&#228;nder
Markus Kottl&#228;nder

Reputation: 8268

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.

Upvotes: 1

Lee S
Lee S

Reputation: 343

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)

Upvotes: 3

Fluffeh
Fluffeh

Reputation: 33512

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.

Upvotes: 3

Related Questions