PHP / MYSQL Change button text provided

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 page gives the following page through a simple foreach() :

WHAT I TRY TOODO

When the user has ALREADY applied to the displayed task, I want the blue "Apply" button to change to something like "Applied"

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

Here is a look at my database:

WHAT DID I SAY

I tried changing my db request 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 that it returns only those jobs that were applied by a specific user.

Again, what I'm trying to achieve is to show ALL tasks where the user has already applied the text of the task change button to "Applied" or something like that. Below is an example of what I'm trying to achieve.

Any help or advice is greatly appreciated. Please email me if you need more information or a code.

EDIT:

Maybe I should add the 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

Take a picture.

Using this query, you will get a "applyFor" column with each job. If it is 1, the user has applied for the task. If it is 0, the user did not apply for the task.

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

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

It is pretty simple ...

• Just make a LEFT JOIN in the betting table with jobID and userID . This means that if there is a corresponding row bids.bidID in the betting table, bids.bidID will be numeric, otherwise it will be NULL .
• Then I did COALESCE in this column if the NULL value changed to 0 .
• Insert the brackets in the brackets and make sure that the result is greater than 0 . This gives you a boolean , which may be an alias for your use as a result of the request.

I tested using the following circuit:

 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 corresponding to this will look something like this:

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

Ps I looked at your diagram, and it looked as if the bids table contained all the applications for specific users. If this is not true, my example above will be incorrect.

Answer 2

Try the following:

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

Answer 3

You use an internal join where you get records that are common to both tables

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

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

After that, you can make a condition based on null.

Answer 4

for a specific user with the identifier $ userID.so yuo can get null if he has not applied for this task and check if it is null than the 0 else 1.check condition and change the text according to the condition. I am writing only a request. You will get the data in $ job, than run this code. Hope this helps.

 <?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>'; }