Don’t fall for paid link programs, make sure you understand what your SEO firm is doing, and check up on them.  Many practices that SEO firms will advocate will eventually cause problems due to Google eventually catching your cheating.  If you are a SEO SPAMer, it is no big deal, just change your domain, and start again.  However, if you are a reputable company, you are putting your Internet Reputation on the line.  If the SEO pitch is too good to be true, don’t believe it.  If you want to see how even big companies can get crushed, read about how JC Penny seriously harmed their Internet Reputation by not closely watching what their SEO consultant was doing.

Did you know that over 20% of all search queries are now being generated by mobile devices?  And, in many of these cases, the search results are heavily influenced by the customer’s location.  No matter what business you are in, it is even more important that your business is visible, and preferably at the top.  And in areas like the Maryland, DC, Virginia region, optimizing your site to reflect where customers could be searching from is important.   You can’t just provide your business address and hope customers can guess what areas you may service.  Make it clear where your service areas are.

And, while talking about your address, a couple of tips are in order.  First, make your address and phone number easy to read, and to copy and paste.  Do not embed your address in a graphic.  Yes, I have seen this, and if I had known the designer that did it I would have fired them.   Interestingly, the address for this company was Thomas Johnson Drive in Frederick, Maryland.  I tried to copy and paste, but no luck.  Guess how I typed it into Google?  That is right, Thomas JEFFERSON!  Mobile search fail…

If you are still interested in finding some help with your SEO projects, drop us an email.  We would be happy to help you find the proper resource.

Related articles

• What is search engine optimisation (SEO)? (marketing.yell.com)
• How to SEO your domain name (marketing.yell.com)
• Why is Google the most important search engine for businesses to rank in? (marketing.yell.com)

grep -r “texthere” .

Simple, right?  ”texthere” is the string that you are searching for, and the -r says search recursively starting from the current directory (.).  You can also specify specific filenames or types that you would like to search, such as *.txt, *.php, etc.

On some older Unix versions, you may find that Grep does not support the -r syntax.  In that case, try the following :

find ./ -type f | xargs grep “texthere”

Also som version also will not support searching for *.txt as the filename, in that case, try the following :

find /dir/to/search/ -iname *.txt -exec grep ‘texthere’ ‘{}’ \;

Little known piece of trivia, GREP stands for Get Regular Expression and Print

Related articles

• The grep Command (codingexplorer.wordpress.com)
• How to use GREP to find data conveniently and quickly (stackoverflow.com)
• Axel Beckert: grep everything (noone.org)

The core routines include zip_open() that opens the ZIP file for use.  zip_read then allows you to transverse the ZIP directory to identify the files contained within.   The zip_entry routines provide additional details about the file enclosed, and zip_entry_open() provides the door that allows you to open a specific file and then zip_entry_read allows you to extract the contained file.   On our site, the contained files were mostly text files that could be easily displayed.  Simply iterate through the file collecting the contents into a temporary variable.  If displaying on a webpage, use the handy nl2lbr() function to convert line feeds into HTML line breaks.

All in all, PHP’s built-in ZIP functions are a handy tool, a few simple lines of code will allow you to easily manipulate ZIP files and allow individual files to be viewed.  Leave a comment if you have any questions or suggestions.  You can see the final site at The Programmer’s Corner.

Code that will allow you to open a ZIP file, iterate through the directory, and retrieve the files contained within.

   $zip = zip_open('PCorner/' . $_GET['category'] . '/' . $_GET['file']);
    while ($zip_entry = zip_read($zip)) {
        $output.= '<tr class="' . $class . '">';
        $output.= '<td>';
        $file = strtoupper(basename(zip_entry_name($zip_entry)));
        $size = zip_entry_filesize($zip_entry);
        $csize = zip_entry_compressedsize($zip_entry);
        $type = zip_entry_compressionmethod($zip_entry);
    }

Code that will allow you to open a ZIP file, find a specific file, and extract it for viewing, or further manipulation.

   $zip = zip_open('PCorner/' . $_GET['category'] . '/' . $_GET['file']);
        while ($zip_entry = zip_read($zip)) {
            $file = basename(zip_entry_name($zip_entry));
            if (strtoupper($file) == strtoupper($_GET['operation'])) {
                if (!zip_entry_open($zip, $zip_entry)) {
                    die('');
                }
                while ($data = zip_entry_read($zip_entry)) {
                    $output.= nl2br($data);
                }

            }
        }

   echo $output;

Related articles

• How To Open And Extract A Zip File (smartwrite.com.au)
• How to Correct or Recover Corrupt ZIP Files (callingallgeeks.org)
• JSZip – Create .zip files with Javascript (jszip.stuartk.co.uk)

