insert value from lookup table

crush123

I have a database table of items where I want to insert a ‘default’ price from a lookup table, depending on the values I have selected when I add a new record.
I am comfortable doing this using standard php, but can’t work out if this is possible using editor.
Say, for example, the default price is dependent on size, colour and condition of an item, then in php, I would use the post field values with my lookup table to filter to a single row, and insert this value into my items table

I currently have a json data source, using the items table and left joins to several reference tables, but the lookup table has no direct relationship to the items table

mRender

So you would have a bunch of variables that get sent over to your page like this

$color = 'red';
$size = 'large';

Then you could have your ajax page be something like this

ajax: "your_ajax_page.php?color=".$color."&size=".$size.""

This is an easy way to send over data to your ajax page with certain php variables. aka just put them right into the url! You can then access them via $_GET on the ajax page

crush123

Thanks for the reply - though I'm not sure how to implement what you are suggesting.

I have done a bit of work on this since my original question, and I have a partial success.*

My dataTable Editor instance currently uses a json post to update the database.

What i have done is, on posting the editor form, using the 'row' data to perform a sql update.

something like

if ( isset($_POST['action']) && $_POST['action'] === 'create' ) {
    $sql = $db->sql( "UPDATE tblitem SET ItemPrice = '".$rspricelookup->getColumnVal("DefaultPrice")."' WHERE ItemID = '".$rowid."'");
}

don't know if this is the best approach, but the table is updated successfully.

