How to populate a form field via javascript with an SQL query of WordPress table

Question

I have a Contact 7 form with a hidden field labeled confirmation_number. I need to populate that field with an SQL query.

This is my SQL query

select count(*)
from wp_postmeta
where meta_value = "used"

This is the code I put in my functions.php

add_action( 'wp_footer', 'add_confirmation_number' );
function add_confirmation_number() {
  $page_id = get_the_ID();
  if($page_id===11699 || $page_id===11709 || $page_id===11710 ||$page_id===11729 ){
        $wpcf7 = WPCF7_ContactForm::get_current();
        global $wpdb;
        $confirmation_number = $wpdb->get_var(("SELECT count(*) FROM $wpdb->wp_postmeta  WHERE meta_value = 'used' "));
        return $confirmation_number;
    ?>

    <script type="text/javascript">
            document.getElementById('confirmation-number').value = <?php echo $confirmation_number ?>;
    </script>

    <?php
  }
}

I am getting an error Undefined property: wpdb::

But I have tried everything to fix this syntax and nothing works. I'm also not sure if I'm using $wpdb correctly. I'm a budding Wordpress dev, so any help would be appreciated!

Answer 1

I see a few things. One, is that your script is probably never loading because you have as return $confirmation_number which would stop propagation of the function. The second is that $wpdb->table_name properties don't include the prefix. The postmeta table $wpdb->postmeta. You can also use $wpdb->prefix to add the prefix to tables that haven't been added to the global $wpdb object if you need to access those.

I've cleaned up a few things as well, but removing the return statement, and changing $wpdb->wp_postmeta to $wpdb->postmeta should suffice.

add_action( 'wp_footer', 'add_confirmation_number' );
function add_confirmation_number() {
    $confirmation_page_ids = array( 11699, 11709, 11710, 11729 );

    if( in_array(get_the_ID(), $confirmation_page_ids) ){
        global $wpdb;
        $wpcf7 = WPCF7_ContactForm::get_current(); // Is this necessary? No other calls to $wpcf7 below

        $sql = "
            SELECT count(*)
            FROM   $wpdb->postmeta
            WHERE  meta_value = 'used'
        ";

        $confirmation_number = $wpdb->get_var( $sql );

        if( $confirmation_number !== null ){
            printf( '<script type="text/javascript">
                document.getElementById("confirmation-number").value = %d;
            </script>', absint($confirmation_number) );
        }
    }
}