How to filter & sort with php?

[smokey01]

@rockedge Both Ver 11 and 13 are great. I've actually renamed Ver 11 to 12 as we already had one of those. I do like the fancy version it looks really polished and works very well.
The only glitch I see ATM is the Mobile number wrapping on small devices like phones. Well it does here on my Android with Google-Chrome, Opera and Brave. Mobile also wraps on my desktop with Google Chrome. Also just noticed the headers don't stick on Google-Chrome after a search. It does on initial start, go figure.
It seems to work best in Firefox even on mobile devices.
Thanks

[rockedge]

@smokey01 here is a version that has the JavaScript functions and CSS in separate files.

Added all together in a directory and compressed, Also added back in the box shadow effect for eye candy.

[smokey01]

@rockedge It looks really nice, thanks.

[smokey01]

My next challenge is to load a different database using your code. No luck so far as it's bent out of shape and not reading all the fields. I thought this would be easy .

I added the extra fields, in the PHP and J's files. I don't expect the CSS file will need changing.

What am I missing?

[rockedge]

@smokey01 ,
what does the target database structure look like?

It should be just adding the extra columns in the same manner. How many more columns are there in your database?

How bent out of shape? Perhaps try with contact11.php basic first? We will figure it out

Look at the CSS and check if the value for width is correct with the added table column header cells.

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

[smokey01]

@smokey01 ,
what does the target database structure look like?

See attached file

It should be just adding the extra columns in the same manner. How many more columns are there in your database?

I made a few errors with the <tr> <th> tags when copy and pasting, oops.

Look at the CSS and check if the value for width is correct with the added table column header cells.

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

There really is too much data to display in a table. I might have to give this more thought.

[rockedge]

@smokey01 since there is a SQLite database at the core of the project we could keep the main table display trim at 5 columns and have an expand button that will call up additional information about each row from the database. We can make the ID field the primary key that will associate additional data in the database to each record. So the record for each would have more fields and tables all linked in the database.

I would create a second table with the extra fields and we can manipulate the additional data using the SQL language queries to piece together what will be displayed when.

I am not explaining it to well, but the gist of it is we can use the power of the database to keep the interface compact but flexible as to what it displays on demand

[norgo]

for info only

BROWSER SUPPORT FOR CSS position:sticky

Google Chrome
Chrome browser version 4 to 22 doesn't supports this browser element. This browser property is not supported but can be enabled by Chrome 23 to 55.CSS position:sticky is partially supported for Chrome 56 to 67.
Mozilla Firefox
Mozilla firefox version 2 to 25 aren't compatible with this property. This property is not supported but can be enabled for Firefox 26 to 31. Firefox 32 to 58 partially support this element. Firefox 59 and 60 supports it completely.
Internet Explorer
IE browser doesn't support CSS position:sticky.
Safari
Safari browser 3.1 to 6 are not supported for this property. Safari 6.1 and 7 partially support this browser property with prefix -webkit-.Later version 7.1 to 11.1 are compatible with this web element.
Microsoft Edge
This browser element is not supported by Microsoft Edge browser 12 to 15.Edge 16 and 17 partially support this browser element.
Opera
Opera browser version 10.1 to 41 doesn't supports this property.Later versions 42 and 53 partially supports CSS position:sticky element.

[rockedge]

@norgo Yes that's the tricky part. Finding CSS and JavaScript solutions that will work the same way across a broad range of browsers.

Our code is working on Opera Version:97.0.4719.63 and looks good on Google Chrome Version 108.0.5359.124 (Official Build).

I have been experimenting with making the filter input panel drag and drop capable which seems to work somewhat on Chrome but not quite on Firefox so far. Just now trying the drag and drop on Chrome almost works where on FIrefox it's dead in the water.

So far on the latest versions I have of Brave, Opera, Chrome, Palemoon and Firefox the position: sticky; is working as expected.

For years working with Zoneminder and streaming video content live worked differently on each browser type I became aware of how there is not a one method that works on all browsers the same.

[smokey01]

@smokey01 since there is a SQLite database at the core of the project we could keep the main table display trim at 5 columns and have an expand button that will call up additional information about each row from the database. We can make the ID field the primary key that will associate additional data in the database to each record. So the record for each would have more fields and tables all linked in the database.

I would create a second table with the extra fields and we can manipulate the additional data using the SQL language queries to piece together what will be displayed when.

I am not explaining it to well, but the gist of it is we can use the power of the database to keep the interface compact but flexible as to what it displays on demand

