SQL

Remove duplicates using SQL

This is a way to remove duplicate rows in a database table using SQL. The column id is unique and the query choose to keep the latest id through this part of the query: `a`.`id` < `b`.`id` Further the column identifier is one duplicate column we use ti find the duplicates

DELETE `a`
FROM
    `zimonitor_ahsay_api_queue` AS `a`,
    `zimonitor_ahsay_api_queue` AS `b`
WHERE
    `a`.`id` < `b`.`id`
    AND (`a`.`identifier` = `b`.`identifier`);

Prevent SQL injection in Zend Framework (Pimcore)

After some attempt to prevent SQL injections by using mysql_real_escape_string that worked fine on my local server but not on the production server, I use the Zend Framework built in protection against SQL injection. When using the select and where methods in Zend Framework with argument replacement instead of embedding them in the string, then you get protection from SQL injection.

 

How to use wildcard in a JOIN ON using a string

Found others looking for the same, how to use wildcard in a JOIN ON using a string reference:

This is how I soleved it with wildcard:

SELECT acl.* , node.type FROM acl
LEFT JOIN node ON ( acl.name LIKE concat( '%', node.nid ) )
WHERE 1

This is how I soleved it with fixed value:

SELECT acl.* , node.type FROM acl
LEFT JOIN node ON ( acl.name LIKE concat( 'view_', node.nid ) )
WHERE 1

This is how I soleved it with pattern like [something]_[nid]:

SELECT acl .

MySQL group concat a join select

This is an example of how you can aggregate data using a JOIN in a SELECT statement.

<?php
SELECT node
.*, GROUP_CONCAT(example.nid) AS myfield FROM node LEFT JOIN example USING(nid) GROUP BY node.nid;

Result:
...
"123, 65, 14, 65"...
?>

Make the list semicolon separated and order the values descending and make sure that the values are distinct (no duplicates)

Upper case first in SQL SELECT

This query selects a string from database containing both upper case (STOCKHOLM) and lower case (stockholm) characters and present it as upper case first (Stockholm), and removes duplicates.

<?php
$sql
= "SELECT DISTINCT CONCAT(UPPER(LEFT(LOWER(city_name), 1)), SUBSTRING(LOWER(city_name), 2)) AS city FROM city WHERE 1";
?>

Pages