2010/11 : [1]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

PHP with MySQL

2010/11 : [2]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

PHP with MySQL

• Combining PHP with MySQL is a powerful combination to build applications.

• MySQL provides a fast structured data store.

• PHP provides the user interface to this data.

2010/11 : [3]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

mysqli PHP extension

• PHP communicates with MySQL database using the built-in mysqli extension.

http://php.net/manual/en/ref.mysqli.php

2010/11 : [4]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Connect

• Before using MySQL, a PHP script needs to connect to the database.

• For this, we use the function mysqli_connect().

• Once executed, we check the connection happened OK.

2010/11 : [5]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Connect Example$link = mysqli_connect( ‘mysqlsrv.dcs.bbk.ac.uk’, ‘my_username’, ‘my_password’, ‘my_db_name’ );/* check connection */if (mysqli_connect_errno()) {exit(mysqli_connect_error());

}

2010/11 : [6]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Write Queries

• Write queries like INSERT, UPDATE, DELETE and so on do not return any data.

• Such queries can be executed using the function mysqli_query.

• This function returns TRUE on success, and FALSE on failure.

• Note the query doesn’t need to be terminated with a semi-colon.

2010/11 : [7]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Write Query Example/* add student */

$sql = “INSERT INTO students

VALUES (‘Jane’,26,’female’)”;

$ok = mysqli_query($link,$sql);

if (!$ok) { /* check query ok */

exit(mysqli_error($link));

}

Name Age Sex

Jane 26 femaleTable students

2010/11 : [8]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Read Query

• Read queries (like SELECT) need to return data to PHP.

• The queries are also executed with the function mysqli_query, but this time it returns a result set.

$sql = ‘SELECT * FROM students’;

$result = mysqli_query($link,$sql);

2010/11 : [9]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Data Retrieval

• Rows are retrieved one at a time using a number of functions:

• mysqli_fetch_assoc($result)returns a row as an associative array with the column names as keys.

• mysqli_fetch_array($result)returns a row as an array with both a numerical and an associative index

2010/11 : [10]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Read Query Example/* get all students */$sql = “SELECT name,age FROM students”;$result = mysqli_query($link,$sql);/* check query */if ($result===false) {

exit(mysqli_error($link));}/* fetch associative array */while ($row = mysqli_fetch_assoc($result)) {

echo row[‘name’].’, ’.$row[‘age’].’ yrs old’;}/* free result set */mysqli_free_result($result);

Name Age Sex

… … …Table students

2010/11 : [11]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Read Queries

• Note how the mysqli_free_result($result) function was used to close the result set once the read has completed.

• Many other MySQL related functions exist, and can be found in the PHP manual:

http://php.net/manual/en/ref.mysqli.php

2010/11 : [12]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Escaping Strings

• Certain characters need to be escaped before they can be used safely in MySql queries.Eg: ‘Student’s marks’ becomes ‘Student\’s marks’

• PHP provides functions that do this for us.mysqli_real_escape_string($link,

$string)

• We should pass every variable we want to use in our MySql query to this function.

2010/11 : [13]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Escaping Strings

/* Grab a value from $_POST */$student = $_POST[‘student’];

/* Make the data safe for use in a query. $link must be an existing connection! */

$clean_student = mysqli_real_escape_string( $link, $student);

/* (safely) get student */$sql = “SELECT name,age FROM students

WHERE name = ‘”.$clean_student.“’”;$result = mysqli_query($link,$sql);

2010/11 : [14]PHP with MySQL Building Web Applications using MySQL and PHP (W1)

Exercise

Hands On Exercise

PHP with MySQL