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

  • 07 February 2020
    Warning: session_start(): Failed to read session data: user (path: c:/wamp/tmp) in libraries\joomla\session\handler\native.php on line 260Error: Failed to start application: Failed to start the session Solution: check to make sure $dbprefix in the configuration.php file is set to correctly ma...
  • 12 September 2019
    If you get this message after switching to php version 7.x, change your code to from count() to count(get_object_vars()).
  • 08 August 2019
    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 par...
  • 09 June 2019
    I you're getting Unable to Connect or ERR_CONNECTION_REFUSED when trying to connect to a site on localhost, check the configuration.php file to see if $force_ssl is set to 2. Change it to 0 and the problem should go away. 
  • 02 May 2018
    You can use array_keys to get the keys from an array but how do you get the keys from elements in an object.  Easiest method is to cast the object to an array and use array_keys. $Keys = array_keys(array)$ObjectName);
  • 04 April 2018
    Assuming the Media Manager is already identifying application/pdf as a valid mime type and it's still not working. log into cPanel Choose "select PHP version" under "software" heading Make sure fileinfo is checked
  • 22 January 2018
    If you are trying to hide the author avatar and other related information that appears in a K2 Item, you can do this on an article-by-article basis or you can change the settings for the category that the article belongs to. Edit category > Item View Options > Author options > Display ext...