;>>> 2012/04/12 11:56 -0700, Haluk Karamete >>>>
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument & parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
....
What's the most straightforward way to achieve the following view with one SQL
statement?
____peopleID__________stateID_____________
____1_(joe)___________1__(california)_____
____2_(bob)___________4__(texas)__________
____3_(charlie)_______5__(florida)________
</quote>
if the final table ( PeopleAndStates ) view I want were to be as follows;
____peopleID__________stateID_____________
____1_________________1___________________
____2_________________4___________________
____3_________________5___________________
Then I would have called the function like this;
showtable($dbh,$myTable,"peopleID,stateID")
But if I want to get, the following view instead;
____peopleID__________stateID_____________
____1_(joe)___________1___________________
____2_(bob)___________4___________________
____3_(charlie)_______5___________________
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,"peopleID(PeopleTable.PeopleID >>
FName),stateID")
To mean the following;
When you are outputting the peopleID, provide the corresponding "Fname" field
from the "PeopleTable" where peopleID there is equal to the peopleID you are
outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID >> FName), I
can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such as
ShowRecord($dbh,$table,$fields,"where peopleID<5","limit 100") and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID >> FName),stateID from PeopleAndStates
<<<<<<<<
(note that in MySQL '>>' is only right-shift.)
I fear that for this function in the end you will need
information_schema.COLUMNS.
Peter Brawley already gave you a good answer for one of your examples (but I
thus would write it):
select concat(peopleID, ' (',fname,')') AS peopleID, concat(stateID, '
(',state,')') AS stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
The middle example:
select peopleID, stateID from PeopleAndStates
The last:
select concat(peopleID, ' (',fname,')') AS peopleID, stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
I have assumed that you mean to join only on same-named fields with equality;
if not, JOIN ... ON ... is needed. In any case, you have to keep track of it,
whether in a result field the table name, too, is needed. If you use USING, for
that field leave the table name out.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
