Using Dynamic Data Pulled from a MySQL Table in a WordPress Page

Question

I'd like to pull data from a mySQL database and put it in some tables in a WP page. I'm using WP 3.1.1.

Imagine that I've got a table of sales figures that I want to pull from a mySQL database and then display them in a table on a WordPress page.

I know just enough php to make a db connection, pull the data, and echo it out. But, I'm not sure what is the best way to do this in conjunction with WordPress.

a) Is it okay if I put my tables in the WordPress database? (This way I could use the WP's database connection). Would they cause some sort of conflict? Would they be over-written when I upgrade WP?

b) Where should I put my php code? Can I just plop the php in the WP's HTML editor when I create a new page? If not, should I just make a php file and include it somewhere? If so, where would I put the include line?

c) Is there a plugin that allows you to pull data from a database? I've seen some table plugins, but the data had to be input by hand or pulled from XML. Unfortunately, I've got too many tables to input the data by hand.

Any suggestions?

Thank you!

-Laxmidi

Answer 1

Yes, you can store your data in your own tables in WordPress database. There are few things that you need to take care of:

1. Make sure your data can't be really represented using the built-in Wordpress content types.
2. Prefix your tables with WordPress Prefix + your prefix. WordPress table prefix is $wpdb->prefix.
3. Use the global $wpdb to query your custom tables, don't make a new connection.

Now to answer your questions:

1. Yes, it is okay to store data in custom database tables. It won't conflict if you prefix your table names.

2. There are many places to put your PHP code. But don't put your PHP code in the HTML editor.

   i) You can create your own plugin and for that you can put the code in plugin file which can be stored in wp-content/plugins/ directory.

   ii) You can write your code in your theme's functions.php file.

3. You don't need a plugin to query tables in Wordpress. Just read about $wpdb.

Answer 2

You can put the tables in the WordPress database. The wpdb class can query any table:

$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

You could set this up as a plugin/shortcode, or you could put it directly in a custom page template.