Category Archives: Programming

Wrap Implode Array Elements in Quotes

Posted by on February 3, 2012 No comments 
$myArray = array('A', 'B', 'C');

echo "'" . implode("','", $myArray) . "'"; //Displays 'A', 'B', 'C'

echo implode(',', $myArray); //Displays A, B, C

CREATE TABLE LIKE

Posted by on January 13, 2012 No comments

To duplicate table structure of existing table, we can use this command:

CREATE TABLE student_contact_deleted LIKE student_contact;

jQuery change image code snippet

Posted by on November 1, 2011 No comments 
$("#link").click(function() {
    $("#image").fadeOut(1000, function() {
        $("#image").attr("src",$("#link").attr("href"));
    }).fadeIn(1000);
    return false;
});

Useful code snippet for image change using jQuery. In this code image will change on click. Can be change to other event trigger like mouseover, mouseout.

source

Constant value in insert select

Posted by on October 25, 2011 No comments 
INSERT INTO linking_book_category (book_id, category_id)
SELECT book.id, 1 FROM books AS book;

More advance usage involving substring, trimming leading zeros as I try to migrate college’s intake from migration table.

INSERT INTO intakes (no,month,year,active,`status`,createdby,datecreated)
SELECT DISTINCT academicperiod, TRIM(LEADING '0' FROM SUBSTR(academicperiod,6,2)), SUBSTR(academicperiod,1,4), 1, 1, 1, NOW()
FROM _prestige_intake
WHERE academicperiod IN ('2006/01','2006/07','2007/01','2007/07','2008/01','2008/07','2009/01','2009/07','2010/01','2010/07')
ORDER BY academicperiod ASC

Update field with value from another table

Posted by on October 14, 2011 No comments 
UPDATE table_1 AS t1, table_2 AS t2
  SET
    t1.name = t2.title,
    t1.descr = t2.description,
    t1.price = t2.price,
    t1.available = t2.stock
  WHERE t1.productId = t2.sku

Taken from forum while browsing to find for solution. This query will took some time when dealing with thousands record in both table.

Merge rows as one field

Posted by on October 12, 2011 2 comments 
GROUP_CONCAT(fieldname ORDER BY fieldname ASC SEPARATOR ',')

Full code usage example:

SELECT email, studentname,
GROUP_CONCAT(matrixno ORDER BY matrixno ASC SEPARATOR ',') AS matno,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Code above used to find duplicates for email.