PHP Data Object: How to

With reference to previous post on PDO. Here is a small tutorial and explanation for connecting to database using PDO object. I recommend using PDO as it is more structured way to connect to database rather than doing it by self plus the general functions like mysql_connect are to be deprecated in upcoming versions of PHP. There is another class called mysqli, if you are using mysql you can use it but again if in case you have to change your database, PDO is best.

Required: PHP 5.0+

Using:

<?php
$user = 'user';
$pass = 'mypass';

$color = 'red';

/** PDO is a class for php data object in php. First argument takes the 
  "typeofdatabase:host=localhost;dbname=db"
* typeofdatabase here is mysql, which can postgre, mssql etc.
* host is in general localhost or you use user with which you connect to 
  database.
* dbname is the name of database you want to connect with.
* return the object of the database connection.
**/
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

/** Begin transaction, which is very general term in database. Actually 
   any of CRUD is called transaction.
**/
$dbh->beginTransaction();

$query = "SELECT name FROM fruit WHERE color = ?";    // Query

/** Prepares the query/statement for execution and return the 
   object of statement.
**/
$sth = $dbh->prepare($query);

/** Binds the variables to the prepared query
* first argument takes the position of question mark to which 
  the variable has to be bound.
* second argument is the variable which is to be bound.
* type of variable in the query. Like here the color is string, 
  it can be int etc. and for it there are predefined constants. 
  Its optional.
**/
$sth->bindParam(1, $color, PDO::PARAM_STR);

$sth->execute();     //Query execution.

/** Fetch method is used to fetch each row. There are other methods 
  also like fetchAll
* it takes predefined constant in PDO as argument, FETCH_BOTH returns 
  the row with number and name indexes. Like $result['name'] and $result[0].
* it returns the row array.
**/
while($result = $sth->fetch(PDO::FETCH_BOTH)){
     echo $result['name'];
}

?>

Explanation:

Most of the things which are used in general are explained in the code snippet. But I would still recommend PHP: PDO Manual. Look at the other methods and predefined constants, specially understand the difference between bindParam method and bindValue method. For the record bindParam is better way because it binds the variable at the time of execution of query.

Look at mysqli also for a method called bind_result. Which is quite good, you can extend this PDO class and include this method in it.

Hope you will find this post useful.

Advertisements

PDO: PHP Data Object

There are different ways of connecting to database in PHP. The most general way in use is MySQL extension, which we have discussed here and even everywhere. Well there are two other ways of connecting to database. First one is MySQLi and other is PDO.

Now, question is what is the difference? So, the difference is MySQLi is MySQL improved. As name states, it is improved version of the MySQL extension. It uses both direct function which we use in MySQL extension and the object oriented way of implementing the same. So if I would be using MySQLi, I would choose OOP way. But MySQLi extension is only for MySQL. It is more secured if we use it in OOP way like it eliminates the threat of sql injection.

PDO is more generic because through it you can connect to different type of database by changing just the name of the database you want to connect. You can connect to MySQL, MS SQL, PostgreSQL, SQLite and many more. So it is more generalized, as you will not be needed to change the function call and other things. All you have to do is change the connector name, this is the term which is used for software that connects application to database. It is also based on the OOPs the concepts, which makes it easy to use.

PDO and MySQLi have very much the same way of implementation. Moreover MySQL extension is being maintained and can be deprecated in near future. While the other two are in active development.

Next time we’ll be learning implementation using PDO. Its very easy, meanwhile if you want to look it by yourself, here are the links. PDO and MySQLi.

MySQL: Views

Its been a long time since I have posted anything, hopefully interesting. In this post we’ll see Views in MySQL. Its not just MySQL, every database has it, we are just using MySQL. I’ll try to explain it in the most simplest terms I can.

Views are just like tables but there is a big difference. Views are made by querying the actual tables. Its like you form a query and save it as view in your database. So basically views are virtual tables which provide sort of abstraction, remember sort of, because they are created by querying actual tables. So whenever we select from views it runs the saved query and gives the resulted tuples. It will be more clear when we see the example. Every organization which uses database declare views and set privileges for it according to category of users which we call permissions. Plus views are more secured obviously, in most cases, because users are not interacting with actual database.