Recently while working on a project that used Extjs as the front-end to a MySQL application, we came across an interesting issue with Extjs’s JsonStore.  The JsonStore automatically creates a Jsonreader that is used to map data coming from the MySQL application to the Extjs front-end.  The JsonReader has an optional dateFormat config property which allows the format of the incoming data to be read properly using the format delivered from MySQL.  While using the JsonWriter to update records from a EditorGrid, we noticed that the format that was sent back from the JsonWriter was using Extjs’s default dateFormat (03-04-2011T00:00:00).

It was that the JsonWriter was not respecting the dateFormat defined when mapping the data using JsonReader.  There would appear to be multiple hacks that could be used to fix, such as adding a Listener before the update that would modify the datefield, however we came across this little bit of code that works wonders.  This code will over-ride the default DataWriter class to use the dateFormat property defined in the JsonStore Field mappings.

//This over-ride fixes JsonWriter not using the JsonReader dateFormat when writing

Ext.override(Ext.data.DataWriter, { toHash : function(rec) {
var map = rec.fields.map,
data = {},
raw = (this.writeAllFields === false && rec.phantom === false) ? rec.getChanges() : rec.data,
m;

Ext.iterate(raw, function(prop, value){
if((m = map[prop])){
var key = m.mapping ? m.mapping : m.name;
if (m.dateFormat && Ext.isDate(value)) {
data[key] = value.format(m.dateFormat);
} else {
data[key] = value;
}
}
});
// we don't want to write Ext auto-generated id to hash. Careful not to remove it on Models not having auto-increment pk though.
// We can tell its not auto-increment if the user defined a DataReader field for it *and* that field's value is non-empty.
// we could also do a RegExp here for the Ext.data.Record AUTO_ID prefix.
if (rec.phantom) {
if (rec.fields.containsKey(this.meta.idProperty) && Ext.isEmpty(rec.data[this.meta.idProperty])) {
delete data[this.meta.idProperty];
}
} else {
data[this.meta.idProperty] = rec.id
}
return data;
}
});

Related articles

• JSend: a specification for how JSON responses should be formatted (labs.omniti.com)
• Why You Should Build Your Next API Using JSON (apigee.com)
• Data for journalists: JSON for beginners (onlinejournalismblog.com)

Determining the number of tables that are contained in a MySQL database is very straight-forward, although it is an often asked question. The simplest way to accomplish this is using the following SQL query. In this query, you will provide the database, and the SQL will access MySQL’s internal data scheme (information_schema).

SELECT count(*) as ‘Tables’, table_schema as ‘Database’
FROM information_schema.TABLES
WHERE table_schema= ‘The Database Name’
GROUP BY table_schema

If you need to retrieve this information using PHP, use can use the following code. It creates a connection, runs the query, and returns the number of rows retrieved. This query you notice is slightly different than the one above, often there are multiple ways to get the same result!

$conn = mysql_connect(‘localhost’, ‘USERNAME’, ‘PASSWORD’, 1, 65536);
$res = mysql_query( “select table_name from information_schema.tables where table_schema=’test’”, $conn );
echo mysql_num_rows( $res );

And lastly, there is one more way that you can retrieve the number of tables in a database using PHP and the Show Tables command. In this example, you will need to be connected to a server, and have the database set to the one you are querying against.

echo “<pre>”;
$tbl_List = mysql_query(“SHOW TABLES”);
$i=0;
while($tables = mysql_tablename($tbl_List,$i))
{

echo $tables;

$i++;
}
echo “<br />Table count = $i”;
echo “</pre>”;

Related articles

• Tmobile Comet (neuphones.com)
• Bell P-39N Airacobra – Little Sir Echo – Small Fry (classic-machines.com)
• Topgun Days by Dave “Bio” Baranek – The Truth Behind Topgun and Top Gun (classic-machines.com)

What database does Facebook use is one of the most common questions asked when folks start taking about what database is the most scalable for large scale web applications.   In fact, it is usually a person who is an open source proponent, and knows very well that Facebook uses MySQL as their core database engine.  Because of this fact, this is often the single biggest reason that developers use to push to get MySQL used in their company.  I would imagine that is why it is a very popular Google query.

While Facebook uses MySQL, they do not use it as-is out of the box.  In fact, their team has submitted numerous high-performance enhancements to the MySQL core and Innodb plug-in.  Their main focus has been on adding performance counters to Innodb.  Other changes focused on the IO sub-system, including the following new features :

• innodb_io_capacity – sets the IO capacity of the server to determine rate limits for background IO
• innodb_read_io_threads, innodb_write_io_threads – set the number of background IO threads
• innodb_max_merged_io – sets the maximum number of adjacent IO requests that may be merged into a large IO request

