How to filter & sort with php?

smokey01 · Post by **smokey01** » Mon Apr 10, 2023 9:13 am

I'm looking for some help to solve a couple of problems I'm having with some enhancements.
The attached file contains six files. Only two are important. file:Contacts.sqlite is a sqlite database. File:contact.php is the php file that interfaces with the database. In it's current form it reads data from the database and displays it how I want it on a web page.
The two enhancements. I would like to be able to dynamically filter each record by each field name. Empty filters shows everything. For example: typing Mouse into the Lastname filter would show both Mickey and Minnie Mouse only.
I would also like to be able to sort the records by field name by clicking on the field label. EG: One click A-Z, second click, Z-A. Multiple field sort is not really required.

If someone could squeeze these enhancements into my code I would be very grateful.

Thanks
smokey01

Post by **rockedge** » Mon Apr 10, 2023 10:41 am

@smokey01 Morning!!

I need the PHP Class 'SQLite3' so I can run the code fully on my web server.

So clicking on the field name will sort the column in ascending/descending order? And like the mention functions on this forum, a "live" filter on the top of each column?

smokey01 · Post by **smokey01** » Mon Apr 10, 2023 11:25 am

rockedge wrote: ↑Mon Apr 10, 2023 10:41 am
@smokey01 Morning!!

I need the PHP Class 'SQLite3' so I can run the code fully on my web server.

So clicking on the field name will sort the column in ascending/descending order? And like the mention functions on this forum, a "live" filter on the top of each column?

Yes that's what I'm after.

Clarity · Post by **Clarity** » Tue Apr 11, 2023 9:24 pm

And, I would assume you'd want an arror pointing down (assending) or up (reversed) along side the 'Last Name' in the column header to give the clickable sort direction for the column.

Post by **rockedge** » Tue Apr 11, 2023 10:06 pm

@smokey01 Here is the version contact2.php

Code: Select all

<!DOCTYPE html>
<head>
	
<style>	
body {
  background-color: teal;
}

th {
    cursor: pointer;
    background-color:lightgreen;
}
</style>

</head>

<body>	
 <table id="main-table" border = "3">
     <col span="5" style="background-color:cyan">
<tr>
    <th onclick="comparer(0)">ID</th>
    <th onclick="comparer(1)">First Name</th>
    <th onclick="comparer(2)">Last Name</th>
    <th onclick="comparer(3)">Mobile</th>
    <th onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6


   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('Contacts.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();

?>
</table>

<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));
</script>
</body>
</html>

Right now it sorts each column by clicking on the column's header cell. Filter will come soon hopefully

: Screenshot(1).gif (283.99 KiB) Viewed 3767 times

smokey01 · Post by **smokey01** » Wed Apr 12, 2023 1:28 am

Clarity wrote: ↑Tue Apr 11, 2023 9:24 pm
And, I would assume you'd want an arror pointing down (assending) or up (reversed) along side the 'Last Name' in the column header to give the clickable sort direction for the column.

That would be nice but probably not necessary.
Thanks

Post by **rockedge** » Wed Apr 12, 2023 2:40 pm

Has anyone tried out the code for the web page I provided? Or are waiting for the finished product?

#1 need a bigger data set to work on the filter functions

#2 check if the filter function makes any sense to include for this use case before investing the time and energy on something no one will use.

norgo · Post by **norgo** » Wed Apr 12, 2023 4:42 pm

@rockedge
tested with approx 3000 entries
worked without problems

smokey01 · Post by **smokey01** » Wed Apr 12, 2023 9:40 pm

@rockedge I just tested a database with 1597 records and it worked perfectly as far as I could see. Nice work. Sorry I've been a bit busy the last few days to test.
With so many records it would be nice to be able to freeze the top two rows, "filter and Headings" if that's possible.
Looking forward to the filter option.

Thanks again
smokey01 (Grant)

