Scribd
Upload
197 views67 pages

PHP and MySQL

The document discusses database design and SQL, different PHP MySQL APIs, how to connect to a MySQL database, perform queries, fetch and retrieve data, handle errors, and close connections. It provides examples of connecting and querying databases using MySQLi and PDO in PHP.

Uploaded by

Sai Krishna
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
197 views67 pages

PHP and MySQL

The document discusses database design and SQL, different PHP MySQL APIs, how to connect to a MySQL database, perform queries, fetch and retrieve data, handle errors, and close connections. It provides examples of connecting and querying databases using MySQLi and PDO in PHP.

Uploaded by

Sai Krishna
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 67

PHP and MySQL

Web Development

PHP and MySQL Web tMyn 1


Development
Database Design
• In a relational database, a database is
composed of one or more tables.
• A table is the principal unit of storage in a
database.
• Each table in a database is generally
modeled after some type of real-world
entity
• Eg: customer, product etc

PHP and MySQL Web tMyn 2


Development
• A table is a two-dimensional container for
data that consists of records (rows)
• Each record has the same number of
columns, called fields
• Each table will have one special field
called a primary key
• Used to uniquely identify each record in a
table.

PHP and MySQL Web tMyn 3


Development
PHP and MySQL Web tMyn 4
Development
SQL
• SELECT
• INSERT
• UPDATE
• DELETE

PHP and MySQL Web tMyn 5


Development
Database APIs
• PHP MySQL APIs
1. MySQL extension
• used with versions of MySQL older than 4.1.3.
• replaced with the newer mysqli extension
2. mysqli extension
• provides both a procedural and an object-oriented
approach.
3. PHP data objects (PDOs).
1. This object-oriented API has been available since
2. PHP 5.1

PHP and MySQL Web tMyn 6


Development
The mysql_connect() function opens a non-persistent MySQL connection.
This function returns the connection on success, or FALSE and
an error on failure.
Syntax
mysql_connect(server,user,pwd,newlink,clientflag)
Parameter Description
server Optional. Specifies the server to connect to (can also
include a port number, e.g. "hostname:port" or a path to a
local socket for the localhost).
Default value is "localhost:3306"
user Optional. Specifies the username to log in with. Default
value is the name of the user that owns the server process
pwd Optional. Specifies the password to log in with. Default is ""

PHP and MySQL Web tMyn 7


