Re: Queries and Common Practices

On 22 May 2011 10:50, Ashley Sheridan <ash@ashleysheridan.co.uk> wrote: > On Sun, 2011-05-22 at 05:33 -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 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 >> > > > I don't know of any impact, but I don't use the database name in the > query much, only when I need to perform a join across different > databases. However, I almost always specify the table name, even if I'm > doing a single table query, as I often find I may need to alter the > query to pull in extra information from other tables. I do it much like > you do, but I also give the table a moniker which lets me shorten the > queries as I type: > > SELECT p.id, p.name FROM people p WHERE p.gender = 'male' > > This way, I can easily join in other tables, my typing is kept to a > minimum as I do it also. > > -- > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > I use multiple databases on multiple servers (using linked servers). In many cases the databases contain the same table names. It is a force of habit that I use [server].[database].[owner].[table] alias. If your connection is linked to a single DB and the query is only for that DB, then [server].[database].[owner] is redundant. Richard. -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea