• Combines individual sql operations into one
• Usually start with begin or begin transaction
• Execute the transaction using commit
• Cancel the transaction using rollback
Example
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
Unbuffered MySQL queries execute the query and then return a resource while the data is still waiting on the MySQL server for being fetched. This uses less memory on the PHP-side, but can increase the load on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as “use result”.
A cursor can’t be used by itself in MySQL. It is an essential component in stored procedures. I would be inclined to treat a cursor as a “pointer” in C/C++, or an iterator in PHP’s foreach statement.
With cursors, we can traverse a dataset and manipulate each record to accomplish certain tasks. When such an operation on a record can also be done in the PHP layer, it saves data transfer amounts as we can just return the processed aggregation/statistical result back to the PHP layer (thus eliminating the select – foreach – manipulation process at the client side).
Since a cursor is implemented in a stored procedure, it has all the benefits (and limitations) of an SP (access control, pre-compiled, hard to debug, etc).
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
Asensitive: The server may or may not make a copy of its result table
Read only: Not updatable
Nonscrollable: Can be traversed only in one direction and cannot skip rows
Cursor declarations must appear before handler declarations and after variable and condition declarations.
phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions.
Install phpmyadmin
1) Execute sudo apt-get install phpmyadmin
2) Edit the file /etc/apache2/apache2.conf. and include the line Include /etc/phpmyadmin/apache.conf
3) Restart Apache sudo /etc/init.d/apache2 reload
Features
The mysql_fetch_array() function returns a row from a recordset as an associative array and a numeric array.
If two or more columns of the result have the same field names,then the last column will take precedence.
Below given are the some MySQL drivers
A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (“,”). A consequence of this is that SET member values should not themselves contain commas.
Example
mysql> CREATE TABLE myset (col SET(‘a’, ‘b’, ‘c’, ‘d’));
Query OK, 0 rows affected (0.43 sec)
mysql> select * from myset;
Empty set (0.20 sec)
mysql> insert into myset value(‘p’);
Query OK, 1 row affected, 1 warning (0.23 sec)
mysql> select * from myset;
+——+
| col |
+——+
| |
+——+
1 row in set (0.10 sec)
mysql> insert into myset value(‘a’);
Query OK, 1 row affected (0.18 sec)
mysql> select * from myset;
+——+
| col |
+——+
| |
| a |
+——+
2 rows in set (0.00 sec)
mysql> insert into myset value(‘p,a’);
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> select * from myset;
+——+
| col |
+——+
| |
| a |
| a |
+——+
3 rows in set (0.00 sec)
Following are the differences between CHAR and VARCHAR:
A regular expression is a powerful way of specifying a pattern for a complex search.
MySQL uses Henry Spencer’s implementation of regular expressions, which is aimed at conformance with POSIX 1003.2. MySQL uses the extended version to support pattern-matching operations performed with the REGEXP operator in SQL statements.
ENUM is a string object used to specify set of predefined values and that can be used during table creation.
CREATE table phpcodez(name ENUM(‘Average’,’Good’,’Excellent’));