MySQL Select within WP Page template

Question

I have one WP page which contains a form that posts data to a table in the Wordpress database called "members", data such as first name, surname, email, role and bio. Table posts fine and data is stored fine.

I then have a second page where I want to list the data from the table, I have got as far as the following:

$myrows = $wpdb->get_results( "SELECT first_name, surname FROM members" );
print_r ($myrows);

The above code is displaying an array like this:

Array ( [0] => stdClass Object ( [first_name] => testy [surname] => test ) [1] => stdClass Object ( [first_name] => adey [surname] => jones ) [2] => stdClass Object ( [first_name] => bob [surname] => smith ) [3] => stdClass Object ( [first_name] => final [surname] => test ) )

Could I please get some advice how to actually display the data in a table? I tried echo instead of print_r but just got "array" on the front end.

I'm fairly new to this side of MySQL commands.

Thanks.

Edit: Thanks to gdaniel for the codex link, I have amended code to:

$myrows = $wpdb->get_results( "SELECT first_name, surname FROM members" );
foreach ( $myrows as $row ) {
echo $row->first_name;
}

and as expected this is listing the first names (one after the other)... What I can't see in the codex is if I can set a variable for each of the columns (like $firstname = first_name or $surname = surname) and then echo a list item after the foreach with $surname in where i'd like it to go etc..

Answer 1

@Adrian,

You don't really need to assign anything to variables. You already have the information, you just need to loop through it and add the required HTML as you wish.

If you actually want to use a table you can do something similar to:

<table>
<?php 
$myrows = $wpdb->get_results( "SELECT first_name, surname FROM members" );
foreach ( $myrows as $row ) {
echo "<tr><td>" . $row->first_name . "</td><td>" . $row->surname . "</td></tr>";
}
?>
</table>

That will start the table, loop through each result (adding as a table row with two columns) and then closing the table.

The same can be done with a list:

<ul>
<?php 
$myrows = $wpdb->get_results( "SELECT first_name, surname FROM members" );
foreach ( $myrows as $row ) {
echo "<li>" . $row->first_name . " " . $row->surname . "</li>";
}
?>
</ul>

Just make sure you keep the UL or the TABLE outside the loop.