Now, technically views are just stored queries. They just makes our job easier when it comes to the coding part. You create queries and save in database and when needed that data just call select statement for that view.

Required: MySQL

Using:

Consider tables table1 and table2. Please ignore the order of the tables. Declaration of table is for understanding properly and not actual defination.

table1(
  id int primary key,
  name varchar,
  dept_id int foreign key references table2.id,
)

table2(
  id int primary key,
  department varchar,
  location varchar,
)

Below is the definition of view employees and selecting from view.

CREATE VIEW AS employees(
  SELECT a.name, b.department, b.location FROM table1 a, table2 b WHERE a.dept_id = b.id
);
SELECT * FROM employees;

Explanation: There is nothing to explain for the syntax, its self explanatory.

Syntax is almost same in all databases. There are further more things about views like updating views, which I do not recommend. This is because views can only be updated when it is derived from a single table.

For example consider the above view if we try to insert in the view what will be the value of dept_id in table1. Answer is null but we cannot have null in dept_id column clearly, it must have some value because every name must be associated with some dept_id.

There more options like algorithm, with check options etc. You should explore more about views and about creating views.

MySQL: Query Optimization Tips Part II

In previous post we dealt with the SELECT statement and WHERE clause optimization. In this post we will see optimization of INSERT statement. SELECT statement is very general for dynamic websites while INSERT is used very often where WRITE option is given. The tips for optimization of INSERT statement are:

  1. REPLACE can be used instead of INSERT to overwrite old rows. It can be done using WHERE clause on some UNIQUE INDEX. REPLACE can do job for both INSERT and UPDATE.
  2. Insert multiple rows using INSERT is very efficient method and very important point for optimization. Inserting single row at a time wastes the time and resource of the server and also increases the disk overheads. Multiple inserts at a time is considerably faster.
    INSERT INTO table VALUES (1,'a'),(2,'b'),(3,'c');
  3. DELAYED keyword with INSERT will push the insert value into buffer to run later in case of WRITE/READ is being done on table. This is very useful for MyISAM where table locks on WRITE/READ. Make sure you don’t require insert values immediately. This can be implement where “Login using Request” is used. It works with MyISAM only. Do not work with INSERT…SELECT.
    INSERT DELAYED table VALUES (1,'a'),(2,'b'),(3,'c');
  4. Priority can also be set for the INSERT statements, LOW_PRIORITY and HIGH_PRIORITY, which is also quite useful for some tasks. Works with MyISAM only.
  5. Using IGNORE keyword with INSERT is like a treat. IGNORE converts error into warnings and completes the query. Eg: If your query has insert value which has repeated unique value IGNORE will keep running the query ignoring the error while normal query would have aborted.
  6. The fastest method to load data in a database is LOAD DATA INFILE. It is usually 20 times faster than INSERT statement.
    LOAD DATA INFILE datafile.txt INTO table

If you want more clear picture, read MySQL manual.

MySQL: Query Optimization Tips Part I

Writing SQL query is easy but it can use lots of server resources which is utmost important for huge databases, where minimization of execution delay is important, like Facebook – if they don’t update your status very frequently it will cost them users. Let’s start:

  1. Most important for query optimization is INDEX. Make sure you define index in your table for the columns which you use more often than others or create for every possible column.
  2. Use EXPLAIN statement to determine which indexes are used for a SELECT statement.

These were the general tips. For speeding up queries on MyISAM tables:

SELECT queries

  1. Use ANALYZE TABLE after loading data on it. This updates a value for each index part that indicates the average number of rows that have the  same value. For unique obviously it is always 1. MySQL uses this to decide which index to choose when JOIN is used.
  2. Sorting a table by index is good when reading all rows of the table according to the index.