@rockedge you have explained it exactly how I was thinking. Initial display would be just Rank, FirstName and LastName. By clicking on the table row would expand to show additional information. Probably show the info on several rows so it's easier to read on small devices. Would this be difficult. Creating another table would be easy enough.

[rockedge]

@smokey01 to follow an idea, I made the filter input panel drag and drop. I have it going on the 3 different browser types.

Can you expand the contact8.sqlite database to have the second table? The ID from table "Details" should match the ID field in the new table. So ID=1 will indicate all the data cells belonging to ID=1 across both tables. With an example of the 2nd table we can begin to look for the best way to display the additional information when called upon.

Screenshot(8).gif (509.36 KiB) Viewed 3678 times

[smokey01]

@smokey01 to follow an idea, I made the filter input panel drag and drop. I have it going on the 3 different browser types.

Looks great.

Can you expand the contact8.sqlite database to have the second table? The ID from table "Details" should match the ID field in the new table. So ID=1 will indicate all the data cells belonging to ID=1 across both tables. With an example of the 2nd table we can begin to look for the best way to display the additional information when called upon.

The attached database has two tables, Contacts and Details. I've set ID as the primary key in both tables.

[smokey01]

This would be a good way to display additional data.
I don't know how difficult it would be to implement.

https://datatables.net/examples/api/row_details.html

[rockedge]

@smokey01 I starting to use the datatables js library method as well. It does seem to be the best solution.

Here is a test6.php that uses the 2 table database for a demo table segment popup All it does is read a row from the Contacts table from a specific ID and pops up a div container with the table row

Code: Select all

<!DOCTYPE html>
<head>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>

<style type="text/css">
#mainpop-table {
    background-color: lightgrey;
    display:none;
    position: fixed;
    top:50%;
    left:50%;
    margin:-150px 0 0 -150px;
}

div.box { 
	background-color: #f8f8f8; 
	max-width: 50%; 
	margin:20px auto 50px; 
	padding:20px; 
	border-radius:10px; 
	border:1px solid #808080; 
 	box-shadow:8px 15px 20px #404040 
}
</style>
</head>
<body>
<div class="box" id="mainpop-table">
<table>
<?php
 class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8x2.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }
   $id = 2;  ## the ID from table Details
   $sql = "SELECT * FROM Contacts WHERE ID =". $id;

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

<input id="help_button" type="button" value="<?php echo $id ?>"/>

</body>

<script>
	var showpop = <?php echo json_encode($id, JSON_HEX_TAG); ?> 
	$(document).ready(function() {
    $("#help_button").click(function() {
        $("#mainpop-table").slideToggle(1000, function() {
            if($("#help_button").val() == "Close")
            {
                $("#help_button").val(showpop);
            }
            else
            {
                $("#help_button").val("Close");
            }
        });
    });
});
</script>

Screenshot(9).gif (21.33 KiB) Viewed 3598 times

But I think the enabling child table rows will be the best method to start with.

[rockedge]

@smokey01 This version uses contact8.sqlite and I have added CSS to make the table responsive so columns will only display if the there is enough room on the screen.

It is not quite the solution which would be something like click on the ID and it will display the additional data for each record. Still working on getting DataTables to work or something similar. We'll see......

contact16.php

Code: Select all

<!DOCTYPE html>
<!-- contact16.php -->
<head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
   <script src="https://code.jquery.com/ui/1.13.2/jquery-ui.js">	
   <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
   <link rel="stylesheet" href="contact.css">

<style type="text/css">
.tutorial-table th {
	    text-align: left;
	    background: #f0F0F0;
	    padding: 10px;
	}
.tutorial-table td {
	    border-bottom: #e1e0e0 1px solid;
	    padding: 10px;
}
	
@media screen and (max-width: 900px) and (min-width: 550px) {
		.priority-5{
			display:none;
		}
}
	
@media screen and (max-width: 550px) {
		.priority-5{
			display:none;
		}
		.priority-4{
			display:none;
		}
}
	
@media screen and (max-width: 300px) {
		.priority-5{
			display:none;
		}
		.priority-4{
			display:none;
		}
		.priority-3{
			display:none;
		}
		.priority-2{
			display:none;
        }
	
}
</style>  
</head>

<body>	

<div class="box1 priority-1">
<div id="mydiv"> 
  <div id="mydivheader"><center>Move</center></div>
   <input size="30px" type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
</div>
</div>

<script type=text/javascript>

// Make the DIV element draggable:
dragElement(document.getElementById("mydiv"));

