Compare a selected date against a table column or an array of dates produced from MySQL query

user_10910692 0
Viewed 289

I have a page that looks like this: enter image description here

What I'm trying to do is to compare "Holiday Date:" selection against the row values in "Date" columns from the table below it. I've tried using a code like this:

function dateChk(){
        var dt1 = document.getElementById('PHDate').value;
        var dt2 = document.getElementById('phdateadd').value;
            if (dt1 == dt2) {
               alert("This date already assigned, please try again.");
               document.getElementById('phdateadd').value = 0;
         }
 }

But this only work on the first row. Then I thought of doing array like:

<input id="phdateadd" name="phdateadd" type="date" onchange="myFunction()" required>

<p id="demo"></p>

<script>
var dates= ["2021-01-01", "2021-02-12", "2021-02-13", "2021-04-02", "2021-05-01", 
                        "2021-05-13", "2021-05-14", "2021-05-26", "2021-05-30", "2021-05-31", 
                        "2021-07-06", "2021-07-20", "2021-08-10", "2021-08-31", "2021-09-16", 
                        "2021-10-02", "2021-10-19", "2021-11-04", "2021-12-25"];

function checkDate(date) {
  return date == document.getElementById("phdateadd").value;
}

function myFunction() {
  document.getElementById("demo").innerHTML = dates.find(checkDate);
  var chk = document.getElementById("demo").innerHTML;
  
  if ( chk != "undefined" ) {
        alert("This date already assigned, please try again.");
        document.getElementById("phdateadd").value = 0;}
}
</script>

This does work however I have to specify all the dates from that column as array. I have this idea of passing data from MySQL query with the date values formatted similar to the array but it doesn't seem to recognize. This is the query:

SELECT CONCAT('["',GROUP_CONCAT(phdate SEPARATOR '", "'),'"]') As arrdate FROM table1;

which returns

["2021-01-01", "2021-02-12", "2021-02-13", "2021-04-02", "2021-05-01", "2021-05-13", "2021-05-14",
 "2021-05-26", "2021-05-30", "2021-05-31", "2021-07-06", "2021-07-20", "2021-08-10", "2021-08-31",
 "2021-09-16", "2021-10-02", "2021-10-19", "2021-11-04", "2021-12-25"]

So, I took that result and show it in the page:

<% phDates.forEach((phDates, index) => { %>
        <tr><td><p id="phdates" name="phdates" value="<%= phDates.arrdate %>"><%= phDates.arrdate %>
            <% }) %></p><p id="demo" name="demo">b</p><br><p id="demo1" name="demo1">a</p></td></tr>

<script>
var dates = document.getElementById("phdates").innerHTML;
        document.getElementById("demo").innerHTML = dates;
</script>

enter image description here

As you can see arrdate values that comes from the MySQL query and demo is showing similar format. The demo value is different when I specify the array in var dates (like in my snippet above): enter image description here

If I purposely make the MySQL return data as above though it still doesn't recognize it. I think if this method is workable, I don't even need to compare the selected date to the column in the table. I did a test using $( "div" ).toArray() but I can't get it to work either.

I think this could be the straight forward solution for me but I'm wondering how do I format the MySQL result value to correspond with the correct javascript array format? Or leave the MySQL output as it is and convert that value to javascript array compatible value. I can't help to think that the solution might be simple but I've been looking for a couple of days and I haven't found any.

ejs node.js mysql arrays javascript
2 Answers

Actually there is a straight-forward solution. Let's start our thought process from your query, which is not very far from the solution (this is not good yet, but it's close-enough to start our thought process from it):

SELECT CONCAT('["',GROUP_CONCAT(phdate SEPARATOR '", "'),'"]') As arrdate FROM table1;

You intend here to convert your data into a JSON-like string. Naturally, you could call JSON.parse or eval with value assignment on this result, but I recommend a much more elegant way:

SELECT GROUP_CONCAT(phdate SEPARATOR ',') As dates FROM table1;

The above will contain a string with dates separated by comma. Assuming that you get this into a JS variable called myDates, you can just call:

myDates.split(",")


let myDates = '2021-01-13,2021-02-02,2021-03-04'; //results from db

console.log(myDates.split(','));

user_4365600

Generate either of these in the WHERE clause of SQL:

arrdate IN ("2021-01-01", "2021-02-12", "2021-02-13")

FIND_IN_SET(arrdate, "2021-01-01,2021-02-12,2021-02-13")

The performance of the first may benefit from a suitable INDEX; the second won't.

If you are trying to check whether any of several dates (or other value) is found in any of a second set of dates -- This is not directly available in MySQL. Split one list (or the other list) into rows in a table, then use IN or FIND_IN_SET().

Or use application code.

user_17668310
Related