Tuesday, December 7, 2010

MySql - Get available Database, Tables, Columns and their Details

Get available database and their information: 
select * from INFORMATION_SCHEMA.SCHEMATA; Result Set Columns:
  1. CATALOG_NAME
  2. SCHEMA_NAME
  3. DEFAULT_CHARACTER_SET_NAME
  4. DEFAULT_COLLATION_NAME
  5. SQL_PATH
Note: SCHEMA_NAME column contains the database names as you need.

Get available table names from a database:
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='test_db';

Result Set Columns:
  • TABLE_NAME contains the table name
Get table column details:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_KEY, EXTRA from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test_db' and TABLE_NAME='user_profile';
If you want checkout INFORMATION_SCHEMA database inside MySql for more.

Sunday, October 31, 2010

Disabling Magic Quotes

Why to disable magic quotes?
  • Performance Because not every piece of escaped data is inserted into a database, there is a performance loss for escaping all this data. Simply calling on the escaping functions (like addslashes()) at runtime is more efficient. Although php.ini-development enables these directives by default, php.ini-production disables it. This recommendation is mainly due to performance reasons.
  • Inconvenience Because not all data needs escaping, it's often annoying to see escaped data where it shouldn't be. For example, emailing from a form, and seeing a bunch of \' within the email. To fix, this may require excessive use of stripslashes().

Disabling on php script file.

/**
 * DISABLE magic-quotes
 */
if (get_magic_quotes_gpc()) {
    function strip_array($var) {
        return is_array($var)? array_map("strip_array", $var):stripslashes($var);
    }

    $_POST = strip_array($_POST);
    $_SESSION = strip_array($_SESSION);
    $_GET = strip_array($_GET);
    $_REQUEST = strip_array($_REQUEST);
    $_COOKIE = strip_array($_COOKIE);
}
/** magic-quotes REVERSED **/

Tuesday, October 19, 2010

Creating Multiple Virtual Sites on a WAMP Server Installation

Step 1: Tell your machine to answer to different names.

By default, Windows will answer to the name "localhost" for browser requests originating on the same machine (yours). If you have WAMP Server running, you can browse to http://localhost and see your WAMP site.  We can make Windows answer to multiple names by editing a file called "hosts" located in your /windows/system32 folder.  Browse to:

"C:\windows\system32\drivers\etc"

Once there, you should see a file titled "hosts".  Open that file with a text editor like notepad.  You may have to change the open dialog to "all files" in order to see it.  You should see a line that looks like this:

127.0.0.1        localhost

That tells Windows that the name "localhost" points to the machine who's IP address is 127.0.0.1, which is a standard address for "this machine".  We can multiple entries for 127.0.0.1, and your browser will then associate all of them with your local computer. In our case, let's create one called "testwebsite.dev".  Add a line to the hosts file so it looks like this:

127.0.0.1        localhost
127.0.0.1        testwebsite.dev

You can add as many entries as you like and your browser should display your WAMP Server site for all of them.  Save your hosts file and try visiting http://testwebsite.dev in your browser.  You should see your WAMP site.
If using Windows 7, you may need to open notepad as "Run as administrator" by rightclicking on notepad. If not you may not be able to save the file once edited because of restrictions.
Note: Do not create hosts entries for external sites.  This can cause hours of frustration and needless troubleshooting.  For example, if you added this line:

# Don't do this.
127.0.0.1        google.com

You would no longer be able to access google.com, because it is now associated with your local computer.  The request never reaches the Internet. The same applies to client domains.  For this reason, stick to something identifiable as dev, like "google.dev".

Step 2: Create a Client Site Folder

We need multiple site root folders to house multiple sites. Currently WAMP Server only has one site root, which typically lives at "C:\wamp\www".  Let's create another one at "C:\wamp\testwebsite".  Navigate to "C:\wamp", and click "File -> New -> Folder".  Rename your new folder to "testwebsite". Create a test file called "index.html" in "C:\wamp\testwebsite":
<html>
     <head>
          <title>Test Website</title>
     </head>
     <body>
          Hello from Test Website!
     </body>
</html>

Step 3: Tell Apache to Serve Multiple Sites by Name

WAMP configures Apache to serve a single site that usually lives in "C:\wamp\www".  Apache can handle multiple sites if we tell it where to look.  To open your WAMP Server Apache configuration, left-click the WAMP Server icon and select "Apache -> httpd.conf".  The file should open in notepad. Look for a line like this:

Listen 80

