Skip to content

Instantly share code, notes, and snippets.

View betweenbrain's full-sized avatar
🎯
Focusing

Matt Thomas betweenbrain

🎯
Focusing
View GitHub Profile
@betweenbrain
betweenbrain / gist:11068237
Created April 18, 2014 23:20
MySQL Joomla GROUP_CONCAT Examples
// SQL
SELECT
`users`.`id`,
`users`.`name`,
(SELECT
GROUP_CONCAT(`k2`.`title`)
FROM
`jos_k2_items` AS `k2`
WHERE
`k2`.`created_by` = `users`.`id`

Here is an example of write subquery in Joomla! 3 using JDatabase method.

<?php
// Initialize variables.
$db       = JFactory::getDbo();
$subQuery = $db->getQuery(true);
$query    = $db->getQuery(true);

// Create the base subQuery select statement.
<?php
$con = mysql_connect('localhost', 'dbUser', 'dbPassword');
$key = '1234567890123456';
$table = "CREATE TABLE people (
id INT NOT NULL AUTO_INCREMENT,
full_name VARCHAR(255),
ssn VARCHAR(255),
PRIMARY KEY (id)
### Keybase proof
I hereby claim:
* I am betweenbrain on github.
* I am betweenbrain (https://keybase.io/betweenbrain) on keybase.
* I have a public key whose fingerprint is 54B6 C5FB C8B0 AB9A 1822 143F 8E98 4A78 99B5 F294
To claim this, I am signing this object:
@betweenbrain
betweenbrain / gist:e07c94110d5b843ad0bc
Last active August 29, 2015 14:04
Force group / sort Joomla items by category
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('id')))
->from($db->quoteName('#__categories'))
->where($db->quoteName('published') . ' = ' . $db->quote('1') .
' AND ' . $db->quoteName('extension') . ' = ' . $db->quote('com_content'))
->order('LFT ASC');
$db->setQuery($query);
$categories = $db->loadObjectList('id');
@betweenbrain
betweenbrain / gist:a6ed70c49f98852aae48
Created July 30, 2014 15:27
Sort Joomla items by parent
<?php
$query
->select($this->db->quoteName(array(
'title',
'id',
'parent_id')))
->select($this->db->quoteName('metadesc', 'description'))
->from($this->db->quoteName('#__categories'))
->order($this->db->quoteName('parent_id') . ' ASC')
->order($this->db->quoteName('ordering') . ' ASC');
@betweenbrain
betweenbrain / gist:27fb11b62f05890bbbfe
Created August 5, 2014 16:49
Joomla Country Form Field
<?php defined('_JEXEC') or die;
/**
* File recipients.php
* Created 8/1/14 11:38 AM
* Author Matt Thomas | matt@betweenbrain.com | http://betweenbrain.com
* Support https://github.com/betweenbrain/
* Copyright Copyright (C) 2014 betweenbrain llc. All Rights Reserved.
* License GNU GPL v2 or later
*/
SELECT
table1.id,
table1.firstName,
table1.lastName,
table2.sales,
table2.date
FROM `table1`
LEFT JOIN `table2` ON table1.firstName = table2.firstName;
@betweenbrain
betweenbrain / gist:b0027305656e44fe882e
Created August 14, 2014 21:04
Simple MySQL Join with Last Month Where Clause
SELECT
table1.id,
table1.firstName,
table1.lastName,
table2.sales,
table2.date
FROM `table1`
LEFT JOIN `table2` ON table1.firstName = table2.firstName
WHERE MONTH(table2.date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH);
@betweenbrain
betweenbrain / gist:7a2c2abfecd7fee3d963
Created August 14, 2014 21:05
MySQL Join with Subquery Selecting Joined Data from Last Month
SELECT
table1.id,
table1.firstName,
table1.lastName,
a.sales
FROM `table1`
LEFT JOIN
( SELECT
firstName,
sales,