Re: [PHP] Is select_db necessary?
Paul M Foster pa...@quillandmouse.com wrote in message news:20090812035618.gd2...@quillandmouse.com... On Tue, Aug 11, 2009 at 08:23:21PM -0700, Allen McCabe wrote: I have seen different scripts for working with SQL, and most follow the same method with on difference. Variables are defined (host, password, etc.) mysql_connect command //then, the difference mysql_select_db command //back to common $sql = SELECT ... $result = mysql_query($ql) Is the database selection necessary, or is that implied with a SELECT or other SQL command? All major SQL DBMSes can have multiple databases available. A given database may contain a variety of tables. If you simply start firing SQL commands at a DBMS, it won't know which database to look in unless you tell it. By contrast, the connection process in PostgreSQL must include a database; there is no separate database selection function call. This is not totally accurate. With MySQL you connect to a server which is a container for one or more databases, so you need select_db in order to identify the current database name. With PostgreSQL you connect to a database which is a container for one or more schemas, so you need to issue the SET search_path TO schema command in order to identify the current schema. Oracle is the same in that you connect to a server which is a container for one or more databases, and unless you give every table a public synonym you must use the ALTER SESSION SET CURRENT_SCHEMA = schema command to identify the current schema. In all these cases this will allow you to issue an sql query which contains table names which do not have to be qualified with their database/schema names. If you wish to refer to a table which is not in the current database/schema then you must include the database/schema name. I consider the use of the term schema, as used by PostgreSQL and Oracle, to be inaccurate in that a database table is subordinate to a database, not a schema. That is why it is called a database table and not a schema table. -- Tony Marston http://www.tonymarston.net http://www.radicore.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Is select_db necessary?
Paul M Foster wrote: On Tue, Aug 11, 2009 at 08:23:21PM -0700, Allen McCabe wrote: I have seen different scripts for working with SQL, and most follow the same method with on difference. Variables are defined (host, password, etc.) mysql_connect command //then, the difference mysql_select_db command //back to common $sql = SELECT ... $result = mysql_query($ql) Is the database selection necessary, or is that implied with a SELECT or other SQL command? All major SQL DBMSes can have multiple databases available. A given database may contain a variety of tables. If you simply start firing SQL commands at a DBMS, it won't know which database to look in unless you tell it. By contrast, the connection process in PostgreSQL must include a database; there is no separate database selection function call. Paul I seem to recall from the manual, that once you have done mysql_connect , any subsequent queries will be directed at the same db until another mysql_connect points to another db. I repeat the call with every query as a precaution. I'm not expert, but I thought I'd offer my observation and am open to correction. ;-) PJ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Is select_db necessary?
PJ af.gour...@videotron.ca wrote in message news:4a82aa58.2020...@videotron.ca... Paul M Foster wrote: On Tue, Aug 11, 2009 at 08:23:21PM -0700, Allen McCabe wrote: I have seen different scripts for working with SQL, and most follow the same method with on difference. Variables are defined (host, password, etc.) mysql_connect command //then, the difference mysql_select_db command //back to common $sql = SELECT ... $result = mysql_query($ql) Is the database selection necessary, or is that implied with a SELECT or other SQL command? All major SQL DBMSes can have multiple databases available. A given database may contain a variety of tables. If you simply start firing SQL commands at a DBMS, it won't know which database to look in unless you tell it. By contrast, the connection process in PostgreSQL must include a database; there is no separate database selection function call. Paul I seem to recall from the manual, that once you have done mysql_connect , any subsequent queries will be directed at the same db until another mysql_connect points to another db. I repeat the call with every query as a precaution. I'm not expert, but I thought I'd offer my observation and am open to correction. ;-) PJ This only works if you specify a database name in the call to mysqli_connect(). If provided (it is optional) it identifies the default database name for all subsequent queries. If, like me, you have multiple databases available, then you need to use select_db in order to switch the default database from one to another. -- Tony Marston http://www.tonymarston.net http://www.radicore.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Is select_db necessary?
mysql_select_db is not necessary but it does make writing queries easier since you don't have to specify which database in each query. Take care, Floyd On Aug 11, 2009, at 11:23 PM, Allen McCabe wrote: I have seen different scripts for working with SQL, and most follow the same method with on difference. Variables are defined (host, password, etc.) mysql_connect command //then, the difference mysql_select_db command //back to common $sql = SELECT ... $result = mysql_query($ql) Is the database selection necessary, or is that implied with a SELECT or other SQL command? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Is select_db necessary?
Jay Blanchard wrote: [snip] Is the database selection necessary, or is that implied with a SELECT or other SQL command? [/snip] It depends on the database (as you have seen in many of the responses), but there is a way to keep from doing this if the database is ANSI compliant using proper SQL syntax; SELECT a.foo, a.bar FROM myDatabase.myTable a WHERE you set other conditions here All that is required is that you establish a connection to a server. If I recall correctly, this will cause issues with replication in MySQL... insofar as you perform amodifying query. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Is select_db necessary?
[snip] Jay Blanchard wrote: [snip] Is the database selection necessary, or is that implied with a SELECT or other SQL command? [/snip] It depends on the database (as you have seen in many of the responses), but there is a way to keep from doing this if the database is ANSI compliant using proper SQL syntax; SELECT a.foo, a.bar FROM myDatabase.myTable a WHERE you set other conditions here All that is required is that you establish a connection to a server. If I recall correctly, this will cause issues with replication in MySQL... insofar as you perform amodifying query. [/snip] You're correct with regards to queries that modify on replicated systems. If all you're doing is gathering data then this will work just fine, is somewhat self-documenting (especially in lengthier code containers), and very flexible. It also leaves the selection in the database's hands, and as we almost always say, let the database do the work when it can. If you are not using replicated systems you can set up your modifying queries the same way. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Is select_db necessary?
here a basic background to this question. all databases are build from various module bases. one module is the database itself prosessing eg. the sql's another module is the database connectivity. e.g. mySQL has a ability to connect thru ADO, .NET and an server via IP. MySQL supports unlimited databases comtaining tables. so from the point of the database u have always to selct the database and then to the table. however, if u study the various Database extensions u will find functions (eg. mysql_db_query() ) where u point the database in the function call while others don't (eg. mysql_query() ) on those u have to do a db select first because the function itself doesn't do it, while mysql_db_connect() does. so if we know that now, we are coming to the question, why are database extensions do have those two types of processing a sql statement? the answer is: speed ! while those commands with a pointing out the database do internally a select of the database they do it every time on each call. if u have a application which does a lot of stuff at the same time other then just select statement, this comes into consideration. it saves time to do one select_db first and then 50 just raw sql's to that database. now after dumping that much stuff on u, it depends on ur design if u need a select_db first or not. hope that helps Ralph ralph_def...@yahoo.de Allen McCabe allenmcc...@gmail.com wrote in message news:657acef20908112023y222de6f4q63e64cd1e2785...@mail.gmail.com... I have seen different scripts for working with SQL, and most follow the same method with on difference. Variables are defined (host, password, etc.) mysql_connect command //then, the difference mysql_select_db command //back to common $sql = SELECT ... $result = mysql_query($ql) Is the database selection necessary, or is that implied with a SELECT or other SQL command? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
