Building an select input type with data from databaase
Building an select input type with data from databaase
mankramo
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 
<select>
<option value='currencyId'> currencyName </option>
</select>
Please how do i do this ?
thank you
This question has accepted answers - jump to:
This discussion has been closed.
Answers
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
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:
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:
make the changes at the front end too, and you're done.
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(
**************CODE ENDS***************
I get an error " DataTables warning: table id=nestor_exchangerates - Invalid JSON response".
What am i doing wrong here ??
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:
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)
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(
you are selecting from this table:
The table is nestor_exchangerates.
Then you do the left join with itself:
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' )
);
Good Luck - hope it works now!
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/1I just dont know whats happening
If you post your code (javascript, HTML and PHP) I will take a look!
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: [
} );
}(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 ')
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(
->process( $_POST )
->json();
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:
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:
in order to avoid displaying an id field to the user.
The updated javascript with currency name instead of the id in the data table should look like this:
The updated PHP also selecting currency name like this:
Your codes worked. I will have to critically look out for some of these mistakes in my codes. Than you very much @rf1234
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
What is the error please?
Allan
there is a bracket missing in the second last line and you don't need the "=" either. Just try this:
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.