function dragElement(elmnt) {
  var pos1 = 0, pos2 = 0, pos3 = 0, pos4 = 0;
  if (document.getElementById(elmnt.id + "header")) {
    // if present, the header is where you move the DIV from:
    document.getElementById(elmnt.id + "header").onmousedown = dragMouseDown;
  } else {
    // otherwise, move the DIV from anywhere inside the DIV:
    elmnt.onmousedown = dragMouseDown;
  }

  function dragMouseDown(e) {
    e = e || window.event;
    e.preventDefault();
    // get the mouse cursor position at startup:
    pos3 = e.clientX;
    pos4 = e.clientY;
    document.onmouseup = closeDragElement;
    // call a function whenever the cursor moves:
    document.onmousemove = elementDrag;
  }

  function elementDrag(e) {
    e = e || window.event;
    e.preventDefault();
    // calculate the new cursor position:
    pos1 = pos3 - e.clientX;
    pos2 = pos4 - e.clientY;
    pos3 = e.clientX;
    pos4 = e.clientY;
    // set the element's new position:
    elmnt.style.top = (elmnt.offsetTop - pos2) + "px";
    elmnt.style.left = (elmnt.offsetLeft - pos1) + "px";
  }

  function closeDragElement() {
    // stop moving when mouse button is released:
    document.onmouseup = null;
    document.onmousemove = null;
  }
}

</script>
<div class="box">
 <table id="main-table" border="0">
   <col span="5" style="background-color:lightblue">
<tr class="hdgo">
    <th class='priority-1' onclick="comparer(0)">ID</th>
    <th class='priority-2' onclick="comparer(1)">First Name</th>
    <th class='priority-3' onclick="comparer(2)">Last Name</th>
    <th class='priority-4' onclick="comparer(3)">Mobile</th>
    <th class='priority-5' onclick="comparer(4)">Email</th>
</tr>
<?php


   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }
 $ret = $db->query("SELECT * FROM Details");
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     echo "<tr><td class='priority-1'>".$row['ID']."</td>";
     echo "<td class='priority-2'>".$row['FirstName']."</td>";
     echo "<td class='priority-3'>".$row['LastName']."</td>";
     echo "<td class='priority-4'>".$row['Mobile']."</td>";
     echo "<td class='priority-5'>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>
</div>
<script type="text/javascript" src="functions15.js"></script>
</body>
</html>

And this next version is also responsive as the one above and it has pagination somewhat functioning. This is experimental and looking how to refresh to a paged filter output

contact16-page.php

Code: Select all

<!DOCTYPE html>
<!-- contact16-page.php -->
<head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
  <script src="https://code.jquery.com/ui/1.13.2/jquery-ui.js">	
   <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
   <link rel="stylesheet" href="contact16.css">

<style type="text/css">
.tutorial-table th {
	    text-align: left;
	    background: #f0F0F0;
	    padding: 10px;
	}
.tutorial-table td {
	    border-bottom: #e1e0e0 1px solid;
	    padding: 10px;
}
	
@media screen and (max-width: 900px) and (min-width: 550px) {
		.priority-5{
			display:none;
		}
}
	
@media screen and (max-width: 550px) {
		.priority-5{
			display:none;
		}
		.priority-4{
			display:none;
		}
}
	
@media screen and (max-width: 300px) {
		.priority-5{
			display:none;
		}
		.priority-4{
			display:none;
		}
		.priority-3{
			display:none;
		}
		.priority-2{
			display:none;
        }
	
}
</style>  
</head>

<body>	

<div class="box1 priority-1">
<div id="mydiv"> 
  <div id="mydivheader"><center>Move</center></div>
   <input size="30px" type="text" id="FirstName" name="FirstName" placeholder="Filter" onkeyup="filterTable()"></th>
</div>
</div>

<script type=text/javascript>

// Make the DIV element draggable:
dragElement(document.getElementById("mydiv"));

function dragElement(elmnt) {
  var pos1 = 0, pos2 = 0, pos3 = 0, pos4 = 0;
  if (document.getElementById(elmnt.id + "header")) {
    // if present, the header is where you move the DIV from:
    document.getElementById(elmnt.id + "header").onmousedown = dragMouseDown;
  } else {
    // otherwise, move the DIV from anywhere inside the DIV:
    elmnt.onmousedown = dragMouseDown;
  }

  function dragMouseDown(e) {
    e = e || window.event;
    e.preventDefault();
    // get the mouse cursor position at startup:
    pos3 = e.clientX;
    pos4 = e.clientY;
    document.onmouseup = closeDragElement;
    // call a function whenever the cursor moves:
    document.onmousemove = elementDrag;
  }

  function elementDrag(e) {
    e = e || window.event;
    e.preventDefault();
    // calculate the new cursor position:
    pos1 = pos3 - e.clientX;
    pos2 = pos4 - e.clientY;
    pos3 = e.clientX;
    pos4 = e.clientY;
    // set the element's new position:
    elmnt.style.top = (elmnt.offsetTop - pos2) + "px";
    elmnt.style.left = (elmnt.offsetLeft - pos1) + "px";
  }

  function closeDragElement() {
    // stop moving when mouse button is released:
    document.onmouseup = null;
    document.onmousemove = null;
  }
}

