Using field value in WHERE statement on ->options

Lennart Oester

In my SQL file I have a huge problem using the value of the row in a where clause.
The hardcoded value 8069875 is present in the projects_keywords_elements.related_keywords

if I use it like this it works

Field::inst( 'projects_keywords_elements.related_keywords' )
        ->options( Options::inst()
        ->table( $_SESSION['project_keywords_database'] . '.related_searches' )
        ->value( 'id' )
        ->label( 'title' )
        ->where( function ($q) {
          $q->and_where($_SESSION['project_keywords_database'] . '.related_searches.keyword_id', '8069875', '=');
          } )
        )
        ->validator( Validate::dbValues() ),

But if I do this as I would expect to work I get empty result

Field::inst( 'projects_keywords_elements.related_keywords' )
        ->options( Options::inst()
        ->table( $_SESSION['project_keywords_database'] . '.related_searches' )
        ->value( 'id' )
        ->label( 'title' )
        ->where( function ($q) {
          $q->and_where($_SESSION['project_keywords_database'] . '.related_searches.keyword_id', 'projects_keywords_elements.related_keywords', '=');
          } )
        )
        ->validator( Validate::dbValues() ),

Whats the trick to use value from the db in the where clause

Lennart Oester

If I add the ,false param then I get an error stating that the column i get the following error

An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'admin_sbn.projects_keywords_elements.keyword' in 'where clause'"

even if i prefix the table with the database name since I join on differentdatabases

allan

I don't actually see where projects_keywords_elements.keyword' is defined in the code you show?

However, line 7 in the second block of code probably isn't doing what you want case Editor's PHP libraries will automatically "bind" the value given, in order to prevent SQL injection attacks. You can pass an optional fourth parameter in order to have it not do the binding, and thus let it treat the value given as an SQL statement (i.e. a column name).

The error you are getting isn't quite what I'd expect from that being the underlying issue, but from the code above I don't see where the error is coming from at all. There is no "keyword" column referenced on its own.

Allan

Lennart Oester

Heres all the code

Can you give anexample as I cannot find anything online

    <?php

    include( "dist/editor/lib/DataTables.php" );
    session_start(); // Bacuse the database.php conflicts in the controller with the Datatables.net

    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;


    $db->sql( 'set names utf8' );
    Editor::inst( $db, 'projects_keywords_elements' )
        ->field(

            Field::inst( 'projects_keywords_elements.project_id' )
            ->options( Options::inst()
            ->table( 'projects_keywords' )
            ->value( 'id' )
            ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),

            Field::inst( 'projects_keywords_elements.id' ),
            Field::inst( 'projects_keywords_elements.publish' ),
            Field::inst( 'projects_keywords_elements.enabled' ),
            Field::inst( 'projects_keywords_elements.cornerstone' ),
            Field::inst( 'projects_keywords_elements.published' ),

            Field::inst( 'projects_keywords.id' ),
            Field::inst( 'projects_keywords.user_id' ),
            Field::inst( 'projects_keywords.name' ),

            Field::inst( 'projects_keywords.domain_id' ),

            Field::inst( 'projects_keywords_elements.domain_category' )
            ->options( Options::inst()
            ->table( 'domains_categories' )
            ->value( 'category_id' )
            ->label( 'name' )
            ->where( function ($q) {
              $q->and_where('domains_categories.domain_id', $_SESSION['project_keywords_id'], '=');
              } )
            )
            ->validator( Validate::dbValues() ),
            Field::inst( 'domains_categories.name' ),

            Field::inst( 'projects_keywords_elements.domain_user' )
            ->options( Options::inst()
            ->table( 'domains_users' )
            ->value( 'user_id' )
            ->label( 'username' )
            ->where( function ($q) {
              $q->and_where('domains_users.domain_id', $_SESSION['project_keywords_id'], '=');
              } )
            )
            ->validator( Validate::dbValues() ),

            Field::inst( 'domains_users.username' ),

            Field::inst( 'projects_keywords_elements.keyword' )
            ->options( Options::inst()
            ->table( $_SESSION['project_keywords_database'] . '.keywords' )
            ->value( 'id' )
            ->label( 'keyword' )
            ->where( function ($q) {
              $q->and_where($_SESSION['project_keywords_database'] . '.keywords.id', '8069875', '=', true);
              } )
            )
            ->validator( Validate::dbValues() ),
            Field::inst( $_SESSION['project_keywords_database'] . '.keywords.keyword' ),

            Field::inst( 'projects_keywords_elements.related_keywords' )
            ->options( Options::inst()
            ->table( $_SESSION['project_keywords_database'] . '.related_searches' )
            ->value( 'id' )
            ->label( 'title' )
            ->where( function ($q) {
              $q->and_where($_SESSION['project_keywords_database'] . '.related_searches.keyword_id', '8069875', '=');
              } )
            )
            ->validator( Validate::dbValues() ),


            Field::inst( 'projects_keywords_elements.also_keywords' )
            ->options( Options::inst()
            ->table( $_SESSION['project_keywords_database'] . '.people_also_ask' )
            ->value( 'id' )
            ->label( 'title' )
            ->where( function ($q) {
              $q->and_where($_SESSION['project_keywords_database'] . '.people_also_ask.keyword_id', '8069875', '=');
              } )
            )
            ->validator( Validate::dbValues() ),

            Field::inst( 'projects_keywords_elements.videos' )
            ->options( Options::inst()
            ->table( $_SESSION['project_keywords_database'] . '.videos' )
            ->value( 'id' )
            ->label( 'title' )
            ->where( function ($q) {
              $q->and_where($_SESSION['project_keywords_database'] . '.videos.keyword_id', '8069875', '=');
              } )
            )
            ->validator( Validate::dbValues() ),

        )

        ->leftJoin( 'projects_keywords', 'projects_keywords.id', '=', 'projects_keywords_elements.project_id' )
        ->leftJoin( 'domains_categories', 'domains_categories.category_id', '=', 'projects_keywords_elements.domain_category' )
        ->leftJoin( 'domains_users', 'domains_users.user_id', '=', 'projects_keywords_elements.domain_user AND projects_keywords.domain_id = domains_users.domain_id' )
        ->leftJoin( $_SESSION['project_keywords_database'].'.keywords', $_SESSION['project_keywords_database'].'.keywords.id', '=', 'projects_keywords_elements.keyword' )
        ->where( function ($q) {
            $q->and_where('projects_keywords.user_id', $_SESSION['id'], '=');
            $q->and_where('domains_categories.domain_id', $_SESSION['project_keywords_id'], '=');
            $q->and_where('projects_keywords.domain_id', $_SESSION['project_keywords_id'], '=');
          } )
        ->debug(false)
        ->process($_POST)
        ->json();
    ?>

