working on it ...

Filters

Explore Public Snippets

Sort by

Found 393 snippets

    public by yourfriendcaspian  340  0  4  0

    How to install MySQL with Python and Django Debian/Derivatives

    #Install Mysql with Python and Django Debian/Derivatives 
    
    #To install we need to have some dependencies in the system for now we will show on
    #Debian and Derivatives.
    
    #But first install updates and MySQL
    
    ```
    $ sudo apt-get update
    $ sudo apt-get upgrade
    ```	
    
    # ** NOTE: ** 
    #Each System has its commands for updating, if your machine is not derived from Debian ** look for them **
    
    ##Install MySQL
    
    ```
    $ sudo apt-get install mysql-server mysql-client
    Passwd for 'root' user: <password>
    ```
    
    #At the end we execute this command to give more security to our BD
    
    ```
    $ mysql_secure_installation
    ```
    
    #Check carefully the changes that will be made, the first question is the passwd for root,
    #IF you want to keep or change it, and continue with other security questions.
    
    ##Create a database and a user for the DB
    
    #Now we will create the DB that will be connected to DJango and a User with Passwd to access it.
    #There are two ways to do this:
    
    ```
    echo "CREATE DATABASE <DATABASENAME>;" | mysql -u root -p
    echo "CREATE USER '<DATABASEUSER>'@'localhost' IDENTIFIED BY '<PASSWORD>';" | mysql -u root -p
    echo "GRANT ALL PRIVILEGES ON <DATABASENAME>.* TO '<DATABASEUSER>'@'localhost';" | mysql -u root -p
    echo "FLUSH PRIVILEGES;" | mysql -u root -p
    ```
    
    #So they should put their passwd of mysql in each line or
    #they can also do it of the following way
    
    ```
    $ mysql -u root -p
    ```
    
    #Enter your passwd and then do the following.
    
    ```
    CREATE DATABASE <DATABASENAME>;
    CREATE USER '<DATABASEUSER>'@localhost IDENTIFIED BY '<PASSWORD>';
    GRANT ALL PRIVILEGES ON <DATABASENAME>.* TO '<DATABASEUSER>'@localhost;
    FLUSH PRIVILEGES;
    exit
    ```
    
    #We Check Dependencies
    #There are only a few dependencies but you have to be sure
    
    ```
    $ sudo apt-get install libmysqlclient-dev python-dev
    ```
    
    #So far it's all just proceed to install pip in our virtual environment or globally
    
    ```
    $ sudo -H pip install mysql-python
    ```
    
    #As you can see now you can create DB and Users for each Django Project

    public by snip2code  743  3  4  0

    First Snippet: How to play with Snip2Code

    This is the first example of a snippet: - the title represents in few words which is the exact issue the snippet resolves; it can be something like the name of a method; - the description (this field) is an optional field where you can add interesting information regarding the snippet; something like the comment on the head of a method; - the c
    /* place here the actual content of your snippet. 
       It should be code or pseudo-code. 
       The less dependencies from external stuff, the better! */

    public by sebastian.piskorski  3717  3  5  2

    [MySQL] Calculate Database size

    [MySQL] Calculate Database size using simple single query.
    SELECT table_schema "Data Base Name",
        sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
        sum( data_free )/ 1024 / 1024 "Free Space in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema ;

    public by p.kontalis  99081  2  5  0

    VTiger Extraction Customers' Data by City

    Extract from Vtiger Customers' List with address and to whom they have been assigned
    SET @city = '[CITY NAME]'; #Replace [CITY NAME] with your preferable city name
    SET @db_name='[DATABASE NAME]'; #Replace [DATABASE NAME] with your database name
    SET @myquery = CONCAT('
    SELECT	', @db_name, '.vtiger_account.accountid AS \'Company Code\',
    		', @db_name, '.vtiger_account.accountname AS \'Company\', 
    		', @db_name, '.vtiger_account.phone AS \'Phone 1\', 
    		', @db_name, '.vtiger_account.otherphone AS \'Phone 2\', 
    		', @db_name, '.vtiger_account.email1 AS \'Email 1\', 
    		', @db_name, '.vtiger_account.email2 AS \'Email 2\', 
    		', @db_name, '.vtiger_account.website AS \'Website\', 
    		', @db_name, '.vtiger_account.fax AS \'Fax\',
    		', @db_name, '.vtiger_accountbillads.bill_city AS \'City\',
    		', @db_name, '.vtiger_accountbillads.bill_code AS \'TK\',
    		', @db_name, '.vtiger_accountbillads.bill_country AS \'Country\',
    		', @db_name, '.vtiger_accountbillads.bill_state AS \'State\',
    		', @db_name, '.vtiger_accountbillads.bill_street AS \'Street\',
    		', @db_name, '.vtiger_accountbillads.bill_pobox AS \'TK\', 
    		', @db_name, '.vtiger_users.last_name AS \'Αssignment το\'
    FROM ', @db_name, '.vtiger_account
    INNER JOIN ', @db_name, '.vtiger_accountbillads 
    ON ', @db_name, '.vtiger_account.accountid = ', @db_name, '.vtiger_accountbillads.accountaddressid
    INNER JOIN ', @db_name, '.vtiger_crmentity
    ON ', @db_name, '.vtiger_account.accountid = ', @db_name, '.vtiger_crmentity.crmid	
    LEFT JOIN ', @db_name, '.vtiger_users
    ON ', @db_name, '.vtiger_crmentity.smownerid = ', @db_name, '.vtiger_users.id
    WHERE ', @db_name, '.vtiger_accountbillads.bill_city=@city
    AND ', @db_name, '.vtiger_crmentity.setype=\'Accounts\' 
    ');
    PREPARE stmt FROM @myquery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
                
    

    public by p.kontalis  2110  0  5  0

    SQL Join with Static Columns

    Identifies the origin of each row after the join of two tables.
    DROP TABLE IF EXISTS signed_A;
    DROP TABLE IF EXISTS signed_B;
    CREATE TEMPORARY TABLE signed_A AS (SELECT 'TableA' AS STATUS, A.col1, A.col2 FROM A);
    CREATE TEMPORARY TABLE signed_B AS (SELECT 'TableB' AS STATUS, hostings.col3 FROM B);
    SELECT * FROM A LEFT JOIN B ON A.col2=B.col3;

    public by p.kontalis  1627  0  5  0

    Move Cell Values from a Table Column to another

    ---Take a look of the join tables before the move
    SELECT * 
    FROM vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    WHERE vtiger_accountshipads.ship_street !="" AND vtiger_accountbillads.bill_street=""
    ---Copy the Values
    UPDATE vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    SET vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street
    WHERE vtiger_accountbillads.bill_street="" AND vtiger_accountshipads.ship_street != "";
    ---Erase the copied values  (or the values are the same in both cells)
    UPDATE vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    SET vtiger_accountshipads.ship_street=""
    WHERE vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street;
    ---Validation of all transfers
    SELECT * #vtiger_accountbillads.accountaddressid,vtiger_accountbillads.bill_code,vtiger_accountshipads.ship_code
    FROM vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    WHERE (vtiger_accountshipads.ship_city != "" AND vtiger_accountbillads.bill_city="")
    OR (vtiger_accountshipads.ship_code != "" AND vtiger_accountbillads.bill_code="")
    OR (vtiger_accountshipads.ship_country != "" AND vtiger_accountbillads.bill_country="")
    OR (vtiger_accountshipads.ship_state != "" AND vtiger_accountbillads.bill_state="")
    OR (vtiger_accountshipads.ship_pobox != "" AND vtiger_accountbillads.bill_pobox="")
    OR (vtiger_accountshipads.ship_street != "" AND vtiger_accountbillads.bill_street="");
    

    public by p.kontalis  1979  2  5  0

    Contacts Export from VTiger to Google Contacts

    SELECT 
    REPLACE (REPLACE (accountname, '"', '_'), ',', '_') AS 'Name',
    REPLACE (REPLACE (accountname, '"', '_'), ',', '_') AS 'Given Name',
    '' AS 'Additional Name',
    '' AS 'Family Name',
    '' AS 'Yomi Name',
    '' AS 'Given Name Yomi',
    '' AS 'Additional Name Yomi',
    '' AS 'Family Name Yomi',
    '' AS 'Name Prefix',
    '' AS 'Name Suffix',
    '' AS 'Initials',
    '' AS 'Nickname',
    '' AS 'Short Name',
    '' AS 'Maiden Name',
    '' AS 'Birthday',
    '' AS 'Gender',
    '' AS 'Location',
    '' AS 'Billing Information',
    '' AS 'Directory Server',
    '' AS 'Mileage',
    '' AS 'Occupation',
    '' AS 'Hobby',
    '' AS 'Sensitivity',
    '' AS 'Priority',
    '' AS 'Subject',
    '' AS 'Notes',
    '* My Contacts' AS 'Group Membership',
    '* Work' AS 'E-mail 1 - Type',
    REPLACE (REPLACE (email1, '"', '_'), ',', '_') AS 'E-mail 1 - Value',
    'Εργασία 2' AS 'E-mail 2 - Type',
    REPLACE (REPLACE (email2, '"', '_'), ',', '_') AS 'E-mail 2 - Value',
    'Εργασία 2' AS 'Phone 1 - Type',
    REPLACE (REPLACE (phone, '"', '_'), ',', '_') AS 'Phone 1 - Value',
    'Work' AS 'Phone 2 - Type',
    REPLACE (REPLACE (otherphone, '"', '_'), ',', '_') AS 'Phone 2 - Value',
    'Fax' AS 'Phone3 - Type',
    REPLACE (REPLACE (fax, '"', '_'), ',', '_') AS 'Phone 3 - Value',
    'Home' AS 'Address 1 - Type',
    CONCAT('"',bill_street,'\n',bill_city,bill_state,' ',bill_pobox,'\n',bill_country,'"') AS 'Address 1 - Formatted',
    REPLACE (REPLACE (bill_street, '"', '_'), ',', '_') AS 'Address 1 - Street',
    REPLACE (REPLACE (bill_city, '"', '_'), ',', '_') AS 'Address 1 - City',
    '' AS 'Address 1 - PO Box',
    REPLACE (REPLACE (bill_state, '"', '_'), ',', '_') AS 'Address 1 - Region',
    REPLACE (REPLACE (bill_pobox, '"', '_'), ',', '_') AS 'Address 1 - Postal Code',
    REPLACE (REPLACE (bill_country, '"', '_'), ',', '_') AS 'Address 1 - Country',
    '' AS 'Address 1 - Extended Address',
    'Home Page' AS 'Website 1 - Type',
    REPLACE (REPLACE (website, '"', '_'), ',', '_') AS 'Website 1 - Value'
    FROM vtigercrm600.vtiger_account
    LEFT JOIN vtigercrm600.vtiger_accountbillads
    ON vtiger_account.accountid = vtiger_accountbillads.accountaddressid;
    # NOTE: - After the execution click on export button and save it as csv
    # NOTE: The " and , have been replaced by _    
    # NOTE: If you want to use this snippet, consider to change the "vtigercrm600" with your database name. ;)
    

    public by lauhin  2797  1  5  0

    Find tables where certain columns exists

    if you need to find all tables where a column name exists, you can search that with this query
    //this query will give you all table names in a database where certain column names exists.
    select distinct table_name
        from information_schema.columns
        where column_name in ('columnA','ColumnB')
            and table_schema = 'YourDatabase';            

    public by vivek1986  2429  0  5  0

    Update string/substring in a column with REPLACE

    UPDATE table SET field = REPLACE(field, 'string1', 'string2') WHERE field LIKE '%string1%';
    --OR--
    UPDATE table_name SET field_name = REPLACE(field_name,'search','replace') WHERE field_name LIKE '%some_value%';
    
    --example--
    UPDATE urls SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/');
    UPDATE yourtable SET url = REPLACE(url, 'http://domain1.com/images/', 'http://domain2.com/otherfolder/')
                    WHERE url LIKE ('http://domain1.com/images/%');
    
    --or--
    --Using REGEXP in select/update query--
    SELECT * FROM 'table' WHERE `name` REGEXP '\r';--Just to verify data we are updating--
    UPDATE 'table' SET 'field' = replace(field,"findvalue", "reaplcevalue") WHERE REGEXP 'regexp search expression';

    public by nsssim  2269  0  6  0

    delete duplicate in mysql - nassim baci

    delete duplicate in mysql
    delete
    from products using products,
        products p1
    where products.id > p1.id
        and products.idd = p1.idd
    		and products.brandedName = p1.brandedName             
    • Public Snippets
    • Channels Snippets