Query Caching in mySql

In some installations of MySQL, the query cache is disabled by default so you will have to do a little configuration to set things up. The way to check is to log into MySQL and issue the following command:
mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
While the query_cache_type variable is set to ON, the query_cache_size variable is set to zero, and this effectively disables the query cache. Setting the query_cache_type variable to ON (caches all SELECT queries except for those with the SQL_NO_CACHE hint) or DEMAND (only caches SELECT queries with the SQL_CACHE hint) along with a query_cache_size variable something greater than zero enables the query cache.
At the moment, however, the query cache is disabled so let's issue a particular query that computes the top five commissioned brokers at an investment management company a couple of times and see what happens:

mysql> SELECT A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME,
-> SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
->FROM BROKER A,
-> CLIENT_TRANSACTION B
->WHERE A.BROKER_ID = B.BROKER_ID
->GROUP BY A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME
->ORDER BY 4 DESC
->LIMIT 5;
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)

< execute query again >

+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)
Each result set is returned fairly quickly at .11 seconds, which isn't too bad. However, now let's enable the query cache by setting it to 50M (MySQL allows the dynamic setting of many variables without starting/stopping MySQL) and then see what happens when we execute the same query twice again:

mysql> set global query_cache_size=50000000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'query%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 49999872 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+

mysql> SELECT A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME,
-> SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
->FROM BROKER A,
-> CLIENT_TRANSACTION B
->WHERE A.BROKER_ID = B.BROKER_ID
->GROUP BY A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME
->ORDER BY 4 DESC
->LIMIT 5;
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)

< execute query again >

+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.00 sec)

mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 49988976 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+----------+

The first query once again takes .11 seconds to execute, however the second identical query takes literally no computed time at all because it is serviced from the query cache. Checking MySQL's status counters that relate to the query cache confirm this, with the Qcache_inserts counter showing a value of 1 (indicating that the first query was loaded into the cache) and the Qcache_hits counter showing a value of 1 indicating one hit/match on an issued query/cached query combination.

The query cache works with raw SQL queries as demonstrated above and also works for queries issued within MySQL stored procedures:

mysql> delimiter //
mysql> create procedure test_query_cache()
-> begin
-> SELECT A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME,
-> SUM(BROKER_COMMISSION) TOTAL_COMMISSIONS
-> FROM BROKER A,
-> CLIENT_TRANSACTION B
-> WHERE A.BROKER_ID = B.BROKER_ID
-> GROUP BY A.BROKER_ID,
-> A.BROKER_FIRST_NAME,
-> A.BROKER_LAST_NAME
-> ORDER BY 4 DESC
-> LIMIT 5;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> flush status;
mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)
mysql> call test_query_cache();
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.11 sec)

mysql> call test_query_cache();
+-----------+-------------------+------------------+-------------------+
| BROKER_ID | BROKER_FIRST_NAME | BROKER_LAST_NAME | TOTAL_COMMISSIONS |
+-----------+-------------------+------------------+-------------------+
| 20 | STEVE | BOYCE | 3864173.64 |
| 1 | JONATHAN | MORTON | 1584621.39 |
| 13 | JIM | SANDERS | 1369157.73 |
| 4 | DAVE | TUCKER | 1214111.75 |
| 14 | DENISE | SCHWARTZ | 1041040.98 |
+-----------+-------------------+------------------+-------------------+
5 rows in set (0.00 sec)

mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 49988976 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+----------+

We first create a stored procedure that issues the same broker commission query we originally used to test the query cache, flush the MySQL status counters to reset the query cache statistics to zero, reset the query cache to remove all queries from the cache, then call the procedure twice to show that MySQL caches the query issued from within the procedure. Note that queries called from views, new in 5.0, work as well. However, SQL statements that utilize input parms from procedures will not make use of the query cache.
Managing the Query Cache

There are times when you may have to tweak the MySQL query cache to ensure optimal performance, so let's review some of the more important query cache-related status counters and configuration variables. To begin, the status counter Qcache_free_blocks indicates the contiguous nature of the memory assigned to the cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement. Note that this command does not remove queries from the cache, but coalesces memory free space chunks.

The Qcache_free_memory counter provides insight into the cache's free memory. Low amounts observed vs. total allocated for the cache may indicate an undersized cache, which can be remedied by altering the global variable query_cache_size.

Qcache_hits and Qcache_inserts shows the number of times a query was serviced from the cache and how many queries have been inserted into the cache. Low ratios of hits to inserts indicate little query reuse or a too-low setting of the query_cache_limit, which serves to govern the RAM devoted to each individual query cache entry. Large query result sets will require larger settings of this variable.

