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

Use a left join. The rows that don't have a matching record will return null values for it's columns. — Sloan Thrasher
– Sloan Thrasher, Commented May 1, 2017 at 5:17
Why don't u make one more query for fetching all JobIds which user has already applied at the time of showing the list just compare the APPLIED JOB IDs with total JOB IDs list & build that text "Apply or Applied" accordingly? — Suresh
– Suresh, Commented May 1, 2017 at 5:25

Sloan Thrasher · Accepted Answer · 2017-05-01 05:18:53Z

5

Try this:

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

answered May 1, 2017 at 5:18

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

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

4 Comments

Tim C Over a year ago

Thank You. I'll give it a try and let you know

Tim C Over a year ago

Thank You Sloan, The query you provided returns all jobs HOWEVR I suppose I would need an if() condition somewhere, (guessing in my foreach) loop to check whether user has applied or not...

Sloan Thrasher Over a year ago

Of course, unless you're planning on using SQL to generate the html. ;-) You just test one of the columns from the bids table, if it's null, then there's no bid, it it has a value, then a bid row exists. Don't forget to mark the answer as accepted if it works for you.

Tim C Over a year ago

Im sorry if im being stupid here but the current query, only returns jobs which user applied for. I did modify my foreach to check for applied condition but still getting same result...

Tom Wright · Accepted Answer · 2017-05-03 08:34:22Z

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...

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.
I then did a COALESCE on that column meaning if the value is NULL it would be changed to 0.
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.

edited May 3, 2017 at 8:34

answered May 3, 2017 at 8:25

Tom Wright

2,8714 gold badges24 silver badges30 bronze badges

6 Comments

Tom Wright Over a year ago

No problem. Let me know how you get on.

Tim C Over a year ago

Tom thats some seriously good sql skills you've got there mate -- made me realize I still have a looong way to go. Thank You so much for the detailed explanation. Worked like a bomb. Nailed IT!

Tim C Over a year ago

Sorry I can only award bounty after 23-hours will do when time expires...

Tom Wright Over a year ago

My pleasure. I'm glad I could help. You learn something new every day!

Paul Spiegel Over a year ago

(COALESCE(bids.bidId, 0) > 0) Why so complicated? It's the same as bids.bidId IS NOT NULL.

|

Ahmed Ginani · Accepted Answer · 2017-05-01 08:33:54Z

1

+50

You are using inner join where you get records that are common on both table

LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL

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

After this you can make condition based on null.

edited May 1, 2017 at 8:33

answered May 1, 2017 at 5:24

Ahmed Ginani

6,6452 gold badges17 silver badges33 bronze badges

2 Comments

Tim C Over a year ago

Thanks Ahmed I suppose I would need an if() statement somewhere in my foreach() loop to check the condition

Strawberry Over a year ago

This answer is a bit confused

Piyush Kumar · Accepted Answer · 2017-05-04 12:38:37Z

for a particular user whose id is $userID.so yuo can get null if he has not applied for that job and check if it is null than 0 else 1.check condition and change text according to condition. i am writing query only.you will get data in $job than run this code.i hope it may help.

<?php
    $sql="SELECT a.*,b.*,if(b.bidID is null,0,1) as applied from jobs a left join bids b on a.jobID=b.jobID AND b.userID=$userID";


    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']; ?>"><?=$job['applied']=='1'?'Applied':'Apply'?></button>
        <?php echo '</div>
        <hr style="border: dotted thin #eeefff" />';
        echo '</div>';
    }

Collectives™ on Stack Overflow

PHP / MYSQL Change button text on condition

4 Answers 4

4 Comments

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related