Re: arbitrary ORDER BY
Peter Brawley wrote:
Steve,
Look for 'FIELD(' at
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html.
PB
Thanks, Pete!
For posterity, this is how I solved the problem:
To create an abitrary for the ORDER BY clause, create a field like this:
SELECT
FIELD( field,
arbitrary sort string 2,
arbitrary sort string 3,
arbitrary sort string 1
) as sort_order
FROM table
ORDER BY sort_order
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
arbitrary ORDER BY
Hey folks - I have a project where I need to sort rows by an arbitrary order. I seem to recall at some point where I was able to do some SQL like: ORDER BY type = the first type, type = another type, type = yes another arbitrary type ... However, my arbitrary ORDER BY statements don't seem to be working on MySQL 5.0.16. Am I remembering correctly? I can't seem to find anything in the MySQL manual. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird characters from mysqldump?
Actually, it came from Mysql 4.1.1 (not 4.1.3 as I mentioned earlier). When I do select queries, the data displays just fine, but when I dump, I get this garbage. Jeremy Cole wrote: Hi Steve, I'm trying to export data from mysql 4.1.3 with mysqldump. I'm getting weird characters from the system. Here's what I've discovered so far: ' becomes ^À^Ù, e.g. didn't becomes didnâ^À^Ùt - becomes â^À^Ó, e.g. 1-2 becomes 1â^À^Ó2 è becomes è, e.g. Entrèe becomes Entrèe What gives? Looks like an application of the GIGO (Garbage In, Garbage Out) principle to me. The above data wouldn't have happened to come from a Microsoft Office application, would it? Looks like Smart Quotes etc., strikes again, to me. Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
weird characters from mysqldump?
Hey folks -- I'm trying to export data from mysql 4.1.3 with mysqldump. I'm getting weird characters from the system. Here's what I've discovered so far: ' becomes ^À^Ù, e.g. didn't becomes didnâ^À^Ùt - becomes â^À^Ó, e.g. 1-2 becomes 1â^À^Ó2 è becomes è, e.g. Entrèe becomes Entrèe What gives? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
foreign key design stratgies
Hey folks -
I'm having a little problem understanding ON DELETE foreign key
constraints. Here's my options from the manual:
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
When do I use each one? I can find anything in the online manual that
suggests how to construct a database using this.
Let's take two examples, and here are my guesses:
1. A simple accounting database, with a clients, invoices, and
line_items table.
My guess is that you would give invoices a foreign key in clients with
ON DELETE CASCADE, meaning that when you delete a client record, it
deletes its children in the invoices table. Also, you would give
line_items a foreign key in the invoices table with ON DELETE CASCADE,
so that deleting an invoice also causes its children in the line items
table to be deleted. Is this right?
2. A class scheduling program, with a table of students, classes, and a
'merge' table called registration. That way we can have a many-to-many
relationship, where one student has many classes, and one class has many
students. The registration table would have only two foreign keys, and
an index making them unique.
Hm, I seem to only want to have parent rows delete their children. When
would I use these other key restraint types?
Steve Lefevre
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks and SQL Syntax help
First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INSERT confirmation? PHP
I'm designing a user database for PHP 4 and MySQL 3.32 or whatever. Users enter a class and it's workshops for their students. The 'Class' table has fields 'Name' and 'ID'. ID is the primary key and its an auto-incremented integer The 'Workshop' table has a field ID, Number, ClassID, Date, etc. The 'ClassID' should be the value of the ID field of its parent class. After I use an insert to create the Class data, how do I get the appropriate ClassID for the 'Workshop' table inserts? I could assume to use the very latest one, but that *might* break, as this is a multi-user database. Is there a way I can issue an insert statement, and get a return of the ID value it got? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
