0

I have a search field with 3 fields (2 field for date range and 1 field for person). I would like if I select the person, then the data based on this person to appear on the screen. Same as I am using the while loop below.

When I select the date range, only the data for this date range to appear on the screen. I am able to do that using the following query.

    $startDate = $_POST['startDate'];
    $endDate = $_POST['endDate'];
    $employer = $_POST['employer'];

    $sql = "
SELECT * 
  FROM jobs 
 WHERE employer_id = '$employer' 
    OR (endDate BETWEEN '$startDate' AND '$endDate')
";
    $res = mysqli_query($db, $sql) or die(mysqli_error($db));

I would also want if I select both the date range and the person field, then I would like the data for both of them to appear on the screen with the while loop shown below.

I want this to be achieved using the query above, but I do not have a solution.

Then, I extract all data in a while cycle:

while($row = mysqli_fetch_assoc($res))
{
   $firstname = $row['firstname'];
   $lastname = $row['lastname'];
   $title = $row['job_title'];
   $date = $row['endDate'];
}
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Kaloyan
  • 41
  • 5
  • Sorry, I do not understand what are you asking exactly. What do you mean by "popup"? A pop-up dialog to show the data? Are you asking how to implement the popup dialog? – Valentino Dec 23 '18 at 18:44
  • 'contracts' it is second table in DB? – Ivan Pirus Dec 23 '18 at 18:47
  • I have updated the question. Hope it is clear now. Forget about the contracts table, because it is confusing. – Kaloyan Dec 23 '18 at 20:50
  • Please explain what does not work with current query (except the dangerous exposure to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection)) as it appears to do what you desire. – Parfait Dec 23 '18 at 20:59
  • Right now, if I fill both criteria (date range and person), only one criteria is being executed. So, either the employer criteria or the date range critetia is executed. I want if I fill both criteria, both of them to be executed. I know I can do that with AND clause, but then, if I do not fill one criteria, the query will give me no results. – Kaloyan Dec 23 '18 at 21:12
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Dec 23 '18 at 21:59
  • Change the ```OR``` to ```AND``` and you'll need to provide three variables to get the query to work: ```employer```, ```startDate``` and ```endDate```. The way it is at the moment it will apply either employer OR between as the predicate. – Dougie Dec 23 '18 at 22:02
  • @Dougie Yes, I did that, but that way, it executes both criteria. If I do not fill one criteria, it gives me no result. – Kaloyan Dec 24 '18 at 10:29
  • You can't have it both ways with that SQL statement. – Dougie Dec 24 '18 at 12:31
  • @Dougie I see. Is it possible to do that if I modify the SQL statement, or I have to do it with 3 if cases and 3 different queries? I wanted to avoid the 3 queries and repetition of code. – Kaloyan Dec 26 '18 at 12:33
  • You need three SQL statements. Do them with prepare and execute. Create an index on employer_id to make your queries run quicker. – Dougie Dec 26 '18 at 14:25
  • @Dougie Yes, but that means that I have to call 3 times the while cycle and that will be a lot of code repetition. Is there a way to avoid that? – Kaloyan Dec 30 '18 at 22:49
  • You can't avoid it if your query needs to run with a missing predicate. – Dougie Dec 31 '18 at 11:12
  • @Dougie Actually, I can and I found a solution myself. I had to do a dynamic query. Anyway, thank you for your help. – Kaloyan Jan 02 '19 at 10:55

0 Answers0