1

I am creating a custom plugin for our martial arts school and have come up against a problem that is doing my head in. No doubt I am missing something obvious but have never really dealt with Wordpress in this capacity before so rather than smash my computer, thought I would ask the brains trust what I am doing wrong ;)

the following code works perfectly and generates the dropdown:

<?php
$servername = "localhost";
$username = "xxx";
$password = "xxx";
$dbname = "testing_db";

$conn = new mysqli($servername, $username, $password, $dbname);

?>
<html>
<body>

<select name="dojang">
        <option selected="selected">Choose…</option>
<?php 
$sql = mysqli_query($conn, "SELECT dojangName FROM wp_tmadm_dojangs");

while ($row = $sql->fetch_assoc()){
    $dojang = $row['dojangName'];
    echo "<option value='$dojang'>$dojang</option>";
}
?>
</select>

but the following doesn't work from within Wordpress:

<select name="dojang">
    <option selected="selected">Choose…</option>

<?php 
global $wpdb;

$sql = $wpdb->mysqli_query("SELECT dojangName FROM wp_tmadm_dojangs");

while ($row = $sql->fetch_assoc()){
    $dojang = $row['dojangName'];
    echo "<option value='$dojang'>$dojang</option>";
}
?>

</select>

Am I not connecting to the database correctly? Is there something else I am missing?

Appreciate any help. Thanks

EDIT: Apologies, I pasted the wrong code in the 2nd block - should have been mysql_query, not get_results

Improve this question
5
  • $wpdb->get_results() returns an array, so you should just do foreach ( $sql as $row ) { echo $row->dojangName; ... }. Have a look at the reference.
    – Sally CJ
    Commented Apr 1, 2020 at 0:36
  • @SallyCJ Apologies, I pasted the wrong code in the 2nd block - should have been mysql_query, not get_results. Does that make any difference to your answer?
    – Daniel Florian
    Commented Apr 1, 2020 at 1:06
  • $wpdb doesn't have a mysqli_query() method. That might be just a typo, but couldn't resist in saying it.. But then, if the table is in the WordPress database, why not just use $wpdb->get_results()? Have you tried the foreach in my previous comment? Did it work?
    – Sally CJ
    Commented Apr 1, 2020 at 1:17
  • @SallyCJ Thanks, I think this is where I am getting confused. Obviously a lot has changed since the last time I used php for anything meaningful :D. I am unclear how I get the $row into the <option> tags though
    – Daniel Florian
    Commented Apr 1, 2020 at 2:45
  • Well, I hope the answer helps you. :)
    – Sally CJ
    Commented Apr 1, 2020 at 4:33

1 Answer 1

Reset to default
0

If that testing_db is your WordPress database, then you should use the WordPress wpdb API/class to interact with the data in your custom table (wp_tmadm_dojangs).

So if this is how you do it in "regular PHP", i.e. using native PHP functions:

$sql = mysqli_query($conn, "SELECT dojangName FROM wp_tmadm_dojangs");

while ($row = $sql->fetch_assoc()){
    $dojang = $row['dojangName'];
    echo "<option value='$dojang'>$dojang</option>";
}

Then in WordPress, this is one way of how you can do it:

$results = $wpdb->get_results( "SELECT dojangName FROM {$wpdb->prefix}tmadm_dojangs", ARRAY_A );
var_dump( $results ); // for debugging

foreach ( $results as $row ) {
    $dojang = $row['dojangName'];
    echo "<option value='$dojang'>$dojang</option>";
}

Please check the $wpdb->get_results() (or wpdb::get_results()) reference for details on the function's syntax, parameters, etc, but what I can tell you from the above example are:

  1. $wpdb->get_results() returns an array of rows from the queried table and by default, each row is a PHP object with each column as a property in the object, e.g. $row->dojangName.

    However, in the code, you could see I used the ARRAY_A constant which gives us each row as an associative array with the table column as the array key, e.g. $row['dojangName'].

    But regardless it's an object or associative array, the value is of course the (raw) value in the corresponding database column.

  2. If you're retrieving a single column/field from the database table, then you can just use $wpdb->get_col() which returns the column values in an indexed array, i.e. array( 0 => 'value', 1 => 'value2', 2 => 'value3', ... ).

  3. I presumed your WordPress database (table) prefix is wp_, and instead of hard-coding the prefix, you should use $wpdb->prefix to get the prefix.

So using $wpdb->get_col(), the example I gave would be:

$results = $wpdb->get_col( "SELECT dojangName FROM {$wpdb->prefix}tmadm_dojangs" );
var_dump( $results ); // for debugging

foreach ( $results as $dojang ) {
    echo "<option value='$dojang'>$dojang</option>";
}

Btw, please make sure you escape the database values prior to presenting them to the user. E.g. echo '<option value="' . esc_attr( $dojang ) . '">' . esc_html( $dojang ) . '</option>';

Improve this answer
1
  • 1
    Thank you very much. It would seem I have a lot to un-learn :D
    – Daniel Florian
    Commented Apr 1, 2020 at 5:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.