Is select_db necessary?

--001636426c1d58b7fc0470e9578c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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?

--001636426c1d58b7fc0470e9578c--

Re: Is select_db necessary?

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

--
Paul M. Foster

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

Re: Is select_db necessary?

"Paul M Foster" wrote in message
news:20090812035618.GD2555@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 " 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 = " 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: 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: Is select_db necessary?

"PJ" wrote in message
news:4A82AA58.2020806@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: Is select_db necessary?

[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.

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

Re: 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: 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: 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]
>=20
> 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;
>=20
> SELECT a.foo, a.bar
> FROM myDatabase.myTable a
> WHERE you set other conditions here
>=20
> All that is required is that you establish a connection to a server.

If I recall correctly, this will cause issues with replication in=20
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: 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_deffke@yahoo.de

"Allen McCabe" wrote in message
news:657acef20908112023y222de6f4q63e64cd1e2785d35@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

Re: Is select_db necessary?

'Twas brillig, and Jay Blanchard at 12/08/09 13:53 did gyre and gimble:
> Jay Blanchard wrote:
>> 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".

I'm interested to know why you consider this to be very flexible and how
this leaves the selection in the database's hands?

If I were to implement this and they try some destructive testing/demo
on a sacrificial database, I'd have to use a whole other server instance
(as all the queries would hardcode in the db name).

Is it not more flexible if you omit the table name in every single query
and specify it once in your bootstrap/connection code? Thus doing tests
on other dbs etc. is a pretty simple switch of the connection code.

Also telling the db engine what database you want to use in every query
is not, IMO, leaving the selection in the the database's hands.

Just curious as to the rationale here :)

Col




--

Colin Guthrie
gmane(at)colin.guthr.ie
http://colin.guthr.ie/

Day Job:
Tribalogic Limited [http://www.tribalogic.net/]
Open Source:
Mandriva Linux Contributor [http://www.mandriva.com/]
PulseAudio Hacker [http://www.pulseaudio.org/]
Trac Hacker [http://trac.edgewall.org/]


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

Re: Is select_db necessary?

I agree totally, are we not dicussing speed issues all the time? and then we
recommend a code doing an unessesary job on every call?

an ANSI selct db in the sql forces any database to run the internal select
db because there would be no check if the databse is the current one.
because, databasedevelopers can espext some smartness of us, the
programmers. its a lot off stuff to do for the database to select a
database. for shure, the database leafs that IN OUR hand to avoid to force
time consuming server resources.

ralph
ralph_deffke@yahoo.de

"Colin Guthrie" wrote in message
news:h5ug1h$tjb$1@ger.gmane.org...
> 'Twas brillig, and Jay Blanchard at 12/08/09 13:53 did gyre and gimble:
> > Jay Blanchard wrote:
> >> 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".
>
> I'm interested to know why you consider this to be very flexible and how
> this leaves the selection in the database's hands?
>
> If I were to implement this and they try some destructive testing/demo
> on a sacrificial database, I'd have to use a whole other server instance
> (as all the queries would hardcode in the db name).
>
> Is it not more flexible if you omit the table name in every single query
> and specify it once in your bootstrap/connection code? Thus doing tests
> on other dbs etc. is a pretty simple switch of the connection code.
>
> Also telling the db engine what database you want to use in every query
> is not, IMO, leaving the selection in the the database's hands.
>
> Just curious as to the rationale here :)
>
> Col
>
>
>
>
> --
>
> Colin Guthrie
> gmane(at)colin.guthr.ie
> http://colin.guthr.ie/
>
> Day Job:
> Tribalogic Limited [http://www.tribalogic.net/]
> Open Source:
> Mandriva Linux Contributor [http://www.mandriva.com/]
> PulseAudio Hacker [http://www.pulseaudio.org/]
> Trac Hacker [http://trac.edgewall.org/]
>



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

Re: Re: Is select_db necessary?

--0016e64697429a17bf0470f27c59
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Wed, Aug 12, 2009 at 10:37 AM, Ralph Deffke wrote:

> I agree totally, are we not dicussing speed issues all the time? and then
> we
> recommend a code doing an unessesary job on every call?
>
> an ANSI selct db in the sql forces any database to run the internal select
> db because there would be no check if the databse is the current one.
> because, databasedevelopers can espext some smartness of us, the
> programmers. its a lot off stuff to do for the database to select a
> database. for shure, the database leafs that IN OUR hand to avoid to force
> time consuming server resources.
>
> ralph
> ralph_deffke@yahoo.de
>
> "Colin Guthrie" wrote in message
> news:h5ug1h$tjb$1@ger.gmane.org...
> > 'Twas brillig, and Jay Blanchard at 12/08/09 13:53 did gyre and gimble:
> > > Jay Blanchard wrote:
> > >> 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".
> >
> > I'm interested to know why you consider this to be very flexible and how
> > this leaves the selection in the database's hands?
> >
> > If I were to implement this and they try some destructive testing/demo
> > on a sacrificial database, I'd have to use a whole other server instance
> > (as all the queries would hardcode in the db name).
> >
> > Is it not more flexible if you omit the table name in every single query
> > and specify it once in your bootstrap/connection code? Thus doing tests
> > on other dbs etc. is a pretty simple switch of the connection code.
> >
> > Also telling the db engine what database you want to use in every query
> > is not, IMO, leaving the selection in the the database's hands.
> >
> > Just curious as to the rationale here :)
> >
> > Col
> >
> >
> >
> >
> > --
> >
> > Colin Guthrie
> > gmane(at)colin.guthr.ie
> > http://colin.guthr.ie/
> >
> > Day Job:
> > Tribalogic Limited [http://www.tribalogic.net/]
> > Open Source:
> > Mandriva Linux Contributor [http://www.mandriva.com/]
> > PulseAudio Hacker [http://www.pulseaudio.org/]
> > Trac Hacker [http://trac.edgewall.org/]
> >
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


$link = mysql_connect( /* settings */);
mysql_select_db( 'database', $link);
$result = mysql_query( 'SELECT * FROM