Can't get simple search to work (split of previous question)
Can't get simple search to work (split of previous question)
obrienj
Posts: 93Questions: 38Answers: 0
Allan,
The simple search question, I didn't know there needed to be some special PHP code but to answer your request, this development is only running on my development laptop using WAMP64 installed on it so a link is virtually impossible.
So I have included both the Datatbales definition and my PHP code. As noted below I have not included some purpose built code in preCreate, preEdit, and preRemove which makes the PHP code quite long. If it is necessary I can include it in a subsequent post.
Datatables (client-side):
table = $('#cc-list').DataTable({
processing: true,
serverSide: true,
paging: true,
searching: true,
select: true,
responsive: true,
ordering: true,
orderFixed: [0, 'asc'],
ajax: {
url: "OTCalMaint.php",
type: "POST"
},
columns: [
{
"data": "start",
"searchable": true
},
{
"data": "end",
"searchable": true
},
{
"data": "title",
"searchable": true
},
{
data: null,
searchable: false,
className: "center",
defaultContent: '<a href="" class="editor_edit">Edit</a> / <a href="" class="editor_copy">Copy</a> / <a href="" class="editor_remove">Delete</a>'
}
],
columnDefs: [
{
"targets": 0,
"data": null,
"title": "Date",
"width": "10%",
"render": function (data, type, row, meta) {
var wrks = moment(row.start, 'dddd, MMMM D, YYYY h:mm A');
var wrke = moment(row.end, 'dddd, MMMM D, YYYY h:mm A');
var wrkd = moment(wrks).format('ddd MMM D');
return "" + wrkd;
}
},
{
"targets": 1,
"data": null,
"title": "Time",
"width": "14%",
"render": function (data, type, row, meta) {
var wrks = moment(row.start, 'dddd, MMMM D, YYYY h:mm A');
var wrke = moment(row.end, 'dddd, MMMM D, YYYY h:mm A');
if (row.allDay == '1') {
var wrkd = moment(wrks).format('h:mm A') + " - All Day";
} else {
var wrkd = moment(wrks).format('h:mm A') + " - " + moment(wrke).format('h:mm A');
}
return "" + wrkd;
}
},
{
"targets": 2,
"data": null,
"title": "Event",
"width": "60%",
"render": function (data, type, row, meta) {
var wloc = "";
var wrkt = "";
if (row.location == null) {
wrkt = row.title + " - " + row.city + ", " + row.state + " " + row.zipcode;
} else {
wrkt = row.title + " - " + row.location;
}
return "" + wrkt.trim();
}
}
],
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
]
});
Server-side PHP
#region Editor definition
//---------------------------------------------------------------------
// Build Editor instance and process the data coming from _POST
//---------------------------------------------------------------------
Editor::inst( $db, 'calendar.events', 'RID' )
->fields(
Field::inst( 'RID' )
->set(false), // ID is automatically set by the database on create
Field::inst( 'id' ),
Field::inst( 'title' ),
Field::inst( 'allDay' ),
Field::inst( 'action' ),
Field::inst( 'start' )
->validator( 'Validate::dateFormat', array(
"format" => 'l, F j, Y g:i A',
"message" => "Please enter a date in the correct format"
) )
->getFormatter( 'Format::datetime', array(
'from' => 'Y-m-d H:i:s',
'to' => 'l, F j, Y g:i A'
) )
->setFormatter( 'Format::datetime', array(
'from' => 'l, F j, Y g:i A',
'to' => 'Y-m-d H:i:s'
) ),
Field::inst( 'end' )
->validator( 'Validate::dateFormat', array(
"format" => 'l, F j, Y g:i A',
"message" => "Please enter a date in the correct format"
) )
->getFormatter( 'Format::datetime', array(
'from' => 'Y-m-d H:i:s',
'to' => 'l, F j, Y g:i A'
) )
->setFormatter( 'Format::datetime', array(
'from' => 'l, F j, Y g:i A',
'to' => 'Y-m-d H:i:s'
) ),
Field::inst( 'whenHold' )->setFormatter( 'Format::nullEmpty' ),
Field::inst( 'rptHold' )->setFormatter( 'Format::nullEmpty' ),
Field::inst( 'rfc5545' ),
Field::inst( 'rfcDates' ),
Field::inst( 'rfcid' ),
Field::inst( 'location' ),
Field::inst( 'description' ),
Field::inst( 'placeID' ),
Field::inst( 'institution' ),
Field::inst( 'address' ),
Field::inst( 'city' ),
Field::inst( 'state' ),
Field::inst( 'country' ),
Field::inst( 'zipcode' ),
Field::inst( 'neighborhood' ),
Field::inst( 'phone' ),
Field::inst( 'webURL' ),
Field::inst( 'mapURL' ),
Field::inst( 'locPicURL' ),
Field::inst( 'latitude' ),
Field::inst( 'longitude' )
)
#endregion
// I have left out preCreate, preEdit, preRemove, and several support functions
#region Processing
->process( $_POST )
->json();
#endregion
If I type a word into the client-side "Search" box that I know is in a "title" nothing happens.
Regards,
Jim
This discussion has been closed.
Answers
What does the server respond with? This tech note shows how you can see what the server is responding with.
Allan
Allan,
I looked the the traffic via the Network component of the Web Console in Firefox and found that there is a request and response for every character I type in the searchbox, "s i n g l e" and all of the responses include all of the table rows (10 in my test database).
Only one of the rows in the database includes the word "single" anywhere and that is in the column "title" and it is declared searchable but it is not explicitedly displayed in the Datatables list but is used as part of an aggregate column in the list as can be seen in the "columnDefs: target:2 Rendering" above..
To repeat for clarity, I have made no changes to my server-side or client-side to support searching other than to make certain that the required columns are declared searchable and those remain the same as shown in the original post above.
Regards,
Jim
That suggests that the WHERE condition for the server-side processing search isn't having any effect.
First question is - do you need server-side processing? If you aren't going to be working with tens of thousands or more rows, just use client-side processing.
If you do need server-side processing, enable the debug feature of the libraries (add
->debug( true )immediately before the->process(...)call), and then show me the JSON that is being returned from the server when you search for something.Allan
Allan,
Where do I find the JSON of which you speak?
In the mean time I'll keep looking for it?
Regards,
Jim
Follow this tech note to get the JSON response:
https://datatables.net/manual/tech-notes/1
Kevin
Allan,
I looked in the Network tab again and this is the last request (I believe) and the response is follows.
Is there something else you are looking for?
And this is the companion response:
And I have verified the row I noted above is the only place "single:" occurs.
Regards,
Jim
Also Allan may be looking for the output from this for your search:
Kevin
Allan,
To answer a question that I missed, I do need to do searching on the server-side.
Regards,
Jim
Allan,
I just saw the "debugSql" piece of the response when I search and it follows:
Allan,
This is getting more curious:
The following works just like it is supposed to:
But this does not and neither does the search box:
Regards,
Jim
Hi Jim,
Could you give me the raw JSON from the "Network" tab please? I'm finding it quite hard to follow the nesting of the data as it is pasted in above.
Thanks,
Allan
Allan,
I will do my best. Current Firefox a bit different than the tech note but here are the request and the response.
I am using less data this time and the search word is "c h" which should match only one row of 3.
And this is using the "vanilla" search box.
The request:
This is the response when the two characters "c h" were entered. Note that the response when just the "c" was entered was identical. All nodes are expanded but copy and paste removes the little triangles and messes up the format.
I find it easier to access the pure JSON using the Firebug add-on for Firefox.
Allan,
I took tangerine's suggestion and used Firebug to get the raw JSON.
But my post was too long.
So, please let me know if there is a way to put this in a file and send that to you.
Regards,
Jim
pastebin.com.
Seriously - you can't get the raw JSON easily from Firefox now? Urgh... I'll update the tech note.
Allan
Allan,
I have no idea what I'm doing with pastebin, but try this URL
https://pastebin.com/BZv6kV9E
Regards,
Jim
@Allan: I really couldn't say, it's just that I always use Firebug and never needed whatever inherent method Firefox has for digging deeper.
The search term there is
ch, which appears in a number of locations in the three displayed rows - screenshot attached.Allan
Allan,
I am very sorry about that, I was in a hurry and traveling.
I've run a second test using the search word "chorus" which only appears twice but in two columns in the same row, yet I get all three rows in the database.
The pastebin is: https://pastebin.com/yjBpszeY
I omitted the POST and Response for the letters "c h o r u" and only included POST and Response when I typed the "s", thus the search for "c h o r u s".
Regards,
Jim
Thank you.
The SQL statement being executed contains:
That
OR 1=1is going to be causing everything to be matched.Your code above doesn't show a
wherecondition being applied. Can you show me the full PHP without retractions?Thanks,
Allan
Allan,
Here is the full PHP in Pastebin https://pastebin.com/QR7CeKXX
Regards,
Jim
Allan,
Again note that I do nothing relative to Search other than request the search box at the top of the list.
The SQL statement you see must have been generated by Datatables because I didn't.
And, a question of curiosity, is that conventional SQL, I don't recall ever seeing notation like in a SELECT:
or are the phrases ":where_5" a substitution notation that is satisfied later in the process?
Yes. It's called "binding". You might want to read up on it.
tangerine,
Familiar concep,t different name.
It's been more years than I want to remember since I worked with an SQL language design team, relational database internal design and relational algebra.
Regards,
Jim
Got it now! This was a bizarre one - thanks for your help in tracing it down Jim.
The issue is caused by the column called
endwhich is a function name in PHP and it was incorrectly executing that function, rather than actually just using the column name!To fix, open
php/Database/Query.phpand find:in the
or_where()method. Replace with:and that will fix it.
It will be in 1.6.5!
Allan
Allan,
Thank you and glad I could help.
I'm on the road right now but will try this evening.
One other thing I may try, since "end" is a datetime as is "start" is make them not searchable.
Regards,
Jim
If you are using MySQL as the database, they will be searchable - but not in the format shown in the table since that is a PHP renderer, while the search happens in SQL. They will be searchable in ISO8601.
Allan
Allan,
Made you code change and all is working as it should.
Thanks again.
To finish up search:
For the occasional user do you prefer/recommend:
Dynamic search, i.e., repopulate the list on each search character typed OR
Static search, i.e., where the repopulate only occurs once the user has entered one or more search stings and presses a button,, say "Search"
My instinct and Usability training says static search as the user may find the dynamic search "list flashing" to be distracting to say nothing of a load on the server.
Your thoughts please.
Regards,
Jim
For something like a DataTable, personally I like the dynamic search. It "feels" responsive (as long as it actually is - if it lags, then its horrible!).
This is part of the reason why it is important only to enable server-side processing on tables with have tens of thousands or more rows.
Allan