• reason it is only a partial success is that the database table is updated correctly, but the datatable is not refreshed on screen. I think this is because i am processing the $_POST before running the sql update, (but I can't parse the php otherwise)

allan

Hi,

Using the code you have is the best way of doing it with 1.3. If you fancy trying the 1.4 beta libraries (apologies, I can't remember if you already are - so many support threads to keep track of in these forums these days!) the new setValue() option might be the best way of going it.

Allan

crush123

Hi Allan,

I am using 1.4 beta now - Elsewhere I used the setValue option to insert a session variable value.

That is working fine because the session value was already known when the ajax page was loaded.

what i want to do is use the values returned from the new editor row to return a value from a lookup table, and insert it.

the insert works ok, but i need to f5 my page, and i cant work out how to do the insert before ->process

here is a link http://test2.forthwebsolutions.com/plugins/shop/stocklist.php

if you add an item, (leave everything as a default) the price is shown as 0 on the table, but pressing f5 shows the value has been inserted ok

Thanks

allan

You can use setValue with an anonymous function as well:

->setValue( function () use ($db) {
  // Make Database call to get value, etc
  return $value;
} )

That way your anonymous function is only called to evaluate the value when needed, and the row will be fully up-to-date when Editor reads it for the display.

Allan

crush123

Ok, I understand the principle. - but can't do the syntax

I'm very much a learner by example,

Here's what i have tried - a section from within the ajax editor instance, with the sql commented out

I presume I will use the values posted by the editor ?

Having got errors when i ran this, i simplified the statement to use fixed values rather than variables, and I still get the error, so I assume my syntax is wrong

Field::inst( 'tblitem.ItemPrice' )
        ->setValue( function () use ($db) {
          //retrieve values from editor
          //$itemtypeid = $_POST['tblitem.ItemTypeID'];
          //$sizeid = $_POST['tblitem.ItemSizeID'];
          //$qualityid = $_POST['tblitem'.'ItemQualityID'];


              //$value = $db->sql('SELECT refpricelookup.DefaultPrice FROM refpricelookup WHERE refpricelookup.ItemTypeID = '.'1'.' AND refpricelookup.SizeID = '.'20'.' AND refpricelookup.QualityID = '.'1'.'');
              //$value = $db->sql('SELECT refpricelookup.DefaultPrice FROM refpricelookup WHERE refpricelookup.ItemTypeID = '.$itemtypeid.' AND refpricelookup.SizeID = '.$sizeid.' AND refpricelookup.QualityID = '.$qualityid.'');
          return $value;
        } ),

second part to this question, I only want to update the price field when i use editor create.
On edit, there will be a text input.

What is the best method to do this ? ie should i use different ajax pages to load and save the data, or is it best to just add conditions within the same page ?

Thanks for your patience

allan

Editor submits the data from the form in the data parameter (docs) so you would need to use something like: $_POST['data']['tblitem']['ItemTypeID'] to access the submitted data.

I only want to update the price field when i use editor create

Use the set() method to tell it you want to use it on create only (there is a bug in the documentation in that you need to click on the row to view the full details for the method at the moment).

Allan

crush123

Definitely getting closer...but still doing it wrong

Using the set() method, I can successfully save a static value to the database table, and do this ONLY on create

eg

->setValue( '10')
->set( Field::SET_CREATE ),

However doing this gives me two issues.

1. I am no longer 'getting' the field value from the json - my json source now shows a null for this field
2. What I want to do is set the value on create, but on edit, I want to set the value from the editor form

if I try

 ->setValue( '10')
 ->set( Field::SET_CREATE )
 ->setValue( '20')
 ->set( Field::SET_EDIT ),

the SET_CREATE is ignored, (i cant SET_BOTH here as the values would be different)

The other problem I am having is if I try to setValue with an anonymous function

(Thanks for the tip re the posted field values), but even with a simple query, I get an error returned, eg

->setValue( function () use ($db) {

        $value = $db->selectDistinct( 'refquality', 'QualityID', array('QualityID' => 1) );
        return $value;
        } )
        ->set( Field::SET_CREATE ),

i get a "A system error has occurred..."

the error is

"Catchable fatal error: Object of class DataTables\Database\DriverMysqlResult could not be converted to string ..."

Thanks for your help

allan

What I want to do is set the value on create, but on edit, I want to set the value from the editor form

Okay, so what you need to do is configure the field in an if statement most likely:

$editor = Editor::inst( ... );

if ( Editor::action( $_POST ) === Editor::ACTION_CREATE ) {
  $editor->field(
    Field::inst( ... )->setValue( ... static value ... )
  );
}
else {
  $editor->field(
    Field::inst( ... )->setValue( ... anon function ... )
  );
}

$editor->process( $_POST );
$editor->json();

So basically just create a different field definition based on what you need.

Allan

crush123

great, that really helps. i am now getting the value field returned from my json

if I add a static value, eg

if ( Editor::action( $_POST ) === Editor::ACTION_CREATE ) {
                $editor->field(
                Field::inst('tblitem.ItemPrice')
                ->setValue('5')
                );
                } else {
                $editor->field(
                Field::inst('tblitem.ItemPrice')
                );
            }

the static value '5' is added to the database (on create only, which is what i want), but the datatable displays NaN.N. on edit, the value is saves and displayed correctly

if i try the sql method, eg

if ( Editor::action( $_POST ) === Editor::ACTION_CREATE ) {
                $editor->field(
                Field::inst('tblitem.ItemPrice')
                ->setValue( function () use ($db) {
         $value = $db->selectDistinct( 'refquality', 'QualityID', array('QualityID' => 1) );
        return $value;
        } )
                );
                } else {
                $editor->field(
                Field::inst('tblitem.ItemPrice')
                );
            }    

I still get

Catchable fatal error: Object of class DataTables\Database\DriverMysqlResult could not be converted to string in ~\DataTables-1.10.4\extensions\Editor-1.4.0-beta.1\php\Database\Driver\Mysql\Query.php on line 90

allan

but the datatable displays NaN

Odd - what does the JSON return? Can you link me to the page so I can take a look at the JS in question?

Thanks,
Allan

crush123

test page here...

json returns a null

(i also just noticed, i get an error when i try to delete a row)

http://test2.forthwebsolutions.com/stocklist.php

allan

Regarding the delete row - darn. Sorry about that! There is an error on line 101 (ironically) of Editor.php. It currently has case 'delete': and it should be:

case 'remove':

Are you able to update your local version?

Just looking into the other error just now.

Thanks,
Allan

crush123

updated local copy of Editor.php - sorted ;-)

allan

Good to hear. The other one is a bug as well I'm afraid. In Field.php in the val() method (line 519) you will find:

            if ( $this->_setValue !== null ) {

replace with:

            if ( $this->_getValue !== null ) {

And that should fix it!

Allan

crush123

Excellent.

I can confirm that if I use a fixed value eg

->setValue('5')

Then everything works as it should

If i use an anonymous function, eg

->setValue(function () use ($db) {

        $value = $db->sql('SELECT refpricelookup.DefaultPrice FROM refpricelookup WHERE DefaultPriceID = 1');
        return $value;
        }

i still get the error

Catchable fatal error: Object of class DataTables\Database\DriverMysqlResult could not be converted to string

Syntax error ?

allan

The sql() method returns a Result class, which is what the error says. You would need to do something like:

$result = $db->sql( ... );
return $result->fetch()['DefaultPrice'];

You might want to add error handling for if there are no results found ($result->count() === 0) as well.

Allan

crush123

Awesome, you just made my weekend,

If anyone else is struggling with this, here is a (slightly simplified) version of the working code, taking a value POSTed from the editor form as a parameter, and using setValue and an unnamed function to insert the value into the db table, using a recordset as a data source

->setValue(function () use ($db) {
    //retrieve values from editor
    $sizeid = $_POST['data']['tblitem']['ItemSizeID'];

    $result = $db->sql('SELECT refpricelookup.DefaultPrice FROM refpricelookup WHERE refpricelookup.SizeID = '.$sizeid.'');
    //if ($result->count() === 0) {
      //    $value = 0;
    //} else {
      $row = $result->fetch();
      $value = $row['DefaultPrice'];
    //}
    return $value;                      
    } )
    );

allan

Nice one - thanks for posting your code.

I think setValue is deserving of a future blog post!

Allan

crush123

Doh ! I can't event get the error handling right.

I have updated my code above and commented out the error handling, otherwise it throws an error on create.

There should never be an instance where my lookup table returns 0 rows, but i would like to add the error handling at some point