smokey01 · Post by **smokey01** » Wed Apr 12, 2023 10:10 pm

rockedge wrote: ↑Wed Apr 12, 2023 2:40 pm
Has anyone tried out the code for the web page I provided? Or are waiting for the finished product?

I have now. It looks great.

rockedge wrote: ↑Wed Apr 12, 2023 2:40 pm
#1 need a bigger data set to work on the filter functions

I just tested with a good size data set, 1597 records, but I can't give it to you as it has personal details in it. I will either find or create one for you.

rockedge wrote: ↑Wed Apr 12, 2023 2:40 pm
#2 check if the filter function makes any sense to include for this use case before investing the time and energy on something no one will use.

I will definitely use your code. I've been playing with this on and off for over two years.
Thanks

Post by **rockedge** » Thu Apr 13, 2023 2:35 am

The question is how will the filters interact with each other. It will be possible to have a text entry in more than one dynamic input field at the same time. Might make for some interesting filtering or it might work with unexpected results.

experimenting with the page style as well as technical functions.

: Screenshot(12).jpg (53.49 KiB) Viewed 3393 times

smokey01 · Post by **smokey01** » Thu Apr 13, 2023 4:12 am

Filtering multiple fields will be interesting. It seems to work well in sqlite browser I just don't know how the do it.
I'm also trying to figure out how to make the first two rows frozen. It's handy to have the filter and header row visible when scrolling through a large data set.
You seem to be keeping late hours.
Thanks for your effort.
Cheers

Post by **rockedge** » Fri Apr 14, 2023 1:06 pm

@smokey01 This is the progress so far, Not perfect but seems to at least filter. Still trying out different approaches so I'll make the test web page available to test out and look over.

: Screenshot(13).jpg (73.63 KiB) Viewed 3332 times

smokey01 · Post by **smokey01** » Sat Apr 15, 2023 5:10 am

It does do some filtering but not quite the desired outcome, but it's a good start.

Thanks

Post by **rockedge** » Sat Apr 15, 2023 3:57 pm

@smokey01 This one has improved search but not yet the table format design.

Step closer though with this code which highlights filter matches.

contact6.php

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">	

<style type="text/css">
body {
  background-color: lightgrey;
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
}

th.go {
	background-color: white;
	width:auto;
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040 
}
.box1 {
	background-color:#f8f8f8; 
	max-width:25%; 
	margin:10px auto 50px; 
	padding:20px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040 
}
</style>

</head>

<body>	
<div class="box1">
  <tr>
    <th><input size=33px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr>   
</div>

<div class="box">

 <table id="main-table" border = "3">
     <col span="5" style="background-color:cyan">
<tr>
    <th onclick="comparer(0)">ID<i class="caret"></i></th>
    <th onclick="comparer(1)">First Name</th>
    <th onclick="comparer(2)">Last Name</th>
    <th onclick="comparer(3)">Mobile</th>
    <th onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6


   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('sample.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();

?>
</table>

</div>
</div>
<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));




var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);


function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    name.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    quantity.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    price.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    expiry.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    expiry.innerHTML = data.Email;    

  }

  filterTable();
}


function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }

    if (flag) {
      row.style.display = "";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}


populateTable();

let tableColumns = document.getElementsByClassName('main-table');

for (let column of tableColumns) {
  column.addEventListener('click', function(event) {
    toggleArrow(event);
  });
}

input.addEventListener('keyup', function(event) {
  filterTable();
});

</script>
</body>
</html>

Post by **rockedge** » Sun Apr 16, 2023 3:49 pm

I have worked a lot with phpMyAdmin to create and manage MySQL databases and along the way have run into phpLiteAdmin a tool that facilitates management of SQLite databases but until now have not needed to use it.

Well now I do and I have been using phpLiteAdmin to experiment on a SQLite database while working on this little project and I will say it is very handy and similar to phpmyadmin in it's controls and makes working with the SQLite db's comfortable and efficient.

