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 = '';

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`)

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

Recent Random Insights

  • 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...
  • 21 January 2018
    When attempting to use the Gavick News Show Pro GK5 module in conjunction with their News template. I've used the Quickstart package on my internal development server and installed K2 so that I can use its features. If I edit any one of the instances of the News Show Pro GK5 modules and save the s...
  • 04 January 2018
    At some point, my Joomla system running on my local WAMP became incredibly slow. It was taking 30-60 seconds to load a page that only takes a few seconds to load on my cloud server. I discovered that when I had enabled xdebug in my php.ini settings, I had turned on the profiler which was generatin...
  • 17 December 2017
    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 = '';
  • 24 October 2017
    For some reason, the default admin user account and password did not appear to exist in the instance of bitnami Joomla that I installed on my AWS through Lightsail. To set your Joomla admin password: Log in using an SSH terminal. You can do this through your Web browser from your AWS ...