global $wpdb displaying garbage data along with re

问题:

I have a custom table in MySQL database, which I am trying to query using global $wpdb. I have defined my query using information available from the following two sources:

1. https://codex.wordpress.org/Class_Reference/wpdb
2. https://wordpress.stackexchange.com/questions/233021/display-data-on-word-press-site-posts-and-pages-from-mysql-table

This is how the data is in phpMyAdmin:

The query seems to be working fine as it selects data from my custom table, however the output seems to contain garbage/unnecessary information apart from the information that is available in the table.

I want it to display as a table/similar to how it is displayed in phpMyAdmin, where I am able to associate the SrNo, Compound etc with other columns in the table:

add_shortcode('wpse_233031_shortcode', function(){
    global $wpdb;
    $myrows = $wpdb->get_results( "SELECT `SrNo`, `Compound` FROM PNaphtha");
    //$results = $wpdb->get_results( "SELECT `SrNo`, `Compound` FROM PNaphtha" );
    ob_start();
    echo var_dump($myrows );
    //return ob_get_clean(); 
});

I get the following results

array(10) {
    [0]=> object(stdClass)#6275 (2) {
        ["SrNo"]=> string(1) "2" 
        ["Compound"]=> string(12) "abietic acid"
    }
    [1]=> object(stdClass)#6274 (2) { 
        ["SrNo"]=> string(1) "3"
        ["Compound"]=> string(12) "acenaphthene"
    }
    [2]=> object(stdClass)#6273 (2) {
        ["SrNo"]=> string(1) "4"
        ["Compound"]=> string(6) "acetal"
    }
    [3]=> object(stdClass)#6272 (2) {
        ["SrNo"]=> string(1) "5"
        ["Compound"]=> string(12) "acetaldehyde"
    }
    [4]=> object(stdClass)#6271 (2) {
        ["SrNo"]=> string(1) "6"
        ["Compound"]=> string(9) "acetamide"
    }
    [5]=> object(stdClass)#6270 (2) {
        ["SrNo"]=> string(1) "7"
        ["Compound"]=> string(11) "acetanilide"
    }
    [6]=> object(stdClass)#6269 (2) {
        ["SrNo"]=> string(1) "8"
        ["Compound"]=> string(11) "acetic acid"
    }
    [7]=> object(stdClass)#6268 (2) {
        ["SrNo"]=> string(1) "9"
        ["Compound"]=> string(16) "acetic anhydride"
    }
    [8]=> object(stdClass)#6267 (2) {
        ["SrNo"]=> string(2) "10"
        ["Compound"]=> string(7) "acetone"
    }
    [9]=> object(stdClass)#6266 (2) {
        ["SrNo"]=> string(2) "11"
        ["Compound"]=> string(19) "acetone cyanohydrin"
    }
}

Although, all of the information I queried is available here, there is also a lot of unnecessary information.

I tried the following changes, however none of them seems to be working

$myrows = $wpdb->get_row( "SELECT `SrNo`, `Compound` FROM PNaphtha"); // get_row

output_type changed to ARRAY_A, ARRAY_N, OBJECT_K

echo var_dump changed to echo array

I will appreciate if you could please advise on how to get the results to format as a table or array. Also, I have placed the above code in the functions.php file of my theme. Is there a better way to do this?

回答1:

So, you're getting object as output, that's why you're seeing that information in there. Make the following change. Add 'ARRAY_A' (associative array) to the end of your function call.

$wpdb->get_results( "SELECT `SrNo`, `Compound` FROM PNaphtha", ARRAY_A);

That will keep your query from being returned as an object and your var_dump will look the way you expect.

回答2:

It may be because you are using var_dump

The var_dump function displays structured information about variables/expressions including its type and value.

I would recommend using print_r

The print_r() displays information about a variable in a way that's readable by humans. array values will be presented in a format that shows keys and elements.

You should be able to see a change if you change

var_dump($myrows )

to

print_r($myrows)

Here's how I did what you are wanting (Without echoing the result):

$sql = "SELECT `Srno`,`compound` FROM `pnaphtha`";
$result = $conn->query($sql);

echo "<table>";
while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row['Srno']."</td><td>".$row['compound']."</td></tr>";
    }

 echo "</table>";

However since you are using WordPress I would suspect it would be something along the lines of

global $wpdb;
$sql =  "SELECT `SrNo`, `Compound` FROM PNaphtha";
//you may need to add ARRAY_A

$myrows = $wpdb->get_results($sql);

echo "<table>";
while($myrows = $result->fetch_assoc()) {
    echo "<tr><td>".$myrows['SrNo']."</td><td>".$myrows['Compound']."</td></tr>";
}
echo "</table>";

I can't actually test the wordpress version as I can't be bothered to set up a wordpress install haha