Thought I'd mention it for those interested more in Database technology

smokey01 · Post by **smokey01** » Sun Apr 16, 2023 10:14 pm

Code: Select all

rockedge wrote: ↑Sun Apr 16, 2023 3:49 pm
I have worked a lot with phpMyAdmin to create and manage MySQL databases and along the way have run into phpLiteAdmin a tool that facilitates management of SQLite databases but until now have not needed to use it.

I agree phpLiteAdmin is great. norgo sent me a copy a little while ago. DB Browser for sqlite is also a very nice application but it can only be used on local files where phpLiteAdmin will work on remote files. This is the main reason I haven't bothered to include Editing, Creating and Deleting records to the php script. I'm trying to keep the operation of the script as simple as possible.

rockedge wrote: ↑Sun Apr 16, 2023 3:49 pm
Well now I do and I have been using phpLiteAdmin to experiment on a SQLite database while working on this little project and I will say it is very handy and similar to phpmyadmin in it's controls and makes working with the SQLite db's comfortable and efficient.

Another little trick I learned while working on this project is this script:

Code: Select all

php phpfile.php > phpout.html

You will need to have php installed locally but it makes a working local html file that you can test and play with. I have found this very useful.

rockedge wrote: ↑Sun Apr 16, 2023 3:49 pm
Thought I'd mention it for those interested more in Database technology

smokey01 · Post by **smokey01** » Sun Apr 16, 2023 11:09 pm

@rockedge I like the change of colour when the results match. Because of this I made a more realistic database for better discrimination.

smokey01 · Post by **smokey01** » Mon Apr 17, 2023 6:35 am

I've managed to fix the header. It's not perfect but it sort of works.

I used an external css file but I suppose it could be added to the internal code you have provided.

contact6G.php

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">	


<link rel="stylesheet" href="fh.css">	<!--Grant Fixed Header-->


<style type="text/css">
body {
  background-color: lightgrey;
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
}

th.go {
	background-color: white;
	width:auto;
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040 
}
.box1 {
	background-color:#f8f8f8; 
	max-width:25%; 
	margin:10px auto 50px; 
	padding:20px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040 
}
</style>
</head>

<body>	
<div class="box1">
  <tr>
    <th><input size=33px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr> 
</div>

<div class="box">

 <table id="main-table" border = "3">
     <col span="5" style="background-color:cyan">
 
   <thead> <!--Grant added thead-->
    
<tr>
    <th onclick="comparer(0)">ID<i class="caret"></i></th>
    <th onclick="comparer(1)">First Name</th>
    <th onclick="comparer(2)">Last Name</th>
    <th onclick="comparer(3)">Mobile</th>
    <th onclick="comparer(4)">Email</th>
</tr>
</thead> <!--Grant added thead-->

<?php
$ID = 6; //set variable ID to 6


   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('sample2.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();

?>
</table>

</div>
</div>
<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));




var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);


function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    name.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    quantity.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    price.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    expiry.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    expiry.innerHTML = data.Email;    

  }

  filterTable();
}


function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }

    if (flag) {
      row.style.display = "";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}


populateTable();

let tableColumns = document.getElementsByClassName('main-table');

for (let column of tableColumns) {
  column.addEventListener('click', function(event) {
    toggleArrow(event);
  });
}

input.addEventListener('keyup', function(event) {
  filterTable();
});

</script>
</body>
</html>

My css file

Code: Select all

body {margin: 0;
  padding: 2rem;
}

table {
  text-align: left;
  position: relative;
  border-collapse: collapse; 
}
th, td {
  padding: 0.25rem;
}
tr.red th {
  background: red;
  color: white;
}
th {
  background: white;
  position: sticky;
  top: 0; /* Don't forget this, required for the stickiness */
  box-shadow: 0 2px 2px -1px rgba(0, 0, 0, 0.4);
}