</script>
<div class="box">
 <table id="main-table" border="0">
   <col span="5" style="background-color:lightblue">
<tr class="hdgo">
    <th class='priority-1' onclick="comparer(0)">ID</th>
    <th class='priority-2' onclick="comparer(1)">First Name</th>
    <th class='priority-3' onclick="comparer(2)">Last Name</th>
    <th class='priority-4' onclick="comparer(3)">Mobile</th>
    <th class='priority-5' onclick="comparer(4)">Email</th>
</tr>
<?php


   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('contact8.sqlite');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
   }
 $ret = $db->query("SELECT * FROM Details");
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
     echo "<tr><td class='priority-1'>".$row['ID']."</td>";
     echo "<td class='priority-2'>".$row['FirstName']."</td>";
     echo "<td class='priority-3'>".$row['LastName']."</td>";
     echo "<td class='priority-4'>".$row['Mobile']."</td>";
     echo "<td class='priority-5'>".$row['Email']."</td></tr>";    
   }
   $db->close();
?>
</table>
</div>
<script type="text/javascript" src="functions16.js"></script>

<script type="text/javascript">
addPagerToTables('#main-table', 20);

function addPagerToTables(tables, rowsPerPage) {

    tables = 
        typeof tables == "string"
      ? document.querySelectorAll(tables)
      : tables;

    for (let table of tables) 
        addPagerToTable(table, rowsPerPage);

}

function addPagerToTable(table, rowsPerPage) {

    let tBodyRows = getBodyRows(table);
    let numPages = Math.ceil(tBodyRows.length/rowsPerPage);

    let colCount = 
      [].slice.call(
          table.querySelector('tr').cells
      )
      .reduce((a,b) => a + parseInt(b.colSpan), 0);

    table
    .createTFoot()
    .insertRow()
    .innerHTML = `<td colspan=${colCount}><div class="nav"></div></td>`;

    if(numPages == 1)
        return;

    for(i = 0;i < numPages;i++) {

        let pageNum = i + 1;

        table.querySelector('.nav')
        .insertAdjacentHTML(
            'beforeend',
            `<a href="#" rel="${i}">${pageNum}</a> `        
        );

    }

    changeToPage(table, 1, rowsPerPage);

    for (let navA of table.querySelectorAll('.nav a'))
        navA.addEventListener(
            'click', 
            e => changeToPage(
                table, 
                parseInt(e.target.innerHTML), 
                rowsPerPage
            )
        );

}

function changeToPage(table, page, rowsPerPage) {

    let startItem = (page - 1) * rowsPerPage;
    let endItem = startItem + rowsPerPage;
    let navAs = table.querySelectorAll('.nav a');
    let tBodyRows = getBodyRows(table);

    for (let nix = 0; nix < navAs.length; nix++) {

        if (nix == page - 1)
            navAs[nix].classList.add('active');
        else 
            navAs[nix].classList.remove('active');

        for (let trix = 0; trix < tBodyRows.length; trix++) 
            tBodyRows[trix].style.display = 
                (trix >= startItem && trix < endItem)
                ? 'table-row'
                : 'none';  

    }

}

// tbody might still capture header rows if 
// if a thead was not created explicitly.
// This filters those rows out.
function getBodyRows(table) {
    let initial = table.querySelectorAll('tbody tr');
  return Array.from(initial)
    .filter(row => row.querySelectorAll('td').length > 0);
}

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

Screenshot(15).jpg (45.01 KiB) Viewed 3552 times

[smokey01]

Sorry I haven't been a bit more responsive lately but my 99YO father had a fall recently and passed away yesterday.

[rockedge]

@smokey01 I am very sorry to hear that your father has died.
My Dad is 86 and he fell recently and is recovering poorly. 99 is amazing

When you are ready and have the mind to get back to the project we can restart

[smokey01]

@smokey01 I am very sorry to hear that your father has died.
My Dad is 86 and he fell recently and is recovering poorly. 99 is amazing

When you are ready and have the mind to get back to the project we can restart

