NYCPHP Meetup

[Brian Pang]

Here are the basics of my way of dealing with this... I use this all the
time, so hopefully peer review won't expose some serious flaw! :)
(besides you often end up defining a bunch of variables you don't
actually need, but, you're smart, you can figure out how to change that)


$fooFields = array("foo.id", "foo.bar");
$barFields = array("bar.id", "bar.foo");

$result = mysql_query("select * from foo, bar");

$fields = array_merge($fooFields, $barFields);

while ($content = mysql_fetch_array($result)) {
    for ($f = 0; $f < count($fields); $f++) {
            ${str_replace(".", "__", $fields[$f])} = $content[$f];
    }
    echo("
        foo.id: $foo__id<br>
        foo.bar: $foo__bar<br>
        bar.id: $bar__id<br>
        foo.id: $bar__foo<br>
}


I dunno if you would consider the double-underscore to be wacky variable
names or not. For me, since I use it so frequently, it works well and I
always know which table the data is coming from.
You can, of course, change the table/column separator to whatever you
like. I found __ to be unique enough to not conflict with anything else.
The "automation" of it is also really handy when dealing with lots of
fields.

enjoy


> On the subject of PHP, SQL, joins and the like, I encountered
something a bit weird and wanted to see what all of you had to say on it.
> 
>  This is from the manual:
> 
> "mysql_fetch_array() is an extended version of mysql_fetch_row(). In
>  addition to storing the data in the numeric indices of the result array,
>  it also stores the data in associative indices, using the field names as
>  keys.
> 
>   If two or more columns of the result have the same field names, the
>  last column will take precedence. To access the other column(s) of the
>  same name, you must use the numeric index of the column or make an alias
>  for the column. For aliased columns, you cannot access the contents with
>  the original column name (by using 'field' in this example)."
> 
> 
> 
> 
>   so I have address fields, for example, that overwrite eachother. I
>  either have to make up wacky names for the database columns, or I have
>  to write out AS names for each (so using Select * is out). Now why
>  wouldn't they throw in an option so your results have table names in
>  front? So client.zip, owner.zip, etc. could all coexist? Doesn't that
>  seem reasonable?
> 
> 
> Josh
> 
> 
> 
> On Tue, 5 Aug 2003, jim.bishop wrote:
> 
> > > I believe the best way to normalize your tables is to apply the
concept
> > > of wether the information is related to that ID and is not going to
> > > change or need to be archived. For example the users first and
last name
> > > and username/password and date of birth, etc are only related to this
> > > user and there is no reason to have additional tables, Addresses
on the
> > > other hand can change and history can be important depending on your
> > > project so a separate table for them is appropriate.
> > 
> > 
> > i had been thinking of that as well.  i think you're right about the
JOIN
> > nightmare to follow.  i just wanted someone else to tell me that it
would
> > be a nightmare, so i wasn't not trying it because it seemed too
abastract.
> > 
> > thanks!
> > 
> > 
> > 
> > >
> > > Your structure is good for random data that will change or be added
> > > later but for specific data that is unique to that ID and not going to
> > > change your prior table structure is the most efficient and easiest to
> > > code. Having over normalized (still up for debate) previous databases
> > > the data is nice and makes sense but writing extensive joins down the
> > > road become gruesome. I would recommend a blend of these two
techniques.
> > >
> > > Planning out your database with a nice entity diagram will make your
> > > life much easier and you should have the fields defined pretty well
> > > before you create your first table. Of course this is the old boring
> > > way but elliminates mistakes that will haunt you down the road.
> > >
> > > Hope this helps.
> > >
> > > On Tuesday, August 5, 2003, at 09:16 AM, jim.bishop wrote:
> > >
> > > >
> > > > I'm designing a new user database for a personal project, and I was
> > > > toying
> > > > with a different method of storing the data than I have used before.
> > > > Usually I create tables with logical column names that reference the
> > > > data
> > > > in the column.
> > > >
> > > > For Example:
> > > >
> > > > ---------------
> > > > | User        |
> > > > ---------------
> > > > | id          |
> > > > | first_name  |
> > > > | last_name   |
> > > > | email       |
> > > >
> > > > ...
> > > > ---------------
> > > >
> > > > But this becomes cumbersome when you have to add fields to the
table.
> > > > I
> > > > have designed a new table structure that looks like this:
> > > >
> > > >
> > > > ---------------
> > > > | User        |
> > > > ---------------
> > > > | uid         |
> > > > ---------------
> > > >
> > > > -----------------
> > > > | UserAttribute |
> > > > -----------------
> > > > | attribute_id  |
> > > > | uid_fk        | * foreign key to user table
> > > > | attribute     |
> > > > | value         |
> > > > -----------------
> > > >
> > > > Okay.  So I've built many user databases before and never
employed the
> > > > system below.  Has anyone else?  Besides the requisit JOIN that
has to
> > > > be
> > > > called with every SELECT, what is the downsides to using this
kind of
> > > > architecture?
> > > >
> > > >
> > > > _______________________________________________
> > > > talk mailing list
> > > > talk at lists.nyphp.org
> > > > http://lists.nyphp.org/mailman/listinfo/talk
> > > >
> > >
> > > _______________________________________________
> > > talk mailing list
> > > talk at lists.nyphp.org
> > > http://lists.nyphp.org/mailman/listinfo/talk
> > >
> > 
> > 
> > 
> > 
> >  -------------------------------------------------------
> >  -------------------------------------------------------
> >  -> the syntax of cummings, the absurdity of faulkner <-
> >  || jimbishop.org  ||   jim.bishop   ||  i heart n y  ||
> >  -------------------------------------------------------
> >  -------------------------------------------------------
> > _______________________________________________
> > talk mailing list
> > talk at lists.nyphp.org
> > http://lists.nyphp.org/mailman/listinfo/talk
> > 
> 
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
> 
>