Database Systems - Web Database Programming with PHP Data Objects PDO - APPFICIAL

PHP and MySQL are commonly used for database programming on the web. PHP is the most widely used programming language used to create dynamic websites, and MySQL is the most popular relational database.

A PHP application uses an API called PHP Data Objects (PDO) to interact with MySQL and other database systems. It requires a database-specific PDO driver, which is usually included when PHP is installed.

To create a connection from your website, first create a PDO object, specifying the DSN and your MySQL credentials to connect to the database. The DSN stands for Data Source Name and is a string containing:

DSN prefix - "mysql:" for MySQL
host – the database server's hostname or address
port – the database server's port number (optional)
dbname – the atabase name

A PDOException is thrown if the PDO constructor method fails to connect to MySQL successfully. Fortunately, PHP can be set up to handle errors in a several ways, such as outputting the error message to the web page or sending the message in a web server log file.

Execute an SQL statement with the PDO object’s query() method, which returns a PDOStatement object. FALSE is returned if there is an error trying to do this.

setAttribute() – makes the PDO object throw a PDOException when an SQL error occurs, rather than checking the query() return value for FALSE .

rowCount() returns the number of rows that are inserted, updated, or deleted.

In the code example:
The PDO object creates a connection to MySQL.
setAttribute() makes PDO throw a PDOException if an error occurs executing an SQL query.
The query() method executes the INSERT statement in the $sql string. The movie Ghostbusters is inserted into the Movie table.
MySQL server returns information to the web server indicating the result of the INSERT statement. The information is returned from query() as a PDOStatement.
The rowCount() method returns 1 because one row was inserted into the Flight table.

Prepared statements

Using a prepared statement is better than a regular statement because it prevent a SQL injection attack, where a hacker can try to change the values of the SQL statements.

A prepared statement is an SQL statement where you can provide parameter values directly from user input by using placeholders called parameter identifiers (?) in the query rather than data literals.

prepare() – prepares a parameterized SQL statement to be ready to get executed and returns a PDOStatement object.
bindValue() - in the SQL statement, it binds a value to a parameter identifier.
execute() - executes the SQL statement with the bound parameters. If there is an error, it will throws a PDOException

In the code:
Lets say a user enters movie information into a form on a webpage. The form submits to addMovie.php, and submitted values are placed in the $_POST array.
The INSERT statement uses ? as placeholders for three values.
prepare() prepares the parameterized statement and returns a PDOStatement.
bindValue() binds the values from $_POST to the parameter identifiers.
execute() executes the INSERT statement. addMovie.php displays a confirmation message.

The execute() method call creates a cursor object when you execute a PDOStatement with a SELECT statement.

fetch() - returns an array of indexed row data, or FALSE if no row is selected. The row data is either indexed by the column name or column number. Use a loop in cases where a SELECT statement returns multiple rows of data. To move the ursor, each call to fetch() returns the next row from the result table, or returns FALSE when there are no more rows to return.

In the code example:

The prepared statement is executed(), selecting the name and price of Amazon movies that are PG-13 and from the year 2020 or newer.
fetch() returns an array containing a single row from the results table.
The while loop processes each row until all rows are processed. Result values are accessed from $row using the column names.

Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!