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
//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.
The example in my original question was like this.
I'm repeating it for convenience purposes.
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs
people table is like this;
and finally the state table goes like this;
What's the most straightforward way to achieve the following view with one
if the final table ( PeopleAndStates ) view I want were to be as follows;
Then I would have called the function like this;
But if I want to get, the following view instead;
I would like to be able to call my function as follows;
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
Once you write the parser, you can deploy it over many different cases such
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
2012/4/12 Halász Sándor <hsv@stripped>
> >>>> 2012/04/11 17:51 -0500, Peter Brawley >>>>
> select b.peopleID, concat('(',p.fname,,')'), b.stateID,
> from bridge b
> join people p on b.peopleID=p.peopleID
> join state s on b.stateID=s.stateID;
> Since the names are the same in the tables, it works to use "USING", too,
> and you are relieved of the burden of an alias:
> from bridge
> join people USING(peopleID)
> join state USING(stateID)
> If the fields "peopleId" and "stateID" are the only field names in common,
> "NATURAL JOIN" also works.
> from bridge
> NATURAL join people
> NATURAL join state
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql