Create Pages from Database

Question

i've recently decided to create a website with wordpress.

My goal is to a visualize data stored in a MySQL database. Each id in the database should represent one page on the website. From what i've read a custom post type or a custom page template should do the trick.

But my biggest problem is that pretty much all tutorials i've read or watched so far explain how to create a page for each "product" manually. My database currently has ~44.000 entries and there are more to come on a weekly basis, so this can't be the right way.

Is there some way to dynamically create a page based of a template after the user searched for it?

Answer 1

There seems little point in creating a new post entry for each of your products if your table of products already has all of the information you wish to display.

Your best bet is to create a single page for example "Product" and then setup your own rewrite rule, using add_rewrite_rule, so that you can setup links such as /product/some-product-alias. You would then need to perform a query on the product alias to lookup the product information from your product table and then display this on the template (either use page-product.php or create a template and assign this in the admin)

There's a little more information on setting up the redirects in this answer of mine.

Answer 2

You could add a custom template when certain URL patterns are matched and then print on it whatever you want using your own functions to return your database query results dynamically.

The following sample adds a new custom template suggestion. So that always when the URL contains foobar as first segment and a non-empty second segment the foobar.php template from your current theme will be rendered.

add_action('template_include', 'template_suggestions');
function template_suggestions($template) {

  $segments = explode('/', parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH));
  $foobar_template = locate_template(['foobar.php']);

  if (isset($segments[1]) && isset($segments[2]) && $segements[1] === 'foobar' && $foobar_template !== '') {

    status_header(200);
    return $foobar_template;
  }

  return $template;
}

Now use any other custom function to return your database query results.

function foobar_results() {

  $myrows = [];
  $segments = explode('/', parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH));

  if (isset($segments[1]) && isset($segments[2]) && $segements[1] === 'foobar') {

    global $wpdb;
    $myrows = $wpdb->get_results( "SELECT * FROM mytable WHERE id = {$segments[2]}" );
  }

  return $myrows; 
}

And then in foobar.php simply loop through the results.

<?php foreach(foobar_results() as $row): ?>
  <?php print $row->id; ?>
<?php endforeach; ?>