@rockedge Thanks. I've had a quick look at your two 16 examples. I think I prefer the first one without the page number links. I see you changed the name of the .js files. That had me going for a little while. I also noticed the search box in no longer movable/floating. Was that intentional?

Now that the funeral is over hopefully things can get back to normal. It's been rather emotionally exhausting.

Cheers

[rockedge]

@smokey01 Good to see you back after this difficult time.

Attached is contact17.php, contact17.css, functions17.js and the data contact8.sqlite which make the version I feel is the best so far. The paging is okay but difficult to implement after any sort or filter action and adding the DataTables functions became a tangle and I like the way contact17.php can filter a large table quickly using the simpler mechanism. So I dropped the paging features which in this case wasn't a good fit.

Also the input panel bar is movable in contact17

[smokey01]

Should this version be contact17 as you already posted contact16 and contact16-page above?

[rockedge]

@smokey01 Yes! you're right we should change the version to contact17. I will replace 16 with 17.

UPDATE: all set! contact17.tar.gz

[smokey01]

@rockedge It looks and works very nicely.

I tried it on my phone and it limits the fields to fit the screen as designed. The problem with that is there is no way to view all the data. I suppose once we can expand the records that won't be a problem if it shows the remaining fields and information.

I can't work out how to move the floating search box on the phone. It's easy with a mouse on the desktop.

I tried it on the latest version of Seamonkey and the headings still scroll up the screen. Not a problem with all the other browsers I have tested.

The search does seem faster.

You forgot to change the path to the .js file. You're testing me right?

BTW how do you do your testing locally?

[rockedge]

@smokey01 I also can't move the panel on an Android phone. We'll have to check into that. Also I am looking for the best way to be able to click on a row and expand the info for the record when it is compacted for small browser screen area.

I am using a Hiawatha v10.8.3 web server I have configured for my local web server. I have PHP 7.4.3 installed with a large collection of extensions including the sqlite3 libraries. I have not installed MySQL or mariaDB as of yet. Usually I do so for working on and with Zoneminder. I have scripted installing and configuring a complete Hiawatha v11+ based web server serving PERL and PHP also Python along with the HTML that uses Pkg to install the necessary components.

Result of

Code: Select all

<?php  phpinfo(); ?> 

Screenshot(24).jpg (59.83 KiB) Viewed 3703 times

[smokey01]

Did you try this method.

Code: Select all

php phpfile.php > phpout.html

Then click on the html file.

[rockedge]

Did you try this method.

Code: Select all

php phpfile.php > phpout.html

Then click on the html file.

Yes I have! I will also explore using the method a bit more. I have the command line php-cli configured the same as php-cgi so it works well on the few tests I have done.

What advantages to you see? A lighter web server could be used?

[smokey01]

@rockedge I'm ready to resume if you are.

[rockedge]

Okay @smokey01 we can resume the project

I just noticed I did not update some file names for the js files in contact17.php though it is easy to adjust.

UPDATE: fixed it up and included the output of:

Code: Select all

php contact17.php > contact17.html

been playing with the PHP built in web server, launching it in the directory to serve::

Code: Select all

php -S localhost:8000 

[smokey01]

Okay @smokey01 we can resume the project

I just noticed I did not update some file names for the js files in contact17.php though it is easy to adjust.

UPDATE: fixed it up and included the output of:

Code: Select all

php contact17.php > contact17.html

been playing with the PHP built in web server, launching it in the directory to serve::

Code: Select all

php -S localhost:8000 

I noticed the the file names but fixed it my end.
I wasn't away of the PHP web server, will have to have a play.

I have my fireftr database working online. I discovered how to exclude some fields.

[rockedge]

I have my fireftr database working online. I discovered how to exclude some fields.

That's excellent!

How many fields per row and how are you excluding them and when?

[smokey01]

I have my fireftr database working online. I discovered how to exclude some fields.

That's excellent!

How many fields per row and how are you excluding them and when?

I think the full database has 13 fields and I reduced it to 5 on one row. This seems to work ok in landscape mode on my phone as well. The rest of the fields are not being read or displayed. I didn't know that was possible.

I haven't thought too much about showing the excluded fields yet. Maybe two PHP scripts is the way to go. Five fields for general users and all fields for privileged users.

Also I'm having trouble defining the CSS file. It would be nice to be able to make the field width to a minimum width such as ID. There are wasted spaces.

It would also be nice to have the background (white area) shrink and stretch in proportion to field width when the browser is made wider or narrower. I've tried playing with auto instead of px but not quite there yet. I'm sure it's possible.