Another indicator of poor query reuse is an increasing Qcache_lowmem_prunes value. This indicates how often MySQL had to remove queries from the cache to make use for incoming statements. Other reasons for an increasing number of Qcache_lowmem_prunes are an undersized cache, which can't hold the needed amount of SQL statements and result sets, and memory fragmentation in the cache which may be alleviated by issuing a FLUSH QUERY CACHE statement. You can remove all queries from the cache with the RESET QUERY CACHE command.

The Qcache_not_cached counter provides insight into the number of statements executed against MySQL that were not cacheable, due to either being a non-SELECT statement or being explicitly barred from entry with a SQL_NO_CACHE hint.

Other server variables that you can use to tweak the query cache are:

* query_alloc_block_size - the allocation size of the RAM blocks that are allocated for objects in cache.
* query_cache_min_res_unit - the minimum size for blocks allocated by the cache.
* query_cache_wlock_invalidate - causes the query cache to invalidate any query in the cache if an object it uses has a write lock executed against it.
* query_prealloc_size - the size of the persistent buffer used by the cache for parsing and execution. Complex queries necessitate larger settings.

Finally, in addition to setting query cache variables that globally govern its use, note that the query cache can be individually managed at the client level. For example, a client can turn off the query cache for their own queries by issuing the statement:

mysql> set session query_cache_type=0;

Prerequisites and Notes for MySQL Query Cache Use

Of course, there are prerequisites and limitations regarding MySQL query cache usage, with the most important being:

* Only identical queries may be serviced from the cache. This includes spacing, text case, etc.
* Any modification (DML, etc.) to a table used by a query in the cache causes the query to be invalidated and removed from the cache.
* Many functions, such as CURRENT_DATE, NOW, RAND and others, negate the use of the cache.
* No query that uses bind variables can be reused.
* No query that makes use of user defined functions can be cached.

For a complete up to date list of query cache limitations, see the MySQL manual.
Conclusion

The MySQL query cache is a unique caching strategy that is currently not utilized by other database engines, and one that can greatly enhance the performance of most any system that experiences high degrees of read activity. Because both physical and logical I/O activity is all but eliminated by the query cache, even systems that must bear the brunt of inefficient SQL statements can many times perform faster than those on other database platforms.

Convert PDF in to a PNG image thumbnail

You can do this using ImageMagik

convert input.pdf output.png

Or if you have PHP5 and the ImageMagik library...

$image = new Imagick('input.pdf[pagenumber]');
$image->setResolution( 300, 300 );
$image->setImageFormat( "png" );
$image->writeImage('output.png');

get dpi of image in php

Hi to get the dpi of any image you can use the following code

php opening tag