Post by **rockedge** » Tue Apr 18, 2023 12:31 am

@smokey01

I think we have it now. Can you test with a real database?

What must be fixed still is the logic to determine if a row has the <th> tag

I combined the CSS back into contact8.php for ease of tuning it. It can be separated out to an external css file at some point.

The fix was to add an additional if conditional statement to test if the filtered row (TR) contains the TH tags which when true sets FLAG = true .
So in the following rewrite of the table rows (during filtering), the test conditional can determine whether the FLAG is set to true or false so the next row is displayed or hidden (Lines 186-188).

contact8.php

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
body {
  background-color: lightgrey;
  margin: 0;
  padding: 2rem;
}

table {
	  border-collapse: "";
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
    padding: 0.25rem;
    position: sticky;
    top: 0;
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040
	overflow: auto; 
}

.box1 {
	background-color:#f8f8f8; 
	max-width:25%; 
	margin:10px auto 50px; 
	padding:20px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040 
}

tr td {
  padding: 0.25rem;
}

</style>
</head>

<body>	

<div class="box1">
  <tr>
    <th><input size=33px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr> 
</div>

<div class="box">
 <table id="main-table" border = "3">
     <col span="5" style="background-color:cyan">

<tr>
    <th onclick="comparer(0)">ID</th>
    <th onclick="comparer(1)">First Name</th>
    <th onclick="comparer(2)">Last Name</th>
    <th onclick="comparer(3)">Mobile</th>
    <th onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('sample2.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>
</div>
</div>

<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));

var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);

function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    ID.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    FirstName.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    LastName.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    Mobile.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    Email.innerHTML = data.Email;    

  }

  filterTable();
}

function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");

  let flag = false;


  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }
    if (row.getElementsByTagName("TH")) {
	  flag = true;
	}
    if (flag) {
      row.style.display = "";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}

populateTable();

</script>
</body>
</html>

Now for the arrow to show sort ascending or descending

smokey01 · Post by **smokey01** » Tue Apr 18, 2023 1:07 pm

rockedge wrote: ↑Tue Apr 18, 2023 12:31 am
@smokey01

I think we have it now. Can you test with a real database?

I found a better database that works better, see attached.

rockedge wrote: ↑Tue Apr 18, 2023 12:31 am
What must be fixed still is the logic to determine if a row has the <th> tag

I combined the CSS back into contact8.php for ease of tuning it. It can be separated out to an external css file at some point.

I think it's better in the one file for neatness, no real need for a separate css file.

rockedge wrote: ↑Tue Apr 18, 2023 12:31 am
The fix was to add an additional if conditional statement to test if the filtered row (TR) contains the TH tags which when true sets FLAG = true .
So in the following rewrite of the table rows (during filtering), the test conditional can determine whether the FLAG is set to true or false so the next row is displayed or hidden (Lines 186-188).

I don't understand this bit. When I tested version 8 it highlighted the filtered data but still showed it. It needs to only show the filter data like in version 6. The header row seems to freeze when scrolling. Somehow we need to freeze the search fields and headers. Then the filtered data may scroll at will. This makes it easier for filtering and placing the data under a header. You probably already know this.

rockedge wrote: ↑Tue Apr 18, 2023 12:31 am
contact8.php

Now for the arrow to show sort ascending or descending

Not sure an arrow is necessary as looking at the data it's obvious.
The header doesn't freeze in Seamonkey but does in Palemoon and google-chrome.

AntonioPt · Post by **AntonioPt** » Tue Apr 18, 2023 2:18 pm

Hi @smokey01 in php when you have too much data its good to creat a patination code in order to avoid crashing
https://www.myprogrammingtutorials.com/ ... mysql.html
hope it help even tho i didnt get it 100% what you wanna do
about css + html you should add some css reset in order to fix layout issues around multiple browsers

if you wanna add difrent colors in html you need to add some html ids or class and in php test it then call it

