How to get the Next Auto Increment number in MySQL

- by admin

In order to get the next Auto Increment number in MySQL just run:
SHOW TABLE STATUS LIKE '$tablename';

The result would be in `Auto_Increment`.

Voila !

MySQL Frequently Used Commands

- by admin

Selecting a database:

mysql> USE database;

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> SHOW TABLES;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Generate the create statement of a table in MySQL:

mysql> SHOW CREATE TABLE tblname;

Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)

Inserting one row at a time:

mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)

Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:

mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)

Extended Regular Expression Matching:

mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"

Counting Rows:

mysql> SELECT COUNT(*) FROM table;

Grouping with Counting:

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')

Selecting from multiple tables:

(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using 'AS')

Currently selected database:

mysql> SELECT DATABASE();

Maximum value:

mysql> SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:

mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");

Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

Removing a column:

mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;

Backing up a database with mysqldump:

# mysqldump --opt -u username -p database > database_backup.sql
(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)

Convert a string into an URL safe address

- by admin

/**
 * Convert a string into a url safe address.
 *
 * @param string $unformatted
 * @return string
 */
public function formatURL($unformatted) {

    $url = strtolower(trim($unformatted));

    //replace accent characters, forien languages
    $search = array('À', 'Á', 'Â', 'Ã', 'Ä', 'Å', 'Æ', 'Ç', 'È', 'É', 'Ê', 'Ë', 'Ì', 'Í', 'Î', 'Ï', 'Ð', 'Ñ', 'Ò', 'Ó', 'Ô', 'Õ', 'Ö', 'Ø', 'Ù', 'Ú', 'Û', 'Ü', 'Ý', 'ß', 'à', 'á', 'â', 'ã', 'ä', 'å', 'æ', 'ç', 'è', 'é', 'ê', 'ë', 'ì', 'í', 'î', 'ï', 'ñ', 'ò', 'ó', 'ô', 'õ', 'ö', 'ø', 'ù', 'ú', 'û', 'ü', 'ý', 'ÿ', 'Ā', 'ā', 'Ă', 'ă', 'Ą', 'ą', 'Ć', 'ć', 'Ĉ', 'ĉ', 'Ċ', 'ċ', 'Č', 'č', 'Ď', 'ď', 'Đ', 'đ', 'Ē', 'ē', 'Ĕ', 'ĕ', 'Ė', 'ė', 'Ę', 'ę', 'Ě', 'ě', 'Ĝ', 'ĝ', 'Ğ', 'ğ', 'Ġ', 'ġ', 'Ģ', 'ģ', 'Ĥ', 'ĥ', 'Ħ', 'ħ', 'Ĩ', 'ĩ', 'Ī', 'ī', 'Ĭ', 'ĭ', 'Į', 'į', 'İ', 'ı', 'IJ', 'ij', 'Ĵ', 'ĵ', 'Ķ', 'ķ', 'Ĺ', 'ĺ', 'Ļ', 'ļ', 'Ľ', 'ľ', 'Ŀ', 'ŀ', 'Ł', 'ł', 'Ń', 'ń', 'Ņ', 'ņ', 'Ň', 'ň', 'ʼn', 'Ō', 'ō', 'Ŏ', 'ŏ', 'Ő', 'ő', 'Œ', 'œ', 'Ŕ', 'ŕ', 'Ŗ', 'ŗ', 'Ř', 'ř', 'Ś', 'ś', 'Ŝ', 'ŝ', 'Ş', 'ş', 'Š', 'š', 'Ţ', 'ţ', 'Ť', 'ť', 'Ŧ', 'ŧ', 'Ũ', 'ũ', 'Ū', 'ū', 'Ŭ', 'ŭ', 'Ů', 'ů', 'Ű', 'ű', 'Ų', 'ų', 'Ŵ', 'ŵ', 'Ŷ', 'ŷ', 'Ÿ', 'Ź', 'ź', 'Ż', 'ż', 'Ž', 'ž', 'ſ', 'ƒ', 'Ơ', 'ơ', 'Ư', 'ư', 'Ǎ', 'ǎ', 'Ǐ', 'ǐ', 'Ǒ', 'ǒ', 'Ǔ', 'ǔ', 'Ǖ', 'ǖ', 'Ǘ', 'ǘ', 'Ǚ', 'ǚ', 'Ǜ', 'ǜ', 'Ǻ', 'ǻ', 'Ǽ', 'ǽ', 'Ǿ', 'ǿ');
    $replace = array('A', 'A', 'A', 'A', 'A', 'A', 'AE', 'C', 'E', 'E', 'E', 'E', 'I', 'I', 'I', 'I', 'D', 'N', 'O', 'O', 'O', 'O', 'O', 'O', 'U', 'U', 'U', 'U', 'Y', 's', 'a', 'a', 'a', 'a', 'a', 'a', 'ae', 'c', 'e', 'e', 'e', 'e', 'i', 'i', 'i', 'i', 'n', 'o', 'o', 'o', 'o', 'o', 'o', 'u', 'u', 'u', 'u', 'y', 'y', 'A', 'a', 'A', 'a', 'A', 'a', 'C', 'c', 'C', 'c', 'C', 'c', 'C', 'c', 'D', 'd', 'D', 'd', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'E', 'e', 'G', 'g', 'G', 'g', 'G', 'g', 'G', 'g', 'H', 'h', 'H', 'h', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'I', 'i', 'IJ', 'ij', 'J', 'j', 'K', 'k', 'L', 'l', 'L', 'l', 'L', 'l', 'L', 'l', 'l', 'l', 'N', 'n', 'N', 'n', 'N', 'n', 'n', 'O', 'o', 'O', 'o', 'O', 'o', 'OE', 'oe', 'R', 'r', 'R', 'r', 'R', 'r', 'S', 's', 'S', 's', 'S', 's', 'S', 's', 'T', 't', 'T', 't', 'T', 't', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'W', 'w', 'Y', 'y', 'Y', 'Z', 'z', 'Z', 'z', 'Z', 'z', 's', 'f', 'O', 'o', 'U', 'u', 'A', 'a', 'I', 'i', 'O', 'o', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'U', 'u', 'A', 'a', 'AE', 'ae', 'O', 'o');
    $url = str_replace($search, $replace, $url);

    //replace common characters
    $search = array('&', '£', '$');
    $replace = array('and', 'pounds', 'dollars');
    $url= str_replace($search, $replace, $url);

    // remove - for spaces and union characters
    $find = array(' ', '&', '\r\n', '\n', '+', ',', '//');
    $url = str_replace($find, '-', $url);

    //delete and replace rest of special chars
    $find = array('/[^a-z0-9\-<>]/', '/[\-]+/', '/<[^>]*>/');
    $replace = array('', '-', '');
    $uri = preg_replace($find, $replace, $url);

    return $uri;
}

