Using My SQL from PHP Connecting Retrieving Data

Using My. SQL from PHP Connecting, Retrieving Data, Executing SQL Commands, … Svetlin Nakov Technical Trainer www. nakov. com Software University http: //softuni. bg

Table of Contents 1. 2. 3. 4. 5. 6. 7. 8. 9. Connecting database from PHP Sending query Fetching data Persistent connections with Database Best practices PDO Prepared Statements PDO examples mysqli_ examples Doctrine/Propel 2

Connecting database from PHP

PHP and Databases § PHP supports about 20 RDBM servers § Including My. SQL, Oracle, MS SQL, DB 2, Firebird and Paradox § Supports connection over ODBC driver § Provided different sets of functions for accessing the different RDBMS § Each function starts with prefix – the DB server type Example: mysql_connect, mssql_query, etc

Connecting My. SQL § mysql_connect – function to connect to My. SQL server § Parameters: $server, $username, $password, $new_link, $client_flags § Returns resource result, identifying the new link (link identifier) § The result is used as parameter to other mysql_ functions mysql_connect("localhost", "userpassword");

Connecting My. SQL{2} § Once connected a database must be selected to perform queries upon § In some cases it is not required – show databases query for instance § mysql_select_db ($dbname, $link) – selects database from the server § Returns true if successful $dblink = mysql_connect("local host", "userpassword"); mysql_select_db("mydb", $dblink);

Connecting to Database Live Demo

Sending Query

Executing Query § mysql_query ($query, $link) – execute query on database § $query is string – the query to be executed § $link is database link identifier § The returned result depends on the query § If query is select, show, describe, explain – returns resource or false on error § Otherwise true if successful, false on error mysql_query("select * from users", $dblink); § The link parameter can be omitted in all mysql_ functions if working with only one database § Only one call to msql_connect in the script

Select Query Results § PHP provides several functions for working with My. SQL select query results § mysql_query returns resource when performing select query that holds the data § The result is accessed row-per-row from first towards last with internal pointer § Additional functions to get number of affected rows on update/delete or auto-generated id of inserted row

Fetching Data

Fetch Row From Result § mysql_fetch_row – returns numerical array, containing the current row from the result and moves the pointer to the next row § Returns false if there are no more rows $res = mysql_query ("select id, name from people"); $row = mysql_fetch_row($res); if ($row) print_r($row); // 0 ->id, 1 ->name else echo "No results!";

My. SQL Query Live Demo

Fetching Row From Result (2) § mysql_fetch_assoc – returns associative array containing the current row in result and moved the pointer to the next one § The field names are keys in the array § Returns false if no more rows $res = mysql_query ("select id, name from people"); $row = mysql_fetch_assoc($res); if ($row) echo "Name: ". $row['name'];

mysql_fetch_assoc Live Demo

Fetching Single Value § mysql_result ($result, $row, $field) – return the value or single cell In My. SQL query result § $field is either field index or name § Returns false on failure § Must NOT be mixed with other functions for reading query result § Much slower than fetching data row-per-row $res = mysql_query ("select count(*) from people"); echo mysql_result($res, 0, 0);

mysql_result Live Demo

Number of Rows § mysql_num_rows ($result) – returns the number of rows in the result set § Does not work with unbuffered queries (mysql_unbuffered_query) $res = mysql_query ("select id, name from people"); $count = mysql_num_rows($res); echo $count;

mysql_num_rows Live Demo

Internal Pointer Change § mysql_data_seek ($result, $row) – changes the position of the internal pointer in the result § Allows you to reuse result once fetched with mysql_fetch_* functions § Returns true on success, false on failure $res = mysql_query ( … ); $count = mysql_num_rows($res); for ($i = $count - 1; $i >= 0; $i--) { mysql_data_seek($res, $i); $row = mysql_fetch_assoc($res); print_r($row); }

Internal Pointer Change Live Demo