$html_class="" ; # just to avoid undefine var
if ( $var1 == $var2 ) { $html_class="class_blue" ; } else { $html_tag="class_red" ; }

in html code
<table class="whatever">
<th class="'.$html_class.'">
what ever text
</th>
</table>

smokey01 · Post by **smokey01** » Tue Apr 18, 2023 10:27 pm

AntonioPt wrote: ↑Tue Apr 18, 2023 2:18 pm
Hi @smokey01 in php when you have too much data its good to creat a patination code in order to avoid crashing
https://www.myprogrammingtutorials.com/ ... mysql.html
hope it help even tho i didnt get it 100% what you wanna do
about css + html you should add some css reset in order to fix layout issues around multiple browsers

if you wanna add difrent colors in html you need to add some html ids or class and in php test it then call it

$html_class="" ; # just to avoid undefine var
if ( $var1 == $var2 ) { $html_class="class_blue" ; } else { $html_tag="class_red" ; }

in html code
<table class="whatever">
<th class="'.$html_class.'">
what ever text
</th>
</table>

@AntonioPt thanks for your input. I tried your css code it looks useful but not the output I desire. The web page needs to be really simple so even the most inexperienced users can drive it.
The data base has less than 2k records so hopefully it shouldn't crash.

Post by **rockedge** » Tue Apr 18, 2023 11:59 pm

@smokey01 I solved it.

All it took was a CSS class with display: contents !important;. The key here is the !important part.

Should all work now

contact9.php

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
body {
  background-color: lightgrey;
  margin: 0;
  padding: 2rem;
}

table {
	  border: solid 1px;

}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
    padding: 0.25rem;
    position: sticky;
    top: 0;
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040
	overflow: auto; 
}

.box1 {
	background-color:#f8f8f8; 
	max-width:25%; 
	margin:10px auto 50px; 
	padding:20px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040 
}

tr td {
  padding: 0.25rem;
}

.hdgo {
  display: contents !important; 
}
	
</style>
</head>

<body>	

<div class="box1">
  <tr>
    <th><input size=33px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr> 
</div>

<div class="box">
 <table id="main-table" border = "3">

     <col span="5" style="background-color:cyan">

<tr class="hdgo">
    <th [scope='col'] onclick="comparer(0)">ID</th>
    <th [scope='col'] onclick="comparer(1)">First Name</th>
    <th [scope='col'] onclick="comparer(2)">Last Name</th>
    <th [scope='col'] onclick="comparer(3)">Mobile</th>
    <th [scope='col'] onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>
</div>
</div>

<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));

var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);

function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    ID.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    FirstName.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    LastName.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    Mobile.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    Email.innerHTML = data.Email;    

  }

  filterTable();
}

function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }
	
    if (flag) {
      row.style.display = "content";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}
populateTable();

</script>
</body>
</html>

Example :

: Screenshot(4).gif (295.23 KiB) Viewed 3274 times

smokey01 · Post by **smokey01** » Wed Apr 19, 2023 12:33 am

@rockedge Excellent. It is very close. I notice when I type rose in the input box it gives me 4 entries. When I delete rose from the input box the 4 entries remain but are no longer highlighted. Once the input box criteria is deleted it should show all entries once again.
If you refresh the browser all data is shown again.
In Google-Chrome the initial Mobile field seems too narrow for the number, it wraps. After doing a search it fits nicely. It fits in Seamonkey and Palemoon. Unfortunately the header doesn't freeze in Seamonkey but it does in GC and PM.

It would also be nice to be able to freeze the search input box just above the headers so it doesn't scroll with the data.

Searching by field has advantages over searching entire data. EG: 07 will produce results in multiple fields. You may only want 07 in the Mobile field. I guess the search needs to be True/False and True/False etc.

We are very close.

Thanks again.

Post by **rockedge** » Wed Apr 19, 2023 2:49 am

