Reference blog

Tag: mysql

  • Prestashop: Searching orders by the products they contain via mysql

    Source: https://www.prestashop.com/forums/topic/437518-searching-orders-by-the-products-they-contain/?tab=comments#comment-2033625

    SELECT product_id, product_attribute_id, product_name, GROUP_CONCAT(id_order SEPARATOR ‘, ‘) AS orders FROM ps_order_detail group by product_name order by product_id, product_attribute_id

    Can add where product_id = to the number you want to narrow down the specific product

    SELECT product_id, product_attribute_id, product_name, GROUP_CONCAT(id_order SEPARATOR ‘, ‘) AS orders FROM ps_order_detail where product_id = XYZ group by product_name order by product_id, product_attribute_id

  • phpmyadmin: mysql export Simplified Chinese utf csv gibberish text issue

    The database collation is utf8_unicode_ci but can’t guarantee the Chinese text encoding was 100% correct during data entry (may have mixed with Traditional Chinese?)

    When we use the usual route in phpmyadmin to export the data to .csv, the generated .csv file doesn’t show the Simplified Chinese text properly. Same issue when exporting to Microsoft .csv

    Manual workaround: Export the data to .csv but select “View output as text”. This somehow generates the data with the Simplified Chinese text correctly.

    Select all, copy and paste to your Microsoft Excel and… problem solved.

  • Database: MySQL Split value from one field to two

    Source: http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two

    Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

    With that function, you would be able to build your query as follows:

    SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

    If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

    In case you don’t want to use a function, this query handles things cleaner than the other answers:

    SELECT IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
        `membername`
    ) AS memberfirst,
    IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
        NULL
    ) AS memberlast
FROM `user`;

    Compared to other answers this approach takes care of:

    • membername values without a space: it will add the whole string to memberfirst and sets memberlast to NULL.
    • membername values that have multiple spaces: it will add everything before the first space to memberfirst and the remainder (including additional spaces) to memberlast.

    The UPDATE version would be:

    UPDATE `user` SET
    `memberfirst` = IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
        `membername`
    ),
    `memberlast` = IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
        NULL
    );

  • mysql: How to copy a database in phpMyAdmin

    How to copy a database in phpMyAdmin

    This tutorial will demonstrate how to copy or duplicate a database in phpMyAdmin.

    Begin by accessing phpMyAdmin via cPanel .

    Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
    Once inside the database, select the Operations tab.
    Scroll down to the section where it says “Copy database to:”
    Type in the name of the new database.
    Select “structure and data” to copy everything. Alternately, you can select “Structure only” if you want the columns but not the data.
    Check the box “CREATE DATABASE before copying” to create a new database.
    Check the box “Add AUTO_INCREMENT value.”
    Click on the Go button to proceed.

    If you click on the home icon, you will notice your new database listed in the left column. If you go inside, you will see the database is a copy of the previous database.
    Source: https://my.bluehost.com/cgi/help/2154

  • CSV to SQL Converter

    Microsoft excel can’t seem to export csv with double quotes anymore =_=, can’t import csv directly using phpmyadmin.

    Found this really site that helps to make the conversion from csv to mysql statements.

    http://www.convertcsv.com/csv-to-sql.htm

  • mysql: Change character encoding for database and tables

    http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8

    Use the ALTER DATABASE and ALTER TABLE commands.

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

    If not possible,
    Export structure and data with phpmyadmin
    Custom – display all possible options
    Database system or older MySQL server to maximize output compatibility with: MYSQL40
    Syntax to use when inserting data: include column names in every INSERT statement (Example: INSERT INTO tbl_name (col_A,col_B,col_C) VALUES (1,2,3))
    After export, remove all TYPE=InnoDB from the text file (.sql)
    Create new database in utf8_unicode_ci;
    Import the .sql file and tables created will inherit utf8_unicode_ci.