allan

Thanks for the full code. So taking this field:

Field::inst( 'projects_keywords_elements.related_keywords' )
        ->options( Options::inst()
        ->table( $_SESSION['project_keywords_database'] . '.related_searches' )
        ->value( 'id' )
        ->label( 'title' )
        ->where( function ($q) {
          $q->and_where($_SESSION['project_keywords_database'] . '.related_searches.keyword_id', '8069875', '=');
          } )
        )
        ->validator( Validate::dbValues() ),
 ```

And changing only the `where` condition to be:

```php
$q->and_where($_SESSION['project_keywords_database'] . '.related_searches.keyword_id', 'projects_keywords_elements.related_keywords', '=', false);

Results in the error:

Column not found: 1054 Unknown column 'admin_sbn.projects_keywords_elements.keyword' in 'where clause'"

<?php ! ?>

That is bizarre!

Could you send me the full JSON response from the server when that error happens please?

Thanks,
Allan

Lennart Oester

Yes. Thats the error I get.

This is the full json
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'projects_keywords_elements.related_keywords' in 'where clause'","data":[],"ipOpts":[],"cancelled":[]}

Can this be related to the fact that I use two different databasen in this ?

Lennart Oester

I set up debug as well.
Heres the full code for this

{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'projects_keywords_elements.related_keywords' in 'where clause'","data":[],"ipOpts":[],"cancelled":[],"debug":["Editor PHP libraries - version 2.2.2",{"query":"SELECT  `projects_keywords_elements`.`id` as 'projects_keywords_elements.id', `projects_keywords_elements`.`project_id` as 'projects_keywords_elements.project_id', `projects_keywords_elements`.`publish` as 'projects_keywords_elements.publish', `projects_keywords_elements`.`enabled` as 'projects_keywords_elements.enabled', `projects_keywords_elements`.`cornerstone` as 'projects_keywords_elements.cornerstone', `projects_keywords_elements`.`published` as 'projects_keywords_elements.published', `projects_keywords`.`id` as 'projects_keywords.id', `projects_keywords`.`user_id` as 'projects_keywords.user_id', `projects_keywords`.`name` as 'projects_keywords.name', `projects_keywords`.`domain_id` as 'projects_keywords.domain_id', `projects_keywords_elements`.`domain_category` as 'projects_keywords_elements.domain_category', `domains_categories`.`name` as 'domains_categories.name', `projects_keywords_elements`.`domain_user` as 'projects_keywords_elements.domain_user', `domains_users`.`username` as 'domains_users.username', `projects_keywords_elements`.`keyword` as 'projects_keywords_elements.keyword', `db_keywords_da_dk`.`keywords`.`keyword` as 'db_keywords_da_dk.keywords.keyword', `projects_keywords_elements`.`related_keywords` as 'projects_keywords_elements.related_keywords', `projects_keywords_elements`.`also_keywords` as 'projects_keywords_elements.also_keywords', `projects_keywords_elements`.`videos` as 'projects_keywords_elements.videos' FROM  `projects_keywords_elements` LEFT JOIN `projects_keywords` ON `projects_keywords`.`id` = `projects_keywords_elements`.`project_id`  LEFT JOIN `domains_categories` ON `domains_categories`.`category_id` = `projects_keywords_elements`.`domain_category`  LEFT JOIN `domains_users` ON `domains_users`.`user_id` = projects_keywords_elements.domain_user AND projects_keywords.domain_id = domains_users.domain_id  LEFT JOIN `db_keywords_da_dk`.`keywords` ON `db_keywords_da_dk`.`keywords`.`id` = `projects_keywords_elements`.`keyword` WHERE `projects_keywords`.`user_id` = :where_0 AND  `domains_categories`.`domain_id` = :where_1 AND  `projects_keywords`.`domain_id` = :where_2 ","bindings":[{"name":":where_0","value":1,"type":null},{"name":":where_1","value":"632","type":null},{"name":":where_2","value":"632","type":null}]},{"query":"SELECT DISTINCT  `id` as 'id', `name` as 'name' FROM  `projects_keywords` ","bindings":[]},{"query":"SELECT DISTINCT  `category_id` as 'category_id', `name` as 'name' FROM  `domains_categories` WHERE (`domains_categories`.`domain_id` = :where_1 )","bindings":[{"name":":where_1","value":"632","type":null}]},{"query":"SELECT DISTINCT  `user_id` as 'user_id', `username` as 'username' FROM  `domains_users` WHERE (`domains_users`.`domain_id` = :where_1 )","bindings":[{"name":":where_1","value":"632","type":null}]},{"query":"SELECT DISTINCT  `id` as 'id', `keyword` as 'keyword' FROM  `db_keywords_da_dk`.`keywords` WHERE (`db_keywords_da_dk`.`keywords`.`id` = :where_1 )","bindings":[{"name":":where_1","value":"8069875","type":null}]},{"query":"SELECT DISTINCT  `id` as 'id', `title` as 'title' FROM  `db_keywords_da_dk`.`related_searches` WHERE (`db_keywords_da_dk`.`related_searches`.`keyword_id` = `projects_keywords_elements`.`related_keywords` )","bindings":[]}]}

Lennart Oester

I solved it by doing this

$q->where($_SESSION['project_keywords_database'] . '.related_searches.keyword_id', '(SELECT DISTINCT keyword FROM admin_sbn.projects_keywords_elements WHERE project_id = '.$_SESSION['project_keywords_projectid'].')', 'IN', false);

Lennart Oester

No did not, because it selects to much for project ID. I need the row id/keyword Id

Lennart Oester

I simply cant get it to work.
My last idea is to set the keyword in a session var when I click the table and then use it like normal.
But I cant get it to register the click everywhere on the table when using inline edit, which is a must

Lennart Oester

Last hope - no dice

I tried this in hope that even tho9ugh it gets set before the inline edit, its not soon enough for the sql code

 $('#tblarticles').on( 'click', 'tbody td', function () {
        console.log('select');
        var table = $('#tblarticles').DataTable();                                
        var data = table.rows(this).data();
        var id = data[0]['projects_keywords_elements']['keyword'];
        storeSelectedId(id);

        console.log('click');
        editorarticles.inline( this, {
            onBlur: 'submit'
        } );
    } );    



} );

