There are times when you need to store a file (such as one that you sell for profit) outside of the document root of your domain and let the buyers download it via a PHP script so as to hide the real path, web address or URL to that file. Use of this approach enables you to:

  1. Check for permissions first before rendering the file download thus protecting it from being downloaded by unprivileged visitors.
  2. Store the file outside of the web document directory of that domain – a good practice in web security in protecting sensitive and important data.
  3. Count the number of downloads and collect other useful download statistics.

Now the actual tip. Given that you have put the file to be downloaded via the PHP script in place at /home/someuser/products/data.tar.gz, write a PHP file with the following content in it and put it in the web document directory where your site visitors can access:

$path = '/home/someuser/products/data.tar.gz'; // the file made available for download via this PHP file
$mm_type="application/octet-stream"; // modify accordingly to the file type of $path, but in most cases no need to do so

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public");
header("Content-Description: File Transfer");
header("Content-Type: " . $mm_type);
header("Content-Length: " .(string)(filesize($path)) );
header('Content-Disposition: attachment; filename="'.basename($path).'"');
header("Content-Transfer-Encoding: binary\n");

readfile($path); // outputs the content of the file

exit();

Now your site visitors can and can only download the protected file via the PHP script.

{ Comments on this entry are closed }

Well, MySQL doesn’t actually have anything called Foreign Key, it’s just my way of saying a alien column from another table.

Let’s say 2 tables are related, 1 is books and the other is subjects. Any book in book table falls into one of the subjects, thus having a subject column referencing the ID of one of the subjects in subject table.

You need to know the number of books from each of the subjects, namely, 243 books in subject Education, 77 books in Science, etc.

Now how do we do this? Simple. Just perform the following SQL query:

SELECT subject.id, COUNT(book.id) FROM subject, book GROUP BY book.subject HAVING book.subject = subject.id

{ Comments on this entry are closed }

As MySQL doesn’t have inherent support for updating more than one rows or records with a single update query as it does for insert query, in a situation which needs us to perform updating to tens of thousands or even millions of records, one update query for each row seems to be too much.

Reducing the number of SQL database queries is the top tip for optimizing SQL applications.

So, is there any other way around with just a few MySQL queries that  equals to millions of single row update queries?

Yes, but you need a temporary table.

Step 1: Create a temporary table

This table should have 2 columns: 1) an ID column that references the original record’s primary key in the original table, 2) the column containing the new value to be updated with.

Creating this table, you can use insert queries as it conveniently inserts more than one rows at a time (with a single query) – actually, as many rows as you need, like 1000 a time.

Step 2: Transfer the new values from the temporary table to the intended table

Use the query below:

UPDATE original_table, temp_table SET original_table.update_column = temp_table.update_column WHERE original_table.id = temp_table.original_id

To transfer all the new values you have in the temporary table to the original table. After this, you have successfully updated the original table with much much less than a million queries, probably just a hundred or so.

UPDATE =============================================

So here’s perhaps a better idea in most of the cases, use the CASE WHEN ELSE END syntax of MySQL to update multiple rows in the options table of your web application:

UPDATE `options` SET `value` = CASE `name`
WHEN 'site_name' THEN 'My Blog'
WHEN 'site_url' THEN 'http://www.example.com'
WHEN 'site_email' THEN 'sam@example.com'
ELSE `value`
END

Make sure you include the ELSE `value` because if you don’t, the other rows not mentioned here will be updated to have empty values.

Update multiple records in a table by `ID` =============================================

Below is an example of updating more than one rows in a table by the id field. The field_name field is updated to the corresponding value: value_1, value_2, or value_3 if the id equals to 1, 2, or 3:

UPDATE `table_name` SET `field_name` = CASE `id`
WHEN '1' THEN 'value_1'
WHEN '2' THEN 'value_2'
WHEN '3' THEN 'value_3'
ELSE `field_name`
END

Yet another way =============================================

