Sunday, 14 September 2014

Find Last Message FROM All Users


 Query to find out the last message from all users in conversation

select messages.message_text, messages.sender_uuid, messages.receiver_uuid, messages.add_date  FROM messages 
INNER JOIN (
SELECT  MAX(add_date) AS time, IF(`receiver_uuid` = '1',  `sender_uuid`, `receiver_uuid`) AS user FROM     dp_messages GROUP BY user) m2 
ON messages .add_date = m2.time 
AND (messages .`sender_uuid` = m2.user  OR dp_messages .`receiver_uuid` = m2.user)
WHERE 

`sender_uuid` = '1' OR `receiver_uuid` = '1' 
ORDER BY messages .add_date DESC LIMIT 0, 20

Tuesday, 9 September 2014

Mysql, Query to find out table comments?

To List all tables from database use: 

show tables from DatabaseName;


To List Table Structure you can use: 

DESC tablename;
Or You can use
SHOW COLUMNS FROM file FROM DatabaseName;

Query to find out Comments on Table

SELECT a.COLUMN_NAME, a.COLUMN_COMMENT FROM information_schema.COLUMNS a WHERE a.TABLE_NAME = 'YOUR_TABLE_NAME'

Just Change the name of YOUR_TABLE_NAME and run it.

Monday, 21 July 2014

Export Data From Server Using Terminal

To Export Data from Terminal Use Following Command :


mysql -p -u databasename > databasename.sql 
OR
mysqldump -p -u databasename > databasename.sql




To Import Data from Terminal Use Following Command :

mysql -p -u databasename < databasename.sql

OR
mysqldump -p -u databasename > databasename.sql

Copy Data from server to localhost using ssh



 First Of all compress the file using following command

tar -cf name.tar name_of_folder

To copy a compressed folder from the server use following command

scp user@remotehost.com:/filepath/file.txt /some/localhost/folder

scp user@remotehost.com:/var/html/project.tar /var/www/



Keep Exploring

Friday, 27 June 2014

Get Mysql Query Count

This Query will return the count of queries fired in database

 
mysqladmin ext | grep -e 'Com_\(update\|select\|insert\)'

Wednesday, 14 May 2014

Php: Generate Captcha Secure Image

global $image;
    $_SESSION['count'] = time();
   
    $image              = imagecreatetruecolor(200, 50) or die("Cannot Initialize new GD image stream");
    $background_color   = imagecolorallocate($image, 255, 255, 255);
    $text_color         = imagecolorallocate($image, 0, 255, 255);
    $line_color         = imagecolorallocate($image, 64, 64, 64);
    $pixel_color        = imagecolorallocate($image, 0, 0, 255);

    imagefilledrectangle($image, 0, 0, 200, 50, $background_color);

    for ($i = 0; $i < 3; $i++) {
        imageline($image, 0, rand() % 50, 200, rand() % 50, $line_color);
    }

    for ($i = 0; $i < 1000; $i++) {
        imagesetpixel($image, rand() % 200, rand() % 50, $pixel_color);
    }


    $letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
    $len = strlen($letters);
    $letter = $letters[rand(0, $len - 1)];

    $text_color = imagecolorallocate($image, 0, 0, 0);
    $word = "";
    for ($i = 0; $i < 6; $i++) {
        $letter = $letters[rand(0, $len - 1)];
        imagestring($image, 7, 5 + ($i * 30), 20, $letter, $text_color);
        $word .= $letter;
    }
    $_SESSION['captcha_string'] = $word;

    $images = glob("*.png");
    foreach ($images as $image_to_delete) {
        @unlink($image_to_delete);
    }
   
    imagepng($image, "image" . $_SESSION['count'] . ".png");


To display The captcha

<img src="../image<?php echo $_SESSION['count'] ?>.png">