PHP / MYSQL Change button text on condition

Question

I have the following simple function

function getAllJobs($userType)
{
    if ($userType == 2) {
        $sql = "SELECT * FROM jobs";
        $stmnt = $db->prepare($sql);
        $stmnt->execute();
        return $stmnt->fetchAll();
    } else {
        return false;
    }
}

The above produces the following page, via a simple foreach() loop:

WHAT I AM TRYING TODO

When a user ALREADY applied for a job displayed, I want the blue "Apply" button to change to something like "Applied"

Now I believe this can / should be achieved by changing the mysql query in my function above.

Here is a look at my database:

WHAT I TRIED

I tried changing my db query in getAllJobs() function as follows:

SELECT jobs.*, bids.*
        FROM bids
        INNER JOIN jobs on jobs.jobID = bids.jobID
        WHERE bids.jobID = jobs.jobID

The problem with the above query is it only returns the jobs which the specific user applied for.

Again what I am trying to achieve is to display ALL jobs where the user Already applied for a job change button text to "Applied" or something similar. The below image serves as an example of what I am trying to achieve.

Any help or advice much appreciated. Kindly drop me a comment should you need more information or code.

EDIT:

I should probably add my foreach code here:

  <?php
    foreach ($jobs as $job){
    $description = $job['description'];
    $jobDescription = substrwords($description, 30);
    echo '<div>' . $job['headline'] . '</div>';
    echo '<div>' . $jobDescription . '</div>';
    echo '<div>' . $job['datePosted'] . '</div>';
    echo '<div>' . $job['amount'] . '</div>';
    echo '<div>' . $job['location'] . '</div>';?>
    <?php
    echo '<div class="jobPosting">';
    ?>
    <input type="text" value="apply" name="action" style="display: none" />

    <button name="placeBid" type="submit" class="btn btn-primary" value="<?php echo $job['jobID']; ?>">Apply</button>
    <?php echo '</div>
                <hr style="border: dotted thin #eeefff" />';
    echo '</div>';
    }//foreach

Answer 1

Try this:

SELECT jobs.*, bids.*
FROM jobs
LEFT JOIN bids 
    ON jobs.jobID = bids.jobID

Answer 2

Give this a shot.

Using this query you will get an "appliedFor" column with each job. If this is 1, the user has applied for the job. If it is 0 then the user has not applied for the job.

SELECT
  jobs.*,
  (COALESCE(bids.bidId, 0) > 0) AS appliedFor
FROM jobs
LEFT JOIN bids ON bids.jobID = jobs.jobID AND bids.userID = 1;

Before the explanation, see the test here: http://sqlfiddle.com/#!9/eb5a4f/1/0

It's quite simple really...

1. Just do a LEFT JOIN on the bids table using the jobID and the userID. This means that if there is a corresponding row in the bids table bids.bidID will be numeric, otherwise it will be NULL.
2. I then did a COALESCE on that column meaning if the value is NULL it would be changed to 0.
3. Wrap the above in brackets and ensure that the result is more than 0. This gives you a boolean which can be aliased for your use in the query result.

I tested using the following schema:

CREATE TABLE jobs (
  jobID int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (jobID)
);

CREATE TABLE bids (
  bidID int NOT NULL AUTO_INCREMENT,
  userID int,
  jobID int,
  PRIMARY KEY (bidID)
);

INSERT INTO jobs () VALUES();
INSERT INTO jobs () VALUES();
INSERT INTO jobs () VALUES();
INSERT INTO bids (userID, jobID) VALUES(1, 1);
INSERT INTO bids (userID, jobID) VALUES(2, 1);
INSERT INTO bids (userID, jobID) VALUES(1, 3);

The PHP code to match up with this will look something like the following:

<?php
foreach ($jobs as $job){
    $description = $job['description'];
    $jobDescription = substrwords($description, 30);
    $buttonText = "Apply";
    if ($job['appliedFor']) { // This is where we're using the new value we've selected
        $buttonText = "Applied";
    }
    ?>
    <div><?php echo $job['headline']; ?></div>
    <div><?php echo $jobDescription; ?></div>
    <div><?php echo $job['datePosted']; ?></div>
    <div><?php echo $job['amount']; ?></div>
    <div><?php echo $job['location']; ?></div>
    <div class="jobPosting">
        <input type="text" value="apply" name="action" style="display: none" />
        <button name="placeBid" type="submit" class="btn btn-primary" value="<?php echo $job['jobID']; ?>"><?php echo $buttonText; ?></button>
    </div>
    <hr style="border: dotted thin #eeefff" />
    <?php
}//foreach

P.s. I looked at your schema and it looked to me as if the bids table contained all of the job applications for specific users. If this isn't correct my example above will be incorrect.