Inserting incremental weeks in MySQLhttps://www.joellipman.com/articles/database/mysql/inserting-incremental-weeks-in-mysql.html

15 April 2011 in his last year. He used functions to mktime and simulate the dates. A function goes in with a normal calendar date and returns an academic week number and the week commencing date. Problem? The 1st of January 2010 was a Friday. The 1st of January 2011...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

MySQL parameters in Excel 2007 PivotTableshttps://www.joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 than... MS Reporting Services? Yes by far. Fast, quick to setup and a big fat refresh button for the executives. SQL query returns individual items while Excel allows drag and drop grouping/drilldown pivot tables. Have managed to get this working with...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

SSRS Parameters in Oracle Stored Procedurehttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-parameters-in-oracle-stored-procedure.html

02 September 2011 The Stored Procedure Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Excel PivotTable Filter List Orderinghttps://www.joellipman.com/articles/microsoft/excel/excel-pivottable-filter-list-ordering.html

05 September 2011 against wall. Bang "Head" against wall. Bang "Head" against wall. Alternatively Change the SQL query so that the date returns in yyyy-mm-dd format (eg. 2012-03-04) Reverse Dates Order If you have a date filter list and want it sorted in descending...

• Type: Article
• Author: Joel Lipman
• Category: Excel
• Language: en-GB

AutoHotkey Format Date and Format Secondshttps://www.joellipman.com/articles/automation/autohotkey/autohotkey-format-date-and-format-seconds.html

20 September 2011 1, 2) ThisDate=%ThisDate% val=%ThisDate%%ThisTime% } FormatTime, val, %val%, ddd dd-MMM-yyyy HH:mm:ss Return val } // returns format Sat 01-Jan-2011 01:00:00 The next function usually accompanies the above one. It will basically convert seconds into...

• Type: Article
• Author: Joel Lipman
• Category: AutoHotkey
• Language: en-GB

Basic Oracle Function Structurehttps://www.joellipman.com/articles/database/pl-sql/basic-oracle-function-structure.html

15 January 2012 [EXCEPTION exception_section] END [function_name]; Example: My example accepts an Active Directory (AD) username and returns the student ID: CREATE OR REPLACE FUNCTION fn_get_sref_from_adname (ad_username IN varchar2) RETURN number IS student_no number;...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

No rows returned in Oracle causes SP to failhttps://www.joellipman.com/articles/database/pl-sql/no-rows-returned-in-oracle-causes-sp-to-fail.html

24 January 2012 warnings. So What? The problem is that if the student does not have a username but has an ID number, then the first query returns NO ROWS and then the second query errors and the whole stored procedure fails. The same problem happens when using these as...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 DROP FUNCTION ufn_DataJumble ; GO -- Create user defined function CREATE FUNCTION ufn_DataJumble ( @OrigVal varchar(max) ) RETURNS varchar(max) WITH ENCRYPTION AS BEGIN -- Variables used DECLARE @NewVal varchar(max); DECLARE @OrigLen int; DECLARE...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Inline Labels in Form Fields using Javascripthttps://www.joellipman.com/articles/web-development/js/inline-labels-in-form-fields-using-javascript.html

21 August 2012 so the field is empty and click somewhere on the page (not on a link as this will take you to another page) and the field returns to displaying "Name:". Why? I've noticed a lot of solutions now use JQuery or Mootools but most of these will not work in...

• Type: Article
• Author: Joel Lipman
• Category: JavaScript
• Language: en-GB

DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 FUNCTION ufn_DataScramble ; GO -- Create user defined function CREATE FUNCTION ufn_DataScramble ( @OrigVal varchar(max) ) RETURNS varchar(max) WITH ENCRYPTION AS BEGIN -- Variables used DECLARE @NewVal varchar(max); DECLARE @OrigLen int; DECLARE...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Error 1065: Query was Emptyhttps://www.joellipman.com/articles/database/mysql/error-1065-query-was-empty.html

13 December 2012 common. Why? Bespoke systems are the usual suspects. Developers will have told the system what to do when newline, carriage returns and tab characters are entered in the data. Those who are security-conscious will also make their system escape...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

Delete related records from multiple tableshttps://www.joellipman.com/articles/database/mysql/delete-related-records-from-multiple-tables.html

11 January 2013 148797 148797 148797 148798 148798 148798 148799 (null) 148799 148800 148800 148800 148686 148686 (null) -> The select here returns 5 rows -> A delete here would affect 13 rows. This should show all records to be deleted.

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

Improve Default Joomla Search https://www.joellipman.com/articles/cms/joomla/improve-default-joomla-search-heuristics.html

07 May 2013 database, and there is one specific article you visit a lot, it would be nicer just to type a number like "1234" and this returns that specific article. In the case of Joomla, we'll use the article ID. Pre-Notes: This article consists of instructions to...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Joomla Component: Set default params on installationhttps://www.joellipman.com/articles/cms/joomla/joomla-component-set-default-params-on-installation.html

24 October 2013 component for Joomla CMS 2.5.x which is restricted to registered users only. On installation, and by default, the menu item returns a 500 - Server Error because the parameters haven't been set. I need to go into the Joomla Admin panel, view the Options...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Generate a Timesheet in MySQL https://www.joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 listed irrespective of whether any time has been associated to them. We need to be able to specify any date, and the system returns the timesheet for the week that date occurs in. How? The example below shows how to do this when your weeks...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

Convert to Proper Case in T-SQLhttps://www.joellipman.com/articles/database/t-sql/convert-to-proper-case-in-t-sql.html

04 June 2014 with exceptions to the rule, words that you want in a specific case. CREATE FUNCTION ufn_ProperCase(@Text AS VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN -- declare some variables DECLARE @Reset BIT; DECLARE @Ret VARCHAR(8000); DECLARE @i INT; DECLARE...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

SSIS Multiple Lookups in onehttps://www.joellipman.com/articles/microsoft/ssis/ssis-multiple-lookups-in-one.html

05 August 2014 this: But we didn't want to do this. How? As a proof of concept, I created one lookup task that executes a SQL query which returns all the joined tables and displays the decode name. Let's pretend the info we want is rather simple: SELECT [sequenceID],...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

XML Schema Referencehttps://www.joellipman.com/articles/web-development/xml/xml-schema-reference.html

13 August 2014 of digits allowed. Must be greater than zero whiteSpace Specifies how white space (line feeds, tabs, spaces, and carriage returns) is handled Source(s): w3schools.com

• Type: Article
• Author: Joel Lipman
• Category: Extensible Markup Language
• Language: en-GB

Accessing Apimo WebService APIhttps://www.joellipman.com/articles/automation/api-misc/accessing-apimo-webservice-api.html

29 March 2016 XML declaration from results $output = substr($output, stripos($output, '?>') + 2 ); // remove leading spaces, carriage returns $output = trim($output); // process if type specified was xml if($apimo_type=='xml'){ // send header...

• Type: Article
• Author: Joel Lipman
• Category: API Miscellaneous
• Language: *

PHP Issue: simplexml_load_string parser error : Input is not proper UTF-8, indicate encoding !https://www.joellipman.com/articles/web-development/php/php-issue-simplexml-load-string-parser-error-input-is-not-proper-utf-8-indicate-encoding.html

02 August 2016 "http://joellipman.com/xml_feeds/my_XML_url.xml" ); $file_xml = simplexml_load_string( $file_content ); // doesn't work and returns a load of parser errors The tweaked command using cURL is: function get_data($url) { $ch = curl_init(); $timeout = 5;...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: *