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
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
1 Answer
Reset to default 0If 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:
$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_Aconstant 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.
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', ... ).I presumed your WordPress database (table) prefix is
wp_, and instead of hard-coding the prefix, you should use$wpdb->prefixto 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>';
$wpdb->get_results()returns an array, so you should just doforeach ( $sql as $row ) { echo $row->dojangName; ... }. Have a look at the reference. – Sally CJ Commented Apr 1, 2020 at 0:36mysql_query, notget_results. Does that make any difference to your answer? – Daniel Florian Commented Apr 1, 2020 at 1:06$wpdbdoesn't have amysqli_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 theforeachin my previous comment? Did it work? – Sally CJ Commented Apr 1, 2020 at 1:17