Okay
  Public Ticket #1849739
bug: sql statement can not have 2 concat functions
Open

Comments

  • Larry Kolinek started the conversation

    if the SQL Query has 2 concat functions, the global search does not work.

    you can test it. create a simple SQL query like this:

    select field1, field2 from table
    ==> global search works

    select concat("a",field1), concat("b",field2) from table
    ==> global search does NOT work

    :(

  •   Larry Kolinek replied privately
  •   Larry Kolinek replied privately
  •  32
    Aleksandar replied

    Hello again Larry.

    Like I mentioned in the other ticket, when You add CONCAT, or JOIN or anything else other than a simple SELECT * FROM query, the result may be just as You described - search doesn't work, or filters don't work, or similar issues.

    You'd need to create a VIEW for the table to function properly.

    Please try this and let me know if it works.

    If You want we can continue the correspondence on one of these tickets if they have similar issues, just to avoid situations like this - where I quote myself.

    Best regards.

    Check out our new Support Help Center!

    Check out our newest product - Amelia - Enterprise-Level WordPress Appointment Booking Plugin - promo price for launch period!

    Kind Regards, 
    Aleksandar Vukovic
    aleksandar.vukovic@tms-outsource.com

  • Larry Kolinek replied

    thanks!!  sorry I didn't catch the "Use a View"  :)

    HOWEVER, I did make the view and it still doesn't work?
    I created the view and created the SQL via wpdatatables in 2 ways (manuall and view the wizzard)... both ways do not allow the global search to work?

    You can login and see it? this seems weird, the view should work?!?!? look at the last 

    my guess.... the "photo field", which is where I had the "view-test" table. I can't get any simpler than that?

    thanks

    PS. my other question about custom code/how to disguise the ID number... a view would "fix" that issue too!

  • Larry Kolinek replied

    okay, :)

    I know the problem now, its not the concat, its the end result of the field. I ran the view with and without the "photo" field.
    Global search works when the photo field is NOT included and doesn't work when it IS included.

    THE QUESTION: why does the below string text break Global Search?

    So for whatever reason, the photo string field, (has this data in it). Below is the string text in the photo field from the view

    <a href="https://www.site.com/abc/15444306/"><img src="https://www.site.com/images/1.jpg"></a>
    
  • Larry Kolinek replied

    I really need this issue to be solved ASAP :(
    What could help both of us.... IS THERE a way to NOT include this field in the Global Search?

    No one will be searching on an image :)

    PS. I already turned off all the filter/search settings, but didn't help. Obviously, I need the photo/link to be in the table, but it is not needed to be searched on or filtered.

  •  32
    Aleksandar replied

    Hello again Larry.

    Let's try a simple fix. Can You try disabling Filtering only for that column?

    That should exclude it from the search, and it should work like that.

    Also, I'm having issues opening Your website https://www.trueshares.com/wp-admin/. I don't know what's going on, but just as a side-note.

    Best regards.

    Check out our new Support Help Center!

    Check out our newest product - Amelia - Enterprise-Level WordPress Appointment Booking Plugin - promo price for launch period!

    Kind Regards, 
    Aleksandar Vukovic
    aleksandar.vukovic@tms-outsource.com

  • Larry Kolinek replied

    please go down below in the private messages for access :)  I have a user/pass on the whole site

    note:

    - I've turned off ALL filters/sorting, etc.
    - I've tried different data types: string and url (shows image, but don't allow global search to work)

    ALSO, I saw in codecanyon comments/support a reply about "using javascript to exclude a field from global search" ??? but there was no other info about that idea.

  •  32
    Aleksandar replied

    Hello again Larry.

    It's not that I can't log in with the credentials You provided. It's just that it loads the page "forever", and it never does. I deleted cookies, cache and cleared history, but I'm still having issues with even getting to the log-in page, or any other part of the site for that matter. After a few minutes it comes to this:

    7800761576.png

    Do You, maybe, have mode_security turned on, or any other security plugin?

    If You do, could You please make sure my IP isn't blocked: 178.148.79.105

    If excluding the column from filtering option didn't help, then hiding the field from the global search using javascript would also be considered a customization, but I can't say for sure until I log in again and take a look.

    Please let me know when You check the settings.

    Best regards.

    Check out our new Support Help Center!

    Check out our newest product - Amelia - Enterprise-Level WordPress Appointment Booking Plugin - promo price for launch period!

    Kind Regards, 
    Aleksandar Vukovic
    aleksandar.vukovic@tms-outsource.com

  • Larry Kolinek replied

    I whitelisted your IP :)

    please try again

    FYI. the 1st (#1) WP Data Table is being used. The SQL is SELECT * from wpdt_listings;

    and that is a mysql view.

    you can see, global search not working :(
    and the cause is the photo field. 

    ALSO, I created 2 other tables named (working and not working). the ONLY difference between those 2 tables is the photo field :(

  •   Aleksandar replied privately
  •   Larry Kolinek replied privately
  •   Aleksandar replied privately
  • Larry Kolinek replied

    Hi Aleksandar,

    this is how I changed the text:
    https://tmsplugins.ticksy.com/ticket/1848099/  (via that ticket)

    In my prior reply below, YOU CAN recreate the database table. You don't need a zip file, I gave you the SQL statement to run. It has the structure and a sample record. You only need 1 record to see if Global Search is working or not.

    FYI. as you can see now, Global Search doesn't return ANY rows.

    again, the photo field is the issue and that is because its a string with html in it.

    ALSO, I really, really need this working ASAP :) My project is falling behind and it seems I only get 1 response from you all each day. And this response only repeated what I said yesterday :(

    thank you

  •  32
    Aleksandar replied

    Hi Larry.

    I apologize for late replies, it's just that I'm going over all the tickets one at a time, and Yours appears to be among the last during the day. I'll prioritize this ticket until we get it resolved, I assure You.

    We've managed to create tables, and generate the view just like You did, and the issue is present locally as well. We took the issue "under maintenance" so to speak, and I'll let You know as soon as we're done. I just wanted to let You know that we're working on finding the cause and resolving it as soon as possible.

    I apologize once again for late response.

    Best regards.

    Check out our new Support Help Center!

    Check out our newest product - Amelia - Enterprise-Level WordPress Appointment Booking Plugin - promo price for launch period!

    Kind Regards, 
    Aleksandar Vukovic
    aleksandar.vukovic@tms-outsource.com

  •   Aleksandar replied privately
  •   Larry Kolinek replied privately
  •   Larry Kolinek replied privately
  • Larry Kolinek replied

    the solution you provided does not work... below has escape character for double quotes

    here  is a simplistic version that does not work:

    SELECT  concat('<a href=\"https://www.site.com/\"><img src=\"https://www.site.com/test.jpg\"></a>') AS Photo, Ad_Number FROM owner
    

    and if you create the view to include the escape character for double quotes, once the view is created with data, the escape characters disappear

  • Larry Kolinek replied

    I did more testing, you can't have any quotes or double quotes in the SELECT part of the query...

    none of the following SQL allows the global search to work:

    SELECT \"test\" as test, ID FROM table;
    SELECT \'test\' as test, ID FROM table;
    SELECT "test" as test, ID FROM table;
    SELECT 'test' as test, ID FROM table;
    
  •   Aleksandar replied privately
  • Larry Kolinek replied

    uhm... thanks for the video.

    so there MUST be a difference :( 
    Yes, I see your setup working correctly.

    Question, If you look at the database values in mysql, DO you still see the double quotes in the photo field text?

    also, when you mentioned this:
    As for quotes - The example You sent us is not what we meant. Apostrophes are used for selecting values, and if You have more of them in between the two it'll break the query. Same goes for quotes.

    In my prior example, I used everything in the "selecting values", none of them worked?

    I'm about to test again, your exact text to create the view... will let you know the results shortly.

    thanks

  •   Larry Kolinek replied privately