SQL Statement in the Editor or Template

Depending on where the data needs to go, I’d suggest either do it as a custom template (which you’ve tried) or set up a shortcode for it.

I’d probably start off with a template if it’s only going to be used in one place. That way you’re not using that code anywhere else in the system where it’s not needed.

Also, for database queries, it’s mostly a good idea to use the $wpdb object. It has a lot of good built-in functions that will help out.

You did say that the template didn’t work, but how did it not work? If you can set up a pastebin with the code we could have a look through it.

I’m using the typical SELECT statement.

$servername = "localhost";
$username = "xxx";
$password = "xxx";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
 if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = mysqli_query($conn,"SELECT * FROM dsp_user_settings");
echo $sql['userId'];

• This reply was modified 8 years, 3 months ago by paulsvang.

I have a table with the column userId, globalShipping, freeShipping and I want to be able to pull them in. THe table is named dsp_user_settings.

If I can do it with $wpdb, I dont mind trying that route. I appreciate it Catacaustic!

The $wpdb way is easier, and doesn’t need you to set up any extra databaseo connections.

As a very broad example…

// You may need the next line, depending on the way your template is set up. You may be able to leave it out
global $wpdb;

$query = "SELECT userId, globalShipping, freeShipping FROM dsp_user_settings";
$result = $wpdb->get_results ($query);

foreach( $result as $row ) {
    echo '<p>ID: '" . $row->userId . "'</p>';
}

I will give that a try.

I modified it and got it to work. How would I go about just looking for one record so that I avoid the foreach loop.

 $query = "SELECT userId FROM dsp_user_settings WHERE userID = 1";
$result = $wpdb->get_results ($query);
echo  $result->userId ;

I tried what I modified and it didn’t work. Am I doing something wrong here?

• This reply was modified 8 years, 3 months ago by paulsvang.

You need to read up on the codex page for WPDB. That will show you how things work.

You are almost there, but for a single record you need to use a different function.

If you want a whole row, you can use this:

$query = "SELECT userId FROM dsp_user_settings WHERE userID = 1";
$row = $wpdb->get_row ($query);
echo  $result->userId;

If you want just a single value only, you can use this:

$query = "SELECT userId FROM dsp_user_settings WHERE userID = 1 LIMIT 1";
$value = $wpdb->get_var ($query);
echo  $value;

I will definitely read up on it. And thanks for your input again! Good night.

Your code worked by the way. Thanks a bunch!