List:General Discussion« Previous MessageNext Message »
From:(Hal Date:April 13 2012 12:59am
Subject:Re: The most elegant/efficient way to pull this SQL query
View as plain text  
;>>> 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.

Thread
The most elegant/efficient way to pull this SQL queryHaluk Karamete11 Apr
  • Re: The most elegant/efficient way to pull this SQL queryPeter Brawley11 Apr
    • Re: The most elegant/efficient way to pull this SQL queryhsv12 Apr
      • Re: The most elegant/efficient way to pull this SQL queryHaluk Karamete12 Apr
        • Re: The most elegant/efficient way to pull this SQL queryhsv13 Apr
  • Re: The most elegant/efficient way to pull this SQL queryhsv12 Apr