@smokey01 another step closer..... version 10 keeps the filter bar and header at the top.

Searching by field has advantages over searching entire data. EG: 07 will produce results in multiple fields. You may only want 07 in the Mobile field. I guess the search needs to be True/False and True/False etc.

The filter logic code will have to be changed to accomplish this. These versions filter by matching patterns across the entire data set and that scope would need to be narrowed to individual columns. Will have to see how complicated the nesting gets.

contact10.php

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
body {
  background-color: lightgrey;
  margin: 0;
  padding: 2rem;
}

table {
    border: solid 1px;
	overflow: scroll;
	 
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
    padding: 0.25rem;
    position: sticky;
    top: 0px;
    
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040
	overflow: auto;

}

.box1 {
	background-color:''; 
	max-width:25%; 
	margin:10px auto 10px; 
	padding:10px; 
	border-radius:10px; 
	border:1px solid #808080; 
	box-shadow:8px 15px 20px #404040;
	position: sticky;
	top: 0px;
	z-index: 999;
}

tr td {
  padding: 0.25rem;
}

.hdgo {
  display: contents !important; 
}

.button {
  background-color: #000000;
  color: #FFFFFF;
  padding: 2px;
  border-radius: 10px;
  -moz-border-radius: 10px;
  -webkit-border-radius: 10px;
  margin: 2px;
}
    
.small {
  width: 50px;
  height: 15px;
  font-size: 9px;
}
	
</style>
</head>

<body>	

<div class="box1">
  <tr>
    <th><input size=32px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr> 
    <center><button class="small" onClick="window.location.reload();">Refresh</button></center>
</div>

<div class="box">

 <table id="main-table" border = "3">
     <col span="5" style="background-color:cyan">

<tr class="hdgo">
    <th [scope='col'] onclick="comparer(0)">ID</th>
    <th [scope='col'] onclick="comparer(1)">First Name</th>
    <th [scope='col'] onclick="comparer(2)">Last Name</th>
    <th [scope='col'] onclick="comparer(3)">Mobile</th>
    <th [scope='col'] onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>
</div>
</div>

<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));

var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);

function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    ID.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    FirstName.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    LastName.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    Mobile.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    Email.innerHTML = data.Email;    

  }

  filterTable();
}

function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }
	
    if (flag) {
      row.style.display = "content";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}
populateTable();

</script>
</body>
</html>

: Screenshot(6).gif (505.17 KiB) Viewed 3244 times

smokey01 · Post by **smokey01** » Wed Apr 19, 2023 11:56 am

@rockedge The floating search panel is rather cool but it does obscure the headers which is unfortunate. It would be nice if it was above the headers instead of over the top. Transparent is nice though.

Post by **rockedge** » Thu Apr 20, 2023 12:36 am

@smokey01 a bit different position for the filter input panel. Working on making it drag & drop-able as well. I'm fiddling with the CSS to get a dock effect or some mechanism that docks the 2 divisions when they collide during scrolling the page down.

contact11.php

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
body {
  background-color: lightgrey;
  margin: 0;
  padding: 2rem;
}

table {
    border: solid 1px;
	overflow: scroll;
	 
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
    padding: 0.25rem;
    position: sticky;
    top: 0px;
    
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080 
	overflow: scroll;

}

.box1 {
	background-color: transparent; 
	max-width:25%; 
	margin:10px auto 10px; 
	padding:10px 10px 10px 10px; 
	border-radius:10px; 
	border:1px solid #808080; 
	position: sticky;
	top: 40px;
        z-index: 10;
    
}

tr td {
  padding: 0.25rem;
}

.hdgo {
  display: contents !important; 
}
	
</style>
</head>

<body>	

<div class="box1">
  <tr>
    <th><input size=24px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
    <button onClick="window.location.reload();">refresh</button>
  </tr>
</div>

<div class="box">
<table id="main-table" border = "3">

 <col span="5" style="background-color:cyan">

