Support » Developing with WordPress » Searching a custom database table

  • tkdoherty

    (@tkdoherty)


    I installed the Gwolle Guestbook WordPress plugin. The guestbook is simple and clean, and does everything I’d like, except that it uses a custom database table and is not searchable. I’d like to customize my site’s search to include the guestbook, but I’m a novice when it comes to scripting. The guestbook’s table is named wp_gwolle_gb_entries and has a column named content which contains the guestbook entries. Is there a simple way to add this content to WordPress search? I’m using the Naturespace theme, if that makes a difference. (BTW, I did ask this on the support page for Gwolle Guestbook, but the plugin’s author could not recall how to do it.)

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator bcworkz

    (@bcworkz)

    Not exactly. The WP search function only searches title and content columns in the posts table. You would need to replace the WP search SQL query with your own. This is possible if the posts table can be related to the guestbook table through some sort of JOIN. There needs to be a column in each table with data in common. This is usually an ID of some sort.

    You say the table is not searchable. Do you mean WP just doesn’t search it or that the data is stored in a manner that cannot be easily searched, like base64 encoded data? The first can be accommodated if there is a common element with posts. The latter would require storing the data differently.

    If the data is not encoded but is totally unrelated to posts, you could just run two queries with the same search criteria. The WP one and a second one for guestbook entries. The results would have to be listed separately in this case because you cannot run non-post data through the standard WP loop.

    Thank you for the quick response. The guestbook table isn’t encoded etc. But, as you suggested, it has nothing to do with posts and so isn’t currently searched with the default WP search. A second query would work fine. But I don’t know how to set this up -any pointers or examples would be greatly appreciated, thanks.

    Moderator bcworkz

    (@bcworkz)

    I’m sorry the quick responses did not continue. Generally the response times are highly variable, from mere minutes to days.

    You would compose your own SQL query to get the desired data. You can use the global $wpdb object’s methods to execute the query. For example, $wpdb->get_results(). You can get the search terms from $_GET[‘s’] or global $wp_query->get(‘s’). You can add the code to do the query and display results to your theme’s search results template. When modifying themes, you should create a child theme to contain your custom work so theme updates do not overwrite your data.

    If your posts search results are paginated, you could paginate the guestbook results in parallel by by getting the ‘paged’ query var from the global $wp_query object, which should be the main search query object, e.g: $wp_query->get(‘paged’) Paged will contain the requested page number. Limit the guestbook results per page by determining the offset into the results based on page number. Use the SQL LIMIT clause accordingly. For example, if you want 10 results per page, and page 2 is requested, Use LIMIT 10, 10 in your SQL to get results 10-19. The entries per page do not need to match the posts per page because you are calculating your offset independently.

    If you would rather the guestbook results had their own independent pagination, that is possible by using Ajax techniques to get a new page of data without reloading the entire page. In this case you need to manage your own page numbering independent from ‘paged’. Keep track of the current page in a way that will persist when other searched post pages are requested. Session variables perhaps. Your script would also need to know when a new search is requested instead of a different page of the current search. Maybe by comparing previous and current search terms? As you can see, parallel pagination is much easier.

    Dinamicore

    (@dinamicore)

    Hello support:

    I have exactly the same need; a table created by a plugin.

    I need to make a custom search linked to that, and only that table, to display the results so the visitor can sort from those results, click on them, and go to the corresponding page.

    Nevertheless, I canĀ“t find a specific reference to the code to accomplish this.

    Can you provide that code?

    Thanks

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Searching a custom database table’ is closed to new replies.