Module 0301: General MySQL use in a PHP script

Tak Auyeung, Ph.D.

October 6, 2017

1 About this module

2 Making a connection

A connection to a MySQL database is persistent from the time the connection is opened to the time the connection is explicitly closed of the script terminates.

A typical method to connect with a database is as follows:

 
$link = mysqli_conneect(’localhost’, ’username’, ’password’, ’dbname’) 
  or die("Error:".mysqli_connect_error().PHP_EOL;

In this code, the following happens:

1.
mysqli_connect is called. If successful, it returns a connection “object”. If failed, it returns false.
(a)
if a connection is made
i.
mysqli_connect returns a connection object, this is considered “true”
ii.
the other side of “or” is ignored because of short circuited boolean evaluation
iii.
the connection object is stored in the variable $link
(b)
if a connection is not made
i.
mysqli_connect returns false
ii.
the other side of “or” is evaluated
iii.
the “die” statement is like “echo”, except that it also exits the entire script
iv.
“mysqli_connect_error” returns the text description of the connection error

3 Queries

After a connection is made, queries can be formulated and executed.

An SQL query is generally speaking a string. The main types are as follows:

Note that SELECT is a non-DML (data manipulating language) query, while INSERT, DELETE and UPDATE are DML queries. As a non-DML query, a SELECT query is often followed by another function call to get the result of the query.

3.1 Query formulation

Queries are formulated much like SQL queries used in the interactive client “mysql”. However, when a query is formulated in PHP, it is possible to specify components of a query using variables.

For example, if the PHP variable “employeeId” stores a single employee ID that is used look up the row in an employee table, then the following code can be used to make a query:

 
$query = "select␣*␣from␣employee␣where␣id=’$employeeId’;";

This is fine as long as the variable employeeId is properly validated so that the query cannot be used to perform unwanted operations. For example, if the variable employeeId contains a single quote, then it can easily extend the intended query.

A safe method to make sure any variable to be expanded in a query is safe is to use the function mysqli_real_escape_string. This function does not actually validate a string, but rather it adds the necessarily escaping needed so that a string can be safely used in single quoted values passed to an SQL query. In our example, the new code may look like the following (assuming the value in variable employeeId is not previously validated:

 
$employeeId = mysqli_real_escape_string($employeeId); 
$query = "select␣*␣from␣employee␣where␣id=’$employeeId’;";

3.2 SELECT query

A select query can be quite complex. However, the most general use consists of a list of fields, a table where rows are retrieved from, a filter to limit results to rows that satisfy the filter criterion, and a order specification so that rows are returned in a particular order.

For example, observe the following query:

 
select id, startDate from employee where id < 4000 and id > 3000 order by lastname desc;

In this example, the fields to be returned include the ID and the start date. Rows are retrieved from the employee table. Only rows with IDs between 3000 and 4000 exclusively are included in the result. Last, the rows are retrieved in a descending order based on the last name of the rows. Note that the last name is not even a part of the fields returned!

Of course, SELECT statements an be simpler or more complex than the one shown here. For example, JOIN operations can significantly increase the complexity of a SELECT query.

The following is a general framework of using a SELECT query in PHP code:

 
<?php 
// formulate SELECT query and store in $query 
// $link should be initialized at this point 
$queryResult = mysqli_query($link, $query); 
if (!$queryResult) 
{ 
  // something went wrong 
  die $query.’␣failed:␣’.mysqli_error($link); 
  // note that sometimes you may not want the script to terminate 
} 
else 
{ 
  // the SQL query was successful 
  if (mysqli_num_rows($queryResult) > 0) 
  { 
    // sometimes you may just have 0 or 1 rows returned 
    // and you only need to know whether it is 0 or 1 
    // optionally, you can retrieve each row returned 
    // and process it 
    while ($row = mysqli_fetch_row($queryResult)) 
      // mysqli_fetch_row returns an array or false 
      // it returns an array if it successfully retrievess 
      // another row of the result of the previous query, 
      // or it returns false if there is no more rows to 
      // retrieve 
      // the return value of mysqli_fetch_row is stored in 
      // $row 
      // if $row is false, the loop exits, 
      // otherwise, it enters the loop body to perform an 
      // iteration 
    { 
      // $row is an array with numeric indices 
      // each iteration gives you one row of result 
    } 
  } 
} 
?>

3.3 INSERT query

An insert query is used to insert one or more rows in a table. There are several formats. To insert just one row, the following code structure is suitable:

 
<?php 
  // prepare $query 
  $val1 = mysqli_real_escape_string($val1); 
  $val2 = mysqli_real_escape_string($val2); 
  $val3 = mysqli_real_escape_string($val3); 
  $val4 = mysqli_real_escape_string($val4); 
  $query = "insert␣into␣tablename␣set␣field1=’$val1’,␣field2=’$val2’,␣field3=’$val3’,␣field4=’$val4’;"; 
  $queryResult = mysqli_query($link, $query); 
  if (!$queryResult) 
  { 
    // something went wrong 
    die $query.’␣has␣an␣error:␣’.mysqli_error($link); 
    // die may not be correct some times 
  } 
  else 
  { 
    // the query went through 
    // but did it insert anything? 
    // there is no way to tell without 
    // querying the database with a select 
  } 
?>

In this example, four fields are specified in the INSERT query. Note that the number of fields specified depends on how the table is set up. If a field is optional, it does not need a value in an INSERT statement. Likewise, if a field is automatically assigned, it also does not (in fact, should not) have a value in an INSERT statement.

3.4 DELETE query

A delete query is used to remove one or more rows from a table. The following is a general structure of PHP code to execute a DELETE query:

 
<?php 
  // prepare $query 
  $val1 = mysqli_real_escape_string($val1); 
  $val2 = mysqli_real_escape_string($val2); 
  $query = "delete␣from␣tablename␣where␣field1␣=␣’$val1’␣and␣field2␣=␣’$val2’;"; 
  $queryResult = mysqli_query($link, $query); 
  if (!$queryResult) 
  { 
    // something went wrong 
    die $query.’␣has␣an␣error:␣’.mysqli_error($link); 
    // die may not be correct some times 
  } 
  else 
  { 
    // the query went through 
    // but did it delete anything? 
    // there is no way to tell without 
    // querying the database with a select 
  } 
?>

Note that the WHERE statement can be complex using logical operators. Be careful when you use a DELETE query because it can potentially delete many rows from a table.

3.5 UPDATE query

An update query is used to change/alter one or more rows in a table. An UPDATE query consists of a section to specify updates, and a section to filter which rows in a table is affected.

The following is an example of how to use UPDATE in a PHP script:

 
<?php 
  // prepare $query 
  $val1 = mysqli_real_escape_string($val1); 
  $val2 = mysqli_real_escape_string($val2); 
  $val3 = mysqli_real_escape_string($val3); 
  $val4 = mysqli_real_escape_string($val4); 
  $val5 = mysqli_real_escape_string($val5); 
  $query = "update␣tablename␣set␣field1=’$val1’,␣field2=’$val2’,␣field3=’$val3’,␣field4=’$val4’␣where␣field5=’$val5’;"; 
  $queryResult = mysqli_query($link, $query); 
  if (!$queryResult) 
  { 
    // something went wrong 
    die $query.’␣has␣an␣error:␣’.mysqli_error($link); 
    // die may not be correct some times 
  } 
  else 
  { 
    // the query went through 
    // but did it update anything? 
    // there is no way to tell without 
    // querying the database with a select 
  } 
?>

Note that the WHERE section can be much more complex. Furthermore, the SET section can also use fields of the same row as the right hand side of the equal.