Voila !

Scanning for Viruses Using PHP and ClamAV

- by admin

Here what I found and like most.

PHP Code for directory scanning:
    // Set the allowed types for reading and upload. 
    $types = array ('jpg', 'jpeg', 'txt'); 
     
    // Start a variable. 
    $dir_files = array(); 
     
    // If it is a directory add all the files. 
    if (is_dir ($dir)) { 
        // Open the directory. 
        if ($handle = opendir($dir)) { 
            // Read the file names of all the files available. 
            while (false !== ($file = readdir($handle))) { 
                // Make sure the file is not this directory or its parent and not the .DS_Store file. 
                if ($file != "." && $file != ".." && $file != '.DS_Store') { 
                    // Get the file parts. 
                    $file_parts  = pathinfo($file); 
                    // Make sure the extension is allowed. 
                    if (in_array(strtolower ($file_parts['extension']),$types)) { 
                        // Add the file to the array. 
                        $dir_files[] = array ('original_name'=>$file, 'type'=>$file_parts['extension']); 
                    } 
                } 
            } 
            // Close the handle. 
            closedir ($handle); 
        } 
    } 

    // If any files exist in the upload directory check them for viruses. 
    if (count ($dir_files) > 0) { 
        // Get the dir and prepare it for the command line. 
        $real_path = realpath ($dir); 
        $safe_path = escapeshellarg($real_path); 
        // Set the variables for the cmd. 
        $return = -1; 
        $out =''; 
        $cmd = '/usr/local/clamXav/bin/clamscan ' . $safe_path; 
        // Execute the cmd. 
        exec ($cmd, $out, $return); 
        // If a virus is found loop through each of the files and delete the virus, write the user a message, and add them to a db table. 
        if ($return != 0) { 
            // Loop through the files. 
            foreach ($dir_files as $k=>$v) { 
                // Get the dir and prepare it for the command line. 
                $real_path = realpath ($dir . $v['original_name']); 
                $safe_path = escapeshellarg($real_path); 
                // Reset the values. 
                $return = -1; 
                $out =''; 
                $cmd = '/usr/local/clamXav/bin/clamscan ' . $safe_path; 
                // Execute the command. 
                exec ($cmd, $out, $return); 
                // If the file is clean do nothing. 
                if ($return == 0){} 
                // If the file contains a virus remove it and add a note to the db. 
                else if ($return == 1) { 
                    // Delete the file. 
                    unlink ($dir . $v['original_name']); 
                    // Unset the file from the records. 
                    unset ($dir_files[$k]); 
                    // Notify the user. 
                    $message .= "The file {$v['original_name']} contained a known virus.  It has been deleted from the server.<br />"; 
                    $message_class = 'error'; 
                    // Add the user who uploaded the file to the db and a time and date. 
                    // Query the db to record who uploaded the file, not necessary but fun info to have. 
                } 
                else { 
                    // Delete the file. 
                    unlink ($dir . $v['original_name']); 
                    // Unset the file from the records. 
                    unset ($dir_files[$k]); 
                    // Notify the user. 
                    $message .= "The file {$v['original_name']} caused an unknown error and was removed from the server.<br />"; 
                    $message_class = 'minor_error'; 
                } 
            } 
        } 
    }

