Solution - extra WHERE clause for foreign table

A

alpha2zee

Guest
DaDaBik allows one to fill the select_single pull down menu with options that are from values in a foreign table. The problem is, it shows all items from the table. I want to be able to selectively choose from the foreign table. So here is my fix (confirmed for version 3.2 beta).

Idea
----

Create new field named 'linked_fields_extra_mysql' for the dadabik_tables which can be used to pass 'WHERE' clauses in the MySQL query

How
----

1. If you already have DaDaBik installed - meaning DaDaBik has been run once atleast, as a result of which all the dadabik_ tables have been generated in the database:

Using something like phpmyadmin or Navicat, create a new field in all of the dadabik_ tables with these parameters -

linked_fields_extra_mysql
varchar(255)
not null
utf-8_general_ci

This is not needed if you have not run DaDaBik yet.

2. Edit include/internal_table.php

Add at end:

$int_fields_ar[25][0] = "Extra for MySQL statement:";
$int_fields_ar[25][1] = "linked_fields_extra_mysql";
$int_fields_ar[25][2] = "text";
$int_fields_ar[25][3] = "25";

3. Edit admin.php

Look for:

$linked_fields_order_type_field_temp = addslashes($fields_labels_ar[$j]["linked_fields_order_type_field"]);

Below it, add:

$linked_fields_extra_mysql_temp = addslashes($fields_labels_ar[$j]["linked_fields_extra_mysql"]);

Look for:
".$quote."linked_fields_order_type_field".$quote."

After the last double-quote mark, add (note the first comma):

,".$quote."linked_fields_extra_mysql".$quote."

Look for:

'$linked_fields_order_type_field_temp'

At end of that, add (note the first comma):

,'$linked_fields_extra_mysql_temp'

4. Edit include/business_logic.php

Go to function build_fields_labels_array($table_internal_name, $order) and look for:

".$quote."linked_fields_order_type_field".$quote.",

After that, add:

".$quote."linked_fields_extra_mysql".$quote.",

Scroll down to look for:

$fields_labels_ar[$i]["linked_fields_order_type_field"] = $field_row["linked_fields_order_type_field"]; // the order type (ASC|DESC) to use in the order clause when retreiving the linked fields
Below that, add:

$fields_labels_ar[$i]["linked_fields_extra_mysql"] = $field_row["linked_fields_extra_mysql"]; // extra MySQl command to use when retrieving the linked fields

Go to function build_form($table_name, $action, $fields_labels_ar, $form_type, $res_details, $where_field, $where_value) and look for:

$sql .= " FROM ".$quote.$primary_key_table_field.$quote;

Below, add:

// extra mysql clauses
if ($linked_fields_extra_mysql != "")
{$sql .= $linked_fields_extra_mysql;}

Go to function create_internal_table($table_internal_name) and look for:

linked_fields_order_type_field VARCHAR(255) NOT NULL,

Add after that:

linked_fields_extra_mysql VARCHAR(255) NOT NULL,

Using
-----

Now when you configure fields for tables, you will see a field 'Extra for MySQL statement.' It can be filled with something like

WHERE `status` = 'Current'

Now, the pull down menu will be filled with values from the foreign table only if the above condition is satisfied.

Help
----

I have not extensively tested this. Please correct any bugs in this forum.
 
A

Anthony Esper

Guest
Can you insert an example of your table structure and your exact where statement?

I tried using this, I get the box in the interface for extra sql and I enter statements but they seem to take more effect. I will go over the process again.
 
A

alpha2zee

Guest
A few notes are about my first post may help. Please note that I have tried this only once and on my set up.

For point 1
---------

You have to create the new field for all the dadabik_ tables that are the internal tables that DaDaBik uses. Using phpmyadmin, I inserted the new field *after* the linked_fields_order_type_field field.

For point 2
---------

When adding the new int_fields_array array items in internal_table.php, I suggested the key '25' above. (May be this is the problem). I actually used key '17,' as it is next to 16 that is used for linked_fields_order_type_field. Obviously, I had to change the values for the others (e.g., what had 17 before, select_type_field, now used key 18... and so forth).

The code thus went -
[ snip ]
$int_fields_ar[16][0] = "Order type:";
$int_fields_ar[16][1] = "linked_fields_order_type_field";
$int_fields_ar[16][2] = "text";
$int_fields_ar[16][3] = "25";

$int_fields_ar[17][0] = "Extra for MySQL statement:";
$int_fields_ar[17][1] = "linked_fields_extra_mysql";
$int_fields_ar[17][2] = "text";
$int_fields_ar[17][3] = "25";