function get_dpi($filename){

// open the file and read first 20 bytes.

$a = fopen($filename,’r');

$string = fread($a,20);

fclose($a);



// get the value of byte 14th up to 18th

$data = bin2hex(substr($string,14,4));

$x = substr($data,0,4);

$y = substr($data,4,4);

return array(hexdec($x),hexdec($y));

}

// sample, output the result:

print_r(get_dpi(‘demo_files/abc.png’));

php close tag

PHP website performance tricks

Hi All,
The following are few points that I just wanted to share with you all. These points are the basic that should be kept in mind and will increase site performance. HAPPY CODING

1. Don't use .htaccess
2. Lock all ports down
3. Stop ping
4. Cut Apache modules down to bare minimum required for security
5. Do not install all PHP modules
6. Benchmark code, workaround narrow code, cache everything.
7. If a ... COUNT(*) ... GROUP BY ... is killing you, just don't do it! Add some app layer logic to increment some counter in some PRIMARY KEY table instead and have instant access to your stats.
8. Use shared folders in PHP instead of putting files outside web root
9. Enable APC
10. Enable Memcached
11. Use Redis or something for sessions
12. Careful programming, do not use slow or insecure functions and do not use header variables
13. Do not connect to your db directly in your code
14. Be careful with long standing selects and updating in MyISAM in MySQL. Table locking can occur causing all other selects to queue and pile up until eventually you have server downtime.
15. Normalize your DB up to 3NF
16. how about stopping hot linking, caching, protecting files from being leeched, stopping viewing of directories.
17. If you have to use .htaccess it directly into Apache config
18. Use CDN
19. Use jmeter. It's a graphical server performance testing tool, for both static and dynamic resources (files or CGI, Servlets, Perl scripts).

Zoom on image in jquery

You can find nice collections of zoom on image in jquery in the given below url

http://www.professorcloud.com/mainsite/cloud-zoom.htm

http://www.tripwiremagazine.com/2010/02/15-jquery-plugins-to-create-stunning-image-zoom-effects.html

http://www.denbagus.net/jquery-image-zoom-and-tutorial/

Thanks

jqzoom conflicts with jquery

Hi All,

I used jqzoom in one of my sites and found the after using jqzoom the binding facility of jquery was not getting executed. So I tried cloud zoom and its working fine. So if anyone trying to use jqzoom keep in mind that it conflicts with jquery.

And for cloud zoom you can find this in the blow ur.

http://www.professorcloud.com/mainsite/cloud-zoom.htm

Thanks

Jawed

Change image color to sepia,grayscale,b/w

You can change the image color to sepia or b/w or grayscale with the use of GD library and PHP. Here is the sample example.

1.Change Color to Greyscale

//to black and white
if(!file_exists(‘dw-bw.png’)) {
$img = imagecreatefrompng(‘dw-manipulate-me.png’);
imagefilter($img,IMG_FILTER_GRAYSCALE);
imagepng($img,’db-bw.png’);
imagedestroy($img);
}

2. Change Color to B/W

//to negative
if(!file_exists(‘dw-negative.png’)) {
$img = imagecreatefrompng(‘dw-manipulate-me.png’);
imagefilter($img,IMG_FILTER_NEGATE);
imagepng($img,’db-negative.png’);
imagedestroy($img);
}

3. Change Color to Sepia

//to black and white, then sepia
if(!file_exists(‘dw-sepia.png’)) {
$img = imagecreatefrompng(‘dw-manipulate-me.png’);
imagefilter($img,IMG_FILTER_GRAYSCALE);
imagefilter($img,IMG_FILTER_COLORIZE,100,50,0);
imagepng($img,’db-sepia.png’);
imagedestroy($img);
}

Thanks.

count files in a folder/directory

Hi all,

We can count files in a folder using the following function.
It return the no of files in the directory provided as input argument

function count_files ($dir) {
$count = count(glob($_SERVER['DOCUMENT_ROOT'].$dir . "*")) ;
return $count;
}

thanks
Jawed

generate excel sheet in php

// ----- begin of function library -----
// Excel begin of file header
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
// Excel end of file footer
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
// Function to write a Number (double) into Row, Col
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
// Function to write a label (text) into Row, Col
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
// ----- end of function library -----
?>

//
// To display the contents directly in a MIME compatible browser
// add the following lines on TOP of your PHP file:

header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
header ('Content-type: application/x-msexcel');
header ("Content-Disposition: attachment; filename=EmplList.xls" );
header ("Content-Description: PHP/INTERBASE Generated Data" );
//
// the next lines demonstrate the generation of the Excel stream
//
xlsBOF(); // begin Excel stream
xlsWriteLabel(0,0,"This is a label"); // write a label in A1, use for dates too
xlsWriteNumber(0,1,9999); // write a number B1
xlsEOF(); // close the stream
?>

merging a image with a watermark image

header('content-type: image/jpeg');

$watermark = imagecreatefrompng('watermark.png');
$watermark_width = imagesx($watermark);
$watermark_height = imagesy($watermark);
$image = imagecreatetruecolor($watermark_width, $watermark_height);
$image = imagecreatefromjpeg($_GET['src']);
$size = getimagesize($_GET['src']);
$dest_x = $size[0] - $watermark_width - 5;
$dest_y = $size[1] - $watermark_height - 5;
imagecopymerge($image, $watermark, $dest_x, $dest_y, 0, 0, $watermark_width, $watermark_height, 100);
imagejpeg($image);
imagedestroy($image);
imagedestroy($watermark);

?>

userfull FFMPEG commands

Getting infos from a video file

ffmpeg -i video.avi

Turn X images to a video sequence

ffmpeg -f image2 -i image%d.jpg video.mpg

This command will transform all the images from the current directory (named image1.jpg, image2.jpg, etc…) to a video file named video.mpg.
Turn a video to X images

ffmpeg -i video.mpg image%d.jpg

This command will generate the files named image1.jpg, image2.jpg, …

The following image formats are also availables : PGM, PPM, PAM, PGMYUV, JPEG, GIF, PNG, TIFF, SGI.
Encode a video sequence for the iPpod/iPhone

ffmpeg -i source_video.avi input -acodec aac -ab 128kb -vcodec mpeg4 -b 1200kb -mbd 2 -flags +4mv+trell -aic 2 -cmp 2 -subcmp 2 -s 320x180 -title X final_video.mp4

Explanations :

* Source : source_video.avi
* Audio codec : aac
* Audio bitrate : 128kb/s
* Video codec : mpeg4
* Video bitrate : 1200kb/s
* Video size : 320px par 180px
* Generated video : final_video.mp4

Encode video for the PSP

ffmpeg -i source_video.avi -b 300 -s 320x240 -vcodec xvid -ab 32 -ar 24000 -acodec aac final_video.mp4

Explanations :

* Source : source_video.avi
* Audio codec : aac
* Audio bitrate : 32kb/s
* Video codec : xvid
* Video bitrate : 1200kb/s
* Video size : 320px par 180px
* Generated video : final_video.mp4

Extracting sound from a video, and save it as Mp3

ffmpeg -i source_video.avi -vn -ar 44100 -ac 2 -ab 192 -f mp3 sound.mp3

Explanations :

* Source video : source_video.avi
* Audio bitrate : 192kb/s
* output format : mp3
* Generated sound : sound.mp3

Convert a wav file to Mp3

ffmpeg -i son_origine.avi -vn -ar 44100 -ac 2 -ab 192 -f mp3 son_final.mp3

Convert .avi video to .mpg

ffmpeg -i video_origine.avi video_finale.mpg

Convert .mpg to .avi

ffmpeg -i video_origine.mpg video_finale.avi

Convert .avi to animated gif(uncompressed)

ffmpeg -i video_origine.avi gif_anime.gif

Mix a video with a sound file

ffmpeg -i son.wav -i video_origine.avi video_finale.mpg

Convert .avi to .flv

ffmpeg -i video_origine.avi -ab 56 -ar 44100 -b 200 -r 15 -s 320x240 -f flv video_finale.flv

Convert .avi to dv

ffmpeg -i video_origine.avi -s pal -r pal -aspect 4:3 -ar 48000 -ac 2 video_finale.dv

Or:

ffmpeg -i video_origine.avi -target pal-dv video_finale.dv

Convert .avi to mpeg for dvd players

ffmpeg -i source_video.avi -target pal-dvd -ps 2000000000 -aspect 16:9 finale_video.mpeg

Explanations :

* target pal-dvd : Output format
* ps 2000000000 maximum size for the output file, in bits (here, 2 Gb)
* aspect 16:9 : Widescreen

Compress .avi to divx

ffmpeg -i video_origine.avi -s 320x240 -vcodec msmpeg4v2 video_finale.avi

Compress Ogg Theora to Mpeg dvd

ffmpeg -i film_sortie_cinelerra.ogm -s 720x576 -vcodec mpeg2video -acodec mp3 film_terminée.mpg

Compress .avi to SVCD mpeg2

NTSC format:

ffmpeg -i video_origine.avi -target ntsc-svcd video_finale.mpg

PAL format:

ffmpeg -i video_origine.avi -target pal-svcd video_finale.mpg

Compress .avi to VCD mpeg2

NTSC format:

ffmpeg -i video_origine.avi -target ntsc-vcd video_finale.mpg

PAL format:

ffmpeg -i video_origine.avi -target pal-vcd video_finale.mpg

Multi-pass encoding with ffmpeg

ffmpeg -i fichierentree -pass 2 -passlogfile ffmpeg2pass fichiersortie-2

Nice jquery tooltip

This link contain nice jquery tool tips and even callout tooltip based on jquery

http://graphicalerts.com/best-jquery-tooltip-plugins-demos-tutorials-examples/

Thanks
Jawed

Finding Rows with No Match in Another Table

The preceding sections focused on finding matches between two tables. But the answers to some questions require determining which records do not have a match (or, stated another way, which records have values that are missing from the other table). For example, you might want to know which artists in the artist table you don't yet have any paintings by. The same kind of question occurs in other contexts, such as:

*

You're working in sales. You have a list of potential customers, and another list of people who have placed orders. To focus your efforts on people who are not yet actual customers, you want to find people in the first list that are not in the second.
*

You have one list of baseball players, another list of players who have hit home runs, and you want to know which players in the first list have not hit a home run. The answer is determined by finding those players in the first list who are not in the second.

For these types of questions, you need to use a LEFT JOIN.

To see why, let's determine which artists in the artist table are missing from the painting table. At present, the tables are small, so it's easy to examine them visually and determine that you have no paintings by Monet and Picasso (there are no painting records with an a_id value of 2 or 4):

mysql> SELECT * FROM artist ORDER BY a_id;
+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 3 | Van Gogh |
| 4 | Picasso |
| 5 | Renoir |
+------+----------+
mysql> SELECT * FROM painting ORDER BY a_id, p_id;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | 5 | The Rocks | IA | 33 |
| 5 | 6 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+

But as you acquire more paintings and the tables get larger, it won't be so easy to eyeball them and answer the question by inspection. Can you answer the question using SQL? Sure, although first attempts at solving the problem generally look something like the following query, using a WHERE clause that looks for mismatches between the two tables:

mysql> SELECT * FROM artist, painting WHERE artist.a_id != painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Picasso | 1 | 1 | The Last Supper | IN | 34 |
| 5 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | Van Gogh | 1 | 2 | The Mona Lisa | MI | 87 |
| 4 | Picasso | 1 | 2 | The Mona Lisa | MI | 87 |
| 5 | Renoir | 1 | 2 | The Mona Lisa | MI | 87 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 4 | Picasso | 3 | 3 | Starry Night | KY | 48 |
| 5 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Picasso | 3 | 4 | The Potato Eaters | KY | 67 |
| 5 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
| 1 | Da Vinci | 3 | 5 | The Rocks | IA | 33 |
| 2 | Monet | 3 | 5 | The Rocks | IA | 33 |
| 4 | Picasso | 3 | 5 | The Rocks | IA | 33 |
| 5 | Renoir | 3 | 5 | The Rocks | IA | 33 |
| 1 | Da Vinci | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 2 | Monet | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 4 | Picasso | 5 | 6 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+

That's obviously not the correct result! The query produces a list of all combinations of values from the two rows where the values aren't the same, but what you really want is a list of values in artist that aren't present at all in painting. The trouble here is that a regular join can only produce combinations from values that are present in the tables. It can't tell you anything about values that are missing.

When faced with the problem of finding values in one table that have no match in (or that are missing from) another table, you should get in the habit of thinking, "aha, that's a LEFT JOIN problem." A LEFT JOIN is similar to a regular join in that it attempts to match rows in the first (left) table with the rows in the second (right) table. But in addition, if a left table row has no match in the right table, a LEFT JOIN still produces a row—one in which all the columns from the right table are set to NULL. This means you can find values that are missing from the right table by looking for NULL. It's easier to observe how this happens by working in stages. First, run a regular join to find matching rows:

mysql> SELECT * FROM artist, painting
-> WHERE artist.a_id = painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 5 | The Rocks | IA | 33 |
| 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+

In this output, the first a_id column comes from the artist table and the second one comes from the painting table.

Now compare that result with the output you get from a LEFT JOIN. A LEFT JOIN is written in somewhat similar fashion, but you separate the table names by LEFT JOIN rather than by a comma, and specify which columns to compare using an ON clause rather than a WHERE clause:

mysql> SELECT * FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | The Mona Lisa | MI | 87 |
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 5 | The Rocks | IA | 33 |
| 4 | Picasso | NULL | NULL | NULL | NULL | NULL |
| 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+

The output is similar to that from the regular join, except that the LEFT JOIN also produces an output row for artist rows that have no painting table match. For those output rows, all the columns from painting are set to NULL.

Next, to restrict the output only to the non-matched artist rows, add a WHERE clause that looks for NULL values in the painting column that is named in the ON clause:

mysql> SELECT * FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> WHERE painting.a_id IS NULL;
+------+---------+------+------+-------+-------+
| a_id | name | a_id | p_id | title | price |
+------+---------+------+------+-------+-------+
| 2 | Monet | NULL | NULL | NULL | NULL |
| 4 | Picasso | NULL | NULL | NULL | NULL |
+------+---------+------+------+-------+-------+

Finally, to show only the artist table values that are missing from the painting table, shorten the output column list to include only columns from the artist table:

mysql> SELECT artist.* FROM artist LEFT JOIN painting
-> ON artist.a_id = painting.a_id
-> WHERE painting.a_id IS NULL;
+------+---------+
| a_id | name |
+------+---------+
| 2 | Monet |
| 4 | Picasso |
+------+---------+

The preceding LEFT JOIN lists those left-table values that are not present in the right table. A similar kind of operation can be used to report each left-table value along with an indicator whether or not it's present in the right table. To do this, perform a LEFT JOIN to count the number of times each left-table value occurs in the right table. A count of zero indicates the value is not present. The following query lists each artist from the artist table, and whether or not you have any paintings by the artist:

mysql> SELECT artist.name,
-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection'
-> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
-> GROUP BY artist.name;
+----------+---------------+
| name | in collection |
+----------+---------------+
| Da Vinci | yes |
| Monet | no |
| Picasso | no |
| Renoir | yes |
| Van Gogh | yes |
+----------+---------------+

As of MySQL 3.23.25, you can also use RIGHT JOIN, which is like LEFT JOIN but reverses the roles of the left and right tables. In other words, RIGHT JOIN forces the matching process to produce a row from each table in the right table, even in the absence of a corresponding row in the left table. This means you would rewrite the preceding LEFT JOIN as follows to convert it to a RIGHT JOIN that produces the same results:

mysql> SELECT artist.name,
-> IF(COUNT(painting.a_id)>0,'yes','no') AS 'in collection'
-> FROM painting RIGHT JOIN artist ON painting.a_id = artist.a_id
-> GROUP BY artist.name;
+----------+---------------+
| name | in collection |
+----------+---------------+
| Da Vinci | yes |
| Monet | no |
| Picasso | no |
| Renoir | yes |
| Van Gogh | yes |
+----------+---------------+

Elsewhere in this book, I'll generally refer in discussion only to LEFT JOIN for brevity, but the discussions apply to RIGHT JOIN as well if you reverse the roles of the tables.

Digital Clock Script

Hi,

People you cant find JavaScript digital clock here at the following links.

1. http://www.hitmill.com/programming/js/digitalClock.html
2. http://www.ricocheting.com/js/digiclock.html

Thanks
Jawed

Gmail like chat

http://anantgarg.com/2009/05/13/gmail-facebook-style-jquery-chat/

Changes in dreamweaver to open .ctp and .thtml file

There are ultimately 3 files that you need to edit. The first two are located in the folder that you installed Dreamweaver, in my case this is “C:\Program Files\Adobe\Adobe Dreamweaver CS3\configuration” although I’m using the latest version of Dreamweaver previous versions also have a “configuration” folder so go there.

Open up “Extensions.txt” and on the first line at the very end add THTML and CTP separated by commas, so the line should read:

1. ,MASTER,THTML,CTP:All Documents

,MASTER,THTML,CTP:All Documents

Similarly add these two extensions to the “:PHP Files” line.

1. PHP,PHP3,PHP4,PHP5,TPL,THTML,CTP:PHP Files

PHP,PHP3,PHP4,PHP5,TPL,THTML,CTP:PHP Files

Next open the “DocumentTypes” folder and edit the “MMDocumentTypes.xml” file, just open it up using notepad or wordpad. Search for the line which has an id “PHP_MySQL” and add the THTML/CTP file extensions to both the “winfileextension” and “macfileextension” so the line should read:

1. winfileextension="php,php3,php4,php5,thtml,ctp"
2. macfileextension="php,php3,php4,php5,thtml,ctp"

winfileextension="php,php3,php4,php5,thtml,ctp"
macfileextension="php,php3,php4,php5,thtml,ctp"

The final file is another version of the “Extensions.txt” which is located in your “Documents and Settings” Folder in my case this is “C:\Documents and Settings\James\Application Data\Adobe\Dreamweaver 9\Configuration” just add the very same things you inserted earlier.

A solution for e-mail handling in CakePHP

The emailComponent (cake\libs\controller\components\email.php) is a way for you to using the same concepts of layouts and view ctp files to send formated messages as text, html or both. It supports sending via the built in mail functions of PHP, via smtp server or a debug mode where it writes the message out to a session flash message. It also supports file attachments.
Implements it in three easy Steps
1. Controller (STEP 1)

In your controller you need to add the component to your $components array or add a $components array to your controller like:
1. 2. var $components = array('Email');
3. ?>

In this example we will set up a private method to handle sending the email messages to a user identified by an $id. In our controller (let's use the User controller in this example)

1. 2. function _sendNewUserMail($id) {
3. $User = $this->User->read(null,$id);
4. $this->Email->to = $User['User']['email'];
5. $this->Email->bcc = array('anuragtrivediphp@gmail.com');
6. $this->Email->subject = 'Welcome to cakePHP email handling functionally';
7. $this->Email->replyTo = 'anuragtrivediphp @ gmail.com';
8. $this->Email->from = Anurag Blog ';
9. $this->Email->template = ‘contact'; // note no '.ctp'
10. //Send as 'html', 'text' or 'both' (default is 'text')
11. $this->Email->sendAs = 'both'; // because we like to send pretty mail
12. //Set view variables as normal
13. $this->set('User', $User);
14. //Do not pass any args to send()
15. $this->Email->send();
16. }
17. ?>

You have sent a message; you could call this from another method like
1. $this->_sendNewUserMail( $this->User->id );

2. Setting up the Layouts (Step 2)
To use both text and html mailing message you need to create layout files for them, just like in setting up your default layouts for the display of your views in a browser, you need to set up default layouts for your email messages. In the app/views/layouts/ directory you need to set up (at a minimum) the following structure
email/
html/
default.ctp
text/
default.ctp
These are the files that hold the layout templates for your default messages. Some example content is below
email/text/default.ctp
1.

email/html/default.ctp
1.
2. < html >
3. < body >
4.
5. < /body >
6. < /html >

3. Setup an email element for the message body
(Step 3)
In the app/views/elements/email/ directory you need to set up folders for text and html unless you plan to just send one or the other. In each of these folders you need to create templates for both types of messages referring to the content that you send to the view either by using $this->set() or using the $contents parameter of the send() method. Some simple examples are shown below. For this example we will call the templates
contact.ctp
1.Dear ,

In html

Dear ,< br />
Thank you for your interest.

Image Upload and Resize Component for CakePHP

image.php component file
/*
File: /app/controllers/components/image.php
*/
class ImageComponent extends Object
{
/*
* Uploads an image and its thumbnail into $folderName/big and $folderName/small respectivley.
* the generated thumnail could either have the same aspect ratio as the uploaded image, or could
* be a zoomed and cropped version.

* Directions:
* In view where you upload the image, make sure your form creation is similar to the following
* create('FurnitureSet',array('type' => 'file')); ?>
*
* In view where you upload the image, make sure that you have a file input similar to the following
* file('Image/name1'); ?>
*
* In the controller, add the component to your components array
* var $components = array("Image");
*
* In your controller action (the parameters are expained below)
* $image_path = $this->Image->upload_image_and_thumbnail($this->data,"name1",573,80,"sets",true);
* this returns the file name of the result image. You can store this file name in the database
*
* Note that your image will be stored in 2 locations:
* Image: /webroot/img/$folderName/big/$image_path
* Thumbnail: /webroot/img/$folderName/small/$image_path
*
* Finally in the view where you want to see the images
* image('sets/big/'.$furnitureSet['FurnitureSet']['image_path']);
* where "sets" is the folder name we saved our pictures in, and $furnitureSet['FurnitureSet']['image_path'] is the file name we stored in the database

* Parameters:
* $data: CakePHP data array from the form
* $datakey: key in the $data array. If you used file('Image/name1'); ?> in your view, then $datakey = name1
* $imgscale: the maximum width or height that you want your picture to be resized to
* $thumbscale: the maximum width or height that you want your thumbnail to be resized to
* $folderName: the name of the parent folder of the images. The images will be stored to /webroot/img/$folderName/big/ and /webroot/img/$folderName/small/
* $square: a boolean flag indicating whether you want square and zoom cropped thumbnails, or thumbnails with the same aspect ratio of the source image
*/
function upload_image_and_thumbnail($data, $datakey, $imgscale, $thumbscale, $folderName, $square,$controller) {
if (strlen($data[$controller][$datakey]['name'])>4){
$error = 0;
$tempuploaddir = "img/temp"; // the /temp/ directory, should delete the image after we upload
$biguploaddir = "upload/".$folderName."/big"; // the /big/ directory
$smalluploaddir = "upload/".$folderName."/small"; // the /small/ directory for thumbnails

// Make sure the required directories exist, and create them if necessary
if(!is_dir($tempuploaddir)) mkdir($tempuploaddir,true);
if(!is_dir($biguploaddir)) mkdir($biguploaddir,true);
if(!is_dir($smalluploaddir)) mkdir($smalluploaddir,true);

$filetype = $this->getFileExtension($data[$controller][$datakey]['name']);
$filetype = strtolower($filetype);

if (($filetype != "jpeg") && ($filetype != "jpg") && ($filetype != "gif") && ($filetype != "png"))
{
// verify the extension
return;
}
else
{
// Get the image size
$imgsize = GetImageSize($data[$controller][$datakey]['tmp_name']);
}

// Generate a unique name for the image (from the timestamp)
$id_unic = str_replace(".", "", strtotime ("now"));
$filename = $id_unic;

settype($filename,"string");
$filename.= ".";
$filename.=$filetype;
$tempfile = $tempuploaddir . "/$filename";
$resizedfile = $biguploaddir . "/$filename";
$croppedfile = $smalluploaddir . "/$filename";

if (is_uploaded_file($data[$controller][$datakey]['tmp_name']))
{
// Copy the image into the temporary directory
if (!copy($data[$controller][$datakey]['tmp_name'],"$tempfile"))
{
print "Error Uploading File!.";
exit();
}
else {
/*
* Generate the big version of the image with max of $imgscale in either directions
*/
$this->resize_img($tempfile, $imgscale, $resizedfile);

if($square) {
/*
* Generate the small square version of the image with scale of $thumbscale
*/
$this->crop_img($tempfile, $thumbscale, $croppedfile);
}
else {
/*
* Generate the big version of the image with max of $imgscale in either directions
*/
$this->resize_img($tempfile, $thumbscale, $croppedfile);
}

// Delete the temporary image
unlink($tempfile);
}
}

// Image uploaded, return the file name
return $filename;
}
}

/*
* Deletes the image and its associated thumbnail
* Example in controller action: $this->Image->delete_image("1210632285.jpg","sets");
*
* Parameters:
* $filename: The file name of the image
* $folderName: the name of the parent folder of the images. The images will be stored to /webroot/img/$folderName/big/ and /webroot/img/$folderName/small/
*/
function delete_image($filename,$folderName) {
unlink("upload/".$folderName."/big/".$filename);
unlink("upload/".$folderName."/small/".$filename);
}

function crop_img($imgname, $scale, $filename) {
$filetype = $this->getFileExtension($imgname);
$filetype = strtolower($filetype);

switch($filetype){
case "jpeg":
case "jpg":
$img_src = ImageCreateFromjpeg ($imgname);
break;
case "gif":
$img_src = imagecreatefromgif ($imgname);
break;
case "png":
$img_src = imagecreatefrompng ($imgname);
break;
}

$width = imagesx($img_src);
$height = imagesy($img_src);
$ratiox = $width / $height * $scale;
$ratioy = $height / $width * $scale;

//-- Calculate resampling
$newheight = ($width <= $height) ? $ratioy : $scale;
$newwidth = ($width <= $height) ? $scale : $ratiox;

//-- Calculate cropping (division by zero)
$cropx = ($newwidth - $scale != 0) ? ($newwidth - $scale) / 2 : 0;
$cropy = ($newheight - $scale != 0) ? ($newheight - $scale) / 2 : 0;

//-- Setup Resample & Crop buffers
$resampled = imagecreatetruecolor($newwidth, $newheight);
$cropped = imagecreatetruecolor($scale, $scale);

//-- Resample
imagecopyresampled($resampled, $img_src, 0, 0, 0, 0, $newwidth, $newheight, $width, $height);
//-- Crop
imagecopy($cropped, $resampled, 0, 0, $cropx, $cropy, $newwidth, $newheight);

// Save the cropped image
switch($filetype)
{

case "jpeg":
case "jpg":
imagejpeg($cropped,$filename,80);
break;
case "gif":
imagegif($cropped,$filename,80);
break;
case "png":
imagepng($cropped,$filename,80);
break;
}
}

function resize_img($imgname, $size, $filename) {
$filetype = $this->getFileExtension($imgname);
$filetype = strtolower($filetype);

switch($filetype) {
case "jpeg":
case "jpg":
$img_src = ImageCreateFromjpeg ($imgname);
break;
case "gif":
$img_src = imagecreatefromgif ($imgname);
break;
case "png":
$img_src = imagecreatefrompng ($imgname);
break;
}

$true_width = imagesx($img_src);
$true_height = imagesy($img_src);

if ($true_width>=$true_height)
{
$width=$size;
$height = ($width/$true_width)*$true_height;
}
else
{
$width=$size;
$height = ($width/$true_width)*$true_height;
}
$img_des = ImageCreateTrueColor($width,$height);
imagecopyresampled ($img_des, $img_src, 0, 0, 0, 0, $width, $height, $true_width, $true_height);

// Save the resized image
switch($filetype)
{
case "jpeg":
case "jpg":
imagejpeg($img_des,$filename,80);
break;
case "gif":
imagegif($img_des,$filename,80);
break;
case "png":
imagepng($img_des,$filename,80);
break;
}
}

function getFileExtension($str) {

$i = strrpos($str,".");
if (!$i) { return ""; }
$l = strlen($str) - $i;
$ext = substr($str,$i+1,$l);
return $ext;
}
} ?>


How to use in controller?
Sample code is here:-
class UserAvatarsController extends AppController {
var $name = 'UserAvatars';
var $uses = array('UserAvatar','User'); //using modules
var $helpers = array('Html','Form','Javascript','Session');
var $paginate = array('limit' => 15); // Set records per page
var $components = array('Image'); // used Image components
function upload()
{
$this->user_session_check();
if (!empty($this->data))
{
$image_path = $this->Image->upload_image_and_thumbnail($this->data,"avatar",573,80,"avatar",true,'UserAvatar');
$this->data['UserAvatar']['avatar']=$image_path;
if ($this->UserAvatar->save($this->data))
{
$this->flash('Your avatar has been uploaded successfully',_ROOT.'avatar/upload',1);
}
}
}
}
?>

Custom 404 error page with CakePHP

1. Create your error layout in app/view/layouts/ (with name error.ctp)
2. Create your 404 error view in app/view/errors/ (with name error404.ctp)
In /cake/libs/view/errors you will find all the default error views.
You can copy and paste error404.ctp in your app/view/errors directory or create a new file and customize this view as you like.
3. Set the error layout in app_controller.php
Then add this to your app_controller.php :
function _setErrorLayout() {
if ($this->name == ‘CakeError’) {
$this->layout = ‘error’;
}
}

function beforeRender () {
$this->_setErrorLayout();
}