0

I'm looking for some help building the base code to query my WP database, retrieve a table and then post this on a page. There seems to be a lot on the web to assist with this, however with limited php/sql coding background I'm finding it hard to wade through everything. I find things easier to learn by reverse engineering and building from there.

I've downloaded the plugin PHP code snippets (Insert PHP) which gives me the ability to add php code into posts (being deprecated in next version) or shortcodes.

I believe the following code should be sufficient to query the wp_post table and return the table data in an array.

global $wpdb;
$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->posts . ' LIMIT 10');

What I want to be able to do from here is generate a html table in a WP page using this data. How would I go about this?

Once I've got this base code sorted I can build off this and develop what I need.

Any help is greatly appreciated.

-1
$html= "<table>";    
foreach($result as $r){
  $html .="<tr>";
  $html .="<td>".$r->post_title."</td>";
  $html .="<td><a href='".get_the_permalink($r->ID)"'>".$r->post_title."</a></td>";
  $html .="</tr>";
}
$html .="</table>";
echo $html;

To avoid the depreacated usage of PHP code snippets you can wrap it in a shortcode and use anywhere in a page/post content editor

function Stack_308511_post_grid( $atts ) {
 $atts = shortcode_atts( array(
  'limit' => 10,
  ), $atts,'limits'  );

 global $wpdb;
 $html = "";

 $result = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix.'posts LIMIT '.$atts['limit']);
 $count = $wpdb->num_rows;
 if($count >0){
  $html .="<table>";    
  foreach($result as $r){
    $html .="<tr>";
    $html .="<td>".$r->post_title."</td>";
    $html .="<td><a href='".get_the_permalink($r->ID)."'>".$r->post_title."</a></td>";
    $html .="</tr>";
   }
  $html .="</table>";
}

return $html;
}
add_shortcode( 'postGrid', 'Stack_308511_post_grid' );

So in the html editor you can use

  • [postGrid] which will use the default 10 post limit or i.e.
  • [postGrid limit=20] which will pick the latest 20 posts
| improve this answer | |
  • So, If I understand this correctly, The first code section should print a html table from the code I had in my question? The second code block is a consolidated (all in 1) "query and print" shortcode solution? – Morts81 Jul 14 '18 at 7:46
  • yes correct..try it it's not fully testedPlease note that the query in your code :$result = $wpdb->get_results('SELECT * FROM ' . $wpdb->posts . ' LIMIT 10'); is wrong it should be $wpdb->prefix.'posts' instead of $wpdb->posts – Andrea Somovigo Jul 14 '18 at 7:52
  • Cool, I'm getting a result print out using $result = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix.'posts' . ' LIMIT 10'); and then print_r($result);. So I know the query is working. Just struggling to get anything to print out with the $html code. I'll take some time to work through it and see what I need to change. You've been a huge help, thankyou so much – Morts81 Jul 14 '18 at 8:06
  • But why do you query posts using raw SQL? – Krzysiek Dróżdż Jul 14 '18 at 9:46
  • quite newbie here, just to ask : why downvote? – Andrea Somovigo Jul 15 '18 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.