php - Display Results of SQL Query on WP site

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.

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.

• php
• mysql

Share Improve this question edited Jul 27, 2021 at 19:40 Tom J Nowell♦ 60.7k77 gold badges7777 silver badges147147 bronze badges asked Jul 27, 2021 at 19:35 wgfy_workingwgfy_working 111 bronze badge 4

• the code in your question is vulnerable to injection and escaping attacks. None of the sql queries are ran through $wpdb->prepare and have dynamic values inserted. Likewise you're embedding variables directly into HTML output without any escaping. You've also put the $mydb->close after the return statement, so it will never be reached – Tom J Nowell ♦ Commented Jul 27, 2021 at 19:41
• Also, return "$thing"; on its own should just be return $thing, you don't need to wrap it in quotes – Tom J Nowell ♦ Commented Jul 27, 2021 at 19:42
• @TomJNowell - thanks Tom. To be clear, the variables are being read in from the database and are not open for end users to submit - is it still best practice to obfuscate those? I am actively writing to get an MVP up - this guidance is very helpful; thank you! – wgfy_working Commented Jul 29, 2021 at 13:50
• Yes, just because it should be ok, doesn't mean it will be ok, the only time you don't use prepare is when the SQL query is static with no variables. Escaping takes all the uncertainty out of output by enforcing assumptions. e.g. if I do echo esc_url( $foo ) I will always get a URL, even if $foo contains javascript or a ransom message. It might be a garbled URL, but it is guaranteed to be a URL – Tom J Nowell ♦ Commented Jul 29, 2021 at 14:46

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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