MySQL

How do I get the domain name from a full URL in MySQL?

I too had that same question friend. It is actually quite simple thankfully!

This little snippet should do the trick for you:

SELECT count(visit_id) as total_visits,
SUBSTRING_INDEX(TRIM(leading "http://" from TRIM(url_column_from_table)),'/',1) as url_visited
FROM url_visits
GROUP BY 1;

Tags:

Thursday, August 11th, 2011 MySQL No Comments

Multi Table Delete Syntax for MySQL

Today I needed to do a little clean-up in one of my applications that I re-vamped. There were several tables that had entries that were no longer relevant to the code base so I went about deleting them and any entries that were dependent upon them. It would have been a huge pain to have to go through each table and figure out which entries were associated, so luckily MySQL provides a simple solution. I did have to mess around with this a bit before it would work, mainly due to my prepending the database name in front of the table, ‘db_name.tb_name’, and for some reason that was not well liked… So I instead specified the database I wanted to execute this query on, and removed the database name and it worked like a charm. I needed to remove all references to a module that was no longer within the framework.

The actual query is the following:

DELETE aa, ac, am, aua, aga, ausp, agsp, asp, mi, a
FROM acl_action AS aa
INNER JOIN acl_controller AS ac ON (ac.acl_controller_id = aa.acl_controller_id)
INNER JOIN acl_module AS am ON (am.acl_module_id = ac.acl_module_id)
INNER JOIN acl AS a ON (a.acl_action_id = aa.acl_action_id)
LEFT JOIN acl_user_acl AS aua ON (aua.acl_id = a.acl_id)
LEFT JOIN acl_group_acl AS aga ON (aga.acl_id = a.acl_id)
LEFT JOIN acl_subprivilege As asp ON (asp.acl_id = a.acl_id)
LEFT JOIN acl_group_subprivilege AS agsp ON (agsp.acl_subprivilege_id = asp.acl_subprivilege_id)
LEFT JOIN acl_user_subprivilege AS ausp ON (ausp.acl_subprivilege_id = asp.acl_subprivilege_id)
LEFT JOIN menu_item AS mi ON (mi.acl_id = a.acl_id)
WHERE am.acl_module_id = 1;
Friday, June 10th, 2011 MySQL No Comments

Finding the Week of the Month using MySQL

Recently for a project I needed to be able to query when something was at least a day old and wanted to make sure that I updated the item every month on the first wednesday. I wanted to utilize mysql to query those that I needed and was able to find another blog’s entry on how to do that – the following is the code needed to retrieve the day of the week for the specified date:

SELECT WEEK(my_date_field,5) -
WEEK(DATE_SUB(my_date_field, INTERVAL DAYOFMONTH(my_date_field)-1 DAY),5)+1

Thanks go to:

http://think-robot.com/2009/04/week-of-the-month-in-mysql/

Tuesday, March 2nd, 2010 MySQL No Comments

MYSQL Shorthand If/Else Statement

I seem to forget this frequently and it is quite useful, so what better place to put it than here?

The IF() function in mysql is similiar to the PHP shorthand if statement:

PHP: ($var) ? ‘Var is true’ : ‘Var is false’;
MySQL: IF(COLUMN > 1,true,false)

SELECT count(U.id) as Active,
SUM(IF(US2.id IS NOT NULL,1,0) ) as Canceled
FROM User
WHERE DateAdded BETWEEN '2009-09-01' AND '2009-09-02' 

MySQL IF() Function Documentation

Wednesday, December 2nd, 2009 MySQL 1 Comment

MySQL Find and Replace Query

Something I frequently find myself having to do is using a find and replace query to change out some text inside of entries inside a database table. So I wanted to throw the syntax on the blog!

UPDATE `YOUR_DATABASE_TABLE`
SET `YOUR_FIELD_NAME` = REPLACE(`YOUR_FIELD_NAME`, 'find this', 'replace with this')

Works like a charm!

Wednesday, November 11th, 2009 MySQL No Comments
[ad code=1 align=center]