<tr class="hdgo">
    <th [scope='col'] onclick="comparer(0)">ID</th>
    <th [scope='col'] onclick="comparer(1)">First Name</th>
    <th [scope='col'] onclick="comparer(2)">Last Name</th>
    <th [scope='col'] onclick="comparer(3)">Mobile</th>
    <th [scope='col'] onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>
</div>

<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));

var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);

function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    ID.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    FirstName.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    LastName.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    Mobile.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    Email.innerHTML = data.Email;    

  }

  filterTable();
}

function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }
	
    if (flag) {
      row.style.display = "content";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}
populateTable();

</script>
</body>
</html>

smokey01 · Post by **smokey01** » Thu Apr 20, 2023 2:15 am

11 is actually quite functional now once you work out how to drive it.

It would be nice it the filtering results changed when deleting search criteria like it does when adding it. Then we wouldn't need the refresh button.

Very nice.

Post by **rockedge** » Thu Apr 20, 2023 7:36 pm

@smokey01

I returned to a setting in the filter logic that forms the table, that re-enables the hidden table cells when the filter input string is cleared. So no reset button required.
The table filter reverses itself repopulating the table.

contact11.php (basic)

Code: Select all

<!DOCTYPE html>
<!-- contact11.php -->

<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<style type="text/css">
body {
  background-color: lightgrey;
  margin: 0;
  padding: 2rem;
}

table {
    border: solid 0px;
	overflow: scroll;
	display: contents; 
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
    padding: 0.25rem;
    position: sticky;
    top: 0px;
    
}

div.box1 {
	background-color: transparent; 
	max-width:25%; 
	margin:10px auto 10px; 
	padding:10px 10px 10px 10px; 
	border-radius:10px; 
	border:0px solid #808080; 
	position: sticky;
	top: 40px;
    z-index: 10;
    
}

tr td {
  padding: 0.25rem;
}

.hdgo {
  display: contents !important; 
}
	
</style>
</head>
<body>	

<div class="box1">
  <tr>
    <th><input size=24px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr>
</div>

<table id="main-table" border = "3">

 <col span="5" style="background-color:cyan">

<tr class="hdgo">
    <th onclick="comparer(0)">ID</th>
    <th onclick="comparer(1)">First Name</th>
    <th onclick="comparer(2)">Last Name</th>
    <th onclick="comparer(3)">Mobile</th>
    <th onclick="comparer(4)">Email</th>
</tr>
</div>
<?php
$ID = 6; //set variable ID to 6

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>

<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));

var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
   console.log(tableData);

function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    ID.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    FirstName.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    LastName.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    Mobile.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    Email.innerHTML = data.Email;    

  }

  filterTable();
}

function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }
	
    if (flag) {
      row.style.display = "";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}
populateTable();

</script>
</body>
</html>

contact13.php (fancy)

Code: Select all

<!DOCTYPE html>
<!-- contact13.php -->

<head>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js">	
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">	

<style type="text/css">
body {
  background-color: lightgrey;
}

th {
    cursor: pointer;
    background-color: lightgreen;
    width:150px;
    position: sticky;
    top: 0px;
}

div.box { 
	background-color:#f8f8f8; 
	max-width:805px; 
	margin:20px auto 50px; 
	padding:50px; 
	border-radius:10px; 
	border:1px solid #808080; 
 
}
.box1 {
	background-color: transparent;
	max-width:25%; 
	margin:10px auto 50px; 
	padding:20px; 
	border-radius:10px; 
	border:0px solid #808080; 
    position: sticky;
    top: 40px;
}

.hdgo {
  display: contents !important; 
}

</style>

</head>

<body>	
<div class="box1">
  <tr>
    <th><input size=33px type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
  </tr>   
</div>

<div class="box">

 <table id="main-table" border = "1">
     <col span="5" style="background-color:cyan">
