JSON_CONTAINS not returning result when used in prepared statement

Question

I am writing a subquery to show certain information from a journal if a character is shown. Here is my function.

function getcharacterstories($con, $characterid) {
    
    $id = '"'.$characterid.'"';
    
    $sql = "SELECT dtr_entries.entreetitle,
                   dtr_entries.entreeinternallink, 
                   dtr_entries.entreetimestamp
            FROM dtr_entries 
            WHERE JSON_CONTAINS(entreeattachments, ?, '$.character')
            AND dtr_entries.entreetype = 3";
    
    $stmt = mysqli_prepare($con, $sql);
    
    if(!$con->connect_errno) {
                
        $stmt = mysqli_prepare($con, $sql);

        if ($stmt) {

            if (!empty($characterid)) {
                
                mysqli_stmt_bind_param($stmt, 'i' , $id);
                
            }
        
            // shoot it all to the database

            if(!mysqli_stmt_execute($stmt)) {
    
                echo "error in following query:". $sql; 
                                    
                echo 'stmt error: '.mysqli_stmt_error($stmt);

            } else {
                
                mysqli_stmt_bind_result($stmt, $title, $link, $timestamp);

                while (mysqli_stmt_fetch($stmt)){
        
                    echo $title;
        
                }
                
                mysqli_stmt_close($stmt);
                
            }   
            
        } else {
            
            pre($con);
            
            echo $sql; 
                    
            die('mysqli error: '.mysqli_error($con));
            
        }
        
    } else {
        
        die( 'connect error: '.mysqli_connect_error() );
        
    }
    
}

The code on itself is correct, but returns empty (no errors what so ever). When i run this with numbers in the sql panel of phpmyadmin and i use double quotes arround my value in json contains, i actually get result, hence i reformatted the characterid to the id, but still no result.

The query in phpmyadmin that yields result is as following.

SELECT dtr_entries.entreetitle, dtr_entries.entreeinternallink, dtr_entries.entreetimestamp FROM dtr_entries WHERE JSON_CONTAINS(entreeattachments, '"1"', '$.character') AND dtr_entries.entreetype = 3

The data that is stored in the column entreeattachements is as following

{"character":"1","updated-by":"Lazarus","update-time":1599124101}

What am i doing wrong and how can i fix this?

This is btw the 10.2.27 MariaDB.

Answer 1

Problem

Originally $characterid may have been an integer, but you would have changed this here:

$id = '"'.$characterid.'"';

and as a result may not successfully bind as an integer here:

mysqli_stmt_bind_param($stmt, 'i' , $id);

Possible Solutions

Approach 1

You could consider ensuring binding as a string as shown below

mysqli_stmt_bind_param($stmt, 's' , $id);

It would be best to ensure $characterid is indeed an integer eg.

$id = '"'.intval($characterid).'"';

Approach 2

or updating your sql to quote the parameter as intended:

$sql = "SELECT dtr_entries.entreetitle,
                   dtr_entries.entreeinternallink, 
                   dtr_entries.entreetimestamp
            FROM dtr_entries 
            WHERE JSON_CONTAINS(entreeattachments, '\"?\"', '$.character')
            AND dtr_entries.entreetype = 3";

and using

$id = $characterid;

Additional Resources

Determining if there was an error during the bind -https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php