Database query not working in WordPress template page

Question

Totally New to WordPress and I keep seeking answers as why is the same code that totally works is not working in WordPress.

$arr = array();
if (!empty($_POST['keywords'])) {
    $keywords = $db->real_escape_string($_POST['keywords']);
    $sql = "SELECT result_id, end_home_odd, home_name FROM tbl_live WHERE home_name LIKE '%".$keywords."%'";
    $result = $db->query($sql) or die($mysqli->error);
    if ($result->num_rows > 0) {
        while ($obj = $result->fetch_object()) {
            $arr[] = array('id' => $obj->result_id, 'home' => $obj->end_home_odd, 'title' => $obj->home_name);
        }
    }
}
echo json_encode($arr);

And in WordPress template page, tried this:

$arr = array();
if (!empty($_POST['keywords'])) {
    $keywords = $_POST['keywords'];
    $sql = $wpdb->get_results("SELECT result_id, end_home_odd, home_name FROM tbl_live WHERE home_name LIKE '%".$keywords."%'");
    $result = $db->query($sql) or die($mysqli->error);
    if (count($result) > 0) {
        while ($obj = $result->fetch_object()) {
            $arr[] = array('id' => $obj->result_id, 'home' => $obj->end_home_odd, 'title' => $obj->home_name);
        }
    }
}
echo json_encode($arr);

What am I missing that it won't work in WordPress? Can someone shed light on this?

for one thing, $mysqli->error won't work with a $db connection variable. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 26, 2018 at 19:58

Sally CJ · Accepted Answer · 2018-02-27 00:56:47Z

1

Here it is the WordPress way:

$arr = array();
if (!empty($_POST['keywords'])) {
    $keywords = $_POST['keywords'];

    // Properly escapes the POST data.
    $like = '%' . $wpdb->esc_like( $keywords ) . '%';
    $sql = $wpdb->prepare( "SELECT result_id, end_home_odd, home_name FROM tbl_live WHERE home_name LIKE %s", $like );

    $result = $wpdb->get_results( $sql );
    if ( ! empty( $result ) ) {
        foreach ( $result as $obj ) {
            $arr[] = array('id' => $obj->result_id, 'home' => $obj->end_home_odd, 'title' => $obj->home_name);
        }
    }
}
echo json_encode($arr);

Tried and tested with WordPress 4.9.4.

answered Feb 27, 2018 at 0:56

Sally CJ

15.7k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database query not working in WordPress template page

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related