RE: Help with SELECT statement for date range
SELECT * FROM table_name WHERE EndDate < now(); Is this what you need? -Original Message- From: Rob Sirota [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 4:12 AM To: [EMAIL PROTECTED] Subject: Help with SELECT statement for date range Hello, I am having a problem when doing a SELECT. Here is the scenerio: I have a table that has an event StartDate and EndDate, based on the current Date "NOW()" I need to know which records are currently active. Can anyone help with a quick SELECT statement? Thanks. = __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Ask Questions the Smart Way...
Maybe the weed has got me high right now, but here is something some of you on this list may find funny, others may find this useful. This is not spam, this is actually a good manual you should all read: http://catb.org/~esr/faqs/smart-questions.html Good Reading. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Books advice
Here are two other books that I would recommend, specially the first one: * SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by Michael J. Hernandez, John L. Viescas Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko -Original Message- From: Fawad Siddiqui [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 5:06 PM To: [EMAIL PROTECTED] Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT Searching
I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative.
I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query:
SELECT p2c.categories_id, p.products_id, pd.products_name,
p.products_quantity, p.products_image, p.products_bimage,
p.products_price, p.products_date_added, p.products_last_modified,
p.products_date_available, p.products_status
FROM products p, products_description pd, products_to_categories p2c
WHERE MATCH(p.products_model,pd.products_name,pd.products_description)
AGAINST('pumps')
AND p.products_id = pd.products_id
AND p.products_id = p2c.products_id
ORDER BY pd.products_name;
This is how my table structures look:
CREATE TABLE `products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(4) NOT NULL default '0',
`products_model` varchar(12) default NULL,
`products_image` varchar(64) default 'image_na.gif',
`products_bimage` varchar(64) default 'image_na.jpg',
`products_price` decimal(15,4) NOT NULL default '0.',
`products_date_added` datetime NOT NULL default '-00-00 00:00:00',
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(5,2) NOT NULL default '0.00',
`products_status` tinyint(1) NOT NULL default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) default NULL,
`products_ordered` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added` (`products_date_added`),
FULLTEXT KEY `products_model` (`products_model`)
) TYPE=MyISAM AUTO_INCREMENT=928 ;
CREATE TABLE `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(64) NOT NULL default '',
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`),
FULLTEXT KEY `products_name_2`
(`products_name`,`products_description`)
) TYPE=MyISAM AUTO_INCREMENT=928 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BOOK: MySQL Cookbook
Paul, I just picked up your book today: MySQL Cookbook and I just wanted to comment on how great it is. I've spent hours at the bookstore looking through mySQL books, and by far this is the best one I come across. I strongly recommend it to anybody--Beginners, Intermediate, or Advanced Users. MySQL Cookbook By Paul DuBois Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Multiple Tables for Categories & Products
TASK: I have to generate a report with all categories, subcategories and products in database. PROBLEM: I want to write one query that will return: 1. category 2. subcategory: determined by parent_id 3. products in each category or subcategory. I have four tables with the following structure: --- CREATE TABLE `categories` ( `categories_id` int(11) NOT NULL auto_increment, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, PRIMARY KEY (`categories_id`), ) INSERT INTO `categories` VALUES (1, 0, 1); INSERT INTO `categories` VALUES (2, 1, 1); INSERT INTO `categories` VALUES (3, 0, 2); INSERT INTO `categories` VALUES (4, 3, 1); CREATE TABLE `categories_description` ( `categories_id` int(11) NOT NULL default '0', `categories_name` varchar(32) NOT NULL default '', PRIMARY KEY (`categories_id`), ) INSERT INTO `categories_description` VALUES (1, 'Sedans'); INSERT INTO `categories_description` VALUES (2, 'Honda'); INSERT INTO `categories_description` VALUES (3, 'Luxury'); INSERT INTO `categories_description` VALUES (4, 'Mercedez'); CREATE TABLE `products_to_categories` ( `products_id` int(11) NOT NULL default '0', `categories_id` int(11) NOT NULL default '0', PRIMARY KEY (`products_id`,`categories_id`) ) INSERT INTO `products_to_categories` VALUES (100, 1); INSERT INTO `products_to_categories` VALUES (101, 2); INSERT INTO `products_to_categories` VALUES (102, 4); INSERT INTO `products_to_categories` VALUES (103, 4); CREATE TABLE `products` ( `products_id` int(11) NOT NULL auto_increment, `products_model` varchar(12) default NULL, `products_image` varchar(64) default 'image_na.gif', `products_price` decimal(15,4) NOT NULL default '0.', PRIMARY KEY (`products_id`), ) INSERT INTO `products` VALUES (100, 'Civic', 'civic.jpg', '15'); INSERT INTO `products` VALUES (101, 'Accord', 'accord.jpg', '25'); INSERT INTO `products` VALUES (102, 'S500', 's500.jpg', '6'); INSERT INTO `products` VALUES (103, 'S600', 's600.jpg', '9'); --- I can do this with multiple queries, but to be more efficient can I do this with one query? Your help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pulling large Microsoft SQL files into MySQL
LOAD DATA INFILE "data.txt" INTO TABLE table_name FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'; You can find more on this at: http://www.mysql.com/doc/en/LOAD_DATA.html -Original Message- From: Rudolf Bekker [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 2:43 AM To: [EMAIL PROTECTED] Subject: FW: Pulling large Microsoft SQL files into MySQL Importance: High -Original Message- From: Rudolf Bekker Sent: Monday, 14 July 2003 14:13 To: MySQL MailList ([EMAIL PROTECTED]) Subject: Pulling large Microsoft SQL files into MySQL Importance: High The MySQL manual mentions that one could specify the column and value separators and the end of file marker explicitly in the LOAD DATA statement. I'm looking for the syntax of this statement to import large data files (.txt) originally exported from Microsoft SQL 7. Example file:. "Extract_Date"|"LAU"|"Customer_Number"|"BAN_Number"|"Telephone_Number"|" Bill_Date"|"Local_Usage_Credit_Amount"|"Local_Usage_Credit_Call_Count"|" Local_Usage_Credit_Call_Seconds"|"National_Usage_Credit_Amount"|"Nationa l_Usage_Credit_Call_Count"|"National_Usage_Credit_Call_Seconds"|"Interna tional_Usage_Credit_Amount"|"International_Usage_Credit_Call_Count"|"Int ernational_Usage_Credit_Call_Seconds"|"Cell_Usage_Credit_Amount"|"Cell_U sage_Credit_Call_Count"|"Cell_Usage_Credit_Call_Seconds"|"Other_Usage_Cr edit_Amount"|"Other_Usage_Credit_Call_Count"|"Other_Usage_Credit_Call_Se conds" "20030409"|"63"|"63000233"|"630002330001"|"--"|"200211"|"0"|"0"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"-555.94"|"0"|"0" "20030409"|"63"|"63000233"|"630002330001"|"--"|"200302"|"0"|"0"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"-594.24"|"0"|"0" "20030409"|"63"|"63000233"|"630002330001"|"--"|"200303"|"0"|"0"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"-615.21"|"0"|"0" "20030409"|"63"|"63000235"|"630002350001"|"0539480616"|"200201"|"-10.53" |"-14"|"-2346"|"-4.89"|"-6"|"-534"|"-1.3"|"-1"|"-26"|"-14.82"|"-4"|"-570 "|"0"|"0"|"0" "20030409"|"63"|"63000283"|"630002830001"|"0539480627"|"200111"|"-6.14"| "-1"|"-7804"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000283"|"630002830001"|"0539480627"|"200201"|"-140.79 "|"-143"|"-101519"|"-68.16"|"-43"|"-9723"|"0"|"0"|"0"|"-5.68"|"-4"|"-240 "|"0"|"0"|"0" "20030409"|"63"|"63000421"|"630004210001"|"05393621802"|"200303"|"0"|"0" |"0"|"-128.27"|"-22"|"-4680"|"0"|"0"|"0"|"-39.65"|"-6"|"-840"|"0"|"0"|"0 " "20030409"|"63"|"63000459"|"630004590001"|"0539481533"|"200302"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"-.01"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000495"|"630004950001"|"0539490419"|"200302"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"-.02"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000519"|"630005190001"|"0539490719"|"200302"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"-.01"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000521"|"630005210001"|"0539823312"|"200111"|"0"|"0"| "0"|"-12.29"|"-5"|"-2100"|"-13.5"|"-1"|"-180"|"-264.15"|"-63"|"-17340"|" -34.15"|"-19"|"-6360" I need to specify the text separator as " " and the column separator as |. Thanx, Rudolf. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pulling large Microsoft SQL files into MySQL
LOAD DATA INFILE "data.txt" INTO TABLE table_name FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'; You can find more on this at: http://www.mysql.com/doc/en/LOAD_DATA.html -Original Message- From: Rudolf Bekker [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 5:13 AM To: [EMAIL PROTECTED] Subject: Pulling large Microsoft SQL files into MySQL Importance: High The MySQL manual mentions that one could specify the column and value separators and the end of file marker explicitly in the LOAD DATA statement. I'm looking for the syntax of this statement to import large data files (.txt) originally exported from Microsoft SQL 7. Example file:. "Extract_Date"|"LAU"|"Customer_Number"|"BAN_Number"|"Telephone_Number"|" Bill_Date"|"Local_Usage_Credit_Amount"|"Local_Usage_Credit_Call_Count"|" Local_Usage_Credit_Call_Seconds"|"National_Usage_Credit_Amount"|"Nationa l_Usage_Credit_Call_Count"|"National_Usage_Credit_Call_Seconds"|"Interna tional_Usage_Credit_Amount"|"International_Usage_Credit_Call_Count"|"Int ernational_Usage_Credit_Call_Seconds"|"Cell_Usage_Credit_Amount"|"Cell_U sage_Credit_Call_Count"|"Cell_Usage_Credit_Call_Seconds"|"Other_Usage_Cr edit_Amount"|"Other_Usage_Credit_Call_Count"|"Other_Usage_Credit_Call_Se conds" "20030409"|"63"|"63000233"|"630002330001"|"--"|"200211"|"0"|"0"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"-555.94"|"0"|"0" "20030409"|"63"|"63000233"|"630002330001"|"--"|"200302"|"0"|"0"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"-594.24"|"0"|"0" "20030409"|"63"|"63000233"|"630002330001"|"--"|"200303"|"0"|"0"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"-615.21"|"0"|"0" "20030409"|"63"|"63000235"|"630002350001"|"0539480616"|"200201"|"-10.53" |"-14"|"-2346"|"-4.89"|"-6"|"-534"|"-1.3"|"-1"|"-26"|"-14.82"|"-4"|"-570 "|"0"|"0"|"0" "20030409"|"63"|"63000283"|"630002830001"|"0539480627"|"200111"|"-6.14"| "-1"|"-7804"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000283"|"630002830001"|"0539480627"|"200201"|"-140.79 "|"-143"|"-101519"|"-68.16"|"-43"|"-9723"|"0"|"0"|"0"|"-5.68"|"-4"|"-240 "|"0"|"0"|"0" "20030409"|"63"|"63000421"|"630004210001"|"05393621802"|"200303"|"0"|"0" |"0"|"-128.27"|"-22"|"-4680"|"0"|"0"|"0"|"-39.65"|"-6"|"-840"|"0"|"0"|"0 " "20030409"|"63"|"63000459"|"630004590001"|"0539481533"|"200302"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"-.01"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000495"|"630004950001"|"0539490419"|"200302"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"-.02"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000519"|"630005190001"|"0539490719"|"200302"|"0"|"0"| "0"|"0"|"0"|"0"|"0"|"0"|"0"|"-.01"|"0"|"0"|"0"|"0"|"0" "20030409"|"63"|"63000521"|"630005210001"|"0539823312"|"200111"|"0"|"0"| "0"|"-12.29"|"-5"|"-2100"|"-13.5"|"-1"|"-180"|"-264.15"|"-63"|"-17340"|" -34.15"|"-19"|"-6360" I need to specify the text separator as " " and the column separator as |. Thanx, Rudolf. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Good Database Philosophy Book?
Joe Celko's SQL for Smarties: Advanced SQL Programming By Joe Celko -Original Message- From: David Thompson [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 4:21 AM To: 'MySQL Mailing List' Subject: Good Database Philosophy Book? Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideasthanks... Regards, David Thompson President Provoix Corporation - Business Web Solutions www.provoix.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