$int_fields_ar[18][0] = "Search operators:";
$int_fields_ar[18][1] = "select_type_field";
$int_fields_ar[18][2] = "text";
$int_fields_ar[18][3] = "25";
[snip]

For point 3
---------

Note the addslashes function. You probably have add_slashes, with the underscore. If you have not modified the original DaDaBik code, you should use add_slashes. In my case, to get around DaDaBik's 'magic quotes on' requirement, I had modifications (see http://www.dadabik.org/forum/read.php?f=1&i=5493&t=5493)

Point on using
------------

The MySQL clause I have entered in the field is a WHERE statement:

WHERE `status` = 'Current'

I wanted to get user names from the table only for rows that had 'Current' in the status field. A space may or may not be there at the front (before WHERE in the example above). But the backticks and the single quote marks are needed. Note that for numerical comparison, the quote marks should not be used (example below).

This is a more complex clause I use in my setup:

WHERE `Category` = 'Mol-bio' AND (`ID`>10 OR `ID`<200) GROUP BY `Vendor`
 
D

Darly Coupet

Guest
Hi,

excellent contribs!

Assuming foreign table is prefilled with those values.

status = current
status = active
status = inactive
status = old

How can I access data based on other conditions?

Thanks, Darly
 
A

alpha2zee

Guest
In DaDaBik, without the modification, data is fetched from a foreign table using a MySQL SELECT statement that is something like this

SELECT `primary_key_field`, `linked_field_1`, `linked_field_2` FROM `foreign_table`

Which fields to fetch are specified when you fill up the 'linked fields' form field. In this case, a total of three fields are being fetched. Say, the fields last name, first name and age have been fetched from a table that has only two entries.

Last name - First name - Age
Doe - John - 22
Doe - Jane - 21

The fetched data is rearranged using PHP to generate the select menu options. The options thus appear like

Doe-John-22
Doe-Jane-21

With the modification above, all these things remain the same. What changes is that instead of fetching all the rows, the system can be made more selective. The 'extra mysql' field lets one pass on extra clauses for the SELECT statement.

Without this in the field, e.g.,

WHERE `Category` = 'Mol-bio' AND (`ID`>10 OR `ID`<200) GROUP BY `Vendor`

, all 'items' (the primary key field is 'item') will be fetched. But with the extra MySQL,

1. Only those items that belong the 'Mol-bio' category, and
2. Whose ID values are between 11 and 199 will be fetched.
3. Furthermore, they will be grouped such that those using the same 'vendor' will appear together.

Note that all the fields are from the same foreign table.

Refer to MySQL documentation for more.
 
D

Darly Coupet

Guest
If I need to access fields based on value, then do I need to create 4 fields?

and each field 'Extra for MySQL statement.'
1. WHERE `status` = 'Current'
2. WHERE `status` = 'Old'
3. WHERE `status` = 'Active'
4. WHERE `status` = 'Inactive'

Thanks, Darly
 
A

alpha2zee

Guest
I am not sure what the main table (the one into which data goes using the DaDaBik insert/update forms) and the foreign table (the one which provides the menu options for the insert/update forms) look like.

Assuming that the foreign table has a column named 'status' where the values are 'current', 'old' etc., and you want to have only those items fetched for which the 'status' field has the value 'Current,' the clause will be

WHERE `status` = 'Current'

If you want flexibility such that 'status' can be 'Current' or 'Old,' the clause will be

WHERE `status` = 'Current' OR `status` = 'Old'

With this, items whose 'status' is either 'Current' or 'Old' will be fetched.

If you have more than one field in the insert/update form that use the foreign table to generate the menu options, then, edit the configuration of each of those fields to include the extra MySQL.
 
D

Darly Coupet

Guest
I have three tables:
Country, States and City

All tables are pre-filled with values (parent and child relashionship).

In Insert/Update form, In country field, I would select a country (USA) and list of States will be displayed in states field, then select a state (Maryland) and list of Cities will be displayed in city field.

thinking a way to accomplish with sql query?

Thanks, Darly

 
A

alpha2zee

Guest
I think implementing what you want (technically referred to as chained selection) would require some further, good modification to the DaDaBik code. The parameter passed by the extra_mysql value is 'static.' For chained selection, it needs to be 'dynamic.' Javascript is perhaps the only way technology to achieve this.

If you want to achieve this without DaDaBik, there are many example scripts on the web. E.g., http://www.zend.com/zend/tut/drop-down.php
 
D

Darly Coupet

Guest
Excellent reference! this feature has been requested many times in this forum and the solution you referenced would solve that problem.

