What?
This article is to remind me how to create a blank weekly timesheet which reads the duration of events from a database and auto-completes your timesheet.

Why?
I'm being tasked to work with EPM (Microsoft Enterprise Project Management) more and more. Similar systems have popped out that support some form of time recording and activity logging. The example below however is within a LAMP/MySQL environment but the SQL basics are here to help me adapt it to whatever environment people keep throwing at me.

What I want:
copyraw
ThisDate    ThisDay     StartTime  TimeOut  TimeIn  EndTime  TotalTimeToday TotalTimeWeek
----------- ----------- ---------- -------- ------- -------- -------------- -------------
2013-12-02  Monday      09:00      12:00    13:00   17:00    7:00           7:00
2013-12-03  Tuesday     08:45      12:00    13:30   17:45    7:30           14:30
2013-12-04  Wednesday   09:00      12:30    13:30   17:00    7:00           21:30
2013-12-05  Thursday    10:00      12:15    12:45   17:15    7:45           29:15
2013-12-06  Friday      07:00      12:00    13:00   16:30    8:30           37:45
2013-12-07  Saturday    -          -        -       -        0:00           37:45
2013-12-08  Sunday      03:00      04:00    -       -        4:00           41:45
  1.  ThisDate    ThisDay     StartTime  TimeOut  TimeIn  EndTime  TotalTimeToday TotalTimeWeek 
  2.  ----------- ----------- ---------- -------- ------- -------- -------------- ------------- 
  3.  2013-12-02  Monday      09:00      12:00    13:00   17:00    7:00           7:00 
  4.  2013-12-03  Tuesday     08:45      12:00    13:30   17:45    7:30           14:30 
  5.  2013-12-04  Wednesday   09:00      12:30    13:30   17:00    7:00           21:30 
  6.  2013-12-05  Thursday    10:00      12:15    12:45   17:15    7:45           29:15 
  7.  2013-12-06  Friday      07:00      12:00    13:00   16:30    8:30           37:45 
  8.  2013-12-07  Saturday    -          -        -       -        0:00           37:45 
  9.  2013-12-08  Sunday      03:00      04:00    -       -        4:00           41:45 
Category: MySQL :: Article: 541

What?
Quick note on how to do this.

I was tasked with cleaning up an english database by replacing all special alphabets (ë to e) and non-alphanumeric symbols with URL friendly characters.

How?
copyraw
-- return all records that contain non-alphanumeric characters
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$';

-- return all records that are non-alphanumeric but ignore underscores
SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$';
  1.  -- return all records that contain non-alphanumeric characters 
  2.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9]+$'
  3.   
  4.  -- return all records that are non-alphanumeric but ignore underscores 
  5.  SELECT * FROM myTable WHERE myColumn NOT REGEXP '^[A-Za-z0-9\\_]+$'
Category: MySQL :: Article: 521

What?
So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a column of relevance and sorts the rows accordingly.

How?
This has to be a real quick one for a dropdown search field which has to find relevant terms to autofill/autocomplete a search form:
copyraw
-- where @ThisSearch is a posted (and sanitized) variable

SET @ThisSearch:="Brains";

SELECT
        columnID,
        columnFullName,
        CASE
                WHEN columnFirstName LIKE @ThisSearch THEN 20
                WHEN columnFullName LIKE @ThisSearch THEN 10
                WHEN columnLastName LIKE @ThisSearch THEN 10
                WHEN columnFullName LIKE @ThisSearch THEN 1
        END as relevance
FROM
        myTable
WHERE
        s.columnPublished
  1.  -- where @ThisSearch is a posted (and sanitized) variable 
  2.   
  3.  SET @ThisSearch:="Brains"
  4.   
  5.  SELECT 
  6.          columnID, 
  7.          columnFullName, 
  8.          CASE 
  9.                  WHEN columnFirstName LIKE @ThisSearch THEN 20 
  10.                  WHEN columnFullName LIKE @ThisSearch THEN 10 
  11.                  WHEN columnLastName LIKE @ThisSearch THEN 10 
  12.                  WHEN columnFullName LIKE @ThisSearch THEN 1 
  13.          END as relevance 
  14.  FROM 
  15.          myTable 
  16.  WHERE 
  17.          s.columnPublished 
Category: MySQL :: Article: 516

What?
So this is an article to serve as documentation for one of the data sources available to download for FREE from this site.

Why?
It's useful to have a CSV list of all the IP ranges and their allocated country. Our use here was to create a refreshable country by IP address list that we can use when parsing our website logs to check where visitors were from.

Note: we also use and love Advanced Web Statistics (awstats) but this was so that you could have your own country lookup script.

The Download
Should contain the following:
  1. create_countriesipranges_table.sql: SQL to create a pre-populated table into a MySQL database.
  2. iana_ipv4_address_space_registry.csv: comma separated values list of all countries, regions and their respective IP ranges.
  3. ip_lookup.php: to use the database
  4. License.txt: Released under GNU/GPL
  5. ReadMe.txt
What is it?
I wanted my own IP lookup script to tell me the country that is allocated a given IP address, this is what I use.

The scripts in the folder you have downloaded are to create a lookup function using a MySQL database and a PHP script. As well as a CSV file equivalent.


How to use:

  1. Download from my site: «Download»
  2. Run the SQL script against a test database,
    1. Modify the table name to use if necessary.
    2. Uncomment the drop statement if running for the second time.
  3. Modify the PHP file to use database login credentials,
    1. $db_host = "localhost"; // Database host name
    2. $db_user = "my_database_user"; // User for database authentication
    3. $db_pass = "my_database_password"; // Password for database authentication
    4. $db_name = "my_database"; // Database name
    5. $db_table = "mytable_countryipranges";// Database table
  4. Upload the PHP file to your website
  5. Test by using a web browser to visit: http://www.mysite.com/ip_lookup.php?ipv4=123.123.123.123 where www.mysite.com is your website where you uploaded this script. // should return China

Any questions, suggestions?
We are looking to add the ability to generate this data using other sources and in time a region by IP for more accuracy.

Feel free to feedback. It's FREE to download unlike others which is probably why I made the generator in the first place but I do like to make people sign up so I can gauge the level of interest.

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?

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:

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?

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?

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?

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?

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

Objective
copyraw
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
  1.  User        Mon    Tue    Wed    Thu    Fri    Sat    Sun    Total 
  2.  --------------- ------- ------- ------- ------- ------- ------- ------- --------- 
  3.  Me        09:00    07:30    08:00    07:00    06:00    00:00    02:00   36:00 
  4.  Myself        07:30    07:30    07:30    07:30    07:00    00:00    00:00   37:00 
  5.  I        03:45    03:30    03:00    03:30    00:00    00:00    00:00   23:00 

Category: MySQL :: Article: 416

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

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

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