1. Functions with Arbitrary Number of ArgumentsYou may already know that PHP allows you to define functions with optional arguments. But there is also a method for allowing completely arbitrary number of function arguments. First, here is an example with just optional arguments:
Now, let’s see how we can build a function that accepts any number of arguments. This time we are going to utilize func_get_args():
2. Using Glob() to Find FilesMany PHP functions have long and descriptive names. However it may be hard to tell what a function namedglob() does unless you are already familiar with that term from elsewhere. Think of it like a more capable version of the scandir() function. It can let you search for files by using patterns.
You can fetch multiple file types like this:
Note that the files can actually be returned with a path, depending on your query:
If you want to get the full path to each file, you can just call the realpath() function on the returned values:
3. Memory Usage InformationBy observing the memory usage of your scripts, you may be able optimize your code better. PHP has a garbage collector and a pretty complex memory manager. The amount of memory being used by your script. can go up and down during the execution of a script. To get the current memory usage, we can use the memory_get_usage() function, and to get the highest amount of memory used at any point, we can use the memory_get_peak_usage() function.
4. CPU Usage InformationFor this, we are going to utilize the getrusage() function. Keep in mind that this is not available on Windows platforms.
That may look a bit cryptic unless you already have a system administration background. Here is the explanation of each value (you don't need to memorize these):
To see how much CPU power the script has consumed, we need to look at the 'user time' and 'system time' values. The seconds and microseconds portions are provided separately by default. You can divide the microseconds value by 1 million, and add it to the seconds value, to get the total seconds as a decimal number. Let's see an example:
Even though the script took about 3 seconds to run, the CPU usage was very very low. Because during the sleep operation, the script actually does not consume CPU resources. There are many other tasks that may take real time, but may not use CPU time, like waiting for disk operations. So as you see, the CPU usage and the actual length of the runtime are not always the same. Here is another example:
That took about 1.4 seconds of CPU time, almost all of which was user time, since there were no system calls. System Time is the amount of time the CPU spends performing system calls for the kernel on the program's behalf. Here is an example of that:
Now we have quite a bit of system time usage. This is because the script calls the microtime() function many times, which performs a request through the operating system to fetch the time. Also you may notice the numbers do not quite add up to 3 seconds. This is because there were probably other processes on the server as well, and the script was not using 100% CPU for the whole duration of the 3 seconds. 5. Magic ConstantsPHP provides useful magic constants for fetching the current line number (__LINE__), file path (__FILE__), directory path (__DIR__), function name (__FUNCTION__), class name (__CLASS__), method name (__METHOD__) and namespace (__NAMESPACE__). We are not going to cover each one of these in this article, but I will show you a few use cases.
Using __LINE__ makes debugging easier. You can track down the line numbers:
6. Generating Unique ID'sThere may be situations where you need to generate a unique string. I have seen many people use themd5() function for this, even though it's not exactly meant for this purpose: There is actually a PHP function named uniqid() that is meant to be used for this.
You may notice that even though the strings are unique, they seem similar for the first several characters. This is because the generated string is related to the server time. This actually has a nice side effect, as every new generated id comes later in alphabetical order, so they can be sorted. To reduce the chances of getting a duplicate, you can pass a prefix, or the second parameter to increase entropy:
This function will generate shorter strings than md5(), which will also save you some space. 7. SerializationHave you ever needed to store a complex variable in a database or a text file You do not have to come up with a fancy solution to convert your arrays or objects into formatted strings, as PHP already has functions for this purpose. There are two popular methods of serializing variables. Here is an example that uses the serialize() and unserialize():
This was the native PHP serialization method. However, since JSON has become so popular in recent years, they decided to add support for it in PHP 5.2. Now you can use the json_encode() and json_decode()functions as well:
It is more compact, and best of all, compatible with javascript and many other languages. However, for complex objects, some information may be lost. 8. Compressing StringsWhen talking about compression, we usually think about files, such as ZIP archives. It is possible to compress long strings in PHP, without involving any archive files. In the following example we are going to utilize the gzcompress() and gzuncompress() functions:
We were able to achive almost 50% size reduction. Also the functions gzencode() and gzdecode() achive similar results, by using a different compression algorithm. 9. Register Shutdown FunctionThere is a function called register_shutdown_function(), which will let you execute some code right before the script finishes running. Imagine that you want to capture some benchmark statistics at the end of your script execution, such as how long it took to run:
At first this may seem trivial. You just add the code to the very bottom of the script and it runs before it finishes. However, if you ever call the exit() function, that code will never run. Also, if there is a fatal error, or if the script is terminated by the user (by pressing the Stop button in the browser), again it may not run. When you use register_shutdown_function(), your code will execute no matter why the script has stopped running:
ConclusionAre you aware of any other PHP features that are not widely known but can be quite useful Please share with us in the comments. And thank you for reading!
1. Optimize Your Queries For the Query CacheMost MySQL servers have query caching enabled. It’s one of the most effective methods of improving performance, that is quietly handled by the database engine. When the same query is executed multiple times, the result is fetched from the cache, which is quite fast. The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. Some things we do can actually prevent the query cache from performing its task.
The reason query cache does not work in the first line is the usage of the CURDATE() function. This applies to all non-deterministic functions like NOW() and RAND() etc… Since the return result of the function can change, MySQL decides to disable query caching for that query. All we needed to do is to add an extra line of PHP before the query to prevent this from happening. 2. EXPLAIN Your SELECT QueriesUsing the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query. This can help you spot the bottlenecks and other problems with your query or table structures. The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned and sorted etc… Take a SELECT query (preferably a complex one, with joins), and add the keyword EXPLAIN in front of it. You can just use phpmyadmin for this. It will show you the results in a nice table. For example, let’s say I forgot to add an index to a column, which I perform joins on:  After adding the index to the group_id field:  Now instead of scanning 7883 rows, it will only scan 9 and 16 rows from the 2 tables. A good rule of thumb is to multiply all numbers under the “rows” column, and your query performance will be somewhat proportional to the resulting number. 3. LIMIT 1 When Getting a Unique RowSometimes when you are querying your tables, you already know you are looking for just one row. You might be fetching a unique record, or you might just be just checking the existence of any number of records that satisfy your WHERE clause. In such cases, adding LIMIT 1 to your query can increase performance. This way the database engine will stop scanning for records after it finds just 1, instead of going thru the whole table or index.
4. Index the Search FieldsIndexes are not just for the primary keys or the unique keys. If there are any columns in your table that you will search by, you should almost always index them.  As you can see, this rule also applies on a partial string search like “last_name LIKE ‘a%’”. When searching from the beginning of the string, MySQL is able to utilize the index on that column. You should also understand which kinds of searches can not use the regular indexes. For instance, when searching for a word (e.g. “WHERE post_content LIKE ‘%apple%’”), you will not see a benefit from a normal index. You will be better off using mysql fulltext search or building your own indexing solution. 5. Index and Use Same Column Types for JoinsIf your application contains many JOIN queries, you need to make sure that the columns you join by are indexed on both tables. This affects how MySQL internally optimizes the join operation. Also, the columns that are joined, need to be the same type. For instance, if you join a DECIMAL column, to an INT column from another table, MySQL will be unable to use at least one of the indexes. Even the character encodings need to be the same type for string type columns.
6. Do Not ORDER BY RAND()This is one of those tricks that sound cool at first, and many rookie programmers fall for this trap. You may not realize what kind of terrible bottleneck you can create once you start using this in your queries. If you really need random rows out of your results, there are much better ways of doing it. Granted it takes additional code, but you will prevent a bottleneck that gets exponentially worse as your data grows. The problem is, MySQL will have to perform RAND() operation (which takes processing power) for every single row in the table before sorting it and giving you just 1 row.
So you pick a random number less than the number of results and use that as the offset in your LIMIT clause. 7. Avoid SELECT *The more data is read from the tables, the slower the query will become. It increases the time it takes for the disk operations. Also when the database server is separate from the web server, you will have longer network delays due to the data having to be transferred between the servers. It is a good habit to always specify which columns you need when you are doing your SELECT’s.
8. Almost Always Have an id FieldIn every table have an id column that is the PRIMARY KEY, AUTO_INCREMENT and one of the flavors of INT. Also preferably UNSIGNED, since the value can not be negative. Even if you have a users table that has a unique username field, do not make that your primary key. VARCHAR fields as primary keys are slower. And you will have a better structure in your code by referring to all users with their id’s internally. There are also behind the scenes operations done by the MySQL engine itself, that uses the primary key field internally. Which become even more important, the more complicated the database setup is. (clusters, partitioning etc…). One possible exception to the rule are the “association tables”, used for the many-to-many type of associations between 2 tables. For example a “posts_tags” table that contains 2 columns: post_id, tag_id, that is used for the relations between two tables named “post” and “tags”. These tables can have a PRIMARY key that contains both id fields. 9. Use ENUM over VARCHARENUM type columns are very fast and compact. Internally they are stored like TINYINT, yet they can contain and display string values. This makes them a perfect candidate for certain fields. If you have a field, which will contain only a few different kinds of values, use ENUM instead of VARCHAR. For example, it could be a column named “status”, and only contain values such as “active”, “inactive”, “pending”, “expired” etc… There is even a way to get a “suggestion” from MySQL itself on how to restructure your table. When you do have a VARCHAR field, it can actually suggest you to change that column type to ENUM instead. This done using the PROCEDURE ANALYSE() call. Which brings us to: 10. Get Suggestions with PROCEDURE ANALYSE()PROCEDURE ANALYSE() will let MySQL analyze the columns structures and the actual data in your table to come up with certain suggestions for you. It is only useful if there is actual data in your tables because that plays a big role in the decision making. For example, if you created an INT field for your primary key, however do not have too many rows, it might suggest you to use a MEDIUMINT instead. Or if you are using a VARCHAR field, you might get a suggestion to convert it to ENUM, if there are only few unique values. You can also run this by clicking the “Propose table structure” link in phpmyadmin, in one of your table views.  Keep in mind these are only suggestions. And if your table is going to grow bigger, they may not even be the right suggestions to follow. The decision is ultimately yours. 11. Use NOT NULL If You CanUnless you have a very specific reason to use a NULL value, you should always set your columns as NOT NULL. First of all, ask yourself if there is any difference between having an empty string value vs. a NULL value (for INT fields: 0 vs. NULL). If there is no reason to have both, you do not need a NULL field. (Did you know that Oracle considers NULL and empty string as being the same) NULL columns require additional space and they can add complexity to your comparison statements. Just avoid them when you can. However, I understand some people might have very specific reasons to have NULL values, which is not always a bad thing. From MySQL docs:
12. Prepared StatementsThere are multiple benefits to using prepared statements, both for performance and security reasons. Prepared Statements will filter the variables you bind to them by default, which is great for protecting your application against SQL injection attacks. You can of course filter your variables manually too, but those methods are more prone to human error and forgetfulness by the programmer. This is less of an issue when using some kind of framework or ORM. Since our focus is on performance, I should also mention the benefits in that area. These benefits are more significant when the same query is being used multiple times in your application. You can assign different values to the same prepared statement, yet MySQL will only have to parse it once. Also latest versions of MySQL transmits prepared statements in a native binary form, which are more efficient and can also help reduce network delays. There was a time when many programmers used to avoid prepared statements on purpose, for a single important reason. They were not being cached by the MySQL query cache. But since sometime around version 5.1, query caching is supported too. To use prepared statements in PHP you check out the mysqli extension or use a database abstraction layer like PDO.
13. Unbuffered QueriesNormally when you perform a query from a script, it will wait for the execution of that query to finish before it can continue. You can change that by using unbuffered queries. There is a great explanation in the PHP docs for the mysql_unbuffered_query() function:
However, it comes with certain limitations. You have to either read all the rows or call mysql_free_result() before you can perform another query. Also you are not allowed to use mysql_num_rows() or mysql_data_seek() on the result set. 14. Store IP Addresses as UNSIGNED INTMany programmers will create a VARCHAR(15) field without realizing they can actually store IP addresses as integer values. With an INT you go down to only 4 bytes of space, and have a fixed size field instead. You have to make sure your column is an UNSIGNED INT, because IP Addresses use the whole range of a 32 bit unsigned integer. In your queries you can use the INET_ATON() to convert and IP to an integer, and INET_NTOA() for vice versa. There are also similar functions in PHP called ip2long() and long2ip(). 15. Fixed-length (Static) Tables are FasterWhen every single column in a table is “fixed-length”, the table is also considered “static” or “fixed-length”. Examples of column types that are NOT fixed-length are: VARCHAR, TEXT, BLOB. If you include even just 1 of these types of columns, the table ceases to be fixed-length and has to be handled differently by the MySQL engine. Fixed-length tables can improve performance because it is faster for MySQL engine to seek through the records. When it wants to read a specific row in a table, it can quickly calculate the position of it. If the row size is not fixed, every time it needs to do a seek, it has to consult the primary key index. They are also easier to cache and easier to reconstruct after a crash. But they also can take more space. For instance, if you convert a VARCHAR(20) field to a CHAR(20) field, it will always take 20 bytes of space regardless of what is it in. By using “Vertical Partitioning” techniques, you can separate the variable-length columns to a separate table. Which brings us to: 16. Vertical PartitioningVertical Partitioning is the act of splitting your table structure in a vertical manner for optimization reasons. Example 1: You might have a users table that contains home addresses, that do not get read often. You can choose to split your table and store the address info on a separate table. This way your main users table will shrink in size. As you know, smaller tables perform faster. Example 2: You have a “last_login” field in your table. It updates every time a user logs in to the website. But every update on a table causes the query cache for that table to be flushed. You can put that field into another table to keep updates to your users table to a minimum. But you also need to make sure you don’t constantly need to join these 2 tables after the partitioning or you might actually suffer performance decline. 17. Split the Big DELETE or INSERT QueriesIf you need to perform a big DELETE or INSERT query on a live website, you need to be careful not to disturb the web traffic. When a big query like that is performed, it can lock your tables and bring your web application to a halt. Apache runs many parallel processes/threads. Therefore it works most efficiently when scripts finish executing as soon as possible, so the servers do not experience too many open connections and processes at once that consume resources, especially the memory. If you end up locking your tables for any extended period of time (like 30 seconds or more), on a high traffic web site, you will cause a process and query pileup, which might take a long time to clear or even crash your web server. If you have some kind of maintenance script that needs to delete large numbers of rows, just use the LIMIT clause to do it in smaller batches to avoid this congestion.
18. Smaller Columns Are FasterWith database engines, disk is perhaps the most significant bottleneck. Keeping things smaller and more compact is usually helpful in terms of performance, to reduce the amount of disk transfer. MySQL docs have a list of Storage Requirements for all data types. If a table is expected to have very few rows, there is no reason to make the primary key an INT, instead of MEDIUMINT, SMALLINT or even in some cases TINYINT. If you do not need the time component, use DATE instead of DATETIME. Just make sure you leave reasonable room to grow or you might end up like Slashdot. 19. Choose the Right Storage EngineThe two main storage engines in MySQL are MyISAM and InnoDB. Each have their own pros and cons. MyISAM is good for read-heavy applications, but it doesn't scale very well when there are a lot of writes. Even if you are updating one field of one row, the whole table gets locked, and no other process can even read from it until that query is finished. MyISAM is very fast at calculating SELECT COUNT(*) types of queries. InnoDB tends to be a more complicated storage engine and can be slower than MyISAM for most small applications. But it supports row-based locking, which scales better. It also supports some more advanced features such as transactions. 20. Use an Object Relational MapperBy using an ORM (Object Relational Mapper), you can gain certain performance benefits. Everything an ORM can do, can be coded manually too. But this can mean too much extra work and require a high level of expertise. ORM's are great for "Lazy Loading". It means that they can fetch values only as they are needed. But you need to be careful with them or you can end up creating to many mini-queries that can reduce performance. ORM's can also batch your queries into transactions, which operate much faster than sending individual queries to the database. Currently my favorite ORM for PHP is Doctrine. I wrote an article on how to install Doctrine with CodeIgniter. 21. Be Careful with Persistent ConnectionsPersistent Connections are meant to reduce the overhead of recreating connections to MySQL. When a persistent connection is created, it will stay open even after the script finishes running. Since Apache reuses it's child processes, next time the process runs for a new script, it will reuse the same MySQL connection. It sounds great in theory. But from my personal experience (and many others), this features turns out to be not worth the trouble. You can have serious problems with connection limits, memory issues and so on. Apache runs extremely parallel, and creates many child processes. This is the main reason that persistent connections do not work very well in this environment. Before you consider using the mysql_pconnect() function, consult your system admin.
Ref: http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/
Accessing MySQL from PHP by George Yohng
This guide is an attempt to provide more or less complete information on accessing MySQL API functionality from PHP web scripting language.
Today PHP is one of the most usable web languages used for server-side scripting. The usability of PHP came obvious since version 3 was released, and version 4 introduced another amount of useful features. A number of companies already moved from Perl/CGI to PHP, and more still to come. PHP engine is typically used with Apache server under POSIX-compatible operating systems (Linux, FreeBSD, Solaris, AIX, other UNIX clones, etc.). However PHP itself isn’t dependent on a particular web-server or operating system. As information changes enough frequently, my recommendation would be to visit official PHP site for a complete list of features, operating system supported and other information on PHP: http://www.php.net In the whole guide, PHP 4.0.6 or later version is assumed, however most of information will also be applicable to earlier versions of PHP. All OS-specific stuff will be marked as such. PHP functionality, including installation process, is about the same for all POSIX-compatible operating systems, while it slightly differs for Microsoft Windows, and thus two MySQL-API installation sections are included to this guide. The information provided in this guide should be mostly OS-independent. All example scripts were tested on SuSE Linux 7.2 Professional with Apache and PHP4 packages installed with default options, and Microsoft Windows 2000 with Apache web-server version 1.3.14 and Win32-precompiled PHP version 4.0.6.
PHP Basics
Adding MySQL Support to PHP Checking MySQL support availability Enabling MySQL API support for POSIX-compatible OS Enabling MySQL API support for Microsoft Windows Using MySQL API in PHP Database Connections Establishing connection Selecting database Closing connection Obtaining information by connection handle Executing queries Executing raw SQL Formatting data for queries Working with rowsets Buffered queries Unbuffered queries Fetching rows from rowsets Querying information about columns in table Freeing rowsets Type conversion of data values Error Handling PEAR Getting PEAR to work PEAR’s database abstraction interfaces Using PEAR’s DB interface Summary
PHP – is a scripting language for writing web applications that execute on server-side. Scripting language itself is much alike C, however it still contains many differences. Here are some basic ones: - While C program needs to be compiled before execution, PHP script is interpreted at runtime; Loop statements, conditionals and comments are identical to C syntax, with exception that each variable should be prepended with dollar sign “$”. PHP outputs data with “echo” command. The essential PHP concept is to embed scripting code to HTML, using unique delimiting tags to separate raw HTML from script code. PHP scripts are executed on server side (like Perl/CGI or Java servlets), and the final user gets pure HTML in a browser. Provided that PHP code is surrounded with <php … > tags, web server is able to filter such sections and puts the execution result instead. Here’s an example of sample PHP script, embedded to HTML: <html> <php // Calculate FOO variable // Output sample string to browser // Output string with embedded value > </html> And the upper example will output this: <html> PHP says Hello!<br><br>2 + 2 = 4 </html> This sample code doesn’t include any calls to MySQL API; it simply shows the basics of outputting data from PHP script. Considering code from the upper example, $foo – is a calculated variable (PHP requires dollar sign ‘$’ to be put before each variable name). If $foo value was fetched from database instead, the same technique is used to output its content to the browser. The following example shows trivial connection to database server and fetching data from there. In further examples HTML code will be mostly omitted, and only PHP code (fully or partially) will be shown, like this: <php // Select database // Execute sample query // Query number of rows in rowset // Output // Iteration loop, for each row in rowset // Outputting data to browser
This section will describe how to check and enable MySQL API support for PHP. If you are 100% sure that PHP is properly installed on your web server with MySQL support, you can safely skip all installation instructions.
Checking MySQL support availability Before trying to run any MySQL-dependent PHP script, it is necessary to ensure that PHP on web server has such API installed. Single line PHP sample below will query all the information about currently installed PHP modules and web server environment variables, and show them up in user-friendly form: <php phpinfo() >
Save this single line to phpinfo.php file within web server space, and load it through your browser. Note, that simply loading this file from local disk will not work – you’ll have to pipe it through web server. In other words, you should make it work by loading some URL, which starts with “http://”. The sample URL may look like: http://localhost/phpinfo.php If using correct URL you’ve got an empty page, or the page shows up unmodified example code, PHP is not installed to your web server, and PHP module requires installation itself. If the information page appeared correctly, seek for “mysql” on the page. There should be a separate section called “mysql” and it will look like this:
If such looking part is shown on the information page, then mysql module is enabled and is working correctly, so you can skip “Enabling…” sections of this guide. If the information page appeared, but no “mysql” section found, then MySQL API support needs to be installed to PHP and enabled.
Enabling MySQL API support for POSIX-compatible OS When using POSIX-compatible OS (e.g., Linux, FreeBSD, Solaris, etc), then MySQL-enabling option should be applied while compiling PHP from distribution.
If you’ve got to compile PHP yourself, specify --with-mysql option to configure script. After proper compiling and installation, MySQL support should be enabled. You can check it again by using phpinfo function sample (above in this guide).
Enabling MySQL API support for Microsoft Windows When running Microsoft Windows, you may run into several issues with PHP itself and MySQL. While C compiler is an essential part of most POSIX-compatible operating systems, Windows system typically doesn’t contain any compiler installed. Anyway, compiling PHP under Windows is pain, if you never did this before; so consider downloading Win32-precompiled version from PHP site. The Win32-precompiled version is available for download from official PHP site, and contains MySQL support integrated.
PHP provides a set of functions to use for accessing and manipulating data on MySQL server. The following sections will provide a step-by-step description of how to create and manage MySQL connections, work with MySQL tables, insert and remove data, etc.
Before any operations are to be made on the data, database connection should be established. Two general types of connection exist – normal connections and persistent connections. There is no commonly used term for mentioning normal (non-persistent) connections. Thus, when connection is mentioned to be “normal”, or simply not mentioned to be persistent, then it is likely non-persistent connection. Within simple PHP scripts, single-time MySQL connections are created, and closed once script execution is completed. This is good for rare connections, when PHP page isn’t requested too frequently. The basic idea of persistent connections is to keep connection open for some particular time, and if the page loads multiple times, PHP code will reclaim the same connection. For high traffic web sites, it would be reasonable to use persistent connections. However, if web site suffers from a very high traffic (million visits per day), for used PHP and MySQL versions the practice showed that performance degrades. For such web sites, I would not recommend to use persistent connections. PHP developers reported, that under really heavy load, persistent connections reclaim too much of web server resources, and thus performance degrades, as the result of continuous memory swapping. However, if you are not planning to run such heavy traffic sites (e.g., like world immigration center, or Microsoft), persistent connection will act with noticeable performance boost, comparing to normal non-persistent connections. As for scripting, persistent connection doesn’t differ from usual connection, except that different function is used for connection establishment. Trying to close persistent connection will do no effect, however it’s often useful to keep closing function calls in PHP code (e.g., for compatibility purposes).
To establish simple connection, function mysql_connect should be used. $handle = mysql_connect ( host [, username[, password ]] ) Working with persistent connection differs with only one feature – connection function name is mysql_pconnect. The function has the same parameter meanings and is used exactly in same way. $handle = mysql_pconnect ( host [, username[, password ]] ) All three parameters are of type string, and connection handle is returned once function is executed. For example, if connection to server “cassy” is desired, the line from PHP script may look like this: $link = mysql_connect ("cassy", "george", "greatpassword1105"); In the upper example, user name is “george” and the password is “greatpassword1105”. If name and password are not specified, the PHP process owner’s user ID is taken, and empty password is assumed. If host name is not specified, “localhost” will apply. You will not be able to specify user name, if sql.safe_mode option is set in php.ini file. In this case, default user will be used instead. The connection line can also specify port number to connect to. By default, port 3306 is assumed. However if MySQL server is installed on different port, specifying it explicitly will help: $link = mysql_connect ("cassy:4444", "george", "greatpassword1105"); Also, it’s possible to connect local server using named sockets: $link = mysql_connect (":/tmp/mysql.sock", "george", "greatpassword1105"); If the connection should perform on localhost with default port number, first parameter can be omitted by specifying null instead: $link = mysql_connect ( null, "george", "greatpassword1105"); In this example, null is not a variable, but a keyword, thus no dollar sign ‘$’ is put before. How to check this against errors Normally, if server data is specified properly and MySQL is accessible, such connection can always be established, however sometimes (e.g., due to server name misspelled, wrong configuration, heavy load, flaw or software bug) such connection will fail. If this happens, in most cases further PHP script execution is useless. You can terminate PHP script with diagnostic message by using die function, like this: $link = mysql_connect ( null, "george", "greatpassword1105"); if (!$link) die("Can't connect to database server"); Using some essential PHP features, the upper code can be optimized to look like this: $link = mysql_connect ( null, "george", "greatpassword1105") More information on error handling could be found under corresponding title later in this guide. Under some circumstances, connection to “localhost” will fail with this string in output: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) In this case, first parameter should explicitly specify 127.0.0.1 as host name (instead of “localhost” or null). You may also want to add port number. Not depending on whether die function is used or not, MySQL will output diagnostic messages (warnings) itself. To suppress such warnings, put @ sign in front of function name. This is typically to be done on a completed and tested PHP application, as keeping MySQL warnings is often helpful for debugging purposes. After a successful connection, $link variable will contain connection handle. This handle is to be used further in all MySQL API function calls.
After connection is established, the default database should be selected to use for SQL queries, which don’t specify database name explicitly. The function mysql_select_db is used for such purposes. mysql_select_db( database_name [, connection_handle] ) The very first parameter specifies name of database to select within connected server space; connection_handle – is the variable, resulted earlier from mysql_connect function. PHP manual describes, that connection_handle can be omitted, if last opened handle is to be used, however this can lead to confusion, when multiple connections are used in the script. So I recommend to specify connection_handle explicitly, when more than one connection is planned (e.g., if you are writing some kind of abstraction layer or PHP database engine). Function mysql_select_db returns either TRUE on successful database selection, or FALSE on error (e.g., database not found, connection handle is bad, lightning hit to the server, etc). The example below connects MySQL server, selects database, executes sample query (to delete all data from a particular table) and closes the connection: <php or die ("Unable to connect"); // Select database // Execute sample query (delete all data in customer table) // Close connection > Sometimes it is desired to use multiple queries on multiple databases in the same PHP script. Of course, it’s pretty valid to use mysql_select_db function before each query, which requires different database to operate on. But if PHP script mostly works with one database, and database switches are made only for few queries, specifying database name explicitly is the best way. The upper example needs not to select current database, if such query is used: ...
Finally, after connection is not needed anymore, mysql_close call should be used to close connection, as you probably guessed yourself from the example in previous section. mysql_close ( connection_handle ) Nothing wrong happens when trying to close persistent connections – the function will simply perform no operation, however to preserve compatibility, even for persistent connections I would recommend keeping mysql_close call anyway (what if in the future it would be desired to change to simple connections). PHP documentation mentions, that it is not necessary to use mysql_close at all, however the practice showed, that opposite to manual, unclosed orphan connections are kept in memory, and are closed only in some time (after timeout expires). This is, of course, a resource black hole for web sites under heavy load.
Obtaining information by connection handle Starting with PHP version 4.0.5, few mysql_get_xxxx_info functions were introduced. These functions will help to obtain some basic information about MySQL API and connection handles:
$text = mysql_get_client_info ( ) $text = mysql_get_server_info ( connection_handle ) $text = mysql_get_host_info ( connection_handle ) $val = mysql_get_proto_info ( connection_handle ) Function mysql_get_client_info will return a string, containing current PHP-MySQL client library version. Note, that this version number is not related to MySQL version installed on server. Either internal PHP-MySQL API library, or MySQL-provided library can be used during PHP compilation. If path to MySQL libraries is not specified during PHP compilation, PHP uses built-in MySQL client library, which typically is older than it could be otherwise. The value of mysql_get_client_info will look like this: 3.23.32 Function mysql_get_server_info needs connection_handle parameter; it queries MySQL server version, using the server connected via connection_handle. The return value will be formatted exactly in the same way, as in mysql_get_client_info function, however these two values are not related to each other, and mysql_get_server_info will typically return different value. For example, on my machine it indicates: 3.23.37 Functions mysql_get_host_info and mysql_get_proto_info are used to get more information on currently connected host and protocol. The example below demonstrates usage of mysql_get_xxxx_info functions: <html><pre> And this outputs the following data on my machine: mysql_get_client_info: 3.23.32 mysql_get_host_info: 192.168.1.2 via TCP/IP
To fetch or alter data, SQL queries are to be executed. “SELECT” queries will obviously have result, and the description of how to deal with rowsets is provided later in this guide. Less obvious is, that “DELETE”, “INSERT” and “UPDATE” queries will have result as well, however the result will contain no rowset, but different miscellaneous information, such as number of rows affected, etc. This section will explain how to execute queries and how to reclaim those “unobvious” results.
MySQL API for PHP contains function named mysql_query, which takes query string and connection handle as parameters. This function either returns rowset handle, miscellaneous info, or zero value (in case of errors). PHP itself doesn’t differ rowset handles from miscellaneous info handles, and in terms of PHP returned value is called “resource handle”; however, different functions are used to fetch data from rowsets, than to get number of affected rows from miscellaneous handle, etc. $res = mysql_query( SQL_query_string [, connection_handle] ) If connection_handle parameter is omitted, then the last connection is used. If you plan to run queries on multiple connections (e.g., copying data from one database server to another, or synchronizing two database servers), omitting connection_handle parameter can lead to confusion, so if you plan to handle two connections in the same PHP script, specify connection_handle parameters explicitly. The SQL_query_string parameter specifies SQL query string to execute. Note, that no semicolon should be put to the end of string. To execute sample SELECT query, you can put it this way: $res = mysql_query( "SELECT * FROM customer", $hd ); In the upper example, $hd is a valid connection handle. And $res – is the target variable for receiving rowset handle. In case of DELETE, INSERT or UPDATE statements usage, $res receives miscellaneous information handle. To show how to work with such handles, a slightly modified table clearing example is provided below: <php // Execute sample query (delete all data in customer table) // Display number of rows affected // Close connection > MySQL API for PHP contains function mysql_affected_rows, used to obtain number of rows affected by the query operation. Note, that this one doesn’t work for SELECT statement queries – it works for data-modifying queries only; there’s a different function to obtain number of rows attached to rowset handles (described later in this guide). $nrows = mysql_affected_rows ( result_handle ) This function receives result_handle, returned by mysql_query function; $nrows – is an integer variable assigned to affected rows count. Note, that when used with UPDATE statement queries, mysql_affected_rows can actually return different number of rows – those records, which already contained desired values would not be counted.
In previous section, we went through description for raw SQL query execution. This would be enough, when all data for query is constant, pre-formatted and already prepared for execution. However, dynamically generated query strings may cause problems, if formatted incorrectly. Suppose, we need to execute this query, where $pm is a parameter variable filled before: $res=mysql_query("SELECT * FROM customer WHERE lname='$pm' "); When $pm is equal to “Smith”, the query expands to the following: SELECT * FROM customer WHERE lname='Smith' This will execute fine. Nothing wrong happens, when this query is executed on these customers: “Jenny Stones”, “Simon Cozens”, “Dave Johns”… Finally, the list ends with “Bill O’Neill”, and PHP application stops working as expected. The query expands to: SELECT * FROM customer WHERE lname='O'Neill' In the upper case, the query will simply fail with “O’Neill”. But what if someone was accidentally (by parents) called “George O’;DROP DATABASE andrewsbase” While being fully legal, the query will expand to: SELECT * FROM customer WHERE lname='O'; For this query, the last thing you want is to drop database. This is typical example of possible security holes – such security holes may exist in any type of PHP application using SQL queries. If such security issue is once missed on some web site, potentially any data from MySQL server can be destroyed. Actually PHP doesn’t support multiple queries in one line, but anyway, while not blocking this issue, MySQL query may be expanded to return listing of some secret or private data, or to show up multiple names in a row. Obviously, to fix this query the apostrophe ' should be prefixed with a backslash. There are other special symbols, which should be prefixed as well to avoid query confusion. Function mysql_escape_string takes care of all such cases and prepares string for inserting to queries: $text = mysql_escape_string( unprepared_string ) Here $text means any available string variable, and unprepared_string – is a parameter, which contains original value (e.g. user name). So the upper example should be modified to make all alien names fetch to query correctly:
// Escape $pm string // Execute SELECT query
When “O’Neill” will apply, the query will expand to: SELECT * FROM customer WHERE lname='O\'Neill' MySQL will understand this correctly, and will execute correct query.
Similar technique can be used to prepare data for inserting and updating records.
It was mentioned earlier in this guide, that SELECT statement query returns some special kind of result – rowset handle. In this section, I will describe the common ways to pull actual data from rowset handles and how to deal with such rowset handles in some other aspects.
SELECT statement queries are executed like all other queries – with mysql_query function: $res = mysql_query("SELECT xxxx FROM yyyy WHERE zzzz”, $connection_handle); After such command execution, $res variable will contain rowset handle, to which buffered rowset is attached. By function mysql_query, the complete rowset is received from SQL server and stored in memory. Normally, rows are extracted in sequential order, as further rows may not be ready, like in case of unbuffered queries (described in next section). Function mysql_query fully buffers received data, so rows can be accessed in random order. You can seek to some particular row by using mysql_data_seek function: mysql_data_seek ( rowset_handle , row_number ) The first parameter is actually the result (e.g., $res variable) returned by mysql_query function. The second parameter means the destination row number, starting with 0. Function returns logical value. TRUE will be returned, if row was successfully found and positioned, FALSE otherwise. All mysql_fetch_xxxx functions will start fetching from positioned row, or by default (if row pointer wasn’t changed) from the beginning of rowset. Total number of rows in rowset can be obtained by mysql_num_rows function: $nrows = mysql_num_rows ( rowset_handle ) As mysql_query buffers all data received from MySQL, total number of rows can be queried simply by specifying rowset_handle, returned from earlier call to mysql_query. Function mysql_num_fields queries number of fields per row: $nfields = mysql_num_fields ( rowset_handle ) Function mysql_num_fields can be useful while retrieving information about table columns. Those two functions query the dimensions of rowset, so by multiplying $nfields by $nrows, we get the total number of fields in rowset. This can be used in data size estimation for tables of equally-typed columns.
While mysql_query waits for the query to complete and buffers the result, mysql_unbuffered_query function returns as soon as query is passed to SQL server. Sometimes, database size does not allow storing all queried results in memory (e.g., database is about 14GB), thus in these cases, for performance issues it’s preferable to use mysql _unbuffered_query function instead of mysql_query. $res = mysql_unbuffered_query ( query_string [, Unbuffered queries are especially useful when not each queried value is needed (like search engines). However, unbuffered query results cannot be scanned for number of rows, cannot be sought randomly, cannot be used for multiple queries, etc.
...
The essential way to extract data row, is to use mysql_fetch_row function: $array = mysql_fetch_row( rowset_handle ) After such execution, $array will contain data row, which can be accessed by numeric index, starting with 0. E.g., if table contains eight fields: customer_id, title, fname, lname, addressline, town, zipcode, phone exactly in this order, $array[0] is assigned to value of field customer_id, $array[1] correspondingly to fname value, and so on. If fields order is fixed, the following code can be used for assigning field values to variables in one PHP line: list($idv, $title, $fname, $lname, Once no row can be fetched (e.g., all rows were fetched before, or query returned no data), zero value is returned instead of array. The easiest way to output all data from table, is to use the following code: ... $res = mysql_query("SELECT * FROM customer", $hd) The upper example can be optimized to this: ...
Obviously, this isn’t the most flexible way to deal with data. By this, you can know field values only. Different PHP functions allow retrieving associative hash with field names instead of indexed array: $hash = mysql_fetch_assoc( rowset_handle ) In this case, fields values can be referred as $hash["customer_id"], $hash["fname"], etc. The last “optimized” example above will also work for hashes. In this case you will get output like this: Field # customer_id = 1 ... ----- If you need to use field values frequently, obviously, it’s not convenient to write $row["FIELDNAME"] each time, plus you can’t easily embed such construction to string constant. PHP provides function extract to create variables, which names are equal to hash keys. ...
So if you aren’t sure about correctness of value extracting, but still eager to embed field values to string constants, it can be done like this:
echo "Here's a value of FNAME – {$rowhash['fname']} "; Notice the single quotes used instead of double quotes surrounding field name. In addition, PHP contains mysql_fetch_array function: $foo = mysql_fetch_array( rowset_handle [, fetch_type ] ) Depending on fetch_type parameter, function can work like mysql_fetch_row, mysql_fetch_assoc, or result a merged hash as if those two were called sequentially and their results merged together; fetch_type parameter can have the following values: MYSQL_ASSOC the function will act like mysql_fetch_assoc This function isn’t much useful, since it may introduce some confusion. First, as you probably guessed yourself, when specifying MYSQL_BOTH as fetch_type, function will return each value twice in hash – one with numeric key and another with named key.
Finally, to fetch row to object variable, mysql_fetch_object function can be used: $foo = mysql_fetch_object( rowset_handle [, fetch_type ] ) The second “fetch_type” parameter has the same meaning, as in function mysql_fetch_array. After row is fetched, object properties are assigned to corresponding field values. If you specify MYSQL_ASSOC or MYSQL_BOTH as fetch_type, then later you can refer the returned field values as object properties (e.g., $foo->fname, $foo->phone, etc).
Querying information about columns in table In some cases, it’s necessary to execute queries on unknown tables (e.g., user-entered queries). For example, storing property values of complex structures sometimes will require different tables to be used with the same code. Thus it would be necessary to query information about table columns before actual data fetching. Earlier in this guide I described how to get number of rows in rowset and how to seek pointer to a particular row. Different PHP function is used to operate with column pointer: mysql_field_seek ( rowset_handle , column_offset ) The second parameter column_offset specifies default offset for column pointer. Field info fetching function can explicitly specify different field offset, but if not specified, the default pointer value will be used. To fetch information of sequential column in a rowset, function mysql_fetch_field is used. $object = mysql_fetch_field( rowset_handle [, field_offset ] ) The object value is returned. You can obtain the following object properties: $object->name the column/field name Of course, mysql_fetch_field function can’t get any particular field value – it operates on column basis, not referring any particular row. This function affects neither row pointer, nor rowset data, but only gets basic information about table used in SELECT query, not depending on its contents. For convenience, there are some PHP functions to obtain single property of a field: $tablename = mysql_field_table ( rowset_handle, field_offset) $typestring = mysql_field_type ( rowset_handle, field_offset) $fieldlength = mysql_field_len ( rowset_handle, field_offset) $sqlflags = mysql_field_flags ( rowset_handle, field_offset) All four functions receive rowset_handle and field_offset in parameters. Note, that purpose of passing field_offset to mysql_field_table function is not obvious. Normally, all fields in a row set belong to the same table, however for some complex merged rowsets it’s possible that single row will contain fields, which belong to different tables. Of course this is up to a particular PHP application, and if you don’t ever plan to use merged rowsets, you can safely pass 0 as the second parameter for mysql_field_table. The following example shows the easy way to output table structure: <html><pre> echo "Table structure:\n";
This is sample output for the upper example: Table customer has 8 field(s) and 15 record(s).
All rowsets are automatically freed upon script execution completes. However, if PHP script is intended to receive some huge data arrays, and when especially doing this in cycle, memory space will be taken and not returned back until script completion. Normally, if script contains only one call to query, freeing rowset is not necessary, however this should be done, if query resulting rowset is not necessary anymore before another query is about to start: mysql_free_result ( rowset_handle ) Notice, that first parameter is a rowset handle, not result handle. This function should be used to free rowsets, obtained by SELECT statement queries, and should NOT be used for other types of query. This is a typical example of code, where using mysql_free_result function will help, however this will need customer ZIP codes in base changed to values from 1 to 15: ... or die("Unable to perform query on ". Note, that in most cases you won’t notice the difference, however if huge rowsets are returned, freeing rowsets will help.
Type conversion of data values Because PHP always returns MySQL field values as strings, you may run into several troubles when using operations over retrieved variables. For example, bitwise operations will work not as you may expect, if two operand numbers are retrieved from MySQL database. If you are about to do some arithmetic or other type-dependent operation on fetched values, use settype() function to specify type explicitly before. For example, if $row is fetched from rowset, to specify type for field ID execute: settype($row["customer_id”], "integer"); Function settype can receive the following constants as its second parameter: "integer" PHP provides two general functions for getting status information on the last MySQL API function execution. You can either get error number with mysql_errno function, or the full message text with mysql_error. $errNr = mysql_errno() $errtext = mysql_error() Function mysql_errno() provides numerical status code of the last executed MySQL operation. If operation was successful, then zero value is returned; otherwise function will return error code, generated by MySQL. Function mysql_error() returns error message string. If actual meaning of error is not important for script functionality (e.g. script simply quits on any error occurrence), while you still want to show the error message up to user, mysql_error function will be useful. The example below shows how to deal with mysql_errno and mysql_error functions: <html><pre> Provided that connection was established successfully, the upper example will output: mysql_errno() = 1064
PHP community made an attempt to create open source code repository (like CPAN is for Perl). As the result, PEAR appeared. PEAR stands for PHP Extension and Application Repository, and represents a large collection of object oriented open source PHP classes. For now, PEAR isn’t documented well. Instead, some of information can be found on PHP official web site, and the other part – as comments in PEAR source code. To use PEAR, you must have some knowledge of object oriented programming in relation to PHP. PHP object oriented programming techniques are described here: http://www.php.net/manual/en/language.oop.php You can find more information on PEAR structure and some good tutorials at the following links: http://pear.php.net Next sections will briefly describe how to perform database connections using PEAR, how to execute queries, fetch results and handle erroneous situations.
Currently, PEAR is a part of PHP distribution. PEAR sources can be found under the directory pear, relative to PHP installation. To enable PEAR functionality, PHP include path variable should be adjusted to include PEAR directory as well. For this, open php.ini file in any text editor and seek for “include_path” variable. Normally on fresh installations this variable will contain no value, so you just simply have to put full path to PEAR there. Otherwise, if some paths are already used, you can add PEAR’s directory to the end of list. Place a colon to separate multiple path entries from each other. In some cases you will need current directory to present in path (for including local files), so add `.’ as additional path entry – this will allow inclusion of files from current script directory. To enable PEAR DB functionality for PHP, you should put the following line to the very begging of script: require_once "DB.php"; This will seek for DB.php in include path (which should include PEAR path as well), and include its contents immediately.
PEAR’s database abstraction interfaces PEAR’s DB abstraction class allows easy manipulation with databases, not depending on server type. No matter whether it would be MySQL or PostgreSQL – to port code from one database to another generally less lines of code will be changed, as opposed to full database-manipulation code reimplementation, when using traditional approach. PEAR interface also adds convenient features to work with multiple results and advanced error handling, by providing corresponding classes and objects. In basic concept, PEAR’s DB abstraction idea is much similar to Perl’s DBI module. The code flexibility is gained at a cost of performance. Typically software developer’s time is more expensive than machine time, however some software solutions may require better performance, than PEAR can provide. And if application is planned to use MySQL-server databases only, it could be reasonable to use standard MySQL API, which provides generally better performance and is typically easier to use in simple projects.
With a purpose to illustrate the basics of PEAR’s DB interface, the example is provided below, which performs a connection to database server, executes query and pulls some data from row set. Conceptually the sample code is built like other samples in this guide, thus looking at the corresponding comments, it would be easy to get understanding of how to deal with PEAR’s DB: <html><pre> // Turn on PEAR's DB functionality // Database connection parameters // Format parameters into DSN // Perform a connection // Check, whether the connection succeeded die ($hd->getMessage()); // Execute SELECT statement query // Check for query execution status die ($res->getMessage()); // Fetch rows in a cycle // Close connection >
As you see in the upper code example, functionality is much similar to MySQL API, however no MySQL functions are used directly. Notice, that connection is done using so-called DSN string, which conceptually is similar to URL line in your browser, however in this case DSN is used to connect MySQL server via database protocol (and, of course, this string will not work in your browser).
PEAR’s error handling PEAR includes advanced technique of error handling. By using PEAR’s function setErrorHandling, you can change the behavior of PEAR’s DB, when an error occurs: $hd->setErrorHandling( handling_policy ) Here $hd means PEAR’s connection handle, obtained by DB:connect function. Parameter handling_policy can take the following values: PEAR_ERROR_RETURN return an error object and continue execution (default policy) More information on PEAR’s error handling can be found at this URL: http://php.net/manual/en/class.pear-error.php
In this guide, the complete information on PHP and MySQL interaction was provided. In the very beginning, PHP basics were described and a sample code for using PHP scripting within HTML. Through the next few sections, we learned how to install and enable MySQL API support to PHP engine. This, of course, was the easiest part to understand, as the complete recommendations were given on installation support. No “installation from source code” section was provided, as it could take one more complete guide, however with given instructions it is practically impossible to install precompiled PHP in wrong way (unless someone tries to install older version). Next section block provided basic information on MySQL connections, connection types and how to avoid common mistakes, typically made at this point. Persistent connections were reviewed and described. As from both programmer’s and user’s side, persistent connections and standard connections look mostly the same, the examples for standard mysql_connect function should also be suitable for usage with mysql_pconnect instead. The next few sections covered information on executing buffered and unbuffered queries, fetching data from given rowsets and working with type conversion. This is probably the longest part, but the structure of sections is more alike an exact tutorial on driving through crowds of SELECT statements and other database stuff. The information supplied should prevent reader from doing common database security mistakes, such as storing passwords in code, executing non-escaped query strings and naming children as “George O;DROP DATABASE andrewsbase”. The “Error Handling” section supplied basics of error handling and reporting. Sequential guide part was dedicated to PEAR Database Abstraction interface. While PEAR is not widely used yet, some users may find it to be useful for portable database projects, such as web engines, etc. Finally, this chapter ended with “Summary” part, which described in briefly, that in this chapter, the complete information… …this is possibly what you have read already. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 INTERNET TV MOBILE PC / MAC EMAIL NEWS RSS |
|
|
 |
||||||||||||||||
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
|||||||||
Click on the links below to read complete Story..
New marketing is about the relationships, not the medium ..Ben Grossman