Building an select input type with data from databaase

Building an select input type with data from databaase

mankramomankramo Posts: 24Questions: 5Answers: 0

Hello everyone, I am having difficulty with creating a select on my form.Basically this is the pproblem:

I have two tables in my DB ( currencies and exchangeRates ) , the table have these columns:

currencies => { id, currencyName, dateAdded, lastEdited }
exchangeRates => {id, currencyId, rateDate, rate}

Now I have a form that will be used to update the exchangeRates table. Now i want the select to to have this attributes :smile:

<select>
<option value='currencyId'> currencyName </option>
</select>

Please how do i do this ?
thank you

This question has accepted answers - jump to:

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    ok, you want the forex currency options for your exchange rates to come from table "exchange rates". Take a look at this please.
    https://editor.datatables.net/manual/php/joins#Options

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Found an example as well. Take a look at this please: https://editor.datatables.net/examples/advanced/joinLinkTable.html
    Looking at the server script you'll find this as part of Editor:

    Field::inst( 'users.site' )
                ->options( Options::inst()
                    ->table( 'sites' )
                    ->value( 'id' )
                    ->label( 'name' )
                ),
            Field::inst( 'sites.name' ),
    

    I also attached the data model behind this. Look at the relationship between "users" and "sites". That's the same as yours between "exchangeRates" and "currencies".

    Your options instance plus the currency field could look like this:

    Field::inst( 'exchangeRates.currencyId' )
                ->options( Options::inst()
                    ->table( 'currencies ' )
                    ->value( 'id' )
                    ->label( 'currencyName ' )
                ),
            Field::inst( 'currencies.currencyName' ),
    

    make the changes at the front end too, and you're done.

  • mankramomankramo Posts: 24Questions: 5Answers: 0

    this my code ; and its still not working

    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'nestor_exchangerates', 'exchangerate_id' )
    ->fields(

        Field::inst( 'nestor_currencies.currency_name' )
            ->options( new Options::inst()
                ->table( 'nestor_currencies' )
            ->value( 'currency_id' )
             ->label( 'currency_name' )
            );
            //->validator( Validate::notEmpty() ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_date' )
            ->validator( Validate::notEmpty() )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-Y' )),
    
        Field::inst( 'nestor_exchangerates.exchangerate_amount' ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_created' )
            ->set( false ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_lastedited' )
            ->set( false ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_company' )
            ->set( false )
    )
    //perform join sql query
    ->leftJoin( 'nestor_exchangerates', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    ->process( $_POST )
    ->json();
    

    **************CODE ENDS***************

    I get an error " DataTables warning: table id=nestor_exchangerates - Invalid JSON response".

    What am i doing wrong here ??

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    well, you have an error in the options instance. You must select the id field, not the name field there. The name is only the label. In addition you must also select the currency_name field itself. Please follow the example very, very closely.

    In addition you select from nestor_exchangerates and do the leftjoin to the same table not nestor_currencies.

    This could work provided you also fix the left join issue:

    Field::inst( 'nestor_exchangerates.currencyId' )
            ->options( Options::inst()
                ->table( 'nestor_currencies' )
            ->value( 'id' )
             ->label( 'currency_name' )
            );
    Field::inst( 'nestor_currencies.currency_name' ),       
     
        Field::inst( 'nestor_exchangerates.exchangerate_date' )
            ->validator( Validate::notEmpty() )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-Y' )),
     
        Field::inst( 'nestor_exchangerates.exchangerate_amount' ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_created' )
            ->set( false ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_lastedited' )
            ->set( false ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_company' )
            ->set( false )
    )
    //perform join sql query
    ->leftJoin( 'nestor_exchangerates', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    ->process( $_POST )
    ->json();
    
  • mankramomankramo Posts: 24Questions: 5Answers: 0
    edited January 2018

    I have made the change to the option instance like you asked. Now i have replaced it with
    Field::inst( 'nestor_exchangerates.exchangerate_currency' )

    exchangerate_currency is the foreign key on nestor_exchangerates that maps to the id column on nestor_currencies. ( i will want to add that the names of the currencies are not on nestor_exchangerates only their id's are stored in exchangerate_currency column)

    In addition you select from nestor_exchangerates and do the left join to the same table not nestor_currencies.

    Please i dont get the above statement clearly, i thought joins are made between two different tables and no a same table ?. Let me post my current code here if it will help

    *****THE TWO TABLES ARE nestor_exchangerates & nestor_currencies******

    ****CODE BEGINS****
    ->fields(

        Field::inst( 'nestor_exchangerates.exchangerate_currency' )
            ->options( new Options::inst()
                ->table( 'nestor_currencies' )
            ->value( 'currency_id' )
             ->label( 'currency_name' )
            );
            //->validator( Validate::notEmpty() ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_date' )
            ->validator( Validate::notEmpty() )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-Y' )),
    
        Field::inst( 'nestor_exchangerates.exchangerate_amount' ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_created' )
            ->set( false ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_lastedited' )
            ->set( false ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_company' )
            ->set( false )
    )
    //perform join sql query
    ->leftJoin( 'nestor_exchangerates', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    ->process( $_POST )
    ->json();
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited January 2018

    you are selecting from this table:

    Editor::inst( $db, 'nestor_exchangerates', 'exchangerate_id' )
    

    The table is nestor_exchangerates.

    Then you do the left join with itself:

    ->leftJoin( 'nestor_exchangerates', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    ->process( $_POST )
    ->json();
    

    You must do the left join with nestor_currencies

    I still do not see you selecting the name of the currency though
    This is still missing:

    Field::inst( 'nestor_currencies.currency_name' ),      
    
  • mankramomankramo Posts: 24Questions: 5Answers: 0

    You must do the left join with nestor_currencies

    I still do not see you selecting the name of the currency though
    This is still missing:

    That is what i want to
    Field::inst( 'nestor_exchangerates.exchangerate_currency' )
    ->options( new Options::inst()
    ->table( 'nestor_currencies' )
    ->value( 'currency_id' )
    ->label( 'currency_name' )
    );

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Good Luck - hope it works now! :smile:

  • mankramomankramo Posts: 24Questions: 5Answers: 0

    am still getting an error message
    DataTables warning: table id=nestor_exchangerates - Invalid JSON response. Formore information about this error, please see http://datatables.net/tn/1

    I just dont know whats happening

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    If you post your code (javascript, HTML and PHP) I will take a look!

  • mankramomankramo Posts: 24Questions: 5Answers: 0

    Alright these are my codes

    ******HTML*****
    <table class="table table-striped table-bordered table-condensed" id="nestor_exchangerates" width="100%"> <thead>
    <tr style='background:#eee;' <th class="not-bold">Currency</th>
    <th class="not-bold">Date</th> <th class="not-bold">Rate</th>
    </tr> </thead>
    ```</table>

    JAVASCRIPT
    var editor2 = new $.fn.dataTable.Editor( {
    ajax: 'assets/datatable_assets/php/table.nestor_exchangerates.php',
    table: '#nestor_exchangerates',
    fields: [

            {
                "label": "Currency:",
                "name": "exchangerate_currency",
                "type": "select"
            },
            {
                "label": "Date:",
                "name": "exchangerate_date",
                "type": "datetime",
                "format": "YYYY-MM-DD HH:mm:ss"
            },
            {
                "label": "Amount:",
                "name": "exchangerate_amount"
            }
        ]
    } );
        var table2 = $('#nestor_exchangerates').DataTable( {
        dom: 'Bfrtip',
        ajax: 'assets/datatable_assets/php/table.nestor_exchangerates.php',
        columns: [
            {
                "data": "exchangerate_currency"
            },
            {
                "data": "exchangerate_date"
            },
            {
                "data": "exchangerate_amount"
            }
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor2 },
            { extend: 'edit',   editor: editor2 },
            { extend: 'remove', editor: editor2 }
        ]
    } );
    
    //END EXCHANGE RATES
    

    } );

    }(jQuery));

    ****PHP***

    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'nestor_currencies', 'currency_id' )
    ->fields(
    /*Field::inst( 'currency_name' )
    ->validator( Validate::notEmpty() )
    ->validator( Validate::unique(
    new ValidateOptions::inst()
    ->message( 'Stock ')

             )),*/
        Field::inst( 'currency_name' )      
        ->validator( Validate::maxLen(15,ValidateOptions::inst()->message( 'Maximum of 15 characters' )) )
        ->validator( 'Validate::unique', array(
                    "required" => true,
                    "notEmpty" => true,
                    "message" => "Currency Exists"
        ) )
            ->setFormatter( function($val, $data, $opts) {
                 return ucwords(strtolower($val));
                 }),
        Field::inst( 'currency_symbol' )        
        ->validator( Validate::maxLen(3,ValidateOptions::inst()->message( 'Maximum of 3 characters' )) )    
    
        ->validator( 'Validate::unique', array(
                    "required" => true,
                    "message" => "Symbol Exists"
        ) )
            ->setFormatter( function($val, $data, $opts) {
                 return strtoupper($val);
                 }),
        Field::inst( 'currency_home' )
            ->set( false ),
        Field::inst( 'currency_state' )
            ->set(false)
            ->validator( Validate::numeric() ),
        Field::inst( 'currency_created' )
            ->set( false ),
        Field::inst( 'currency_lastedited' )
            ->set( false ),
        Field::inst( 'currency_company' )
            ->setValue( $_SESSION['user']['company'] )
    )
    ->where('currency_state',1)
    ->where('currency_company',$_SESSION['user']['company'])
    ->process( $_POST )
    ->json();
    
  • mankramomankramo Posts: 24Questions: 5Answers: 0

    Sorry i mistakenly posted the wrong PHP code. This the the correct code

    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'nestor_exchangerates', 'exchangerate_id' )
    ->fields(

        Field::inst( 'nestor_exchangerates.exchangerate_currency' )
            ->options( new Options::inst()
                ->table( 'nestor_currencies' )
            ->value( 'currency_id' )
             ->label( 'currency_name' )
            );
            //->validator( Validate::notEmpty() ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_date' )
            ->validator( Validate::notEmpty() )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-Y' )),
    
        Field::inst( 'nestor_exchangerates.exchangerate_amount' ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_created' )
            ->set( false ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_lastedited' )
            ->set( false ),
    
        Field::inst( 'nestor_exchangerates.exchangerate_company' )
            ->set( false )
    )
    //perform join sql query
    ->leftJoin( 'nestor_exchangerates', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    

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

    ->process( $_POST )
    ->json();
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Not sure why you are not following the example but anyway I'll try my very best to help you.

    You can't have a valid JSON response because there are two syntax errors in your PHP that your IDE should highlight (at least Netbeans does) and also the left join is still wrong.

    Please try this code instead:

    Editor::inst( $db, 'nestor_exchangerates', 'exchangerate_id' )
    ->fields(
        Field::inst( 'nestor_exchangerates.exchangerate_currency' )
            ->options( Options::inst()
            ->table( 'nestor_currencies' )
            ->value( 'currency_id' )
             ->label( 'currency_name' )
            ),
        Field::inst( 'nestor_exchangerates.exchangerate_date' )
            ->validator( Validate::notEmpty() )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-Y' )),
     
        Field::inst( 'nestor_exchangerates.exchangerate_amount' ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_created' )
            ->set( false ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_lastedited' )
            ->set( false ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_company' )
            ->set( false )
    )
    //perform join sql query
    ->leftJoin( 'nestor_currencies', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    ->process( $_POST )
    ->json();
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    In addition your javascript does not seem to be correct. You are selecting 'nestor_exchangerates.exchangerate_currency' but want to display and edit "exchangerate_currency". I don't think that this will work.

    And do you really want to display an id field in your data table? "exchangerate_currency" should be an id field, shouldn't it?

    That's why I recommended to select this field too and use it in your data table:

    Field::inst( 'nestor_currencies.currency_name' ),   
    

    in order to avoid displaying an id field to the user.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    The updated javascript with currency name instead of the id in the data table should look like this:

    var editor2 = new $.fn.dataTable.Editor( {
        ajax: 'assets/datatable_assets/php/table.nestor_exchangerates.php',
        table: '#nestor_exchangerates',
        fields: [{
                "label": "Currency:",
                "name": "nestor_exchangerates.exchangerate_currency",
                "type": "select"
            },
            {
                "label": "Date:",
                "name": "nestor_exchangerates.exchangerate_date",
                "type": "datetime",
                "format": "YYYY-MM-DD HH:mm:ss"
            },
            {
                "label": "Amount:",
                "name": "nestor_exchangerates.exchangerate_amount"
            }
        ]
    } );
        var table2 = $('#nestor_exchangerates').DataTable( {
        dom: 'Bfrtip',
        ajax: 'assets/datatable_assets/php/table.nestor_exchangerates.php',
        columns: [
            {
                "data": "nestor_currencies.currency_name"
            },
            {
                "data": "nestor_exchangerates.exchangerate_date"
            },
            {
                "data": "nestor_exchangerates.exchangerate_amount"
            }
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor2 },
            { extend: 'edit',   editor: editor2 },
            { extend: 'remove', editor: editor2 }
        ]
    } );
    

    The updated PHP also selecting currency name like this:

    Editor::inst( $db, 'nestor_exchangerates', 'exchangerate_id' )
    ->fields(
        Field::inst( 'nestor_exchangerates.exchangerate_currency' )
            ->options( Options::inst()
            ->table( 'nestor_currencies' )
            ->value( 'currency_id' )
             ->label( 'currency_name' )
            ),
        Field::inst( 'nestor_currencies.currency_name' ),  
        Field::inst( 'nestor_exchangerates.exchangerate_date' )
            ->validator( Validate::notEmpty() )
            ->getFormatter( Format::dateSqlToFormat( 'd-m-Y' ) )
            ->setFormatter( Format::dateFormatToSql( 'd-m-Y' )),
     
        Field::inst( 'nestor_exchangerates.exchangerate_amount' ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_created' )
            ->set( false ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_lastedited' )
            ->set( false ),
     
        Field::inst( 'nestor_exchangerates.exchangerate_company' )
            ->set( false )
    )
    //perform join sql query
    ->leftJoin( 'nestor_currencies', 'nestor_exchangerates.exchangerate_currency', '=', 'nestor_currencies.currency_id' )
    ->process( $_POST )
    ->json();
    
  • mankramomankramo Posts: 24Questions: 5Answers: 0

    Your codes worked. I will have to critically look out for some of these mistakes in my codes. Than you very much @rf1234

  • mankramomankramo Posts: 24Questions: 5Answers: 0

    I am having a bit of a problem with my where statement under the options. First of all I see that all the currencies in the DB (nestor_currencies table) appear for selection when adding a new rate or editing an existing rate, but i don not want the Home currency ( which has column currency_home set to 1, all others are set to 0 ) to be part of the options for selections because there is no point in setting for a home currency. So following the documentation.i added a where clause as shown below

    Field::inst( 'nestor_exchangerates.exchangerate_currency' )
    ->options( Options::inst()
    ->table( 'nestor_currencies' )
    ->value( 'currency_id' )
    ->label( 'currency_name' )
    ->where( function ($q) {
    $q->where( 'currency_home',0,'=');
    }
    ),

    Unfortunately, i get an error. I was thinking this will be straight forward but :'(

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    What is the error please?

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    there is a bracket missing in the second last line and you don't need the "=" either. Just try this:

    Field::inst( 'nestor_exchangerates.exchangerate_currency' )
    ->options( Options::inst()
        ->table( 'nestor_currencies' )
        ->value( 'currency_id' )
        ->label( 'currency_name' )
        ->where( function ($q) {
            $q->where( 'currency_home', 0 );
         })
    ),
    

    Maybe you could try to use markdown; makes your posts easier to read. All you need to do is to copy or type the triple back ticks (see below) before and after your code. That's already good enough.

This discussion has been closed.