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:
copyraw
SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')
  1.  SELECT value FROM my_table WHERE my_id IN ('1, 2, 3') 
  2.  SELECT value FROM my_table WHERE my_id IN ('1', '2', '3') 

Category: MySQL :: Article: 408

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.


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:


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...


Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.

The current structure looks similar to this:

copyraw
ID        SetID            WeekNumber             StartDate
--------- ---------------- ---------------------- ----------------------------
1         2011/2012        1                      2011-07-18 00:00:00
...
52        2011/2012        52                     2012-07-09 00:00:00
  1.  ID        SetID            WeekNumber             StartDate 
  2.  --------- ---------------- ---------------------- ---------------------------- 
  3.  1         2011/2012        1                      2011-07-18 00:00:00 
  4.  ... 
  5.  52        2011/2012        52                     2012-07-09 00:00:00 

Joe you're an idiot!
You might say to me why not run the CMIS Facility application and add a new set, it will put these dates in automatically.

Herein lies the problem
The reason I'm doing this is for another system which decided to "cleverly" use the exports from CMIS Facility so that all the weeks correspond to the rest of the academic data. Unfortunately the developer wrote a system he felt would last the rest of his PhD degree, it's a shame he started 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 was a Saturday. The developer felt that as long as you adjust the script each year you could make the system last another year. Shame he also forgot the academic year ends halfway in a normal people's calendar, so you actually have to adjust this twice a year.

Category: MySQL :: Article: 342

The Why
So I find myself writing increasingly complex SQL scripts and it's at the stage where we need to optimize the queries because some scripts are noticeably slow (as observed by the customer...) and then others not.

The What
I'm going to run these benchmark tests against a system that is both up and running via the front-end and back-end. It's MediaWiki CMS used by Wikipedia.org and the like. I like queries against this database because it involves linking a lot of tables and outputting... just articles and their titles.

I have another table holding the audit trail of content approvers on the system. Approvers can e-sign an article (approving it) by clicking on a button. We want to bring back the articles that aren't listed in the audit table (articles yet to be approved).


In standard SQL we can combine the contents of two tables with a CROSS JOIN, (BTW these are not instructions to create some table I'll never use again unlike the rest of the solutions I found on the web). In MySQL, I only know how to do this with a UNION ALL clause.

Scenario:
I have a Joomla module which should take the keywords from a specified number of different but similar tables. I'm looking to query the Title and the Introductions of any valid articles from BOTH tables and return one table with everything I want.

Well I tried the SQL Server Management Studio solution to connect to a MySQL database then lost my way wondering what was I trying to achieve?

I've written this article because this is how I connected to a MySQL database from within the Business Intelligence Development Studio (BIDS) IDE from Microsoft.

Required:
  • Windows XP (ODBC Data Source Administrator)
  • MS Business Intelligence Development Studio 2008
  • Test/Sample MySQL Database to connect to (server and username + password).
  • Admin Access to the reporting server if you plan on deploying the report to it.
Scenario:
  • We want to report on a MySQL database
  • MySQL login information is a user who exists as a MySQL user and a server user.
  • Reporting server is remote as well as the MySQL database.
  • BIDS is on the client machine.


Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.

One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).


The scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across the tables of the local database which had valid content to extract keywords from.

The following is a MYSQL query that displays the structure of all the columns in all the databases of the localhost:
copyraw
SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME
  1.  SELECT * FROM information_schema.COLUMNS ORDER BY TABLE_NAME, COLUMN_NAME 


The following is a MYSQL query that finds all columns (displayed as "tablename.columnname") that had the data_type TEXT across all databases:
copyraw
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME
  1.  SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' ORDER BY TABLE_NAME, COLUMN_NAME 


The following is a MYSQL query that finds all columns that had the data_type TEXT across a specified database (eg. sample_db):
copyraw
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME
  1.  SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS value FROM information_schema.COLUMNS WHERE DATA_TYPE='text' AND TABLE_SCHEMA='sample_db' ORDER BY TABLE_NAME, COLUMN_NAME 


Note that the user launching this SQL query would need the SELECT privilege as a minimum on "information_schema" (arguable).
Category: MySQL :: Article: 305

Ok is it just me who does everything slowly and badly until someone comes along and says why are you doing it like that?  My justification is that the job has to be done no matter what.

If you've ever sat there with phpMyAdmin or a MySQL Administration Tool (like Navicat or SQLYog), and still there at night editing each field so that it displays correctly... well that's usually me.

Found it a bit boring but here's a short bit of code to speed it up which I now use:

copyraw
update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace');
  1.  update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace')

Source: http://www.mediacollege.com/computer/database/mysql/find-replace.html

 

Category: MySQL :: Article: 248

Suppose you have a column in your table that you use as a counter (storing the value of the counter - eg. times an article has been displayed).

Basically what I used to do is something similar to the following:

  1. SELECT counter_field_value FROM table1 WHERE column1='this_article'
  2. Add 1 to counter_field_value
  3. UPDATE table1 SET counter_field_value=<new_counter_field_value> WHERE column1='this_article'

Combined with a PHP script this could be a few lines for something really small.

 

The quick trick to this is to do it all in one query:

copyraw
UPDATE table1 SET counter_field_value=counter_field_value+1 WHERE column1='this_article'
  1.  UPDATE table1 SET counter_field_value=counter_field_value+1 WHERE column1='this_article' 
Category: MySQL :: Article: 243

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

parameter   server   files   error   find   case   joomla   time   creator   windows   database   google   form   user   where   script   create   data   code   using   deluge   note   list   website   version   client   value   following   need   work   first   added   source   mysql   name   would   table   file   system   zoho   date   used   order   page   license   display   function   report   uploaded   field   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.