Development
Example (MySQLi Object-Oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

PHP and MySQL Web tMyn 8


Development
Example (MySQLi Procedural)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

PHP and MySQL Web tMyn 9


Development
Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
$conn = new PDO("mysql:host=$servername;dbname=myDB",
$username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>

PHP and MySQL Web tMyn 10


Development
Close the Connection
The connection will be closed automatically when the script ends. To
close the connection before, use the following:

MySQLi Object-Oriented:
$conn->close();

MySQLi Procedural:
mysqli_close($conn);

PDO:
$conn = null;

PHP and MySQL Web tMyn 11


Development
// Create database
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " .
mysqli_error($conn);
}

PHP and MySQL Web tMyn 12


Development
The mysql_select_db() function sets the active MySQL database.
This function returns TRUE on success, or FALSE on failure.
Syntax
mysql_select_db(database,connection)

Parameter Description
database Required. Specifies the database to select.
connection Optional. Specifies the MySQL connection. If not specified,
the last connection opened by mysql_connect() or
mysql_pconnect() is used.

PHP and MySQL Web tMyn 13


Development
The mysql_query() function executes a query on a MySQL database.
This function returns the query handle for SELECT queries,
TRUE/FALSE for other queries, or FALSE on failure.
Syntax
mysql_query(query,connection)
Parameter Description
query Required. Specifies the SQL query to send (should not
end with a semicolon).
connection Optional. Specifies the MySQL connection. If not specified,
the last connection opened by mysql_connect() or
mysql_pconnect() is used.

PHP and MySQL Web tMyn 14


Development
The mysql_fetch_array() function returns a row from a recordset as an
associative array and/or a numeric array. This function gets a row from
the mysql_query() function and returns an array on success, or FALSE
on failure or when there are no more rows.
Syntax
mysql_fetch_array(data,array_type)
Parameter Description
data Required. Specifies which data pointer to use. The data
pointer is the result from the mysql_query() function
array_type Optional. Specifies what kind of array to return.
Possible values:
MYSQL_ASSOC - Associative array
MYSQL_NUM - Numeric array
MYSQL_BOTH - Default. Both associative and numeric array
PHP and MySQL Web tMyn 15
Development
The mysql_fetch_object() function returns a row from a recordset as an object.
This function gets a row from the mysql_query() function and returns an object
on success, or FALSE on failure or when there are no more rows.
Syntax
mysql_fetch_object(data)
Parameter Description
data Required. Specifies which data pointer to use. The data
pointer is the result from the mysql_query() function
Tips and Notes
Note: Each subsequent call to mysql_fetch_object() returns the
next row in the recordset.

PHP and MySQL Web tMyn 16


Development
The mysql_affected_rows() function returns the number of affected rows
in the previous MySQL operation. This function returns the number of
affected rows on success, or -1 if the last operation failed.
Syntax
mysql_affected_rows(connection)
Parameter Description
connection Optional. Specifies the MySQL connection. If not specified,
the last connection opened by mysql_connect() or
mysql_pconnect() is used.

PHP and MySQL Web tMyn 17


Development
The mysql_num_rows() function returns the number of rows in a recordset.
This function returns FALSE on failure.
Syntax
mysql_num_rows(data)
Parameter Description
data Required. Specifies which data pointer to use.
The data pointer is the result from the mysql_query() function

PHP and MySQL Web tMyn 18


Development
The mysql_result() function returns the value of a field in a recordset.
This function returns the field value on success, or FALSE on failure.
Syntax
mysql_result(data,row,field)
Parameter Description
data Required. Specifies which result handle to use. The data
pointer is the return from the mysql_query() function
row Required. Specifies which row number to get.
Row numbers start at 0

PHP and MySQL Web tMyn 19


Development
field Optional. Specifies which field to get. Can be field offset,
field name or table.fieldname. If this parameter is not defined
mysql_result() gets the first field from the specified row.
Tips and Notes
This function is slower than mysql_fetch_row(),
mysql_fetch_array(), mysql_fetch_assoc() and
mysql_fetch_object().

PHP and MySQL Web tMyn 20


Development
The mysql_error() function returns the error description of the last
MySQL operation. This function returns an empty string ("") if no error occurs.
Syntax
mysql_error(connection)
Parameter Description
connection Optional. Specifies the MySQL connection. If not specified,
the last connection opened by mysql_connect() or
mysql_pconnect() is used.

PHP and MySQL Web tMyn 21


Development
The mysql_close() function closes a non-persistent MySQL connection.
This function returns TRUE on success, or FALSE on failure.
Syntax
mysql_close(connection)
Parameter Description
connection Optional. Specifies the MySQL connection to close.
If not specified, the last connection opened by
mysql_connect() is used.

PHP and MySQL Web tMyn 22


Development
die — Equivalent to exit()

Description
This language construct is equivalent to exit().

PHP and MySQL Web tMyn 23


Development
exit — Output a message and terminate the current script

Description
void exit ([ string $status ] )
void exit ( int $status )
Terminates execution of the script.

Parameters
status
If status is a string, this function prints the status just before exiting.
If status is an integer, that value will also be used as the exit status.
Exit statuses should be in the range 0 to 254, the exit status 255 is
reserved by PHP and shall not be used. The status 0 is used to
terminate the program successfully.
PHP and MySQL Web tMyn 24
Development
• A typical web database transaction consists of the
following stages, which are numbered in the Figure 1:

1. A user’s web browser issues an HTTP request for a


particular web page. For example, using an HTML form,
she might have requested a search for all books at
MikkeliOnlineProfessionalBooks.com written by Leila
Karjalainen. The search results page is called
results.php.

2. The web server receives the request for results.php,


retrieves the file, and passes it to the PHP engine for
processing.

PHP and MySQL Web tMyn 25


Development
1

Browser 6
Web Server

5 2

MySQL Server 4
PHP Engine

PHP and MySQL Web tMyn 26


Development
3. The PHP engine begins parsing the script. Inside the
script is a command to connect to the database and
execute a query (perform the search for books). PHP
opens a connection to the MySQL server and sends on
the appropriate query.

4. The MySQL server receives the database query,


processes it, and sends the results - a list of books -
back to the PHP engine.

5. The PHP engine finishes running the script, which


usually involves formatting the query results nicely in
HTML. It then returns the resulting HTML to the web
server.
PHP and MySQL Web tMyn 27
Development
6. The web server passes the HTML back to the
browser, where the user can see the list of books she
requested.

• The above described process is basically the same


regardless of which scripting engine or database server
you use.
• Sometimes the web server, PHP engine, and database
server all run on the same machine.
• However, it is quite common for the database server to
run on a different machine. You might do this for reasons
of security, increased capacity, or load spreading. From
a development perspective, this approach is much the
same to work with.
PHP and MySQL Web tMyn 28
Development
• First example reads in and displays the contents of the
Friend table from the database Future.
• Our script will do the following jobs:
– Set up a connection to the appropriate database
– Query the database table
– Retrieve the results
– Present the results back to the user

• First we need to create the needed database and


database table – this time we will do it directly using
MySQL Query Browser:

PHP and MySQL Web tMyn 29


Development
PHP and MySQL Web tMyn 30
Development
PHP and MySQL Web tMyn 31
Development
PHP and MySQL Web tMyn 32
Development
• Next the PHP script:

PHP and MySQL Web tMyn 33


Development
PHP and MySQL Web tMyn 34
Development
PHP and MySQL Web tMyn 35
Development
…and what you can see from the browser:

PHP and MySQL Web tMyn 36


Development
‘$sqlResult = mysql_query...’

• When you select items from a database using


mysql_query(), the data is returned as a MySQL
result. Since we want to use this data in our program we
need to store it in a variable. $sqlResult now holds
the result from our mysql_query().

PHP and MySQL Web tMyn 37


Development
‘while($sqlRow =
mysql_fetch_array( $sqlResult…)’

• The mysql_fetch_array function gets the next-in-line


associative array from a MySQL result. By putting it in a
while loop it will continue to fetch the next array until
there is no next array to fetch. This function can be
called as many times as you want, but it will return
FALSE when the last associative array has already been
returned.

PHP and MySQL Web tMyn 38


Development
1. We can retrieve the next associative array from our
MySQL resource, $sqlResult, so that we can print out
the retrieved information.
2. We can tell the while loop to stop printing out
information when the MySQL resource has returned the
last array, as FALSE is returned when it reaches the end
and this will cause the while loop to halt.

• A resource is a special variable, holding a reference to


an external resource.

PHP and MySQL Web tMyn 39


Development
• In the above script, we have accessed the firstName
column like this: $sqlRow[‘firstName’]. That can
also be done by using integer indexing:

PHP and MySQL Web tMyn 40


Development
PHP and MySQL Web tMyn 41
Development
• Or finding out the number of rows in a recordset:

PHP and MySQL Web tMyn 42


Development
PHP and MySQL Web tMyn 43
Development
PHP and MySQL Web tMyn 44
Development
• Or returning a row from a recordset as an object

PHP and MySQL Web tMyn 45


Development
PHP and MySQL Web tMyn 46
Development
PHP and MySQL Web tMyn 47
Development
• In the next example we will insert one row to the Friend
table. First directly from web server to the database
server without any user interface.

PHP and MySQL Web tMyn 48


Development
PHP and MySQL Web tMyn 49
Development
PHP and MySQL Web tMyn 50
Development
PHP and MySQL Web tMyn 51
Development
Insert data from a form into a database:

• Now we will create an HTML form that can be used to


add new records to the Friend table, file database3.html:

PHP and MySQL Web tMyn 52


Development
PHP and MySQL Web tMyn 53
Development
• When a user clicks the submit button in the HTML form
in the example above, the form data is sent to
database3.php.
• The database3.php file connects to a database, and
retrieves the values from the form with the PHP $_POST
variables.
• Then, the mysql_query() function executes the
INSERT INTO statement, and a new record will be
added to the Friend table.
• Here is the database3.php page:

PHP and MySQL Web tMyn 54


Development
PHP and MySQL Web tMyn 55
Development
PHP and MySQL Web tMyn 56
Development
• A minor modification to the database3.php example:
Let’s test that all the HTML fields have at least
something inputted:

PHP and MySQL Web tMyn 57


Development
PHP and MySQL Web tMyn 58
Development
PHP and MySQL Web tMyn 59
Development
PHP and MySQL Web tMyn 60
Development
PHP and MySQL Web tMyn 61
Development
PHP and MySQL Web tMyn 62
Development
PHP and MySQL Web tMyn 63
Development
• The following example selects the same data as the
example above, but will display the data in an HTML
table:

PHP and MySQL Web tMyn 64


Development
PHP and MySQL Web tMyn 65
Development
PHP and MySQL Web tMyn 66
Development
PHP and MySQL Web tMyn 67
Development

Common questions

Powered by AI

The primary benefits of using the mysqli extension over the older MySQL extension include support for both procedural and object-oriented programming paradigms and enhanced performance. Additionally, mysqli provides support for prepared statements, which helps in preventing SQL injection attacks. The MySQL extension has been replaced due to its lack of these features and is not suitable for newer versions of MySQL .

The mysql_num_rows function is used to return the number of rows in a result set from a SELECT query. It is useful for verifying whether a query returned any results. However, its limitations include dependency on the deprecated mysql extension and inefficiency with large result sets as it requires storing the entirety of the data in memory to count rows, which can lead to increased resource consumption .

The steps in a typical web database transaction include: receiving an HTTP request via a browser, processing the script with the PHP engine, connecting to the database, executing the query on the MySQL server, processing the result with the PHP engine (e.g., formatting in HTML), and sending the HTML back to the browser. These steps are necessary for managing data flow between client-side requests and server-side responses .

The mysql_fetch_array function can be used within a while loop to fetch each row of a result set as an associative, numeric, or both types of arrays. This approach enables easy access to data fields by name or index. One pro is its flexibility in data retrieval formats. However, relying on this function could lead to deprecated warnings as it's from the mysql extension, which is no longer maintained, and might have security issues. A con is the potential inefficiency if handling large datasets due to potentially high memory usage .

Using exit() and die() functions in PHP scripts can help ensure scripts terminate immediately upon encountering an error, which is critical for avoiding unintended execution paths in database interactions. However, overreliance on these functions could lead to poor UX if not handled gracefully with error messages or logging. Best practices suggest using these functions in combination with error logging for debugging and user communication .

Distributing database servers and web servers across multiple machines can be beneficial for increased security, scalability, and load balancing. For security, database servers can be secured behind additional firewalls. For scalability, multiple servers can handle more users and more data efficiently. Load balancing ensures that no single server becomes a bottleneck, improving the application's performance and reliability .

PDO offers database abstraction, which allows the same functions to be used with different database drivers, whereas MySQLi only supports MySQL databases. PDO also supports prepared statements, which mitigate SQL injection risks, and provides a robust error handling mechanism through exceptions. These features make PDO a versatile choice for database interaction compared to MySQLi's limited scope to MySQL databases .

Using the mysql_query function implies reliance on an outdated PHP extension, which lacks support for modern features like prepared statements, increasing vulnerability to SQL injection. Additionally, mysql_query does not support error exceptions as effectively as PDO, making error handling less robust. Developers are encouraged to use mysqli or PDO for better security and functionality .

Using mysqli or PDO instead of the original mysql extension is recommended because the mysql extension is deprecated and lacks support for crucial features such as prepared statements, which help prevent SQL injection attacks. Mysqli provides multiple programming paradigms and enhanced performance, while PDO offers versatility with support for various database systems. Both ensure better security and maintainability of code .

Connecting to a MySQL database with PDO involves creating a PDO instance and setting attributes for error handling, whereas mysqli or mysql_connect functions provide procedural and object-oriented interfaces without a unified way to handle errors. PDO's object-oriented nature allows for better error management and support for various database systems other than MySQL, unlike the mysql_connect function which is procedural and has limited features .

You might also like