There’s also another way of doing this: http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html

Or: INSERT … ON DUPLICATE KEY UPDATE

{ Comments on this entry are closed }

To find / identify a part of an original string and replace it with another string in a SQL query with MySQL, use REPLACE(), the string replace function.

For example, you may want to replace all spaces in one of the varchar or text fields to a single dash:

UPDATE `table` SET `old_field` = REPLACE(`old_field`, ' ', '-')

Or, you create a new column / field out of the old one and:

UPDATE `table` SET `new_field` = REPLACE(`old_field`, ' ', '-')

{ Comments on this entry are closed }

One of the principles of data integrity is to reduce data redundancy as much as possible and wipe out any duplicate database table entries. Another reason for this is that you need to add unique indexing to one or a group of table columns and you need the one column or combination of columns to be unique through out all the rows of the table.

There are basically 2 ways to achieve that:

  1. Identifying duplicate entries, leaving just one of them and deleting the rest.
  2. Identifying only unique table rows and collect them into a new table.

I’ll just go ahead with the 2nd approach.

There are in turn 2 ways to accomplish this task, namely SELECT DISTINCT … FROM … and SELECT … FROM GROUP BY

SELECT DISTINCT … FROM …

Rather intuitively, this SQL query selects all distinct combination of columns from a table and leaving out duplicate ones:

CREATE TABLE new_orders SELECT DISTINCT receipt, quantity, price FROM orders

One thing to note is that you have to leave out the original primary key such as ID so that all rows are only compared for uniqueness on necessary data fields. It is after this creation query is successfully completed when you add a primary key indexing column to the new_orders table.

SELECT … FROM GROUP BY …

GROUP BY clause is usually used for statistical functions of MySQL such as COUNT() or AVG() to only take a distinguished group of columns into calculation. In this situation, however, it comes handy that it inherently excludes multiple table records with the same value in the specified column and includes just one of them in the returned results.

For example, if you need to include only those records from the old table orders that’s with distinct values in column receipt:

CREATE TABLE new_orders SELECT * FROM orders GROUP BY receipt

Now all the records in the new table new_orders are with distinctly unique receipt values.

Depending on whether the original records are referenced by other tables via ID, you can regenerate the ID or leave them in the new table new_orders.

{ Comments on this entry are closed }

I joined Dynadot at the commencement of .tel domains and tried my luck to get some domainers to register a domain with them.

Maybe it’s because they just don’t care about expanding via affiliates, the system might be a little buggy or not so affiliate friendly in that once the referred friend signed up an account with them, even if they have clicked through your affiliate link to register the domains at Dynadot, the newly registered domain and its commission will not be credited to you at all.

Therefore, make sure you send your friend or site visitors the affiliate link before they register the account with Dynadot. So, basically, they should register their account through your affiliate link. The other way won’t work for sure, but still I’m not sure if it works in this way.

Feels a little fishy with Dynadot domain registration affiliate program and totally not worth the while.

{ Comments on this entry are closed }

Amazon Affiliate system does a great job presenting you a variety of ways to make associate affiliate links so that you can take credit of their products that you have referred to others. Today I messed around a little bit with their generators and finally come to a simple solution to generate affiliate links dynamically and simply with server side programming.

The simplest way to link to an Amazon product and take the referral credit is to use this link:

https://www.amazon.com/dp/0470633956?tag=maawe-20

Wherein 0029724600 is the ASIN (equally the 10-digit ISBN) of the product (books!) and maawe-20 is my amazon affiliate tracking ID.

Or this:

https://www.amazon.com/gp/product/0470633956?tag=maawe-20

These would come quite handy when you build a book site with thousands of theme books, possibly on the same subject, and, you have a database of the books that contain the exact ISBN number for each of the book records. Now that you know amazon affiliate links can be built this way, it’s nothing more than a breeze to just insert the 10-digit ISBN number as ASIN into the URL template with your own affiliate ID.

