Handy PHP SQL Commands
References used for these notes include:
- http://www.w3schools.com
- http://php.activeventure.com
Establishing Connections to the MySQL Server
- sql_link mysql_connect(servername, username, password): establishes a connection
within php to a mysql server.
- Returns a MySql link identifier on success, or false on
failure: A link identifier can be subsequently used as a parameter
to many mysql functions if you establish links to multiple sql
servers. However in general you are only going to establish a
single connection and php's mysql functions assume that they
should execute their statements on the last link established if
no link is provided. This "cheat" sheet does not show links as
a parameter to any functions
- Example: mysql_connect("dbs.cs.utk.edu", "cs594bvz", "cs594bvz");
- bool mysql_select_db(string database_name): states that all subsequent
queries should be executed against the named database. Returns true
if the database can be selected and false otherwise.
- bool mysql_close(): closes the connection to the mysql server
Executing Queries
- resource mysql_query(string query)
- For this course, query should be one of SELECT, INSERT, UPDATE, or
DELETE, or CREATE TABLE
- The resource can be passed to a variety of other mysql functions to
extract information
- Returns false if the query fails for any reason
Extracting Information from Queries
- array mysql_fetch_array(resource result): returns the next unread row
from the result as an array that permits both numeric and associative
indices or false if the rows have been exhausted.
- The associative indices correspond to the columns' names.
- The numeric indices correspond to the columns' numeric position in
the returned table.
- If two columns have the same name (e.g., a join has been performed
on two tables and the SELECT statement has extracted columns from
each table with the same name), then the last column will be the
one returned. You can access the "masked" columns by their numeric
positions in the returned table.
- Example code:
$result = mysql_query("SELECT * FROM person");
while($row = mysql_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "
\n";
echo $row[0] . " " . $row[1] . "\n";
}
Note that I can access the columns via either their column names or
their numeric position in the table.
- mysql_fetch_row() can be used to return an array that contains just
numeric indices but it is typically more readable to reference
columns by their names and mysql_fetch_array is not significantly
slower than mysql_fetch_row
- int mysql_num_rows(resource result): returns the number of rows retrieved by a
SELECT query
- int mysql_affected_rows(): returns the number of rows affected by the last
INSERT, UPDATE, or DELETE query