topic 4 : php & mysql
TRANSCRIPT
Topic 4 : PHP & MySQL
Er. Pradip Kharbuja
Our Architecture So Far
PHP
HTML
??
Presentation
Application
Data
New Architecture
PHP
HTML
MySQL
Presentation
Application
Data
PHP & MySQL
• This lecture will introduce MySQL into our dynamic websites architecture.
-MySQL fits into the data layer of our N-Tier architecture.
• PHP acts as the mediator between the client and the database management
system.
-The presentation layer never communicates directly with the data layer.
-The data layer never communicates directly with the client layer.
Connecting to the Database
<?php$host = "localhost";$user = "root";$pass = "softwarica";$database = "dynamic_website";
$connection = mysql_connect($host, $user, $pass) or die ("Couldn't connect to database");
?>
Errors
• If you have a problem connecting to database, it is one of the following:
1. The host is not correct.
2. The username does not exist.
3. The password does is incorrect.
Selecting database
• After successfully connected to the server, you must select the database with which we are going to work.
$host = "localhost";$user = "root";$pass = "softwarica";$database = "dynamic_website";
$connection = mysql_connect($host, $user, $pass) or die ("Couldn't connect to database");
mysql_select_db ($database);
Useful MySQL Functions
1. mysql_connect()
Opens a connection to a MySQL Server
2. mysql_select_db()
Selects a MySQL database
3. mysql_query()
queries are executed through mysql_query() function
4. mysql_error()
mysql_error() is a useful function that returns the error that was encountered.
Creating a Table
$query = "CREATE TABLE test_table (
FirstName varchar(15), SurName varchar(15)
)";
$ret = mysql_query ($query, $connection);
if ($ret) {
echo "<p>Table created!</p>";
} else {
echo "<p>Something went wrong: " . mysql_error() . "</p>";
}
Creating a Table - Described
• This is a process that can be done only once.
- Often this is done as a separate setup.php process.
- After that, it will fail saying that the table already exists.
Inserting Data in a Table
• Almost all MySQL manipulation is done through the mysql_query() function.
$query = "INSERT INTO test_table(FirstName, SurName) VALUES ('Michael', 'Jackson')";
$ret = mysql_query ($query, $connection);
if ($ret) {
echo "<p>Data Inserted!</p>";
} else {
echo "<p>Something went wrong: " . mysql_error() . "</p>";
}
Updating Data
• Almost all MySQL manipulation is done through the mysql_query() function.
$query = "UPDATE test_table SET FirstName = 'Marie';
$ret = mysql_query ($query, $connection);
if ($ret) {
echo "<p>Data updated!<br>No. of rows affected : " . mysql_affected_rows() . "</p>";
} else {
echo "<p>Something went wrong: " . mysql_error() . "</p>";
}
Deleting Data
• Almost all MySQL manipulation is done through the mysql_query() function.
$query = "DELETE FROM test_table;
$ret = mysql_query ($query, $connection);
if ($ret) {
echo "<p>Data deleted!<br>No. of rows affected : " . mysql_affected_rows() . "</p>";
} else {
echo "<p>Something went wrong: " . mysql_error() . "</p>";
}
Getting Data Out of a Database
• Getting data out of a database too is done through queries using mysql_query().
resource mysql_query(string $query [, resource $link])
• However, manipulating that data requires us to do some further processing.
• The results come out in the form of an associative array.
- The keys of each associative array are the fields in the database.
- The values are the contents of the database.
Extracting the Data
• We have 2 main ways of getting to the actual data.
1. mysql_result()
Retrieves the contents of one cell from a MySQL result set.
string mysql_result(resource $result, int $row [, mixed $field])
2. mysql_fetch_array()
Returns an array that corresponds to the fetched row and moves the internal
data pointer ahead
array mysql_fetch_array(resource $result [, int $result_type])
mysql_result() Example
$query = "SELECT * FROM test_table";
$resource = mysql_query ($query, $connection);
$first_name = mysql_result($resource, 0, "FirstName");
$sur_name = mysql_result($resource, 0, "SurName");
echo "<p>The first person in our list is {$first_name} {$sur_name}</p>";
Fetching a Row
- We can use mysql_fetch_array() to fetch each row in order.
- Each call to the function increments the counter by one.
//gives the number of rows returned
$num_results = mysql_num_rows($resource);
for ($i = 0; $i < $num_results; $i++) {
$row = mysql_fetch_array ($resource);
echo $row["FirstName"] . " ";
echo $row["SurName"] . " ";
}
Login with Database
• First Create a table named tbl_user
• Now insert some users
• Then develop HTML Login Form
• Now do Coding
Column Name Data Type
user_id INT(11) PK AUTO
user_name VARCHAR (50) Unique
pass_word VARCHAR (50)
SQL Injection
• Whenever we insert text from a user into a query, there is risk of an SQL
injection attack.
• SQL injection is a code injection technique, used to attack data driven
applications.
• You should sanitise(make clean and hygienic) all user data using the
mysql_real_escape_string() function.
• It escapes special characters in a string for use in an SQL statement.
• ' is changed to \'
Sanitised Input
$user_name = $_POST['user_name'];
$pass_word = $_POST['pass_word'];
will be changed to :
$user_name = mysql_real_escape_string($_POST['user_name']);
$pass_word = mysql_real_escape_string($_POST['pass_word']);
Login Lockouts
• To lock or disable the login page after few incorrect login attempts.
• It should keep a track of how many unsuccessful attempts were made.
• If the number goes above some number like three or four or five, it should
automatically report or lock / disable login page.
MySQL and PHP
• In all cases, you must be mindful of several things:
- Efficiency of data access
- Readability of your code
- Maintainability of your code
- Portability of your architecture
Assignment #4
1. Develop a login system.
• Use MySQL Databse
• Use Session
• Login should be lockouts after 5 attemps
• You must be able to post status.
Last Date to Submit : 25th September 2013
Questions ???
End of Topic 04