Display Results of SQL Query on WP site

Question

I hope this is not redundant - but there always seems to be enough different that I cannot get things working.

I am building a function in my functions.php file - all other functions work fine.

$query = $mydb->get_results("select * from table");

//This works just fine in mysql and I can do things like count(*) and get that to report back.

But for whatever reason I cannot get the table to return results that output to the wordpress page

return "$query";
return "$query[0]";
return "$query["wk"]";

The only output I get to have displayed on any page is the word Array where I am expecting the output to be.

So something like this works perfectly -

function VtVML_visitor(){
    $home = get_field('home');
    $visitor = get_field('visitor');
    $mydb = new wpdb('OMMITTED');
    
    $hWins = $mydb->get_var("SELECT count(*) FROM `table` where v = '$home' and h = '$visitor' and h_ml_win = 1");
    $hLoss = $mydb->get_var("SELECT count(*) FROM `table` where v = '$home' and h = '$visitor' and h_ml_loss = 1");
    $hTie = $mydb->get_var("SELECT count(*) FROM `table` where v = '$home' and h = '$visitor' and h_ml_tie = 1");
    $vWins = $mydb->get_var("SELECT count(*) FROM `table` where h = '$home' and v = '$visitor' and v_ml_win = 1");
    $vLoss = $mydb->get_var("SELECT count(*) FROM `table` where h = '$home' and v = '$visitor' and v_ml_loss = 1");
    $vTie = $mydb->get_var("SELECT count(*) FROM `table` where h = '$home' and v = '$visitor' and v_ml_tie = 1");

    $tWins = $vWins+$hWins;
    $tLoss = $vLoss+$hLoss;
    $tTie = $vTie+$hTie;
    
    return "$visitor vs $home overall:<br>$tWins - $tLoss - $tTie";
    
    $mydb -> close();
}

add_shortcode('VtVML','VtVML_visitor');

But if I repurpose this into a new shortcode and change the queries to pull specific fields:

$schedule = $mydb->get_var("SELECT wk,v,h FROM `table` where h = '$home'");

return "$schedule";

I see the error described above. I am assuming I need to do a foreach or while command - but all attempts to use these have not yielded results.

Answer 1

I see the error described above. I am assuming I need to do a foreach or while command - but all attempts to use these have not yielded results.

Yes, shortcodes return strings, not arrays/lists. You need to convert your array/list into a single string. However, get_var assumes a single result. If you want multiple results you will need to use a different method of WPDB that allows multiple results, such as get_results:

<?php

global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );

The code also needs to use $wpdb->prepare to insert variables into the SQL statement. E.g.:

$wpdb->query(
   $wpdb->prepare(
      "
      INSERT INTO $wpdb->postmeta
      ( post_id, meta_key, meta_value )
      VALUES ( %d, %s, %s )
      ",
      10,
      $metakey,
      $metavalue
   )
);

Notice that the $metakey and $metavalue variables are not inside the string as that would be insecure. Instead they have placeholders %s that get replaced with sanitised versions that prevent them from breaking the query or inserting malicious values

See the official docs for examples on using get_results