Agree that implementing the chained selection in Dadabik code would be a great enhancement to its functionality.

Thanks, Darly
 
D

DebbieS

DaDaBIK Guru
alpha2zee:

First ... let me say this is an awesome hack! I may just use it if the need arises.

Re your post

I think implementing what you want (technically referred to as chained selection) would require some further, good modification to the DaDaBik code. The parameter passed by the extra_mysql value is 'static.' For chained selection, it needs to be 'dynamic.' Javascript is perhaps the only way technology to achieve this.

If you want to achieve this without DaDaBik, there are many example scripts on the web. E.g., http://www.zend.com/zend/tut/drop-down.php
Click to expand...

I've been trying (UNsuccessfully) to incorporate the script noted above into one of my DaDaBIK installs. So far, all I've been able to do is have that available in a separate form from DaDaBIK which writes the data to the DB. While this works, the search and other stuff from within DaDaBIK do not have the same chained select going on which is causing greif with users.

Do you have any ideas how to get a chained select working from WITHIN DaDaBIK so all the features of the program can still be accessed? I've been at this for weeks and nothing I do seems to work.

Thanks!

 
D

DebbieS

DaDaBIK Guru
OK ... I think I may be making some headway here ... this is what I've done so far based on using the chainedSelectors script referenced above ...

header.php
Inserted the following code just BEFORE the </head> tag:
<?
require("./include/chainedSelect.php");
?>
<script type="text/javascript" language="JavaScript">
<?php
$areacode->printUpdateFunction();
?>
</script>

footer.php
Inserted the following code right AFTER the opening DaDaBIK Blurb at the beginning of the file:
<script type="text/javascript" language="JavaScript">
<?php
$areacode->initialize();
?>
</script>

In dadabik_ table for the actual table, I created two additional field types :: chain1 and chain2 so I could reference them in business_logic.php to display the fields.

In the Chained selectors file where it calls the function to build the two select boxes, I split them so that one part could go with chain1 and one with chain2 -- part that was split is here (added part in bold):

print(">$value</option>\n");
}
print("</select>");
}

function printSelectors1()
{
/*
**create empty target selector
*/
$dummyData = str_repeat("X", $this->longestTargetChoice);


business_logic.php
Added two new case types for insert form and added just before case "select_single" on or about line 1034:
case "chain1";
if ($form_type === 'insert' || $show_insert_form_after_error === 1){
$form .= "<td class=\"td_input_form\">";
$form .= $areacode->printSelectors();
$form .= "</td>";
}
break;

case "chain2";
if ($form_type === 'insert' || $show_insert_form_after_error === 1){
$form .= "<td class=\"td_input_form\">";
$form .= $areacode->printSelectors1();
$form .= "</td>";
}
break;

Now here comes the problem. Everything seems to be fine except that the following error occurs when trying to show the insert form:

Fatal error: Call to a member function printSelectors() on a non-object in \...\business_logic.php on line 1037.

Seems as though somewhere along the line, the script forgot that this was an object or maybe I have to declare it again. alpha2zee or Darly -- do either of you have any ideas? I feel like I'm getting really close now to getting this to work.

BTW ... My two files that I'm using for the chained select class look like this:

chainedSelect.php:
FROM:

<?php
/*
** get chainedSelectors class

TO:

//instantiate class
$areacode = new chainedSelectors(
$selectorNames,
$selectorData);
?>

chainedSelectors.php:
FROM:

<?php
/*
** Class: chainedSelectors

TO:

print("update" .$this->names[CS_SECOND_SELECTOR] . "();\n");
}
}
?>

Any portions of the form that is originally part of the script have been removed and placed elsewhere as noted above in the DDB files:

header.php:
<script type="text/javascript" language="JavaScript">
<?php
$areacode->printUpdateFunction();
?>
</script>

</head>

business_logic.php (split into two):
<?php
$areacode->printSelectors();
?>

footer.php:
<script type="text/javascript" language="JavaScript">
<?php
$areacode->initialize();
?>
</script>

I'm going to keep plugging away at this tomorrow, but if anyone comes up with anything before I come up with a fix, it is greatly appreciated.

Thanks!

 
D

DebbieS

DaDaBIK Guru
One thing missing from these instructions if you plan to implement this prior to running install.php. The additional blank field must be added to the INSERT INTO lines for the user table. Beginning on or about line 102, add an additional blank field (,'' ) just before the 'is_equal....' field. If this is not added, then there will be errors displayed for the internal users table after install.php is run.

 
You must log in or register to reply here.
Top