What is npviewer.bin? Why it takes all computer's power?

- by admin

This is a Netscape plugin viewer.

It is used, most of the time, to play some Flash animations in Firefox. These are unsuseful commercial ads very often.

PHP Get File Extension

- by admin

Just some examples:
$filename = 'sample.gif';

// 1. The "explode/end" approach
$ext = end(explode('.', $filename));

// 2. The "strrchr" approach
$ext = substr(strrchr($filename, '.'), 1);

// 3. The "strrpos" approach
$ext = substr($filename, strrpos($filename, '.') + 1);

// 4. The "preg_replace" approach
$ext = preg_replace('/^.*\.([^.]+)$/D', '$1', $filename);

// 5. The "never use this" approach
// From: http://php.about.com/od/finishedphp1/qt/file_ext_PHP.htm
$exts = split("[/\\.]", $filename);
$n = count($exts)-1;
$ext = $exts[$n];

And the last but not the least:
$path_parts = pathinfo('/www/htdocs/inc/lib.inc.php');
echo $path_parts['dirname'], "\n";
echo $path_parts['basename'], "\n";
echo $path_parts['extension'], "\n";
echo $path_parts['filename'], "\n"; // since PHP 5.2.0

output:
/www/htdocs/inc
lib.inc.php
php
lib.inc

Office 2007 File Format MIME Types for HTTP Content Streaming

- by admin

Summary

Office 2007 introduced new file formats for common document types (Word documents, Excel spreadsheets, and PowerPoint presentations), and has standardized on new MIME types for each new format.  The following information is provided to document the new MIME types and their proper use in HTTP content streaming situations.

