Retrieving Data
The process of retrieving data once a connection has been made to the MySQL server is similar to the
process of interacting with the data through the mysql client. The first thing that needs to be addressed is
the database that holds the table (which in turn holds the data).
Selecting a Database
In the mysql client, a database is identified (or selected) by the USE <database> command. In PHP,
this is accomplished with a PHP MySQL API function called mysql_select_db(). This function identifies
the database that should be used for any subsequent requests to the data, which eliminates the need to
qualify every table (or other MySQL resource) called with the database name. There are two ways to use
this function:
• mysql_select_db($db_name, $link_id) – Using the mysql_select_db function in this fashion
ensures that the resource that initiated the connection to the MySQL server would be selected with the
$link_id. The variable, that is identified here as $db_name, would be the table that would be selected
for use in any subsequent queries.
mysql_select_db($db_name, $link_id) or die("Could not select the $db_name database!");
• mysql_select_db($db_name) – Using the mysql_select_db function in this fashion forces PHP to
use the last connection made to the MySQL server to submit the request. Without a $link_id to
choose from, the MySQL server chosen could be the wrong one to use (not containing the database
identified by $db_name). Best practice is to include the $link_id, but it is not necessary.
mysql_select_db($db_name) or die("Could not select the $db_name database!");
As stated, this function is not necessary to query the data in the MySQL server; however, without this step,
any reference to resources in the MySQL server would need to be preceded with the database name (ex.
SELECT * FROM world.City vs. just typing SELECT * FROM City)
Querying MySQL
In the mysql client, any interaction is accomplished through a query command. These query commands
include data definition language (DDL) and data manipulation language (DML) commands that pretty
much include every command that can be issued into MySQL. Of course, MySQL has non-standard SQL
commands such as SHOW ... which can also be queried and can be called from PHP. To query the
MySQL server in PHP, the mysql_query() function is used. Similar to the other functions mentioned up to this point, there are two ways to use this function:
• mysql_query($query, $link_id) – Using the mysql_query function in this fashion ensures that
the resource that initiated the connection to the MySQL server would be selected with the $link_id.
The variable, that is identified here as $query, would be the query that would be executed against the
MySQL server.
$query_results = mysql_query($query, $link_id);
• mysql_query($query) – Using the mysql_query function in this fashion forces PHP to use the last
connection made to the MySQL server to submit the request. Without a $link_id to choose from, the
MySQL server chosen could be the wrong one to use (not being able to return the results of $query
because the server does not contain the data). Best practice is to include the $link_id, but it is not
necessary.
$query_results = mysql_query($query);
The process of retrieving data once a connection has been made to the MySQL server is similar to the
process of interacting with the data through the mysql client. The first thing that needs to be addressed is
the database that holds the table (which in turn holds the data).
Selecting a Database
In the mysql client, a database is identified (or selected) by the USE <database> command. In PHP,
this is accomplished with a PHP MySQL API function called mysql_select_db(). This function identifies
the database that should be used for any subsequent requests to the data, which eliminates the need to
qualify every table (or other MySQL resource) called with the database name. There are two ways to use
this function:
• mysql_select_db($db_name, $link_id) – Using the mysql_select_db function in this fashion
ensures that the resource that initiated the connection to the MySQL server would be selected with the
$link_id. The variable, that is identified here as $db_name, would be the table that would be selected
for use in any subsequent queries.
mysql_select_db($db_name, $link_id) or die("Could not select the $db_name database!");
• mysql_select_db($db_name) – Using the mysql_select_db function in this fashion forces PHP to
use the last connection made to the MySQL server to submit the request. Without a $link_id to
choose from, the MySQL server chosen could be the wrong one to use (not containing the database
identified by $db_name). Best practice is to include the $link_id, but it is not necessary.
mysql_select_db($db_name) or die("Could not select the $db_name database!");
As stated, this function is not necessary to query the data in the MySQL server; however, without this step,
any reference to resources in the MySQL server would need to be preceded with the database name (ex.
SELECT * FROM world.City vs. just typing SELECT * FROM City)
Querying MySQL
In the mysql client, any interaction is accomplished through a query command. These query commands
include data definition language (DDL) and data manipulation language (DML) commands that pretty
much include every command that can be issued into MySQL. Of course, MySQL has non-standard SQL
commands such as SHOW ... which can also be queried and can be called from PHP. To query the
MySQL server in PHP, the mysql_query() function is used. Similar to the other functions mentioned up to this point, there are two ways to use this function:
• mysql_query($query, $link_id) – Using the mysql_query function in this fashion ensures that
the resource that initiated the connection to the MySQL server would be selected with the $link_id.
The variable, that is identified here as $query, would be the query that would be executed against the
MySQL server.
$query_results = mysql_query($query, $link_id);
• mysql_query($query) – Using the mysql_query function in this fashion forces PHP to use the last
connection made to the MySQL server to submit the request. Without a $link_id to choose from, the
MySQL server chosen could be the wrong one to use (not being able to return the results of $query
because the server does not contain the data). Best practice is to include the $link_id, but it is not
necessary.
$query_results = mysql_query($query);
Comments
Post a Comment