2. PHPRunner General questions
  3. Cascaded dependency
This topic is locked

Cascaded dependency

 		2/24/2010 4:45:13 PM
PHPRunner General questions
N
 NAngelGr author

Hi guys

I am a new bee here bla bla bla...

Just a quick question for the experts here. So...

Nothing complicated. 3 simple tables.

(1st)tableCities is linked to the (2nd)tableStates (via StateID) and the latter linked to the (3rd)tableCountries (via CountryID)
talbeCountries(

CountryID integer autoincrement not null

Country varchar(50)

)
tableStates(

StateID integer autoincrement not null

State varchar(50)

CountryID integer

)
tableCities(

CityID integer autoincrement not null

City varchar(50)

StateID integer

)
So my question is how to create a Cities insert/edit/delete page where there will be two dropdown lists; one for the countries and another for the States so that when the user inserts a city then the country has to be selected and the states dropdown would alter its contents accordingly and finally the states would be selected.

The country selection would dictate the states contents and the states selection would be saved into the cities table.
Hope that the above is not confusing...
Thanx a million in advance
NAngelGr
R
 		raver 3/29/2010

Hi everyone,
I have a similar problem (Brands, Models and Inventory), all are one-to-many relationships.

Inventory has a foreign key to Models and Models has a foreign key to Brands.

I would like to have something like dependent/linked dropdown boxes in order to add new items to inventory. First select the brand, then the model, but only the related models should be displayed.

I created a custom query linking all tables, but fields not belonging to Inventory table had to be deselected from Add/Edit pages. They are only allowed on List page because of INSERT and UPDATE actions.
I managed to create a custom dropdown box (code snippet in Add/Edit pages) which returns idBrand.

//create dropdown box

$str = "";

$str.= "<select onchange=\"alert(this.value)\">

<option value=\"\">Please select</option>";

//select values from database

global $conn;

$strSQL = "select idBrand,brandName from Brand";

$rs = db_query($strSQL,$conn);

while ($data = db_fetch_array($rs)) {

$str.="<option value=".$data["idBrand"];

if ($data["idBrand"] == $_SESSION["SelectBrand"])

$str.= " selected=\"\"";

$str.=">".$data["brandName"]."</option>";

}

$str.="</select>";

echo $str;


I was thinking on passing that value as a PHP argument and then refresh the page, but don't really know how to go from here.

Maybe setting a global variable ($_SESSION["SelectBrand"]) and then use it on the "WHERE" clause of the inventory dropdown box.

One doubt is if it's possible to set a global variable by passing an argument on the URL (http://hostname/tablename_add?param=value).
Can someone give some hints?

Thanks in advance.
Sergey Kornilov admin 3/30/2010

I recommend to check 'How to setup dependent dropdown boxes on Edit/Add pages' tutorial at http://xlinesoft.com/phprunner/php-database.htm for more details on setting up cascading dependent dropdowns.
R
 		raver 3/31/2010

Hi,
I already tried to setup dependent dropdown boxes that way, but that implies having both 'idBrand' and 'idModel' fieds on Inventory' table.

IMO this would be redundant because 'Models' table already has a foreign key to 'Brands' table (idBrand).

Brands Models Inventory Requests

----------- ------------ ---------------- -------------

idBrand (PK)-1-. idModel (PK)-1-. idInventory (PK)-1-. idRequest (PK)

brandName `-*-idBrand `-*-idModel `-*-idInventory

modelName ....... ........


Is there a way to setup dependent dropdown boxes with this model?
Thanks in advance.
J
 		Jane 4/2/2010

Hi,
unfortunately PHPRunner do not support to use custom views created on the Datasource tables as link tables.

As workaround create view in the database directly, join Inventory and Models tables, then set up Brand and Model fields as dependent dropdown boxes.
Here is an article on how to update joined tables in PHPRunner:

http://xlinesoft.com/phprunner/docs/update_multiple_tables.htm
R
 		raver 4/5/2010



As workaround create view in the database directly, join Inventory and Models tables, then set up Brand and Model fields as dependent dropdown boxes.


Hi,
Thanks a lot for your tip! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=48984&image=1&table=forumreplies' class='bbc_emoticon' alt='B)' /> It worked perfectly.

I only had to add this line to both "Before record added/updated" events.

unset($values["idBrand"]);


Best Regards
R
 		raver 4/5/2010

There were some problems after all <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=48992&image=1&table=forumreplies' class='bbc_emoticon' alt=':P' />
I had do disable record deletion on Inventory table because the view has a join clause.

The worse is that I have a details table linked to Inventory (master table), and can't add details (inline add) on the master 'Add' page. I get an error related to a foreign key constraint.

However, I'm able to add/edit details on the Inventory master 'Edit' page.

Could it be caused by trying to insert records to a database view? BTW I'm running MySQL 5.0.77.

I'm also unable to use the query designer on the database view (error msg: "PHPRunner was unable to parse your SQL query").
Are there some solutions/workarounds?
Thanks in advance.