Query Question

------=_NextPart_000_0154_01CC1841.03860F40
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I have been working on a class methods for some time now.



I have reached a cross road when it comes to common practice of developing
query structure.



Long ago I wrote queries where I just called the field I wanted on a
particular table unless I was joining them.



Example:

$query = " SELECT id FROM Table WHERE Clause";



Through time I developed a habit of queering as such.

Example:

$query = "SELECT tablename.id FROM db.table WHERE clause";





I have felt that, because my server contains multiple databases and I needed
to jump between databases and tables without changing the connector this
always has been best practice for me.



Someone recently told me,

Rich,

I do not agree with your design of the queries.

There is no need to include the DB and table name in the query if you are
not joining tables.





While I have a very hard time understanding this response as being valid. I
will propose the question.





Is it bad practice to write queries with the database and table name in the
queries even if I am NOT joining tables?

Is there an impact from PHP or MySQL that is caused by doing so?



I know this more a MySQL question but as PHP developers we all deal with
queries on a day to day bases,

and when developing more flexible class methods I build the queries in the
method.











Richard L. Buskirk


------=_NextPart_000_0154_01CC1841.03860F40--

Re: Query Question

I would keep the db names and/or schema names in your queries.

It clarifies what you are doing with the query making it easier for
someone else to debug, improve, ... Certainly for those not familiar
with your db structure.

I am not aware of any drawbacks that can result from this. (Ok, maybe
your program is a few bytes longer?)

regards,
Maarten

On Sun, 2011-05-22 at 05:27 -0400, admin@buskirkgraphics.com wrote:
> I have been working on a class methods for some time now.
>=20
> =20
>=20
> I have reached a cross road when it comes to common practice of developin=
g
> query structure.
>=20
> =20
>=20
> Long ago I wrote queries where I just called the field I wanted on a
> particular table unless I was joining them.
>=20
> =20
>=20
> Example:
>=20
> $query =3D " SELECT id FROM Table WHERE Clause"; =20
>=20
> =20
>=20
> Through time I developed a habit of queering as such.
>=20
> Example:
>=20
> $query =3D "SELECT tablename.id FROM db.table WHERE clause";
>=20
> =20
>=20
>=20
>=20
> I have felt that, because my server contains multiple databases and I nee=
ded
> to jump between databases and tables without changing the connector this
> always has been best practice for me.
>=20
> =20
>=20
> Someone recently told me,
>=20
> Rich,=20
>=20
> I do not agree with your design of the queries.
>=20
> There is no need to include the DB and table name in the query if you are
> not joining tables.
>=20
> =20
>=20
>=20
>=20
> While I have a very hard time understanding this response as being valid.=
I
> will propose the question.=20
>=20
> =20
>=20
>=20
>=20
> Is it bad practice to write queries with the database and table name in t=
he
> queries even if I am NOT joining tables?
>=20
> Is there an impact from PHP or MySQL that is caused by doing so?
>=20
> =20
>=20
> I know this more a MySQL question but as PHP developers we all deal with
> queries on a day to day bases,=20
>=20
> and when developing more flexible class methods I build the queries in th=
e
> method.
>=20
> =20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> Richard L. Buskirk
>=20

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Query Question

That's a very good point,

but since I use postgres that's one point that doesn't affect me and as
such didn't cross my mind at the time.

(Postgres uses schemas where mysql uses databases, a different database
on postgres usually means a different database machine. And joining
tables from different database machines isn't really plausible.)

I read the question as 'schema names' in stead of 'database names'.


On Mon, 2011-05-23 at 06:47 -0500, Bret Hughes wrote:
> I like to set the dbname in a config file and use it only for making the=20
> connection to the database. That way I can test/develop against=20
> different databases on the same server. By locking yourself into=20
> dbnames in the queries themselves I believe you loose a huge amount of=20
> environmental flexibility.
>=20
> Using tablenames and or aliases is something I do a fair amount of but=20
> only when joining or with complicated queries where clarity is an issue.
>=20
> On 05/23/2011 02:20 AM, maarten wrote:
> > I would keep the db names and/or schema names in your queries.
> >
> > It clarifies what you are doing with the query making it easier for
> > someone else to debug, improve, ... Certainly for those not familiar
> > with your db structure.
> >
> > I am not aware of any drawbacks that can result from this. (Ok, maybe
> > your program is a few bytes longer?)
> >
> > regards,
> > Maarten
> >
> > On Sun, 2011-05-22 at 05:27 -0400, admin@buskirkgraphics.com wrote:
> >> I have been working on a class methods for some time now.
> >>
> >>
> >>
> >> I have reached a cross road when it comes to common practice of develo=
ping
> >> query structure.
> >>
> >>
> >>
> >> Long ago I wrote queries where I just called the field I wanted on a
> >> particular table unless I was joining them.
> >>
> >>
> >>
> >> Example:
> >>
> >> $query =3D " SELECT id FROM Table WHERE Clause";
> >>
> >>
> >>
> >> Through time I developed a habit of queering as such.
> >>
> >> Example:
> >>
> >> $query =3D "SELECT tablename.id FROM db.table WHERE clause";
> >>
> >>
> >>
> >>
> >>
> >> I have felt that, because my server contains multiple databases and I =
needed
> >> to jump between databases and tables without changing the connector th=
is
> >> always has been best practice for me.
> >>
> >>
> >>
> >> Someone recently told me,
> >>
> >> Rich,
> >>
> >> I do not agree with your design of the queries.
> >>
> >> There is no need to include the DB and table name in the query if you =
are
> >> not joining tables.
> >>
> >>
> >>
> >>
> >>
> >> While I have a very hard time understanding this response as being val=
id. I
> >> will propose the question.
> >>
> >>
> >>
> >>
> >>
> >> Is it bad practice to write queries with the database and table name i=
n the
> >> queries even if I am NOT joining tables?
> >>
> >> Is there an impact from PHP or MySQL that is caused by doing so?
> >>
> >>
> >>
> >> I know this more a MySQL question but as PHP developers we all deal wi=
th
> >> queries on a day to day bases,
> >>
> >> and when developing more flexible class methods I build the queries in=
the
> >> method.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> Richard L. Buskirk
> >>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php