Facebook uses MySQL as a key-value store in which data is randomly distributed across a large set of logical instances. These logical instances are spread out across physical nodes and load balancing is done at the physical node level.  Facebook has developed a partitioning scheme in which a global ID is assigned to all user data. They also have a custom archiving scheme that is based on how frequent and recent data is on a per-user basis. Most data is distributed randomly.  Amazingly, it has been rumored that Facebook has 1800 MySQL servers, but only 3 full-time DBAs.

Facebook primarily uses MySQL for structured data storage such as wall posts, user information, etc. This data is replicated between their various data centers. For blob storage (photos, video, etc.), Facebook makes use of a custom solution that involves a CDN externally and NFS internally.

It is also important to note that Facebook makes heavy use of Memcache,  a memory caching system that is used to speed up dynamic database-driven websites by caching data and objects in RAM to reduce reading time. Memcache is Facebook’s primary form of caching and greatly reduces the database load. Having a caching system allows Facebook to be as fast as it is at recalling your data. If it doesn’t have to go to the database it will just fetch your data from the cache based on your user ID.

So, while “What database does Facebook use?” seems like a simple question, you can see that they have added a variety of other systems to make it truly web scalable.  But, still feel free to use the argument, “MySQL is as good or better than Oracle or MS SQL Server, heck, even Facebook uses it, and they have 500 Million users!”.

Related articles

• Facebook Showcasing Its Open Source Database (allfacebook.com)
• Google Chrome’s new False Start “Feature” (z-car.com)
• Video: How Facebook Makes MySQL Scale (readwriteweb.com)

mysql> SELECT id, technology from Enigma;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

To sort the records based on the technology, do an ORDER BY on column Technology:

mysql> SELECT id, technology from Enigma ORDER BY Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

It seems that the server does not understand what I have asked, or I have not mastered the entire alphabet …
Let’s add the clause CSA (just in case!)

mysql> SELECT id, technology from Enigma ORDER BY ASC Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

That does not seem to be any better!  MySQL does not sort the data as I wish, am I going crazy? Should I change my RDBMS?
Don’t worry, a careful look at the table structure allows us to see things clearly.

mysql> SHOW CREATE TABLE enigma;

Table: enigma
Create Table: CREATE TABLE `Enigma` (
`Id` int (11) DEFAULT NULL,
`Technology` enum (‘PHP’, ‘Linux’, ‘MySQL’) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1

The column technology is of a type ENUM.  The Enum field is stored as an integer, and that is the index that MySQL uses for sorting.  In other words, 1 is PHP, 3 is MySQL, and 2 is Linux, so sorting does works properly, just not as expected when the Enum data field is storing character data.  So how do you get the desired result?  Simply force MySQL to use the value string and not index when sorting.   This can be done by using either the Concat() or Cast() functions, as follows:

function concat ()
mysql> SELECT id, technology from Enigma ORDER BY concat (technology);
+——+————-+
| Id | technology |
+——+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+————-+
function cast ()
mysql> SELECT id, technology from Enigma ORDER BY cast (technology as char);
+——+————-+
| id | Technology |
+—– -+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+———– – +

Want a quick and dirty way to Truncate all the tables in a MySQL DB?  You can use the following one line script.

mysqldump -ppassword YourDBName –no-data dumpfile | mysql dumpfile

What this does is uses mysqldump to output all the tables in the database “YourDBName” into a dump file.  This dump file contains drop statements, and create table statements for each table in the database.  What this effectively does is drops every table, and recreates it WITHOUT any data inside.  Be very careful, since this is a very powerful script, it has the ability to completely wipe out your database with no hope of recovery unless you have a current backup.

For additional ways to execute specific commands against all tables in a DB, look into using mk-find in Maatkit, it is an excellent tool for advanced MySQL scripting.

Related articles

• Exiting a MySQL Stored Procedure in the middle of the code (prodromus.com)
• MySQL 5.5.8 and Percona Server on Fast Flash card (Virident tachIOn) (mysqlperformanceblog.com)

The simplest way to attack this is by using a regular expression. For example:

SELECT * FROM BadlyDesignedTable WHERE AnswerColumn regexp ‘^[0-9]+’;

or
SELECT * FROM BadlyDesignedTable WHERE AnswerColumn RLIKE ‘^[0-9]+’;

Regex and RLIKE are compatible keywords that both represent regular expression matching.

The regex is described as follows :

^    - Start anchor, used to ensure the pattern matches start of the string.
[    - Start of character class.
0-9  - Any digit
]    - End of character class

Related articles

• How can I select this in SQL? (ask.metafilter.com)
• Test Regular Expressions in Your Browser (mt-soft.com.ar)

As an example, look at the following Stored Procedure :

CREATE PROCEDURE TestProc(Value INT)

ThisSP:BEGIN

IF  Value is null or Value=0 then
Select ‘Invalid Value’;
LEAVE ThisSP;
END IF;

Select * from Table twhere t.Value=Value;

END;