2010/11 : [1]php with mysqlbuilding web applications using mysql and php (w1) php with mysql
TRANSCRIPT
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