MySQL
March 31, 2015
Some sample OS Ticket SQL
UPDATE ost_ticket SET status = 'open' WHERE staff_id = 1 AND status = 'closed' AND ticket_id IN (SELECT DISTINCT ticket_id FROM ost_ticket_event WHERE staff = 'libper' AND state = 'closed' )
Posted by pj at 04:17 PM | Comments (0)
March 29, 2009
Getting MySQL-python-1.2.2 to work with XAMPP
I'm trying to get the Python MySQLdb library to talk to my XAMMP MySQL. Here's how:
1. Before you build the db adaptor, change the site.cfg file to point to XAMMP's mysql_config
# The path to mysql_config. # Only use this if mysql_config is not on your PATH, or you have some weird # setup that requires it. mysql_config = /Applications/xampp/xamppfiles/bin/mysql_config
2. Link the dylib
cp /Applications/xampp/xamppfiles/lib/mysql/libmysqlclient.15.dylib /usr/local/mysql/lib/mysql/libmysqlclient_r.15.dylib mkdir /Applications/xampp/xamppfiles/include ln -s /usr/local/mysql-5.1.32-osx10.5-powerpc/include /Applications/xampp/xamppfiles/include/mysql
3. You have to point your script to the localhost using the machines acutally IP address as if it was a remote server.
Posted by pj at 11:59 AM | Comments (0)
November 21, 2007
SQL for checking event overlap
$sql = sprintf("select d.diary_entry_id,
from_unixtime(d.start_date) as start_time,
from_unixtime(d.end_date) as end_time,
concat(u.forename,' ',u.surname) as user_name
from diary_entry d, diary_user_entry due, user u, diary_entry_status des where (
(d.start_date between %s and %s) or
(d.end_date between %s and %s) or
((%s between d.start_date and d.end_date) and (%s between d.start_date and d.end_date)) or
((d.start_date between %s and %s) and (d.end_date between %s and %s))
)
and d.diary_entry_id = due.diary_entry_id
and due.user_id in(%s)
and due.end_date is null and due.user_id = u.user_id and due.accepted_flag = 1
and d.diary_entry_id = des.diary_entry_id and des.end_date is null
and des.diary_entry_status_type_id != 3 order by u.surname",
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$r['user_id']);
Errmmm.. Except that..
if(($r['date_absent_from']) && ($r['to_date'])){
$where = array();
$where[] = sprintf("(
(%d <= (a.date_absent_from + 86400)) and (%d >= a.date_absent_from)
)", $r['date_absent_from'], $r['to_date']);
}
Posted by pj at 10:49 AM | Comments (0)
May 10, 2007
LEFT JOINS and MySQL 5+
I've had some problems porting SQL which works fine on MySQL 4+ to version 5. It is more picky about your left join syntax and any SQL you have which uses them will break if moved to 5 unless you do the folowing:
The column you want to do your first LEFT JOIN on needs to be LAST in your FROM statement list:
FROM diary_entry_type det, diary_user_entry due, user u, diary_location dl, diary_entry_status des, diary_entry_status_type dest, diary_entry d LEFT JOIN diary_entry deto ON(deto.master_diary_entry_id = d.diary_entry_id AND deto.diary_entry_type_id = 3)
Will work.
FROM diary_entry d, diary_entry_type det, diary_user_entry due, user u, diary_location dl, diary_entry_status des, diary_entry_status_type dest LEFT JOIN diary_entry deto ON(deto.master_diary_entry_id = d.diary_entry_id AND deto.diary_entry_type_id = 3)
Will not. However you can also use brackets thus:
FROM (diary_entry d, diary_entry_type det, diary_user_entry due, user u, diary_location dl, diary_entry_status des, diary_entry_status_type dest) LEFT JOIN diary_entry deto ON(deto.master_diary_entry_id = d.diary_entry_id AND deto.diary_entry_type_id = 3)
Posted by pj at 12:59 PM | Comments (0)
April 18, 2007
MySQL and Unicode
MySQL AB :: Unicode and Other Funny Characters
Also:
10.3. Specifying Character Sets and Collations
Posted by pj at 10:56 AM | Comments (0)
September 19, 2006
Safe backups and copying for MySQL
O'Reilly Network: Data Protection for LAMP Applications
Posted by pj at 12:15 PM | Comments (0)
September 23, 2005
First crack at finance database LDS
-- phpMyAdmin SQL Dump
-- version 2.6.4-pl1
-- http://www.phpmyadmin.net
-- Host: localhost
-- Generation Time: Sep 23, 2005 at 08:32 PM
-- Server version: 3.23.56
-- PHP Version: 4.4.0
-- Database: `fwl_finance`
--
-- Table structure for table `category`
--
CREATE TABLE category (
category_id int(10) NOT NULL auto_increment,
category_label text NOT NULL,
category_description text,
category_is_parent tinyint(1) NOT NULL default '0',
category_parent_id int(10) NOT NULL default '0',
PRIMARY KEY (category_id)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
--
-- Table structure for table `recipient`
--
CREATE TABLE recipient (
recipient_id int(10) NOT NULL auto_increment,
rec_organization_name text,
rec_person_title enum('Mr','Mrs','Miss','Ms','Dr','Professor') NOT NULL default 'Dr',
rec_person_first_name varchar(255) NOT NULL default '',
rec_person_last_name varchar(255) NOT NULL default '',
rec_contact_address text NOT NULL,
rec_email varchar(255) NOT NULL default '',
PRIMARY KEY (recipient_id)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
--
-- Table structure for table `transaction`
--
CREATE TABLE transaction (
trans_id int(10) NOT NULL auto_increment,
trans_record_created_date timestamp(14) NOT NULL,
trans_record_last_modified datetime NOT NULL default '0000-00-00 00:00:00',
trans_date datetime NOT NULL default '0000-00-00 00:00:00',
trans_category_id int(10) NOT NULL default '0',
trans_amount_in int(10) NOT NULL default '0',
trans_amount_out int(10) NOT NULL default '0',
trans_payment_type enum('Invoice','FBA credit card','Cash','Cheque') NOT NULL default 'Invoice',
trans_date_invoiced datetime default '0000-00-00 00:00:00',
trans_date_paid datetime default '0000-00-00 00:00:00',
trans_payment_recipient_id int(10) default '0',
PRIMARY KEY (trans_id),
KEY trans_category_id (trans_category_id,trans_amount_in,trans_amount_out)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
--
-- Table structure for table `event`
--
CREATE TABLE `event` (
`event_id` int(10) NOT NULL auto_increment,
`event_title` text NOT NULL,
`event_description` text NOT NULL,
`event_start_date` date NOT NULL default '0000-00-00',
`event_end_date` date NOT NULL default '0000-00-00',
`event_record_created_date` timestamp(14) NOT NULL,
`event_type` enum('meeting','conference','training','item purchase','hospitality') NOT NULL default 'meeting',
PRIMARY KEY (`event_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
Posted by pj at 08:33 PM
September 12, 2005
Starting MySQL with max_allowed_packet size setting
mysqld_safe --max_allowed_packet=1012MB
Sets to a GB. Onyl works in MySQL 4.0 or greater.
Posted by pj at 09:42 AM