0

I have a HTML table that gets populated with values retrieved from the connected database table 'Attendance' on page load with the session UserID of who's logged in.

html table

To adjust this table, I'm using a collection of filters created with <form> and inputs as such:

filters panel

For Workspace No. it takes all the Workspace number's in the file and lists them as options in the dropdown so they are pre-populated values. The attendance checkboxes correlate to a checkbox with a value of 1 for Present and 0 for Late

So far I have only the filters for ID search and the Workspace No. written out but I'm unsure if this is the way to go as the SQL query will still have the WHERE ... WorkID = ? even if there has been no selection made for Workspace No. rather than being omitted from the query?

$('.apply').click(function () {
      if ($('#idSearch')[0].checkValidity()){ #checks if the form is valid
      $('#idSearch').submit();
    }
    if ($('#chosenWork') != ""){
      $('#WorkIDSearch').submit(); #submits the form to POST the Workspace No.
    }
  });

...

include("config.php");

if (isset($_POST['reqID'])){ #checks if the ID search input field (reqID) has been submitted
  $userid = $_POST['reqID'];
  
} else{                                  
  $userid = $_SESSION['sess_user_id'];
  
}

if (isset($_POST['chosenWork'])){ #checks if the dropdown for Workspace Number (chosenWork) has been selected
  $chosenwork = $_POST['chosenWork'];
  
}

if (isset($_POST['chosenWork']) && isset($_POST['reqID'])){ #checks if both are selected
  $chosenwork = $_POST['chosenWork'];
  $userid = $_POST['reqID'];
  
}


$stmt = $conn->prepare("SELECT Number, ID, Date, Log_Time, WorkID, Att_Type FROM Attendance WHERE ID = ? AND WorkID = ?";
$stmt->bindParam("ii", $userid, $chosenwork);
$stmt->execute();

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

My desired output is that any of these filters can be applied independently or combined to adjust the WHERE clause of the SQL query.

rxm
  • 71
  • 2
  • 11
  • 1
    You could store the filters in a variable like `$filters = [];` and go through the array and build the where clause. If the filters array is empty, don't add Where in the query, else so. But if you want default fields like user_id, then initialize `$filters = ['user_id' => $_SESSION['sess_user_id']]` – Haseeb Ahmad Mar 15 '21 at 00:50

2 Answers2

1

You can try it this way:

$query = "SELECT Number, ID, Date, Log_Time, WorkID, Att_Type FROM Attendance";
$chosenwork = null;

if (isset($_POST['reqID'])){ #checks if the ID search input field (reqID) has been submitted
  $userid = $_POST['reqID'];
  $query .= " WHERE ID = ?";
  
} else{                                  
  $userid = $_SESSION['sess_user_id'];
  $query .= " WHERE ID = ?";
}

if (isset($_POST['chosenWork'])){ #checks if the dropdown for Workspace Number (chosenWork) has been selected
  $chosenwork = $_POST['chosenWork'];
  $query .= " WorkID = ?";
}

if (isset($_POST['chosenWork']) && isset($_POST['reqID'])){ #checks if both are selected
  $chosenwork = $_POST['chosenWork'];
  $userid = $_POST['reqID'];
  $query = "SELECT Number, ID, Date, Log_Time, WorkID, Att_Type FROM Attendance WHERE ID = ? AND WorkID = ?";
}

$query .= ";";
$stmt = $conn->prepare($query);
(isset($userid)) ?? $stmt->bind_param("i", $userid);
(isset($chosenwork)) ?? $stmt->bind_param("i", $chosenwork);
$stmt->execute();

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
rxm
  • 71
  • 2
  • 11
Serghei Leonenco
  • 3,478
  • 2
  • 8
  • 16
  • I'm getting the error `Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 in php:994 Stack trace: #0 php(994): PDOStatement->execute() #1 {main} thrown in php on line 994` which is the line `$stmt->execute();` – rxm Mar 15 '21 at 01:26
  • It missing semicolon at the end of the `sql statement`. I updated – Serghei Leonenco Mar 15 '21 at 01:28
  • I've updated your answer to make the $userid variable consistent in spelling. I don't think the semi-colon was it; I echoed the $query right before it is executed and it doesn't seem as though the session id is being appended where the ? is: `SELECT Number, ID, Date, Log_Time, SubjectID, Att_Type FROM Attendance WHERE ID = ?;` although it is being stored in the $userid variable – rxm Mar 15 '21 at 01:36
  • It should bind this parameter before to execute this statement. – Serghei Leonenco Mar 15 '21 at 01:54
  • Yep, for some reason it's not binding :/ I'm trying to swap out PDO for MySQLi and see if that will work instead – rxm Mar 15 '21 at 01:56
  • 1
    @rxm You can't echo out the query with the parameter bound because that happens later in the database server. When you echo a prepared statement in PHP, you will always see the placeholders in there. – El_Vanja Mar 15 '21 at 10:35
1

It is hard to help debug without more info, but several things I noticed:

  1. Looking at the error, it says the problem is either syntax but it could be the result of an old version. Have you checked your sql version? Prepared statements were introduced in v4.1.

  2. You haven't shown your html or js validation, but since you are telling sql to expect integers, are you absolutely sure your variables are integers? To be sure, add a 0 to $userid and $chosenwork (e.g. $userid = $userid + 0;) before binding them

  3. For security, validating posted form data should be done on the server not client -- it is trivial to circumvent client-side js validation

  4. The null coalescing operators in Serghei Leonardo's answer will only work if you are using php7 or higher. Also, I am not familiar with his usage of it so I may be wrong, but it looks to me it will return $userid and $chosenwork if they are set and only bind if isset($userid) or isset($chosenwork) are null (i.e. if $userid or $chosenwork are null), which would be backwards from what you want. See another explanation here. This might not work, but try changing it to:

    (!isset($userid) ?? $stmt->bind_param("i", $userid)); (!isset($chosenwork) ?? $stmt->bind_param("i", $chosenwork));

jgtokyo
  • 188
  • 5
  • Thank you so much for your suggestions! [1] I'm running MySQL version 8.0.20 [2] I've used the `is_int()` function returning True so there isn't an unexpected data type binding [3] Agreed, I was aiming to get the barebones functionality down before I considered it! [4] I see! Yes, you're right in adding the `!` before the `isset` it does make sense. I've now switched to MySQLi and paired it with the `mysqli_report()` function and now I get `Fatal error: Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement` in the same `stmt->execute()` line. – rxm Mar 19 '21 at 02:56
  • You check if $_POST['reqID'] is set, but don't check if $_SESSION['sess_user_id'] is set before assigning as default value of $userid -- is it set? What happens if it isn't? If not set, why (have you started the session, etc)? – jgtokyo Mar 19 '21 at 05:46
  • When the page loads `$userid` is set to the value of the session, yes. I am able to echo the value before it is binded so I'm not sure why it isn't accepting it as a parameter even with it being present, in the correct format, and data type. – rxm Mar 19 '21 at 22:51
  • At this point, your code is significantly different from your post's, so can only guess. You will need to edit or close this question and start a new question. – jgtokyo Mar 21 '21 at 01:50