Executed Query Result § mysql_insert_id($link) – get the auto generated ID of previous insert/replace query § Returns 0 if no ID was generated, false on error § Works only for AUTO_INCREMENT columns § $link can be omitted if only one link established mysql_query ("insert into people ("name", "age") values ("To 6 ko", "30"); echo mysql_insert_id();

mysql_insert_id Live Demo

Executed Query Result (2) § mysql_affected_rows($link) – returns number of affected rows in most recent insert/update/delete/replace query § As with all mysql_ functions $link can be omitted if only one link established § Returns -1 if last query failed mysql_query ("update people set age+1 where age < 20"); echo mysql_insert_id();

Error Handling § mysql_errno ($link) - returns the error code from the last query § Returns 0 if no error occurred § mysql_error ($link) – returns the error text from the last query § Returns empty string if no error occurred mysql_query ("insert into nosuchtable"); echo mysql_errno(). ": ". mysql_error();

Closing and Freeing § mysql_free_result($resource) – clears the memory occupied by select query result § mysql_close($link) – closes connection to mysql server § When PHP script ends all resources are freed automatically and all connections – closed § Freeing is not necessary § Closing is needed only when using persistent connections

Persistent Connections

Persistent Connections § Persistent connections are connections that are kept open after script ends § Allows reusing § Saves time for next script to connect § Very useful for slow-login databases (MS SQL, Firebird, etc) § When performing persistent connect PHP searches for already opened connection and reuses it § mysql_pconnect – similar to mysql_connect but checks for previous persistent connection with same parameters and reuses it

Best Practices

Escaping § All strings that are generated from user input must be escaped § Quotes, double quotes and back slashes must be prefixed with back slash § Lack of escaping may lead to errors and security issues § mysql_real_escape_string – returns given string with characters escaped, taking into account the character set of the connection § When using Cyrillic this may escape the Cyrillic characters and turn them into hex codes § Escaping may be done by simple string replacement or with regular expressions

Escaping § Example escaping with string replacement mysql_query ("insert into people values (null, '. str_replace("'", "\'", $_POST['name'])'"); § When the string, inserted in the DB is going to be printed to a page, using htmlentities is good idea § Replaces all HTML special chars with their entities § Can be set to include quotes and double quotes htmlentities("пробваме ', ", &^%", ENT_QUOTES); § Second parameter sets quotes converting

PDO Prepared Statements

PDO: What? § PDO - PHP Data Object § An interface for accessing databases in PHP § Provides a data-access abstraction layer § Same functions are used across DBMSs § Object-oriented

PDO: Why? § Portability § Speed § Pre-compiled SQL statements that accept zero or more parameters § Security § Supports prepared statements § Prevents SQL injection by using placeholders for data

Get connected § This creates a database object called $dbh try { //Instantiate a database object $dbh = new PDO("mysql: host=$hostname; dbname=my. DB", $username, $password); echo 'Connected to database'; } catch(PDOException $e) { echo $e->get. Message(); }

Steps § Define the query § $sql = “…”; § Execute the query § Queries that don't return a result set (e. g. INSERT) § $dbh->exec($sql); § Queries that do return a result set (SELECT) § $dbh->query($sql); § Process the result

A SELECT Query: Multiple Rows § query() returns a result set //Define the query $sql = "SELECT * FROM animals"; //execute the query $result = $dbh->query($sql); //process the result foreach($result as $row) { print $row['animal_type']. ' - '. $row['animal_name']; }

A SELECT Query: Multiple Rows § fetch() returns a single row //Define the query $sql = "SELECT * FROM animals WHERE animal_id = 3"; //query() returns the result $result = $dbh->query($sql); //fetch() returns the first row $row = $result->fetch(); print $row['animal_type']. ' - '. $row['animal_name'];

A SELECT Query: Multiple Rows § set exec() returns the number of rows affected § last. Insert. Id() returns the ID of the last inserted row $sql = "INSERT INTO animals(animal_type, animal_name) VALUES ('kangaroo', 'troy')"; $dbh->exec($sql); //Get the ID of the last inserted row $id = $dbh->last. Insert. Id();

Prepared Statements § A prepared statement is a pre-compiled SQL statement § Can be reused § Executes more quickly § Prevents SQL injection

Prepared Statements: Steps § Define the query § $sql = “…”; § Prepare the statement § $statement = $dbh->prepare($sql); § Bind the parameters § $statement->bind. Param(param_name, value, type); § Execute § $statement->execute(); § Process the result

An Insert Query Example //Define the query $sql = "INSERT INTO animals(animal_type, animal_name) VALUES (: type, : name)"; //Prepare the statement $statement = $dbh->prepare($sql); //Bind the parameters $type = 'kangaroo'; $name = 'Joey'; $statement->bind. Param(': type', $type, PDO: : PARAM_STR); $statement->bind. Param(': name', $name, PDO: : PARAM_STR); //Execute $statement->execute();

An Update Query Example //Define the query $sql = "UPDATE animals SET animal_name = : new WHERE animal_name = : old"; //Prepare the statement $statement = $dbh->prepare($sql); //Bind the parameters $old = 'Joey'; $new = 'Troy'; $statement->bind. Param(': old', $old, PDO: : PARAM_STR); $statement->bind. Param(': new', $new, PDO: : PARAM_STR); //Execute $statement->execute();

A Delete Query Example //Define the query $sql = "DELETE FROM animals WHERE animal_type = : type"; //Prepare the statement $statement = $dbh->prepare($sql); //Bind the parameters $type = 'kangaroo'; $statement->bind. Param(': type', $type, PDO: : PARAM_STR); //Execute $statement->execute();

A SELECT Query: a Single Row § fetch() returns a single row //Define the query $sql = "SELECT animal_name, animal_type FROM animals WHERE animal_id = : id"; //Prepare the statement $statement = $dbh->prepare($sql); //Bind the parameters $id = 3; $statement->bind. Param(': id', $id, PDO: : PARAM_INT); //Execute the statement $statement->execute(); //Process the result $row = $statement->fetch(PDO: : FETCH_ASSOC); echo $row['animal_name']. " - ". $row['animal_type'];

A SELECT Query: Multiple Rows Example //Define the query $sql = "SELECT animal_name, animal_type FROM animals"; //Prepare the statement $statement = $dbh->prepare($sql); //Execute the statement $statement->execute(); //Process the result $result = $statement->fetch. All(PDO: : FETCH_ASSOC); foreach ($result as $row) { echo $row['animal_type']. ' - ' . $row['animal_name']; }

mysqli_

A SELECT Query: Multiple Rows § The My. SQLi Extension (My. SQL Improved) is a relational database driver used in the PHP programming language to provide an interface with My. SQL databases. § The mysqli extension features a dual interface § Procedural § Object Oriented

Connections Example <? php $mysqli = new mysqli("localhost", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to My. SQL: (". $mysqli->connect_errno. ") ". $mysqli->connect_error; } echo $mysqli->host_info. "n"; ? >

Navigation through unbuffered results <? php $mysqli->real_query("SELECT id FROM test ORDER BY id ASC"); $res = $mysqli->use_result(); echo "Result set order. . . n"; while ($row = $res->fetch_assoc()) { echo " id = ". $row['id']. "n"; } ? >

Select Example $sql='SELECT col 1, col 2, col 3 FROM table 1 WHERE condition'; $rs=$conn->query($sql); if($rs === false) { trigger_error('Wrong SQL: '. $sql. ' Error: '. $conn ->error, E_USER_ERROR); } else { $rows_returned = $rs->num_rows; }

Iterate Example § Using column names – recommended $rs->data_seek(0); while($row = $rs->fetch_assoc()){ echo $row['col 1']. ' '; } § Using column index $rs->data_seek(0); while($row = $rs->fetch_row()){ echo $row[0]. ' '; }

Insert Example $v 1="'". $conn->real_escape_string('col 1_value'). "'"; $sql="INSERT INTO tbl (col 1_varchar, col 2_number) VALUES ($v 1, 10)"; if($conn->query($sql) === false) { trigger_error('Wrong SQL: '. $sql. ' Error: '. $conn ->error, E_USER_ERROR); } else { $last_inserted_id = $conn->insert_id; $affected_rows = $conn->affected_rows; }

Update Example $v 1="'". $conn->real_escape_string('col 1_value'). "'"; $sql="UPDATE tbl SET col 1_varchar=$v 1, col 2_number=1 WHERE id>10"; if($conn->query($sql) === false) { trigger_error('Wrong SQL: '. $sql. ' Error: '. $conn ->error, E_USER_ERROR); } else { $affected_rows = $conn->affected_rows; }

Delete Example § Use the following syntax: $sql="DELETE FROM tbl WHERE id>10"; if($conn->query($sql) === false) { trigger_error('Wrong SQL: '. $sql. ' Error: '. $conn ->error, E_USER_ERROR); } else { $affected_rows = $conn->affected_rows; }

Doctrine

What is Doctrine? § Doctrine is a Object Relational Mapper built to work with PHP 5. 2. 3 or greater. § Primarily based of Java Hibernate § Influenced by Ruby on Rails Active. Record

Propel

What is Propel? § Propel is an open-source Object-Relational Mapping (ORM) for SQL-Databases in PHP 5. 4. It allows you to access your database using a set of objects, providing a simple API for storing and retrieving data. § But not only plain ORM but it also provides database schema migration, reverse engineering of existing database and much more.

Why Propel? § Propel gives you, the web application developer, the tools to work with databases in the same way you work with other classes and objects in PHP without writing SQL. § Propel: § is blazing fast! § gives your database a well-defined API. § is well documented. § comes with common behaviors.

Summary § TODO 61

PHP & My. SQL ? s n o i t s e u Q ? ? ? https: //softuni. bg/trainings/fasttracks/details/1033

License § This course (slides, examples, demos, videos, homework, etc. ) is licensed under the "Creative Commons Attribution. Non. Commercial-Share. Alike 4. 0 International" license § Attribution: this work may contain portions from § "PHP Manual" by The PHP Group under CC-BY license § "PHP and My. SQL Web Development" course by Telerik Academy under CC-BY-NC-SA license 63

Free Trainings @ Software University § Software University Foundation – softuni. org § Software University – High-Quality Education, Profession and Job for Software Developers § softuni. bg § Software University @ Facebook § facebook. com/Software. University § Software University @ You. Tube § youtube. com/Software. University § Software University Forums – forum. softuni. bg