Using PHP with MySQL

Linking PHP with MySQL

The whole idea behind PHP and MySQL is to publish MySQL data on the web by allowing the content of a website to reside in a database so that content can be pulled dynamically from the database in order to make web pages.

Here is a quick synopsis of this PHP / MySQL model:

  • Browser requests page from the server (Apache).
  • Apache fires up the PHP interpreter to execute the PHP script.
  • PHP commands connect to the MySQL database and request the content.
  • MySQL responds by sending the data to the PHP script.
  • PHP stores the data in variables which are then echoed out as content on the web page.
  • Page is sent to the server as HTML, which is then sent to the browser.

Linking PHP and MqSQL with a unique user account

We need to create a new user account that will be connected to the database and table we intend to use for the web page. This will be unique so as to be secure.

In PHPMyAdmin, click the correct database name on the left hand side panel. In the main window, as a tab on the top, click the ‘privileges’ button. It should now bring up a list of users having access to ‘name of database’. At the moment it may only have access for the root user.. Click ‘add new user’ link at the bottom of the list and fill in the details as follows:

Username – call this [name of database]user.

Host – Change this from the default Any Host, to, localhost. This is so that the database only connects to what is on the same server.

Password – Speaks for itself.

Under database for user, select grant all privileges on database [name of database]. This means the account can do what it wants to the database.