function getSelectedKeywordProjectId()
{
    var table = $('#tblprojectsarticles').DataTable();                                
    var data = table.rows({selected:true}).data();
    var id = data[0]['projects_keywords']['id'];
    return id;
}

function storeSelectedId(id)
{
    $.ajaxSetup({async: false});
        $.post("content_projects_all.ajax.domain.php",
        {
            id: id,
            tab: 'keywords_selected',
        },
        function(data, status){
            //console.log(data); //Keyword set

        });
}

Lennart Oester

I solved it using a reload. Not the way I wanted as it now requires an extra click to activate the inline edit but, its usable

allan

It looks like the error being given isn't the same as the one from the original post. It now says:

Column not found: 1054 Unknown column 'projects_keywords_elements.related_keywords' in 'where clause

Before it was about projects_keywords_elements.keyword.

This is the SQL statement that is failing:

SELECT DISTINCT 
  `id` as 'id',
  `title` as 'title'
FROM  `db_keywords_da_dk`.`related_searches`
WHERE
  (
    `db_keywords_da_dk`.`related_searches`.`keyword_id` =
    `projects_keywords_elements`.`related_keywords`
  )

Is it missing a join? Do you need to leftJoin() to the projects_keywords_elements table? You can certainly do that, but then the list of options would be different for every row. That is not something that Editor's PHP libraries currently support out of the box. It can be done with an Ajax call on each initEdit to get the list of options for the row being edited though - that is how I normally approach such a case.

Allan