<tr class="hdgo">
    <th onclick="comparer(0)">ID<i class="caret"></i></th>
    <th onclick="comparer(1)">First Name</th>
    <th onclick="comparer(2)">Last Name</th>
    <th onclick="comparer(3)">Mobile</th>
    <th onclick="comparer(4)">Email</th>
</tr>

<?php
$ID = 6; //set variable ID to 6


   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }

   $sql =<<<EOF
      SELECT * FROM Details WHERE ID = ID;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     //echo "" . $row['ID'],"	", $row['options'] . "<br> \n"; // needs <br> for browser
     
     echo "<tr><td>".$row['ID']."</td>";
     echo "<td>".$row['FirstName']."</td>";
     echo "<td>".$row['LastName']."</td>";
     echo "<td>".$row['Mobile']."</td>";
     echo "<td>".$row['Email']."</td></tr>";    
   }
   $db->close();

?>
</table>

</div>
</div>
<script type=text/javascript>

const getCellValue = (tr, idx) => tr.children[idx].innerText || tr.children[idx].textContent;

const comparer = (idx, asc) => (a, b) => ((v1, v2) => 
    v1 !== '' && v2 !== '' && !isNaN(v1) && !isNaN(v2) ? v1 - v2 : v1.toString().localeCompare(v2)
    )(getCellValue(asc ? a : b, idx), getCellValue(asc ? b : a, idx));

// do the work...
document.querySelectorAll('th').forEach(th => th.addEventListener('click', (() => {
    const table = th.closest('table');
    Array.from(table.querySelectorAll('tr:nth-child(n+2)'))
        .sort(comparer(Array.from(th.parentNode.children).indexOf(th), this.asc = !this.asc))
        .forEach(tr => table.appendChild(tr) );
})));




var table = document.getElementById('main-table');
var input = document.getElementById('FirstName');
var tableData = [];
   $("table#main-table tr").each(function() {
      var rowDataArray = [];
      var actualData = $(this).find('td');
      if (actualData.length > 0) {
         actualData.each(function() {
            rowDataArray.push($(this).text());
         });
         tableData.push(rowDataArray);
      }
   });
  console.log(tableData);


function populateTable() {
  table.innerHTML = '';
  for (let data of tableData) {
    let row = table.insertRow(-1);
    let ID = row.insertCell(0);
    name.innerHTML = data.ID;

    let FirstName = row.insertCell(1);
    FirstName.innerHTML = data.FirstName;

    let LastName = row.insertCell(2);
    LastName.innerHTML = data.LastName;

    let Mobile = row.insertCell(3);
    Mobile.innerHTML = data.Mobile;
    
    let Email = row.insertCell(4);
    Email.innerHTML = data.Email;    

  }

  filterTable();
}


function filterTable() {
  let filter = input.value.toUpperCase();
  rows = table.getElementsByTagName("TR");
  let flag = false;

  for (let row of rows) {
    let cells = row.getElementsByTagName("TD");
    for (let cell of cells) {
      if (cell.textContent.toUpperCase().indexOf(filter) > -1) {
        if (filter) {
          cell.style.backgroundColor = 'yellow';
        } else {
          cell.style.backgroundColor = '';
        }

        flag = true;
      } else {
        cell.style.backgroundColor = '';
      }
    }

    if (flag) {
      row.style.display = "";
    } else {
      row.style.display = "none";
    }

    flag = false;
  }
}


populateTable();

let tableColumns = document.getElementsByClassName('main-table');

for (let column of tableColumns) {
  column.addEventListener('click', function(event) {
    toggleArrow(event);
  });
}

input.addEventListener('keyup', function(event) {
  filterTable();
});

</script>
</body>
</html>

: Screenshot(7).gif (507.58 KiB) Viewed 3128 times

Puppy Linux Discussion Forum

How to filter & sort with php?

How to filter & sort with php?

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php

Re: php