php databse handling

PHP Database Handling

• Name: Kunj Desai• Enrollment : 140950107022• Branch: CSE-A• Year: 2017

INTRODUCTION:

• Most interactive websites nowadays require data to be presented dynamically and interactively based on input from the user. For example, a customer may need to log into a retail website to check his purchasing history. In this instance, the website would have stored two types of data in order for the customer to perform the check – the customer’s personal login details; and the customer’s purchased items. This data can be stored in two types of storage – flat files or databases.

• Flat files are only feasible in very low to low volume websites as flat files have 3 inherent weaknesses:

1) The inability to efficiently control access by users to the data

INTRODUCTION:

2) The inability to index the data. This makes it necessary to potentially read ALL the data sequentially. This is a major problem if there are a lot of records in the flat file because the time required to read the flat file is proportionate to the number of records in the flat file.

3) The inefficient storage of the data. In most cases, the data would not be encrypted or compressed as this would exacerbate the problem no. 2 above

• The alternative which is, in my opinion, the only feasible method, is to store the data in a database. One of the most prevalent databases in use is MySQL.

• Data that is stored in a database can easily be indexed, managed and stored efficiently. Besides that, most databases also provide a suite of accompanying utilities that allow the database administrator to maintain the database – for example, backup and restore, etc.

PHP MYSQL DATABSE:

• With PHP, you can connect to and manipulate databases.• MySQL is the most popular database system used with PHP.

What is MySQL? MySQL is a database system used on the web MySQL is a database system that runs on a server MySQL is ideal for both small and large applications MySQL is very fast, reliable, and easy to use MySQL uses standard SQL MySQL compiles on a number of platforms MySQL is developed, distributed, and supported by Oracle Corporation

PHP MYSQL DATABSE:

• The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.

• Databases are useful for storing information categorically. A company may have a database with the following tables:

Employees Products Customers Orders

PHP CONNECT TO MYSQL:

• PHP 5 and later can work with a MySQL database using:1) MySQLi extension (the "i" stands for improved)2) PDO (PHP Data Objects)• Earlier versions of PHP used the MySQL extension. However, this extension was

deprecated in 2012.• Whould we Use MySQLi or PDO? Both MySQLi and PDO have their advantages:1) PDO will work on 12 different database systems, where as MySQLi will only work with

MySQL databases.

PHP CONNECT TO MYSQL:

2) So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.

3) Both are object-oriented, but MySQLi also offers a procedural API.4) Both support Prepared Statements. Prepared Statements protect from SQL injection, and

are very important for web application security.

PDO (PHP Data Objects) :

• PDO Installation: For installation details, go to: http://php.net/manual/en/pdo.installation.php


• Notice that in the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

• A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.

• Close the Connection: $conn = null;


• The following PDO example create a database named "myDBPDO":


• A great benefit of PDO is that it has exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block. In the catch block above we echo the SQL statement and the generated error message.

• Create a MySQL Table Using PDO:• The CREATE TABLE statement is used to create a table in MySQL.• We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname",

"email" and "reg_date"


• The data type specifies what type of data the column can hold. For a complete reference of all the available data types, go to our Data Types reference.

• After the data type, you can specify other optional attributes for each column:1) NOT NULL - Each row must contain a value for that column, null values are not allowed


2) DEFAULT value - Set a default value that is added when no other value is passed3) UNSIGNED - Used for number types, limits the stored data to positive numbers and zero4) AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each

time a new record is added5) PRIMARY KEY - Used to uniquely identify the rows in a table. The column with

PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT


Insert Data Into MySQL Using PDO:•After a database and a table have been created, we can start adding data in them.•Here are some syntax rules to follow:1)The SQL query must be quoted in PHP2)String values inside the SQL query must be quoted3)Numeric values must not be quoted4)The word NULL must not be quoted•The INSERT INTO statement is used to add new records to a MySQL table: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)


Select Data Into MySQL Using PDO:•The SELECT statement is used to select data from one or more tables: SELECT column_name(s) FROM table_name•or we can use the * character to select ALL columns from a table: SELECT * FROM table_name

Delete Data Into MySQL Using PDO:•DELETE FROM table_name WHERE some_column = some_value


Update Data Into MySQL Using PDO:•The UPDATE statement is used to update existing records in a table:•UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

•Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

EXAMPLE:

Insert Data:

EXAMPLE:

Select Data:

OUTPUT:.

Engineering

php databse handling