2. PHPRunner General questions
  3. Wrong index on 'where' clause
This topic is locked

Wrong index on 'where' clause

 		10/26/2012 1:14:44 AM
PHPRunner General questions
F
 Flyarbox author

Programmer for 35 years. Total noob to PHPR. Still in "understanding how PHPR does things phase". So...referring to the following database:


I thought I'd start with an atypical case to see how PHPR works. The basic rules are:

  1. A movie CAN have 0 or 1 directors associated with it (a movie might not have had a director assigned yet...).
  2. A director CAN be associated with 0 to many movies (a director may not have directed any movies for the studio yet, or may have done any number of movies).
    All the fields in both tables have been selected in PHPR so as to be available to PHPR, with director_id and movie_id set to not display. A drop down lookup is used to select a director into a movie by name.
    On the SQL UPDATE statements for the "movies" table (when one is changing the director associated with a movie) the WHERE clause being generated by PHPR is, very oddly, something like "UPDATE movies SET <blah blah> WHERE director_id=2". Naturally, this results in ALL the records in the "movies" table in which the director_id=2 to be set to the edited values.
    The WHERE clause should be something like "WHERE movie_id=4" to just hit the one movie record we're trying to update. In other words, in the SQL UPDATE statement for the "movies" table the foreign key director_id is being used in the WHERE clause, where it should be using the "movies" table's own primary key (movie_id) in the WHERE clause.
    Totally lost on this one. I've turned off cascading deletes and suchlike (and sync'd the tables), same thing.
    Any insights would be appreciated.
C
 		cgphp 10/26/2012

Make sure you have set the movie_id as primary key for the movies table. Check the following article:
http://xlinesoft.com/phprunner/docs/edit_page_settings.htm
Choose the primary key for the movies table by clicking the Edit button in the "Choose page" section:
Sergey Kornilov admin 10/26/2012

Cristian is right - make sure correct primary key selected for movies table (movie_id) on 'Choose pages' screen.
F
 		Flyarbox author 10/26/2012

I'm certain this must be the answer. Now I must understand it.
PHPR draws all sorts of information directly from the tables: what the table names are, how many fields of what names and types and sizes, and suchlike.
Is not the primary key part of the collection of information available to PHPR for the asking? If so, why do we have to explicitly specify it in the PHPR UI? I would expect PHPR to use it as the default, and we would specify SECONDARY indexes as a departure in those cases when we need them.
N'est pas?
Sergey Kornilov admin 10/26/2012

PHPRunner recognizes primary keys defined in the database. However there might be situations when PHPRunner gets confused. If you want us to troubleshoot this specific setup contact support team directly sending us your database dump.