{ Comments on this entry are closed }

1 tip about ALTER TABLE xxx ADD INDEX yyy

by Yang Yang on May 14, 2009

Proper indexing can work wonders for large databases in query efficiency, it’s a compromise of disk storage in exchange for sorting speed. For databases with millions of records, indexing takes considerable amount of disk storage, and, AGONIZING lengths of time to create.

I recently compiled a database with one table mytable consisting of over 4 million records, as apparently I want some columns to be identifiable for each of the rows, I need indexing one or 2 of them to do that.

ALTER TABLE mytable ADD INDEX category

Wherein category is one of the columns by which every record in mytable is referenced to that of another table.

You know how long this short query took me?

4 hours. Much longer than the time it took me to do a unique indexing just before it, on the same table.

So there you go, a general indexing takes much much longer time to finish than unique indexing and primary key indexing.

But that’s not all of the tip today.

The tip is that, before you are inserting all the records one by one into that table which would result in a super huge set of rows, configure that particular column to be generally indexed.

This is no better solution than indexing it all after the table is fully populated though, in that with preexisting table indexes, it takes a bit longer to insert every record as MySQL has to write down redundant information every time it inserts a new row into the table.

But in terms of programmer experience, it’s much better than waiting 4 hours hoping for the indexing to succeed and fearing it would not. I’d rather spread the extra required time across each insertion of the records.

{ Comments on this entry are closed }

Largest U.S. Bankruptcies, 1980–Present

by Yang Yang on May 14, 2009

Just imagine, that much amounts of money just vaporized into thin air at a cause of nothing.

Company Bankruptcy date Total assets pre-bankruptcy (in millions)
Lehman Brothers Holdings, Inc. 9/15/2008 $691,063
Worldcom, Inc.1 7/21/2002 $103,914
Enron Corp.2 12/2/2001 $65,503
Conesco, Inc. 12/17/2002 $61,392
Pacific Gas and Electric Co. 4/06/2001 $36,152
Texaco, Inc. 4/12/1987 $34,940
Financial Corp. of America 9/9/1988 $33,864
Refco Inc. 10/17/2005 $33,333
IndyMac Bancorp, Inc. 7/31/2008 $32,734
Global Crossing Ltd. 1/28/2002 $30,185
Bank of New England Corp. 1/7/1991 $29,773
Calpine Corp. 12/20/2005 $27,216
New Century Financial Corp. 4/2/2007 $26,147
UAL Corp. 12/9/2002 $25,197
Delta Air Lines, Inc. 9/14/2005 $21,801
Adelphia Communications 6/25/2002 $21,499
MCorp. 3/31/1989 $20,228
Mirant Corp. 7/14/2003 $19,415
American Home Mortgage Investment Corp. 8/6/2007 $18,829
NTL, Inc. 5/8/2002 $16,834

1. Worldcom, Inc. assets taken from the audited annual report dated 12/31/2001.

2. The Enron assets were taken from the tax documents filed on 11/19/2001. The company has announced that the financials were under review at the time of filing for Chapter 11.

{ Comments on this entry are closed }

Google AdWords mistake (a bug?)

by Yang Yang on May 13, 2009

Naturally, you’d never expect an AdSense ad of a site to appear on itself. But this I just encountered, indicates an inherent *bug* of AdWords.

adwords bug

Or is it? But I’ll never understand otherwise. What’s the point? Maybe there’s an option for the AdWords advertiser to opt out of his advertisement listing being published on his very own site with AdSense on, but can’t Google just do it automatically. My other guess is that for a very large community site such as a popular forum, there are people (who are members of the community) who wants to advertise to the very audience (mostly members) of the site, sets up a campaign via AdWords targeting them and puts the ad exclusively on the site which is also targeted a part of it.

Anyway, I believe Google should automatically turn this off by default and let the advertiser himself to explicitly turn it on.

{ Comments on this entry are closed }