Display data on Word Press site posts and pages from mysql table

Question

I am new with word press and I have a table in phpmyadmin which has the data which i want to display in my word press site on different posts and pages. I have searched a lot around and came up with a plugin named Allow PHP in Post and Pages. This plugin allows you to write php script in posts and pages. I have tried a small code in my page post but it just gives me Resource id #147 in display but not the result i want. This is the code which i tried in one of my post.

[php]
$connect = mysql_connect("localhost", "root", "");
mysql_select_db("ahadith");
$query = mysql_query("SELECT * FROM muslim");
echo ($query);
[/php]

Is there anyone who can help me with this. Or is there another way through which i can fetch desired data from my database and show it in my word press site. Thank you.

Answer 1

Forget about connecting to your database while WordPress is ready and connected by default. Go after wpdb class for DB-specific tasks.

First, call the global $wpdb variable which is the db class, and then use few methods to query your specific custom table.

Not sure about the table structure? describe it first:

global $wpdb;
$table = $wpdb->prefix . "muslim";
$query = $wpdb->get_results( "DESCRIBE $table" );
echo var_dump ( $query );

Now selecting results from this table:

global $wpdb;
$table = $wpdb->prefix . "muslim";
$results = $wpdb->get_results( "SELECT * FROM $table" );
echo var_dump ( $results );

I am assuming the table muslim is there ready and added correctly. right?

Say you got results from the previous tweak, you can just iterate through them using a proper loop.

You can save this in a custom shortcode to be used in posts/pages as you stated, just make sure to read the API about how shortcodes work

add_shortcode('wpse_233021_shortcode', function(){
    global $wpdb;
    $table = $wpdb->prefix . "muslim";
    $results = $wpdb->get_results( "SELECT * FROM $table" );
    ob_start();
    echo var_dump( $results );
    return ob_get_clean(); // or iterate through and put into an HTML table
});

Now there's this [wpse_233021_shortcode] you got, to insert into page/post content.

Assuming that it worked, don't always make calls to your DB when not necessary, so try to cache data and destroy cache upon data update or let transients expire in a given delay, 1 week maybe, you can use Transients API for caching, it's very clear and simple and pretty much effective for non-persistent caching