Database Query plugin recommendations?

  • johnfork

    (@johnfork)


    6 years, 8 months ago

    Hi all,

    I have been swimming in the WordPress world for about a month or so, and I have hit a roadblock for a problem I’m looking to solve – hoping someone on here can point me in the right direction 🙂

    I have a mysql database with 3 linked tables, and I want my site to pull data from this on searching, and show results. However, the structure of my data doesn’t fall into the standard ‘posts’ format in WP – well at least I don’t think it does anyway.

    So my 3 tables then:
    -Author
    – Books
    – Word_In_Books

    In sql, they are all linked via foreign keys – and on a basic basic/html page it works as it should. However when I try to pull this data into a wordpress page, it’s a different story.

    As advised in the many tutorial videos that are out there, I have played with advanced custom fields, custom post types, but I just cannot seem to find a way for me to satisfy my requirements.

    When using a page builder like elementor, for example, I cannot seem to find the functionality where I can design a page with several blocks, and inside that block, I write a html/php script and it pulls back the result.

    Am I using the wrong product here, or am I just thinking about it the wrong way?!??

    Any help much appreciated!

    Thanks
    JF

Viewing 6 replies - 1 through 6 (of 6 total)
  • catacaustic

    (@catacaustic)

    6 years, 8 months ago

    Do you only want to show the search results, or have that data available in the WordPress backend for editing (and possible dislaying to the public)?

    If you want it to be editable via WordPress then the best solution is to look at creating custom post types, and importing the data into them. To do this won’t be straight-forawrd so you’d most likely need to build/write your own transfer script. That jsut takes a small bit of knowledge of how WordPress stores things in the database, so it’s not quite as hard as it sounds.

    If you just want to shwo the search results, you can create a page template, or a shortcode, that takes in the serach value and creates it’s own SQL query. The “easy” way is ti use the $wpdb object if you have these tables in the same database, or to create a new WPDB object to connect to a second database.

    Thread Starter johnfork

    (@johnfork)

    6 years, 8 months ago

    Cheers for advice @catacaustic,

    Yes I want the search function to be able to be available, and the results displayed – eventually, sorting options, ajax filtering, the works!

    So what you’re saying is that I would have to transfer the data in the mysql db into wordpress? Would that not seem like a doubling up of work?

    From my work so far, I understand that each page is technically a “post”? That’s the bit I’m having trouble grasping. I did play with trying to input some php into a post’s field, but it just feels weird – like I’m writing a dynamic entry into a table or something.

    If I do indeed need to transfer my db data into ‘posts’, then that would mean I techincally would have to import a post for each individual word? I’ve done the calcs, it’d make for potential 986,000 posts..yikes! Just doesn’t make sense to me when I should be able to pull in my results on the fly via direct sql queries, like the flat/non-formatted ex-wordpress php file I coded.

    Or maybe I’d be better off sticking to what I have and try to integrate it with the pages that elementor generates…but then I’m thinking it would be a real job to add in filters etc. later on…

    #confused

    catacaustic

    (@catacaustic)

    6 years, 8 months ago

    Do you want each record as a separage page, and searchable through the standard WordPress search? In that case, yes you will need to import everything into a custom WordPress post type (or page or post but that’s probably not the best idea).

    If you just want the data displayed on a single page with search, filter, etc all on that one page, then no everything can stay as it is and you can use custom queries to get the data that you need.

    As far as elementor goes, I can’t answer that as I’ve never used it before. What I said about shortcodes or page templates still stands as that is the most likely best way to display everything.

    Thread Starter johnfork

    (@johnfork)

    6 years, 8 months ago

    I have taken your advice @catacaustic, and made a custom template for my custom post type, and entered the php into the single_custom.php file. All good!

    Now when I try to adapt my current code into the elementor divs, it’s a no-go – although the logic is sound and the code correct, the php returns a blank for all fields.

    I’m definitely one step closer to where I want to be, but alas I think I will have to go another route and maybe look for another page builder (that isn’t elementor) to
    be able to read my php in the template php file.
    Still I find it hard to believe that a page builder within an application that is solely based on php cannot read php code…it doesn’t seem right.

    Then again, I could be going about this from a completely incorrect approach.

    In any case, thanks again for your valuable insight 🙂

    catacaustic

    (@catacaustic)

    6 years, 8 months ago

    As a note, no page builders will read teh PHP (or HTML) in your template file. That’s not what they are made for. The template files are meant to take the content and display it, not the other way around.

    The reasont that I suggested what I did was so that you don’t have to do anything at all in any page builder. For what you want, there’s no reason to involve apage builder at all because it shouldn’t have any sort of control over what you want to end up with.

    Thread Starter johnfork

    (@johnfork)

    6 years, 8 months ago

    Noted..and I agree with you – sticking php into a style template is not a good idea!

    The only reason I am using a page builder, is to easily mock up a draft structure, menus, sub-menus, animations, lightboxes, etc etc. and make a half decent looking site. I already have build the bones of the site outside of elementor, but not being a coder, it’s difficult to design and build a proper site that’s full responsive, with all the bells and whistles of a page builder.

    Perhaps the best way forward would be to build without a page builder (just vanilla WordPress, and go from there).

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘Database Query plugin recommendations?’ is closed to new replies.