2. PHPRunner General questions
  3. Grab ID from one table and use that ID to search on a second table
This topic is locked

Grab ID from one table and use that ID to search on a second table

 		10/22/2007 10:42:29 AM
PHPRunner General questions
G
 gdkoduro author

I am a PHP/MySQL newbie, but I have managed to upload our MS Access database tables to a Linux webserver with relative ease, thanks to the latest version of PHPRunner 4.1.
We have two main tables; the rest are lookup tables.
The two main tables are tblClient and tblClientCase (one Client to many ClientCases). In practice though one Client has one ClientCase in our database. The tables are not linked.
A unique row or recordset in tblClient is identified by ClientID.
A unique row or recordset in tblClientCase is identified by CaseID and foreign key ClientID from tblClient (i.e. 'tblClientCase'.'CaseID' and 'tblClient'.'ClientID').
Client Data is added, edited, or viewed by user on the "Client Data" Tab after log in.
The remaining tabbed pages (provided by PHPRunner) can then be viewed or edited. These remaining tabbed pages each provide a view on a grouped number of fields taken from tblClientCase. The first of these remaining tabbed pages has "Add New" command button in addition to "Edit" hyperlink, the rest have only an "Edit" hyperlink.
I would like to use the ClientID of the currently viewed Client on the Client Data tab to view or edit all the other tblClientCase tabs.
At present a user has to memorise the ClientID value from the first Client Data screen. Then on all other screens relating to ClientCase (tblClientCase views) the user searches for ClientID equals ### before proceeding, which is very time consuming.
I figure from extensive reading of postings on this site that the solution is to store the currently viewed ClientID [Client Data tab] in a global $_SESSION variable and then use this value to access (pardon the pun) all the views on tblClientCase as a constraining variable in a SELECT FROM tblClientCase WHERE 'tblClientCase'. 'ClientID' = 'tblClient'.'ClientID' statement.
The posting I found most similar to my requirement is http://www.asprunner.com/forums/index.php?...&hl=Stoppay, and he seems to have figured it out using $_GET['editid1']
I guess what I want is:
$_SESSION['ClientID'] = Currently viewed ClientID (on Client Data tab/page)

If $_SESSION['ClientID'] is found in 'tblClientCase' then

$strSQL = SELECT FROM tblClientCase WHERE 'tblClientCase'. 'ClientID' = 'tblClient'.'ClientID'

Else

AddNew (insert into tblClientCase (ClientID) values 'ClientID' etc)
OR

$_SESSION['ClientID'] = Currently viewed ClientID (on Client Data tab/page)

If $_SESSION['ClientID'] is found in 'tblClientCase' then

$strSQL = SELECT * FROM tblClientCase WHERE 'tblClientCase'. 'ClientID' = $_SESSION['ClientID']

Else

AddNew (insert into tblClientCase (ClientID) values $_SESSION['ClientID'] etc)
I do not know whether it is a client side javascript solution that is required or whether it could be done server side. I am not sure which Event function (eg BeforeEdit, Onload) or even which php file to edit. In short any help would be immensely valuable and appreciated.
Thanks

darkwa
F
 		frocco 10/22/2007

I'm confused.

Why did you not just setup a Master/Detail relationship in PHPRunner?

Then on the master record, you can click and see all detail records.
Frank
G
 		gdkoduro author 10/28/2007

Thank you for your comment, Frank.
I did implement the tables as Master-Detail while designing the forms in PHPRunner.
PHPRunner gave me hyperlinks on the Master Table Tab (ClientData Tab). From these hyperlinks, program appeared to call ClientDetails_detailspreview.php with its own internal values (which I still have not been able to figure out!) and then call ClientDetails_list.php passing the correct ClientID so that user could view the current Client on other tabs.
The problem is that once away from the Master Table Tab (ClientData Tab), the hyperlinks were out of sight. User still had to search for ClientID on the current Tab (detail table view) or go back to the Master Table Tab (ClientData Tab) to access the hyperlinks.
What I wanted was to have user, having searched once for a Client, to be able to click whichever detail table view and be able to see details for the current Client.
I thought I should feedback to the community how I finally managed to find a solution of sorts. It is not very clever and certainly not bullet-proof but it may save somebody some time and effort.
Step 1:

I traced through the ClientDetails_list.php and found:

//ClientID -

$value="";

$value = ProcessLargeText(GetData($data,"ClientID", ""),"field=ClientID".$keylink,"",MODE_LIST);

$row[$col."ClientID_value"]=$value;
Step 2:

I added this line to define a SESSION variable:

$_SESSION["GlobalClientID"] = $value;
Step 3:

I traced through each of the other X_detail_list.php files to find:

//$strSQL = $gstrSQL;

$strWhereClause="";
Step 4:

I changed the empty $strWhereClause="" clause to:

$strWhereClause="ClientID = ".$_SESSION["GlobalClientID"];
This solution works whether the two tables are linked or not, master-detail or not, so long as the secondary detail table has a corresponding "foreign key" in this case ClientID.
I think a neater solution would be to be able to redesign the forms so that the search form is accessible from all views/pages, rather than from the ClientDetails Tab.
I would be grateful for any advice on how to achieve this.

Image1:

Image2:

Image3:

Image4:

Image5:

The idea is illustrated in Image 1.
User would search for Client from a combo/dropdown box centrally placed and to the right of "Change Password" hyperlink, instead of using the "Search for" routine provided by PHPRunner on the Client Data Tab. In this way the search routine would be available to the user from all views or Tabs. But the rule would still be "search once", if found assign current ClientID to $_SESSION[], use current ClientID with all Tabs until user performed another search.
Thanks to all who stopped by to read this post from a newbie.
darkwa