How to search on lookup table

Stefan,
Let me see if I understand this correctly.

1. You have (potentially) many projects per each onecustomer.
   
2. Each project belongs to only one customer.
   
3. You want to be able to search within the project list to see only projects belonging to a given customer.
   If those are your goals, you can accomplish them without using any php snippets.
   Set up a custom query in the projects list, as follows.
   SELECT
   
   projects.projectid AS proj_projectid,
   
   projects.custid AS proj_custid,
   
   projects.otherfield AS proj_otherfield,
   
   customers.custid AS cust_custid,
   
   customers.custname AS cust_custname
   
   FROM
   
   projects JOIN customers ON (projects.custid = customers.custid)
   Note that I have created aliases for every field, such as proj_projectid in place of projects.projectid. But note that those aliases are not used in the ON clause. For some reason, aliases of field names are not allowed in a WHERE or ON clause.
   (You might be able to get away without an alias for every single field, but I advise against it. SQL can become confusing. Keep it clear and uniform.)
   Of course you can add additional fields using the same naming conventions.
   When you change your existing, pre-generated query to one like the above, you'll notice that each working variable within PHPRunner then appears as the alias name rather than as the original table name. This is exactly what you want. You can then do your search for a specific customer, within the projects list, by searching on cust_custname.
   To be safe, try this technique on a fresh PHPRunner file using your actual database, just to get a feel for it. Once you feel confident that everything is working correctly in the test file, you can carefully transfer the method to your ongoing PHPRunner file. Another good approach is to leave your current courses list alone and instead create a custom view on the Datasource Tables page, using the custom query shown above. That way your changes won't affect anything outside the custom view.
   But first, make a full backup of your work. You must have an organized backup system for all your PHPRunner-related files! Every change you make has the potential to cause trouble or confusion in your .phpr file. Whenever that happens, don't keep trying and trying to fix it. Instead, start over with another copy of your backup files.
   Try this. You may like it! And please let me know if you can't get it working. I've made an effort to be as clear and accurate as I can, but I might easily have made a mistake somewhere in the above. This stuff can be tricky, but if you're organized, and make only a few changes at a time, you can manage the complexity.
   Good luck.
   Ralph
   

   Hi there,
   

   
   Many of us have the problem of proper searching on lookup values... Here's a trick that works, without having to modify the .php files...
   I have a project table and a customer table. Projects are linked to customers by a 'custid' field. This is implemented in the project table as a lookup field on the customer table.
   I have my project table definition set up that both the 'custid' field as the 'custname' field are in there.The field 'projectid' is the key field in here.
   
   My customer table uses 'customerid' as the id field and 'custname' as the customers name.
   In phpRunner for the 'project' definition (I'm using a 4.x version) in the section 'choose fields' I only check the search for the 'custname' field; for the 'custid' field all necessary operations are checked (list, view, add, edit, search,...). The custid field in the add/edit is set to a 'lookup table' field on the table 'customers', Link field is 'customerid', display field is 'custname'.
   I've added this code to both the 'After record added' as 'After record updated' event for the 'project' table:
   ****
   
   global $conn;
   $custname="";
   $rsCust = db_exec("select custname from customers where customerid='".$values["custid"]."'", $conn);
   
   if ($datacs=db_fetch_array($rsCust)) {
   
   $custname = $datacs["custname"];
   
   }
   db_exec("update myprojects set custname='".$custname."' where projectid=".$keys["projectid"], $conn);
   *****
   This snippet of code keeps the custname field in sync with the custid field in the projects table...
   The result on the projects pages:
   

• my list page on projects shows the 'customer name'
  
• the add/edit (inline add/ inline edit), advanced search nicely show dropdown boxes for updating the 'customer'
  
• but most important!!! The small search field on top of the listpage will allow me to search on the 'customer name'
  Try it for yourself!
  Stefan

Hi Ralph,
I had a look to your suggestion... but it's not a solution to my major problem...

1. working with joins gives problems with adding/editing records... (didn't even got the possibility to add the 'edit' functionality).
   
2. still have the problem that I want to show a dropdown of customers in my 'add' or 'edit' interface, but store the customer_id in the database, and have the customer name (and not the id) listed in the 'list' view, and being able to search in the list view on the 'customer name'.
   Could imagine of other workarounds, like build my add and edit pages on the single table, specifying lookup table for the customer_id field (gives me the dropdown functionality, and id-storage); then create another view with a join table for the 'list' view (gives me the customer name and search functionality)..., merging the 'add' and 'edit' buttons of the single table view on this page... would give me all...
   by the way... I'm a PHP programmer and I'm using phpRunner for over a year now, and start getting the real feel of it.
   Regards
   Stefan