WHERE clause

  1. Removal of unnecessary parentheses. eg:
    (a AND b AND c) OR (a AND b AND c AND d)

    instead of

    ((a AND b AND c) OR (a AND b AND c AND d))
  2. Constant folding eg:
    b>5 AND b=c AND a=5

    instead of

    (a<b AND b=c) AND a=5
  3. Constant expression used by indexes are only used once so try to use them as more as you can.
  4. COUNT(*) without WHERE clause on a single table fetch the result directly from MyISAM table information which is pretty fast.
  5. Constant tables are read first before any other tables. Constant table are:
    • Empty tables or table with one row.
    • A table with which WHERE clause is used on PRIMARY KEY or UNIQUE index.
  6. JOIN statement is slower that any other. It checks all the possibilities for join to give best result. If ORDER BY or GROUP BY is used and all the columns come from same table then this table is preferred first in joining.
  7. In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
  8. LIMIT also executes faster than query without it.

I hope you found this helpful. We’ll continue with INSERT statements optimization in next post.

MySQL: Good Efficient Structured Database

MySQL is a well known name in RDBMS world and many of you must have used it. It implements one of the oldest way to create a database. I have never used it with any other application but web though. To run MySQL I use phpmyadmin, the browser application made for ease. We will be discussing some settings for MySQL other than default.

Required: phpmyadmin ( if you can use it from command line good for you! 🙂 )

  1. InnoDB vs MyISAM: These two are storage engines which are widely used. Facebook use InnoDB storage engine but they use their version of MySQL which is like more that 10,000 times more efficient than the default. They have optimized it for write operations (writing in database). We’ll see the difference between two (not all but major) and you choose which is best for you.
    • MyISAM – Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.The maximum number of rows supported amounts to somewhere around ~4.295E+09 and can have up to 64 indexed fields per table. Both of these limits can be greatly increased by compiling a special version of MySQL. Use the option –with-big-tables while creating table.Text/Blob fields are able to be fully-indexed which is of great importance to search functions.Number of indexes can be 64 in a table by default. You can increase it anything up to 128 using –with-max-indexes=N, where N < 128 while compiling MySQL or simply when you run it.Concurrent inserts are supported. Only condition there are no free blocks i.e., a result of deleting rows or an update of a dynamic length row with more data than its current contents.You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options. This is sort of prerequisite for huge databases.Table locking is present so concurrent writes are not possible.
    • InnoDB – It is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data.Consistent nonlocking reads increase multi-user concurrency and performance.Stores user data in clustered indexes to reduce I/O for common queries based on primary keys.It supports Foreign Key which is of great use if  you don’t want to check the values being inserted in table which derives from different table.Designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.Maintains its own buffer pool for caching data and indexes in main memory.

      Stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions).

      Row lock is present so simultaneous writes on a table is possible.

  2. To set auto increment value for primary key but with default starting value other than 1 go to “OPERATIONS” tab for the table change the auto increment value to whatever you like say 1000000
  3. Go to the home page of phpmyadmin and click “PRIVILEGES”. You can assign different privilege to users that are using your database here. When you’ll add the user you can set privileges for him at the same time.
  4. “ENGINES” tab on your home page will tell which engines are installed on your machine.
  5. “VARIABLES” tab is the configuration which is currently set. You can change these variables for example: big table is “off” by default, you can switch it on and can increase the number of rows in a table to twice. Though not useful for InnoDB.

I personally use InnoDB, though it doesn’t matter for the amount of data I have. So, these were the few things which I believe can surely help you in creating a good database structure. Engines plays the most important role that’s why it was discussed in detail. I highly recommend you to read manual for MySQL.

MySQL: Alternate to INNER JOIN

I came across a good way to write queries in mysql. Instead of using INNER JOIN there is a easy way to do it, sort of renaming the tables while writing query. Below is the example:Requires: MySQL

Using:

table1(
     id int,
     name varchar(30),
     age int)
table2(
     id int,
     address varchar(300))
SELECT a.name, a.age, b.address FROM table1 a, table2 b WHERE a.id = b.id;

Explanation:

‘id’ in both the table is primary key.

Look at the query renaming of table is done before retrieving the information we want. WHERE clause is very important in the query because there should be something which is common in both the tables in order to fetch the data. here we have taken id, it should not be necessarily primary key but just have common type.

Note:

  • You can also write queries instead of table name in FROM clause i.e., sub-queries.
  •  If you are not getting results as you expect, there is something wrong with the way query has been written. Try writing it some other way. And this will happen a lot with complex queries. 😛
  • So, this method will make your queries really powerful and easy. Time to replace INNER JOIN.