More Information

The following table documents the HTTP MIME types that are available when working with Office 2007 documents:

 















































































































Ext MIME Type
.doc application/msword
.dot application/msword
.docx application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotx application/vnd.openxmlformats-officedocument.wordprocessingml.template
.docm application/vnd.ms-word.document.macroEnabled.12
.dotm application/vnd.ms-word.template.macroEnabled.12
.xls application/vnd.ms-excel
.xlt application/vnd.ms-excel
.xla application/vnd.ms-excel
.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltx application/vnd.openxmlformats-officedocument.spreadsheetml.template
.xlsm application/vnd.ms-excel.sheet.macroEnabled.12
.xltm application/vnd.ms-excel.template.macroEnabled.12
.xlam application/vnd.ms-excel.addin.macroEnabled.12
.xlsb application/vnd.ms-excel.sheet.binary.macroEnabled.12
.ppt application/vnd.ms-powerpoint
.pot application/vnd.ms-powerpoint
.pps application/vnd.ms-powerpoint
.ppa application/vnd.ms-powerpoint
.pptx application/vnd.openxmlformats-officedocument.presentationml.presentation
.potx application/vnd.openxmlformats-officedocument.presentationml.template
.ppsx application/vnd.openxmlformats-officedocument.presentationml.slideshow
.ppam application/vnd.ms-powerpoint.addin.macroEnabled.12
.pptm application/vnd.ms-powerpoint.presentation.macroEnabled.12
.potm application/vnd.ms-powerpoint.presentation.macroEnabled.12
.ppsm application/vnd.ms-powerpoint.slideshow.macroEnabled.12

 

To fully support the new types, web server administrators are advised to add the MIME types for the Open XML formats to their web server metabase settings so as to add the correct MIME type header in documents saved directly on the server and sent back.  For Windows 2003 Servers running IIS 6.0, you can add the Open XML types in IIS Manager, Server Properties, MIME Types.  These new formats are included in Windows 2008 running IIS 7.0 by default.  For more details on the default MIME types for IIS, please see the following KB article:
936496: Description of the default settings for the MimeMap property and for the ScriptMaps property in IIS

Clients that install Office 2007 or the Office 2007 File Format Compatibility Pack will get client-side MIME mappings to these formats by default.

To use the MIME type in your ASP/ASP.NET code, you should use the built-in Response.ContentType property and set it to the MIME value that matches the content type you are providing.  If you fail to set the correct type, security checks by the client may prevent the content from being opened or may prompt the user with an alert that the file is not in the correct format.

PHP: explode() or split()?

- by admin

Actually explode() isn’t the same as split().

The biggest difference is that explode() takes in parameters a delimiter to split by, while split() takes a regular expression. This means that explode() runs faster. And even more, the PHP documentation says that preg_split() is faster than split(), so really there isn’t much of a reason to use split() at all.

split() is not an alias of explode() while join() is an alias of implode().

And finally:

split() function has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.

CSS Word-Wrap

- by admin

This property specifies whether the current rendered line should break if the content exceeds the boundary of the specified rendering box for an element (this is similar in some ways to the ‘clip’ and ‘overflow’ properties in intent.) This property should only apply if the element has a visual rendering, is an inline element with explicit height/width, is absolutely positioned and/or is a block element.

 

Examples








div { word-wrap: break-word } 

<div style=”word-wrap: break-word”>Here is some content for the div element</div>

 

Possible Values

















Value Description
normal Content will exceed the boundaries of the specified rendering box.
break-word Content will wrap to the next line when necessary, and a word-break will also occur if needed.

jsFiddle

- by admin

JsFiddle is a playground for web developers, a tool which may be used in many ways. One can use it as an online editor for snippets build from HTML, CSS and JavaScript. The code can then be shared with others, embedded on a blog, etc. Using this approach, JavaScript developers can very easily isolate bugs.

« Newer posts

Older posts »