Support » Developing with WordPress » Routing with custom database table – WordPress

  • I have the following setup
    database structure:
    albums-id,name,slug…
    gallery – id, albums_id, number…
    pages- id, gallery_id, file_name…
    and I want to show them like:

    
    http://example.com/albums/ - list of albums
    http://example.com/albums/slug/ - list of galleries in that album
    http://example.com/albums/slug/gallery-number/- show all photos for the gallery
    http://example.com/albums/slug/gallery-number/1-of-.../ - view of photo
    

    I started by creating a albums page and then a plugin.
    With shortcode I show list of albums and witch MyGallery_Class I’m trying to handle the rest of the queries.
    In album_page.php with IF I check what to show and select it from the database.
    I feel like something is wrong with the way the code is written because I do not use WordPress to deal with the verification of url.
    Is this the only way I can solve this problem?

    
    my-gallerys.php
    
    add_shortcode('albums_list_shortcode', function()
    {
     global $wpdb;
       $table = "albums";
       $results = $wpdb->get_results( "SELECT * FROM $table" );
       ob_start();
       foreach($results as $row_album){
      ?> <a>slug;?>/" title="link"><?echo $row_album->slug;?></a>
       <?
       }
     return ob_get_clean(); 
    });
    //[albums_list_shortcode]
    
    class MyGallery_Class
    {
    
        public function init()
        {
    
        	add_filter( 'template_include', array( $this, 'include_template' ) );
        	
            add_filter( 'init', array( $this, 'rewrite_rules' ) );
        }
    	
       public function include_template( $template )
       {
    	
            $album_page = get_query_var( 'album_page' );
    
               if ( $album_page ) {
    		
     
    	$file_name = 'album_page.php';
        $template = dirname( __FILE__ ) . '/templates/' . $file_name;
    		
    		}
     
            return $template;
    			
        }
    
        public function flush_rules()
        {
            $this->rewrite_rules();
    
            flush_rewrite_rules();
        }
    
        public function rewrite_rules()
        {
            add_rewrite_rule( 'albums/(.+?)/?$', 'index.php?album_page=$matches[1]', 'top');
            add_rewrite_tag( '%manga_page%', '([^&]+)' );
        }
    
    }
    
    add_action( 'plugins_loaded', array( new MyGallery_Class, 'init' ) );
    
    // One time activation functions
    register_activation_hook( PATH_TO_PLUGIN_FILE, array( new MyGallery_Class, 'flush_rules' ) );
    
    • This topic was modified 3 months, 1 week ago by  Exword.
    • This topic was modified 3 months, 1 week ago by  Jan Dembowski. Reason: Fixed formatting
Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator bcworkz

    (@bcworkz)

    There are all sorts of ways to handle your “problem”. It’s not even a problem, more of a data schema design exercise 🙂 For the most part, if you find yourself defining several custom tables, you are likely not leveraging the default WP schema to its best advantage. Custom tables can work just fine, but the point of WP is to save you from creating your own CMS system. There are situations where custom tables are warranted, such as when certain data fields need their own index because of the nature of common queries that need to be frequently run and such fields are not part of the WP schema.

    Based on your desired URL structures, they look a lot like those for a custom post type. Why couldn’t albums be a custom post type instead of a custom table? It’ll make things a lot easier. Much or all of what you need will be done by default. If the post type is hierarchical, you could make gallery pages children of their respective album. If you link photos in the galleries to their attachment pages, your entire URL structure will be handled automatically by WP. Other than code to add the custom post type, you may not need any other code. The schema is implemented by how hierarchical posts relate to each other, not by custom code or tables.

    I also intended to use custom post type in the beginning. But then another problem come.
    The gallery I’m trying to transfer to WordPress was written by me when I was learning PHP back in 2008 and since then is used and in ‘pages’ I already have about 15,000 records and any changes to the database may break the links.
    The reason I want to switch to a new system is that I want to add extra functionality and the old PHP code is a mess and to work on it is simply not worth the effort.

    Moderator bcworkz

    (@bcworkz)

    A simple SQL query can change post types in the DB. More complex changes can be done by a one time PHP script. It doesn’t have to be that big a deal no matter the volume. The query might need to be limited to n hundred records to avoid timeouts, so with a large volume maybe the script has to be run a few times. Still not a big deal.

    The old URLs can be rewritten to the new scheme in either WP or .htaccess. Returning a 301 status will cause search engines to update their data and transfer whatever page rank. The problem will be if the rewrite code cannot distinguish between URLs to rewrite and those that need to remain.

    If you are switching to WP, but keeping the same DB tables, you are not really switching to WP. You would have a stand alone site passed through WP. You’re better off with a parallel WP installation for what new needs you have and leave what you already have alone.

    While making a proper conversion could be painful, it will get worse the longer you prolong it. At some point you will have no choice and it will be really ugly. IMO, it’s better to get it over with now than wait. It’s up to you, it’s your site. I’ll help in any way I can either way, but for custom data that is not a WP object, that will be limited.

    If you are rewriting URLs for non-WP data, you may be better off using .htaccess rules instead of WP rewrites. The queries will work better if you keep WP out of it.

    Thank you very much for your help.

    For now I will put on hold the moving to WordPress and think about what is the best option. Because it seems that everything I do until now is to go against WordPress not with it. Anyway the only reason I wanted to use it is to have one admnin panel.

    Best Regards

    Moderator bcworkz

    (@bcworkz)

    You’re welcome. With the scheme you were contemplating, to work out of the WP admin panel, you’d have to build a custom admin UI for your data. Again, that would be better off done outside of WP. To truly make proper use of WP capabilities, you need to convert your data into WP data. You would need a custom conversion script to do so. Writing one needn’t be that difficult. Definitely give this some thought, it’s an important decision. Regards to you as well.

Viewing 5 replies - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.