How can I set the second value of the Editor->where to be a string input

necival

This is regarding the Datatables Editor, Server side PHP, Editor->where [ or Query->where ] :

How can I set the second value of the Editor->where to be a string input:

Editor::inst( $db, 'table', 'id' )
        ->fields(
                Field::inst( 'column1' ),
                Field::inst( 'column2' ),
                Field::inst( 'column3' )
        )
        ->where( 'column2', 'STRING_VALUE', '=' )
        ->process( $_POST )
        ->json();

I keep getting an error "STRING_VALUE" column does not exist in table... How do I specify that value to be a string input rather than trying to compare it as a column.

I also tried:

        ->where( function ( $q ) {
                $q->where( 'column2', ':value', '=', false );
                $q->bind( ':value', 'STRING_VALUE' );
         } )

But got the same error again.

allan

Hi,

Thanks for your question. This is really interesting, I'm afraid I don't have an immediate fix for you as I've not encountered this error before. The second parameter should automatically be defined as a bound string.

What version of Editor are you using and which database type?

Thanks,
Allan

necival

Datatables Editor @version 1.5.6

mysql Ver 14.14 Distrib 5.7.15, for Linux (x86_64)

necival

Also:

PHP 7.0.11-1+deb.sury.org~xenial+1

necival

Also I dug into the Editor Drivers libraries and the default is PARAM_STR in the prepare method:

                        $this->_stmt->bindValue(
                                $binding['name'],
                                $binding['value'],
                                $binding['type'] ? $binding['type'] : \PDO::PARAM_STR
                        );

allan

That is correct - it should automatically bind as a string. I'm afraid we'll need to get a little debug info to try and understand what is going wrong here.

In the php/Database/Drivers/Mysql/Query.php file you'll find a commented out line: file_put_contents( ... );. Could you comment that back in, and update the output path if required for your system. Then reload the page - it will dump the SQL query that the Editor PHP libraries are trying to use into the specified file. Can you show me what that query is please?

Thanks,
Allan

necival

By the way, I ended up creating a table to store the enum values. Then specify the STRING_VALUE in the leftJoin statement...

->leftJoin( '(select value, label from enum_labels
 where table_name="table" and column_name="column2") enums', 'table.column2', '=', 'enums.value' )

This worked for my project, but I never could set the ->where to the string value directly.