Make sure Global Privileges is left unchecked. (We want to keep queries to this database only.

Click GO, and PHPMyAdmin should show confirmation.

just as PHPMyAdmin can connect to a database, so we can make PHP connect with the database. The PDO extension shown above can do this for us.

 


PDO – PHP DATA OBJECT

PHP needs to use PDO to interact with MySQL. MySQL requires a driver in order to do this.

The PDO_MYSQL is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MySQL 3.x, 4.x and 5.x databases.

Think of a new PDO as a built in function which accepts three arguments:

  • String specifying type of database (mySQL), hostname of the server and database name.
  • Username.
  • Password.

PHP functions return a value when they are called. The PDO ‘function’ returns a value called a ‘PDO object’ which identifies the connection that is established. We need to store this value in a variable ($pdo) so that we can keep hold of it.

Using PDO to establish a connection to MySQL

$pdo = new PDO('mysql:host=hostname;dbname=database_name', 
        'username', 
        'password')

…Where hostname is usually ‘localhost’.

PHP try-catch statement

So you’ve set up a connection but what if there’s a problem. You need to put the PDO into a try-catch statement. Try and connect. If connected – great but if not, catch the exception and send an error page.

Note: ‘throw a PHP exception’ means that a PHP script will try and perform a task but if it fails it will tell you about the error and will ‘throw an exception’ at you. If this happens you need to catch it. Otherwise it will show ugly code and maybe password information!

This is what the try-catch statement looks like:

try
{ try and do something like connect to a database }
catch (ExceptionType $error)
{ deal with the exception by serving an error page }

Here it is in action:

try
{ $pdo = new PDO('mysql:host=localhost;dbname=egdb', 'egdbuser', 
'thepassword'); }

catch (PDOException $error)

{ $output = 'Not able to connect this time, sorry.';
  include 'error.html.php';
  exit(); }

The catch block states that if the pdo throws an exception it is called a PDOException and we can store it in a variable called $error (see below for detail). The page that gets included is a generic html page with the php echo statement that echos the value of the output variable.
<?php echo $output; ?>

The exit function is a function with no parameters. This just tells PHP to stop executing the script. This ensures that if there is extra code afterwards, this code is not executed.

 PDO error handling

By default, the PDO object goes into ‘silent failure mode’ if an error occurs after the connection is established. This makes it difficult to catch future errors. In order to catch errors EVERY time something fails within the PDO we call the PDO object’s setAttribute method:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The two argument values are PHP constants which are part of the PDO class. Here, above, we are basically saying that we want to set the PDO attribute that controls the error mode to the mode that throws exceptions.

we also need to configure the character encoding to UTF-8. (default is ISO). the most reliable way to do this is to run the SQL query SET NAMES. The PDO object has a method called exec that we can use to do this:

 $pdo->exec('SET NAMES "utf8"');

 Here is the PDO try catch statement in full:

<?php
try {
   $pdo = new PDO(
      'mysql:host=localhost;dbname=egdb', 'egdbuser',
      'thispassword');
   $pdo->setATTRIBUTE(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $pdo->exec('SET NAMES "utf8"');
   }

catch (PDOException $error) {
   $output = 'Sorry, you are not connected!';
   include 'output.html.php;
   exit();
   }

$output = 'Yeehar, you are connected!';
include 'output.html.php';

getMessage

If there is an error and the PDO throws an exception, the message is stored in the $error variable, which also an object and we can access the getMessage method to display the error message as part of the output file. You would only do this when setting up the pdo as you wouldn’t want to display this to other users.

catch (PDOException $error)

{ $output = 'Not able to connect this time, sorry: ' .
  $error->getMessage();
  include 'error.html.php';
  exit(); }

 

So here is the full template to get PHP to establish a connection to MySQL via the PDO object, including error messages:

<?php
try
{
 $pdo = new pdo('mysql:host=localhost;dbname=egdb', 
 'eguser', 
 'somepassword');
 $pdo->setattribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
 $output = 'Unable to connect to the database server. ' .$e->getMessage();
 include 'output.html.php';
 exit();
}

$output = 'Database connection established.';
include 'output.html.php';

Use this code first to check for errors. The errors usually happen for these reasons:

  • Wrong credentials typed
  • syntax errors (check proper parsing of PHP script, check correct case!
  • New user not set in MySQL privileges

If there are no errors you should revert to the code without error messages:

<?php
try
{
 $pdo = new pdo('mysql:host=localhost;dbname=egdb', 
 'eguser', 
 'somepassword');
 $pdo->setattribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
 $output = 'Unable to connect to the database server. ';
 include 'output.html.php';
 exit();
}

$output = 'Database connection established.';
include 'output.html.php';

 

 Exec method of the PDO object

The exec method of the PDO object can send queries to the database. You send a query to CHANGE aspects of the database such as: create a new table, change the data stored, insert, delete and update data. Here we send a query to show the number of rows in a table:

try {
$sqlQuery = 'CREATE TABLE newTable (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     name TEXT,
     date DATE NOT NULL )
     DEFAULT CHARACTER SET utf8 ENGINE=InnoDB';
$pdo->exec($sqlQuery); }

catch (PDOException $error) {
$output = 'Error creating new table: ' . $error->getMessage();
include 'output.html.php';
exit(); }
$output = 'New table created.';
include 'output.html.php';

In order to modify stored data, ie using the DELETE, INSERT or UPDATE queries , the exec method can return the number of entries (table rows) affected:

try {
$sqlQuery = 'UPDATE newTable SET date="2014-07-07"
             WHERE name LIKE "%Revolver%"';
$affectedRows = $pdo->exec($sqlQuery); }
catch (PDOException $error) {
$output = 'Error updating: ' . $error->getMessage();
include 'output.html.php';
exit(); }

This stores the value returned from the exec method in the $affectedRows variable, which displays the number of rows affected by the update thus:

$output = "Updated $affectedRows rows.";
include 'output.html.php';
exit(); }

SELECT queries using the PDOStatement

SELECT queries are used to view stored data in the database, and they have results.

The QUERY method accepts an SQL query as an argument which is sent to the database. The database server then returns a PDOSTATEMENT object which represents a RESULT SET containing a list of all the entries returned from the query.

Here we ask for the rows from the table:

try
{
   $sql = 'SELECT thisfield FROM thistable';
   $result = $pdo->query($sql);
}
catch (PDOException $e)
{
   $error = 'Error fetching data: ' . $e->getMessage();
   include 'error.html.php';
exit();
}

With the error message thus:

<!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="utf-8">
      <title>Script error</title>
   </head>
   <body>
      <p>
         <?php echo $error; ?>
      </p>
   </body>
</html>

If there was no error, the code will store the result set as a PDOstatement object in the $result variable.

This result contains ALL the text from ALL the fields from the table. This can obviously be quite big so you can filter the results using control structures. the WHILE loop is particularly good with large amounts of data:

while ($row = $result->fetch())
{
   ⋮ process the row
}

Where the FETCH method of the PDOStatement object returns the next row on the result set as an array. (When there are no more, fetch returns false (but PDO in this instance does not throw an exception)). Each row fetched will become be added to the row variable.

Rows of a result set returned by fetch will be associated arrays, their indices represented as the table columns. If $row is a row in the result set, $row[‘thisfield’] is the value in the thisfield column of that row.

What we need to do is store each result as a new item in an array and present them using a php template:

while ($row = $result->fetch())
{ $fields[] = $row['thisfield']; }

Here is the complete code-grab for retrieving fields from a database and listing them:

<?php
try {
   $pdo = new PDO(
      'mysql:host=localhost;dbname=egdb', 'egdbuser',
      'thispassword');
   $pdo->setATTRIBUTE(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $pdo->exec('SET NAMES "utf8"');
   }
catch (PDOException $e) {
   $error = 'Sorry, you are not connected!';
   include 'error.html.php;
   exit();
   }

try {
   $sql = 'SELECT thisfield FROM thistable';
   $result = $pdo->query($sql); }
catch (PDOException $e)
   { $error = "Error fetching fields: ' . $e->getMessage();
   include 'error.html.php';
   exit();
   }

while ($row = $result->fetch())
   { $fields[] = $row['thisfield']; }

include 'showfields.html.php';

 

For the showfields.html.php template we’ll use the FOREACH  loop to display in turn each item of the resulting array:

<?php foreach ( array as $eachfield): ?>
⋮ HTML code to output each $eachfow
<?php endforeach; ?>

Therefore the template will look thus:

<!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="utf-8">
      <title>List of fields in table</title>
   </head>
   <body>
      <p>Here is the list of fields in the table:</p>
      <?php foreach ($thisfield as $eachrow): ?>
            <p><?php 
               echo htmlspecialchars
               $eachrow, ENT_QUOTES, 'UTF-8'); ?>
            </p>
       <?php endforeach; ?>
   </body></html>

here, each row is displayed in a paragraph. (Remember we use htmlspecialchars to display  characters such as < and ? as html entitles – that was the parser won’t mistake these characters as part of the script.)


INSERTING DATA INTO THE DATABASE VIA PHP

Here is the form to insert new fields into the database using php:

<!DOCTYPE html>
<html lang="en">
 <head>
    <meta charset="utf-8">
    <title>Inserting data into a database using a form</title>
 </head>
 <body>
    <form action="?" method="post">
       <div>
       <label for="fieldtext">Type your item here:</label>
       <textarea id="fieldtext" name="fieldtext" rows="3" cols="40">
       </textarea>
       </div>
       <div><input type="submit" value="add field"></div>
    </form>
 </body></html>

 Notice that the value of the action attribute is set to “?”. The URL used to display the form will have a query string. Setting the action to ? strips the query string off the URL when submitting the form.

When the form is submitted, the request will contain the variable = fieldtext which contains the text to be added. The variable will be added to the PHP $_POST and $_REQUEST arrays.

We can add this field insert form into the same page that showed the list of fields.

We can add a link at the top of the list that asks to add a field:

<body>
   <p><a href="?addfield">Add a field</a></p>
   <p>Here are all the fields in the table</p>

The query string above links back to the same PHP script used to generate the page but it adds the query string (?addfield). we can get the controller to detect this query string and use it as a signal to display the ‘add field” form instead of the list of fields. Add this snippet here to the top of the index page:

if (isset($_GET['addfield']))
{  include 'form.html.php';
   exit(); }

This first checks if the query string contains the variable ‘addfield’. This is how we can check if you have clicked the link. Even though there is no value specified by the ‘?addfield’ variable it does create it and we can detect this with the code above. When it is detected.

To insert the field into the database we must execute the INSERT query using the value stored in ‘$_POST[‘fieldtext’] to fill the fieldtext column of the table. We could do it like this:

$sql = 'INSERT INTO table SET
   fieldtext="2 . $_POST['fieldtext'] . '",
   2ndfieldtext="2nd field text"';
$pdo->exec($sql);

BUT – because the contents of $_POST[‘fieldtext’] is under the control of anyone submitting the form is could be used to insert malicious code in the form of an SQL INJECTION ATTACK.  This was so feard by developers that PHP created ‘MAGIC QUOTES’.

A WORD ABOUT MAGIC QUOTES

This is a default setting in PHP that analyses all submitted values and adds backslashes to any so called potentially dangerous characters such as apostrophes.

The problem being that it didn’t always work. In a nutshell, we need to cancel the effects of the magic quote by adding the following script to the top of every page that receives user input in the form of query variables, form submission or browser cookies:

if (get_magic_quotes_gpc())
{
 $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
 while (list($key, $val) = each($process))
 {
 foreach ($val as $k => $v)
 {
 unset($process[$key][$k]);
 if (is_array($v))
 {
 $process[$key][stripslashes($k)] = $v;
 $process[] = &$process[$key][stripslashes($k)];
 }
 else
 {
 $process[$key][stripslashes($k)] = stripslashes($v);
 }
 }
 }
 unset($process);
}

 

PREPARED STATEMENTS

A prepared statement is an SQL query that is sent to the database in advance  of it actually needing it so that it gives the system a chance to prepare it for execution. The SQL code can contain placeholders that you’ll supply the values for later, when the query is to be executed. (In these placeholders, PDO already guards against ‘dangerous characters’ automatically.

This is how you prepare an INSERT query, then execute it safely with $_POST[‘fieldtext’] as the text of the field:

$sql = 'INSERT INTO field SET
   fielditem1 = :fielditem1,
   fielditem2 = "Put field item 2 here" ';
$s = $pdo->prepare($sql);
$s->bindValue(':fielditem1', $_POST['fieldtext']);
$s->execute();

…and this is what is happening:

  • The SQL query is put into the variable $sql
  • There is a placeholder instead of the value :fielditem1.
  • We then call the PREPARE method of the PDO object, passing it to our SQL query as an argument.
  • The prepare method of out PDO object returns a PDOStatement object which we store in the variable $s.
  • Now that MySQL has prepared our statement for execution we can send it the missing value/s by calling the bindValue method of the PDOStatement object (held in $s). We call this method once for each value supplied, passing as arguments the placeholder that we want to fill in (‘:fieldtext’]) and the value that we want to fill it with ($_POST[‘fieldtext’]). Because MySQL knows we are sending it a discrete value, rather than SQL code that needs to be parsed, there’s no risk of characters in the value being interpreted as SQL code. Using prepared statements, SQL injection vulnerabilities is just not possible.
  • Lastly, we call the PDOStatement object’s execute method to tell MySQL to execute the query with the now supplied value/s.

If we want to add a date we can use the MySQL function CURDATE(); thus:

$sql = 'INSERT INTO field SET
   fielddate = CURDATE()';
⋮ rest of code

Now that the query is complete we now arrange it onto the index script. We test if the $_POST has been set. If it has, then execute the query part. Lastly, we include this:

header('Location: .');
 exit();

When we have added the new field text to the database, instead of displaying the original page, we want to see the added field text on the page. One would think that to do this you would get the controller to fetch the updated list from the database after adding the nex field text but if you do this and refresh the page, the browser would resubmit the form – this would cause another copy of the field text to be submitted to the database. This is not the desired effect. What we want is for the browser to treat the updated list as a normal page without re-submitting the form. The way to do this is to answer the browser’s form submission with an HTTP redirect. this is a special response that tells the browser that the page it is looking for is ‘here’.

The PHP header function is a way to send special server responses by inserting specific headers into the response sent to the browser. So to signal a redirect, we must send a location header along with the URL of the page you wish to direct to the browser. In our case we want to send the browser back to our initial index page. we are asking the browser to submit another page request without the form submission attached to it (rather than sending the browser to another location). reloading the current page is expressed as a full stop.

We could use the PHP array variable $_SERVER[‘PHP_SELF’] but this would return the full url including the /index.php

 

This is the full code-grab for the page that not only shows a list of field items from your database but also lets you add some of your own:

<?php
if (get_magic_quotes_gpc())
{
 $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
 while (list($key, $val) = each($process))
 {
 foreach ($val as $k => $v)
 {
 unset($process[$key][$k]);
 if (is_array($v))
 {
 $process[$key][stripslashes($k)] = $v;
 $process[] = &$process[$key][stripslashes($k)];
 }
 else
 {
 $process[$key][stripslashes($k)] = stripslashes($v);
 }
 }
 }
 unset($process);
}

if (isset($_GET['addfielditem']))
{
 include 'form.html.php';
 exit();
}

try
{
 $pdo = new PDO('mysql:host=localhost;dbname=egdb', 
                'egdbuser', 'thispassword');
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $pdo->exec('SET NAMES "utf8"');
}
catch (PDOException $e)
{
 $error = 'Unable to connect to the database server.';
 include 'error.html.php';
 exit();
}

if (isset($_POST['fieldtext']))
{
 try
 {
 $sql = 'INSERT INTO field SET
 fieldtext = :fieldtext,
 fielddate = CURDATE()';
 $s = $pdo->prepare($sql);
 $s->bindValue(':fieldtext', $_POST['fieldtext']);
 $s->execute();
 }
 catch (PDOException $e)
 {
 $error = 'Error adding submitted field text: ' . $e->getMessage();
 include 'error.html.php';
 exit();
 }

 header('Location: .');
 exit();
}

try
{
 $sql = 'SELECT fieldtext FROM field';
 $result = $pdo->query($sql);
}
catch (PDOException $e)
{
 $error = 'Error fetching field texts: ' . $e->getMessage();
 include 'error.html.php';
 exit();
}

while ($row = $result->fetch())
{
 $fields[] = $row['fieldtext'];
}

include 'field.html.php';

So here you go then, a breakdown of what is happening: We connect to the database creating a new PDO object (must come before any query code). We view existing fields, and we can also add new fields to the database – all with a single controller!

 


 Deleting items from the database.

To delete items in a database we identify it uniquely using the id – which is the primary key in the database.

First off we need to add the id to the SELECT query that fetches the data:

$sql = 'SELECT id, fieldtext FROM joke';

We also need to change the while loop that stores the results in the $fielditem array by adding the id:

while ($row = $result->fetch())
{
  $items[] = array('id' => $row['id'], 'text' => $row['itemtext']);
}

When the while loop runs, we get an associative array with two items (id and text). For each item we get its id and text. we need to add this in to the template and add a button to enable us to delete the item from the database:

<?php foreach ($items as $item): ?>
<form action="?deleteitem" method="post">
   <blockquote>
   <p>
   <?php echo htmlspecialchars($item['text'], ENT_QUOTES, 'UTF-8'); ?>
<input type="hidden" name="id" value="<?php echo $item['id]; ?>
<input type="submit" value="delete">
</p>
</blockquote>
</form>
<?php endforeach; ?>
  • Each joke will be displayed on a form which, when submitted will delete that item. We signal this to the controller using the ?deleteitem query string in the action attribute.
  • Now that the items are represented by both id and text, we  identify text using the array $item[‘item’].
  • When the form is submitted, the id is sent via a hidden form form which contains the id array ($item[‘id’]). (No need to use htmlspecialchars as this is not user submitted.

 

 

 

To be continued, ver ver soon

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.