Answers

• allan Posts: 64,743Questions: 1Answers: 10,713 Site admin

  July 6

  Could you show me the JSON data that is being used to load your table please?

  The setup above appears to be inline with what would expect to be working!

  Thanks,
  Allan
• FireNet999 Posts: 18Questions: 5Answers: 0

  July 7 edited July 7

  Hi Allan
  Please see below

  "DT_RowId": "row_326",
        "live": {
          "status": "********",
          "title": "********",
          "firstname": "********",
          "lastname": "********",
          "postnoms": "********",
          "partner": "********",
          "address1": "********",
          "address2": "********",
          "address3": "********",
          "town": "********",
          "county": "********",
          "region": "********",
          "postcode": "********",
          "country": "********",
          "phone1": "********",
          "phone2": "********",
          "email": "********",
          "bio": "********",
          "image": "11",
          "gender": "********",
          "privacy": "********",
          "intouch": "********",
          "deceased": "********",
          "dod": ********,
          "dob": "********",
          "birthplace": "********",
          "nationality": "********",
          "formeremployer": "********",
          "formerpos": "********",
          "retired": "********",
          "presentemployer": "********",
          "presentpos": "********",
          "othercompanies": "********",
          "othercharities": "********",
          "dol": "********",
          "liveryno": "********",
          "doj": "********",
          "dof": "********",
          "freedomofcity": "********",
          "sponsor": "********",
          "seconder": "********",
          "active": "********",
          "notes": "********",
          "personalnote": "********",
          "resigned": "********",
          "donor": "********",
          "ctnotes": "********"
  
• allan Posts: 64,743Questions: 1Answers: 10,713 Site admin

  July 7

  Is there a files object on the JSON object? If you can't make it public (totally understand that) can you PM me the full thing?

  Allan
• FireNet999 Posts: 18Questions: 5Answers: 0

  July 9

  Allan
  Please see below the files option

  "options": [], "files": { "files": { "11": { "id": "11", "filename": "Colin.png", "filesize": "192593", "web_path": "/IMAGES/uploads/11.png", "system_path": "/home/sites/*****.**/public_html/IMAGES/uploads/11.png" } } },
• rf1234 Posts: 3,143Questions: 92Answers: 433

  July 9

  However, it only gives me the ID without the extension.
  How can I get the web_path or system_path inserted instead of the ID?

  Well your files object looks perfectly normal and it also contains web_path and system_path. Maybe we don't understand your issue?!
• FireNet999 Posts: 18Questions: 5Answers: 0

  July 10

  Thanks for the reply
  My issue is that when an image is inserted into a record, it only gives me the ID, but not the ext e.g. in the row it says 11 but I need 11.png
  Hope that helps
• rf1234 Posts: 3,143Questions: 92Answers: 433

  July 10 edited July 10

  You seem to be using this example:
  https://editor.datatables.net/examples/advanced/upload.html

  The example works. Please take a look a the details of the example. Yes, the field "image" of the primary table only contains the id of the file as a foreign key, but that is ok. You need to retrieve the image from the files table. For that purpose you need the foreign key to join the file table with the primary table.
• rf1234 Posts: 3,143Questions: 92Answers: 433

  July 10 edited July 10

  How is the join being done in the example?

  In the example above the join of the file table with the primary table happens here. Editor's "upload" method does it using the file id saved in the "image" field of the primary table.

  // Build our Editor instance and process the data coming from _POST
  Editor::inst( $db, 'users' )
      ->fields(
          Field::inst( 'first_name' ),
          Field::inst( 'last_name' ),
          Field::inst( 'phone' ),
          Field::inst( 'city' ),
          Field::inst( 'image' )
              ->setFormatter( Format::ifEmpty( null ) )
              ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/uploads/__ID__.__EXTN__' )
                  ->db( 'files', 'id', array(
                      'filename'    => Upload::DB_FILE_NAME,
                      'filesize'    => Upload::DB_FILE_SIZE,
                      'web_path'    => Upload::DB_WEB_PATH,
                      'system_path' => Upload::DB_SYSTEM_PATH
                  ) )
                  ->validator( Validate::fileSize( 500000, 'Files must be smaller that 500K' ) )
                  ->validator( Validate::fileExtensions( array( 'png', 'jpg', 'jpeg', 'gif' ), "Please upload an image" ) )
              )
      )
      ->process( $_POST )
      ->json();
  

  Here Editor passes "image" which is the file id into the "display" function to display the details loaded from the files table.

  {
      label: 'Image:',
      name: 'image',
      type: 'upload',
      display: function (file_id) {
          return '<img src="' + editor.file('files', file_id).web_path + '"/>';
      },
      clearText: 'Clear',
      noImageText: 'No image'
  }
  

  Here again "image" - labeled as "file_id" - is passed into another function. In this case the "render" function which gets the details from the files table. not from the primary table:

  {
      data: 'image',
      render: function (file_id) {
          return file_id
              ? '<img src="' + editor.file('files', file_id).web_path + '"/>'
              : null;
      },
      defaultContent: 'No image',
      title: 'Image'
  }
  
• rf1234 Posts: 3,143Questions: 92Answers: 433

  July 10 Answer ✓

  My issue is that when an image is inserted into a record, it only gives me the ID, but not the ext e.g. in the row it says 11 but I need 11.png
  Hope that helps

  What you actually want is this in a simple SQL statement. You want what I call the technical file name which is part of web_path and system_path. Here is the SQL-statement to retrieve it.

  The join between the two tables is done by Editor implicitly. You don't have to worry about it yourself. Neither do you have to write SQL-statements like this. Editor does this all for you.

  SELECT substring_index(a.web_path, "/", -1) as technical_file_name
       FROM files a
  LEFT JOIN live b ON a.id = b.image
      WHERE a.id = 11;
  
• FireNet999 Posts: 18Questions: 5Answers: 0

  July 10

  Many thanks for the detailed walkthrough and the select statement.
  I can confirm it works like a dream
  Thanks again
• rf1234 Posts: 3,143Questions: 92Answers: 433

  July 10 edited July 10

  Glad you got it working!

  The SELECT statement is crap though. The WHERE clause should contain the value that you have. That is the value of "image" (foreign key).

  So it should look like this:

     SELECT substring_index(a.web_path, "/", -1) as technical_file_name
       FROM files a
  LEFT JOIN live b ON a.id = b.image
      WHERE b.image = 11;
  

  But it's merely for illustration purposes ... not really required. You can extract the "technical file name" using Editor's "render" method on the client side as well.