Answers

• Lennart Oester Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 63,498Questions: 1Answers: 10,470 Site admin

  October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 63,498Questions: 1Answers: 10,470 Site admin

  October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023 edited October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023

  No did not, because it selects to much for project ID. I need the row id/keyword Id
• Lennart Oester Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 12Questions: 2Answers: 1

  October 2023

  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 Posts: 63,498Questions: 1Answers: 10,470 Site admin

  October 2023

  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