Re: [PHP] Queries and Common Practices
On Sun, May 22, 2011 at 17:38, tedd wrote: > SELECT p.id, p.name, a.total FROM people p, accounts.a WHERE gender = 'male' > Finding the error in the above code is fun. I'm surprised I spotted, it shows how sensitive one gets to debugging. For that matter, I like the OP's practice of redundancy in the name of consistency. If nothing at the least, it gets us used to looking at the code to debug as above. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Queries and Common Practices
At 10:50 AM +0100 5/22/11, Ashley Sheridan wrote: -snip- 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. Ash: Whenever I see p.id (or similar) I think there is a join coming. So, I always use: SELECT id, name FROM people WHERE gender = 'male' Unless there a join, such as: SELECT p.id, p.name, a.total FROM people p, accounts.a WHERE gender = 'male' Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Queries and Common Practices
On 22 May 2011 10:50, Ashley Sheridan wrote: > On Sun, 2011-05-22 at 05:33 -0400, ad...@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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Queries and Common Practices
On Sun, 2011-05-22 at 05:33 -0400, ad...@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
[PHP] Queries and Common Practices
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
