SQL statement in Wordpress

Question

I'm trying to run an SQL statement on a Wordpress page. I've already tested it in the phpmyadmin and that works:

SELECT post_id FROM wp_postmeta WHERE meta_value = "9bf2c7f9760393fa83e65ad455e00243"

The result on this is the post_id 20

This is my code in the Wordpress template:

<?php
    global $wpdb;
    $uniqueId = $_GET['id'];

    $getPostId = $wpdb->get_var(
        $wpdb->prepare("SELECT post_id FROM " . $wpdb->prefix . "wp_postmeta WHERE meta_value = %d", $uniqueId)
    );
    

    echo $getPostId;
?>

It should echo the value 20, but it does nothing. I've also tested if the variable $uniqueId is set and that's the case.

Answer 1

There are two problems with your code.

1. You have a typo in your query:

"SELECT post_id FROM " . $wpdb->prefix . "wp_postmeta WHERE meta_value = %d"

Should be:

"SELECT post_id FROM " . $wpdb->prefix . "postmeta WHERE meta_value = %d"

You're repeating the wp_ prefix twice:

... $wpdb->prefix . "wp_postmeta WHERE ...

2. As Mike pointed out, %d expects an integer. Your code suggests it should be a string so you should be using %s instead:

$getPostId = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT post_id FROM " . $wpdb->prefix . "postmeta WHERE meta_value = %s",
        $uniqueId
    )
);

Updated code:

<?php
global $wpdb;
$uniqueId = $_GET['id'];

$getPostId = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT post_id FROM " . $wpdb->prefix . "postmeta WHERE meta_value = %s",
        $uniqueId
    )
);

echo $getPostId;
?>

Answer 2

The %d placeholder in the prepare statement expects an integer. But you are assigning a string. So in this case you should use the string placeholder which is %s.

New code:

<?php
    global $wpdb;
    $uniqueId = $_GET['id'];

    $getPostId = $wpdb->get_var(
        $wpdb->prepare("SELECT post_id FROM " . $wpdb->prefix . "wp_postmeta WHERE meta_value = %s", $uniqueId)
    );
    

    echo $getPostId;
?>