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

  • 23 May 2020
    If you're using Joomla Component Builder and your admin list view isn't showing any items, even though there are items in the database, edit your admin view and make sure in the Admin behaviour column is set to Show in All List Views for at least one field.
  • 18 May 2020
    Ok. When creating a custom field: Here's an example of the text that goes in the "The php for the getOptions method" box. // gets the name and IDs of the description articles $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select($db->quoteName...
  • 18 May 2020
    I want a query to return all the fields for all the entries in the table minto_regsix_session that include a value of 2 in the 'day' field. Following is a screenshot from phpMyAdmin of the table including the day field which contains a JSON array. This query did NOT work. SELECT...
  • 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.