JSON in MySQL

Using json_decode in PHP is handing for extracting json parameters from PHP. Similarly, you can use JSON_EXTRACT in an SQL query.
 
SELECT JSON_EXTRACT(`params`, '$.parent_info_session') FROM `minto_categories` WHERE `id` = 96
 
Thise selects the data from the parent_info_session parameter from the JSON data that is stored in the params field of the minto_categories table.

Can't change table structure due to date with 0000-00-00 default

If this happens, get the SQL commands to alter the tables and precede those commands with the following which temporarily disables strict mode an allows the changes to go through.

SET sql_mode = '';

MySQL JOIN

There have been many times when I knew that using a JOIN in my SQL query would have been the right way to get the data that I wanted from multiple tables but I never got them to work properly. I figured I better make notes on this now that it actually worked for me.

Here's an example of an SQL querey that I used to get the email addresses for the users in the comprofiler table (Community builder). In this case, there is a one-to-one mapping but when I add a WHERE to it then it will be a subset.

SELECT email FROM `skat_users` INNER JOIN `skat_comprofiler` ON skat_users.id = skat_comprofiler.user_id
 
Note: if you were using this in a joomla php file you'd use
SELECT email FROM `#__users` INNER JOIN `#__comprofiler` ON #__users.id = #__comprofiler.user_id

Left Join

Using MYSQL JOIN always makes me go running back to the documentation. Hopefully this is enough of a reminder of the syntax.

SELECT `session_type` FROM `#__reg4_registrations` LEFT JOIN `#__reg4_sessions` ON #__reg4_sessions.id = #__reg4_registrations.sessionid WHERE #__reg4_registrations.SCNO = '{$SCNO}'

Some SQL Commands that have been useful

This page is probably only helpful to me, but here are some of the SQL commands I use which I can never remember the syntax for.

 

SELECT `First Name`, `Last Name`, `Age`, `Gender`, `CANSK1 Badge`, `CANSK2 Badge` FROM `#__canskate_skaters`

SELECT COUNT(expression) FROM tables

SELECT phase6.name_natural, phase6.name_first, phase6.name_last, concat(phase6.bio,' ', phase6.link), phase6.other

FROM phase6 WHERE phase6.link <> 'NULL'

ORDER by phase6.name_last;

SELECT concat(`First Name`,' ',`Last Name`) FROM `#__canskate_skaters` WHERE `Prog Type` = 'PROGREG' AND `Program ID` ='PRGID0000000357'

"SELECT `First Name`, `Last Name`, `Age`, `Gender`, `CANSK1 Badge`, `CANSK2 Badge` FROM `#__canskate_skaters` WHERE `Prog Type` = 'PROGREG' AND `Program ID` ='PRGID0000000357'";

INSERT INTO phase1a (name_natural, name_first, name_last, name_first_init, other) SELECT * FROM player_names

UPDATE <table_name> SET <field_name> = REPLACE(<field_name>, 'old text','new text')

Sorting out e-mail lists to generate a list of only those that are in one group but not the other.

INSERT INTO `table 2` SELECT DISTINCT `Email` FROM `table 1` WHERE `Prog Type` = 'PROGREG'

INSERT INTO `fall` SELECT DISTINCT `Email` FROM `table 1` WHERE `Prog Type` = 'PROGREG' AND `Program Name` LIKE '%Sep%'

INSERT INTO `fallnotwinter` SELECT email FROM `fall` WHERE email NOT IN (SELECT email FROM `winter` WHERE email is not null)

INSERT INTO `fallandwinter` SELECT DISTINCT email FROM `table 1`

INSERT INTO from2012 SELECT `Email Address` FROM `table 6` WHERE `Email Address` NOT IN (Select email FROM `fallandwinter`)

Recent Random Insights