MySQL - Joel Lipman .Com

Export/Import Database using MySQL Workbench

: Joel Lipman; MySQL; 05 February 2013; Hits: 19233

What?
This article isn't unique as there are other tutorials out there. This is meant to detail a process to export a database using MySQL Workbench and then to import it into another database.

Why?
If you're confined to use this product then this is how to do it. Personally, even a web-based app such as PhpMyAdmin would fare better.

How?

: Joel Lipman; MySQL; 03 February 2013; Hits: 5170

What?
This is an article on how to replace a string with another in all your joomla articles without modifying other data such as modified date and author.

Why?
I used to have a third-party plugin that would replace my HTML tags <PRE> with <PRE CLASS="brush:php">. Not I want to switch these back.

How?
For demo purposes, I'm searching a column called introtext in a database table called MYTABLE_content. First let me find all the articles that need this change:

: Joel Lipman; MySQL; 01 February 2013; Hits: 19392

What?
So this is a quick article on how to install the mySQL add-on for Oracle SQL Developer v3.0.04.

Why?
I've used various mySQL administration tools to manage mySQL databases over the years, all freeware until someone expresses an interest in using it and then the supplier will implement restrictions and trial based versions. Oracle SQL Developer is currently free at time of print (01-FEB-2013).

I also need to use Oracle databases in my day job so the Oracle SQL developer is already a pre-approved software for our work computers. We have SQL Server Management Studio (SSMS) as well for our SQL Server instances but it wasn't practical to link this to mySQL databases.

How?

: Joel Lipman; MySQL; 11 January 2013; Hits: 5246

What?
So this is a quick article on how to delete from multiple tables in a mySQL database where we use some JOIN statements.

Why?
It is never recommended to delete from multiple tables and instead to use the system you were given. Given a Relational Database Management System (RDBMS) where deleting some erroneous rows in one table will cause errors because the existence of those rows is stored in another table, how do we delete from all the related tables as well?

How?

: Joel Lipman; MySQL; 13 December 2012; Hits: 7148

What?
In view of the fact that this error pops up in so many systems I take over, I have often gone down the wrong path following the red herring as it were when in fact the answer to this is very 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 apostrophes (') and double-quotes ("). But who can honestly build a system that thinks of everything from day one?

How?

: Joel Lipman; MySQL; 27 November 2012; Hits: 6538

What?
We have a specific timetabling system for academic institutions and all our staff/students follow academic week numbers as opposed to calendar week numbers.

Why?
The aim of this article is to quickly generate a calendar for a full academic year for referencing by staff/students.

How?

: Joel Lipman; MySQL; 27 April 2012; Hits: 5498

Given Data
Activities by employees with start dates and end dates in a mySQL database.

Objective

copy raw

User		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
--------------- ------- ------- ------- ------- ------- ------- ------- ---------
Me		09:00	07:30	08:00	07:00	06:00	00:00	02:00   36:00
Myself		07:30	07:30	07:30	07:30	07:00	00:00	00:00   37:00
I		03:45	03:30	03:00	03:30	00:00	00:00	00:00   23:00

User Mon Tue Wed Thu Fri Sat Sun Total
--------------- ------- ------- ------- ------- ------- ------- ------- ---------
Me 09:00 07:30 08:00 07:00 06:00 00:00 02:00 36:00
Myself 07:30 07:30 07:30 07:30 07:00 00:00 00:00 37:00
I 03:45 03:30 03:00 03:30 00:00 00:00 00:00 23:00

Category: MySQL :: Article: 416

: Joel Lipman; MySQL; 26 April 2012; Hits: 9510

What?
Week Ending Date has been requested a lot more frequently now. It's an odd one but the example below shows how to do this for when the week ends on Friday. Assuming it starts on the previous Saturday.

Why?
Problems with MySQL weeks always starting on Sunday means this isn't very useful. I have another system which starts on Monday and ends on the following Sunday. The solution below is for the opposite, where the week starts on the previous Saturday and ends on the last working day of the week.

The Solution

: Joel Lipman; MySQL; 02 January 2012; Hits: 8152

What?
I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room.

The Problem?
When I select specifying the statement "WHERE IN (c.RoomImages)", this is interpreted as a string and when converted to a number only retrieves the first value before the first comma. Consider the following, the first query is how MySQL interprets the query and the second is what I want it to do:

copy raw

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')

Category: MySQL :: Article: 408

: Joel Lipman; MySQL; 06 December 2011; Hits: 11346

Just putting a note as I have spent ages looking for a solution and getting it to work in my environment.

What?
Need to be able to omit HTML tags in certain fields of a mySQL database.

Why?
We are preparing to migrate old content to a new system. From a MediaWiki CMS to a SaaS called Service-Now. The previous interlinking between images could no longer be used.

: Joel Lipman; MySQL; 31 October 2011; Hits: 9724

Why?
I recently made a joomla module that displays the lastest members to signup. It goes a little further and counts activated accounts for the past day, week, month and year (the below examples count all accounts irrespective of being activated or not). It needs to pick up trends as well and compare for example todays, with yesterdays up to the same hour.

How?
I used to use a lot more PHP but since becoming an analyst, I do more at the database level now. What follows should be usable mySQL statements to get all the numbers:

: Joel Lipman; MySQL; 24 August 2011; Hits: 7953

Hopefully the title doesn't put you off but after much Googling and Bing-ing, I still couldn't figure out how to do this. Hopefully this article will help you more than my search engine skills do.

My Setup
Windows XP Workstation
...needs to open...
Excel 2007 SP2
...with ODBC to...
MySQL v5+
...hosting database...
ActivityLog
...contains activity, staffID, resourceID, start time, end time...

Report Specification
PivotTable Report
...resources in row (along the side)...
...staff in columns (along the top)...
...persondays in values (the number my bosses want - 7h 24m or 26640s is 1 person day)...
With date range as parameters
...ouch...
...and it was so easy up to here...