This tells Apache to listen for browser requests on port 80. Change it to this:

Listen *:80

This tells Apache to listen to port 80 of any address. Next look for lines like this:

# Virtual hosts
# Include conf/extra/httpd-vhosts.conf

This is the config file for appache virtual host websites. Now uncomment "#Include conf/extra/httpd-vhosts.conf" like "Include conf/extra/httpd-vhosts.conf" This lets appache find the hosted root folder for the said virtual website.

Step 4: Open and edit httpd-vhosts.conf file

Open the file "httpd-vhosts.conf" file located in folder "C:\wamp\bin\apache\Apache2.2.11\conf\extra". At the end of the file copy and past (modify if required) the following peace of code:

<VirtualHost *:80>
     ServerAdmin webmaster@testwebsite.dev
     DocumentRoot "C:/wamp/www/testwebsite"
     ServerName testwebsite.dev
     ErrorLog "logs/testwebsite.dev-error.log"
     CustomLog "logs/testwebsite.dev-access.log" common
</VirtualHost>

Step 5: Restart Apache and Test

Left-Click your WAMP Server tray icon and select "Restart All Services". Browse to http://testwebsite.dev and you should see your new site.

Friday, October 15, 2010

Increase PHP Memory Allowance Using ini_set()

On rare occasion, I need to up the memory allowed for PHP. Usually it's when I deal with large files and don't necessarily care about optimizing the file reading process but simply getting the file opened. There's an easy way to increase to amount of memory allowed to PHP right in your script:
ini_set('memory_limit','15M');
The above code will increase the maximum amount of memory available to PHP to 15 MB. Again, the setting is only adjusted for the running script.

Increase PHP Script Execution Time Limit Using ini_set()

Though PHP probably isn't the most efficient way of processing the file, I'll usually use PHP because it makes coding process much faster. To prevent the script from timing out, I need to increase the execution time of the specific processing script. Here's how I do it.
ini_set('max_execution_time', 300); //300 seconds = 5 minutes
Place this at the top of your PHP script and let your script loose!

Increase PHP's File Upload Limit Using php.ini

If you need to increase the maximum upload limit, all you need to do is place or update the following code snippet in your php.ini file:
file_uploads = On upload_max_filesize = 50M //needs to be in {x}M format

Saturday, August 14, 2010

UNIX Commands - Introduction

The UNIX operating system is made up of three parts; the kernel, the shell and the programs.

The kernel
The kernel of UNIX is the hub of the operating system: it allocates time and memory to programs and handles the filestore and communications in response to system calls.

As an illustration of the way that the shell and the kernel work together, suppose a user types rm myfile (which has the effect of removing the file myfile). The shell searches the filestore for the file containing the program rm, and then requests the kernel, through system calls, to execute the program rm on myfile. When the process rm myfile has finished running, the shell then returns the UNIX prompt % to the user, indicating that it is waiting for further commands.

The shell
The shell acts as an interface between the user and the kernel. When a user logs in, the login program checks the username and password, and then starts another program called the shell. The shell is a command line interpreter (CLI). It interprets the commands the user types in and arranges for them to be carried out. The commands are themselves programs: when they terminate, the shell gives the user another prompt (% on our systems).

The adept user can customise his/her own shell, and users can use different shells on the same machine. Staff and students in the school have the tcsh shell by default.

The tcsh shell has certain features to help the user inputting commands.

Filename Completion - By typing part of the name of a command, filename or directory and pressing the [Tab] key, the tcsh shell will complete the rest of the name automatically. If the shell finds more than one name beginning with those letters you have typed, it will beep, prompting you to type a few more letters before pressing the tab key again.
The shell keeps a list of the commands you have typed in. If you need to repeat a command, use the cursor keys to scroll up and down the list or type history for a list of previous commands.
Files and processes
Everything in UNIX is either a file or a process.
A process is an executing program identified by a unique PID (process identifier).
A file is a collection of data. They are created by users using text editors, running compilers etc.

Examples of files:
  • a document (report, essay etc.)
  • the text of a program written in some high-level programming language
  • instructions comprehensible directly to the machine and incomprehensible to a casual user, for example, a collection of binary digits (an executable or binary file);
  • a directory, containing information about its contents, which may be a mixture of other directories (subdirectories) and ordinary files.
The Directory Structure
All the files are grouped together in the directory structure. The file-system is arranged in a hierarchical structure, like an inverted tree. The top of the hierarchy is traditionally called root. Check here.

Starting
To start click on the Terminal icon on your drop-down menu.