Dynamic SQL query on multiple fileds
This is one way of creating dynamic SQL queries based on a POST array of fields. In this version it's possible to configure one field to search in multiple database tables.
SQL
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 set Drupal 6.x site offline using SQL
This is how to set Drupal 6.x site offline using SQL
UPDATE variable SET value='s:1:"1";' WHERE name = 'site_offline';
DELETE FROM cache WHERE CID = 'variables';This is how to put it online again
UPDATE variable SET value='s:1:"0";' WHERE name = 'site_offline';
DELETE FROM cache WHERE CID = 'variables';Handy SQL print in table for drupal
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 1This 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 1This is how I soleved it with pattern like [something]_[nid]:
SELECT acl .
MySQL full-text search
If you want to find fragments in multiple table fields use the MySQL full-text search
SQL order by fixed values
This is how you can order a select result by fixed values
<?php
SELECT * FROM `logs` ORDER BY status=3 DESC , status=1 DESC, status=4 DESC
?>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";
?>MySQL select words beginning with uppercase
This is a way to select words beginning with an uppercase. This query check if the first character is an uppercase by the function UPPER() and if the following character is a lowercase with the function LOWER().