ORDER BY two clauses
guys, I have little question, but to make it more clear I wrote it with example of what i want on this pdf, http://www.easyhttp.net/files/query.pdf please advise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select returning more than I want
I am not sure why this is, perhaps some join I am doing wrong. I am getting
back 2 results, or records, identical to each other, where I only want one
record.
SELECT o.id, o.s_first_name, o.s_last_name, u.email
FROM orders AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN order_items AS i
ON (i.order_id = o.id)
INNER JOIN products AS p
ON (i.product_id = p.id)
WHERE o.status IN ('pre-order', 'delayed')
AND (o.delayed_ship_date = DATE_ADD(NOW(), INTERVAL 1 DAY)
OR o.delayed_ship_date = '-00-00')
AND o.authnet_failures 5
AND o.id NOT IN (0)
AND o.authnet_status IN ('empty', 'failed')
2 rows in set (0.02 sec)
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Fax: 313.557.5052
[EMAIL PROTECTED] Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.15-5.0.16 upgrade dependency failure
I have a happily working 5.0.15 installation on RHEL4, installed from the standard RPMs. I'm upgrading using rpm -Uvh to the new 5.0.16 release. -Server, -client and -devel packages install ok, but when I attempt to install -shared, I get a dependency failure on zlib. Of course I have zlib installed (lots of other things depend on it), and rpm says it is there, and it's up to date. I've downgraded to 5.0.15 again (as nothing works in PHP without -shared installed) and that installs without problems. What's changed in .16 to create this seemingly broken dependency? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select returning more than I want
- Original Message -
From: Scott Haneda [EMAIL PROTECTED]
To: MySql mysql@lists.mysql.com
Sent: Sunday, November 27, 2005 6:03 AM
Subject: Select returning more than I want
I am not sure why this is, perhaps some join I am doing wrong. I am
getting
back 2 results, or records, identical to each other, where I only want one
record.
SELECT o.id, o.s_first_name, o.s_last_name, u.email
FROM orders AS o
INNER JOIN users AS u
ON (o.user_id = u.id)
INNER JOIN order_items AS i
ON (i.order_id = o.id)
INNER JOIN products AS p
ON (i.product_id = p.id)
WHERE o.status IN ('pre-order', 'delayed')
AND (o.delayed_ship_date = DATE_ADD(NOW(), INTERVAL 1 DAY)
OR o.delayed_ship_date = '-00-00')
AND o.authnet_failures 5
AND o.id NOT IN (0)
AND o.authnet_status IN ('empty', 'failed')
Is it possible that one of your tables does not have a primary key defined
on it? That would be the most likely cause of the problem in my experience.
Check your table definitions and if any of the them is missing a PRIMARY KEY
clause, that's your likely culprit.
The other possibility that comes to mind is that one or more of your tables
has a multi-column key but that you are only joining on part of the key. In
that case, you could also get duplicate rows. Review each and every join and
make sure you are joining on all of the appropriate columns.
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY two clauses
Your PDF is not very clear at all to me. Is the first part trying to describe the original table and identify the columns? Or is is pseudo code of some kind? Is the table you present the table that the query will read or is it the expected result? Your example query has a WHERE clause that says selected = 1 but I don't see a column named Selected. What do you mean by positioning? If you provide a definition of the original table and ideally a few sample rows from it, and describe what you want the query to do a lot more clearly, someone might be able to help you but right now, I have no idea what you are trying to do. Rhino - Original Message - From: Jad Madi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 27, 2005 3:49 AM Subject: ORDER BY two clauses guys, I have little question, but to make it more clear I wrote it with example of what i want on this pdf, http://www.easyhttp.net/files/query.pdf please advise. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySQL databases on different computers
From: [EMAIL PROTECTED] Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri All you need is do is to setup a MySQL user (not a system user) account on B that your application on A can use. Use the GRANT statement to do this. I am also interested in this and I know that I need to create a MySQL user on the second database, but I don't know if it is possible to use a query like: select database1.users.address from database1, database2 where database1.users.username=database2.members.username; I kno this is possible if both databases are using the same database management system (on the same computer), but I don't know if it is possible to do it with 2 different systems. (But I think the client application will need to make 2 separate queries to each database.) Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.15-5.0.16 upgrade dependency failure
I've also experienced this. The 5.0.16 RPMs for RHEL3 worked fine on a RHEL3 box. As for the RHEL4, I even tried recompiling the source RPM's and still got the dependency error! I ended up using the generic RPM's as those installed fine. For me, it's a moot point anyway as 5.0.16 isn't stable enough for my app -- it crashes on subselects of the same table. I downgraded to 5.0.15. On 11/27/05, Marcus Bointon [EMAIL PROTECTED] wrote: I have a happily working 5.0.15 installation on RHEL4, installed from the standard RPMs. I'm upgrading using rpm -Uvh to the new 5.0.16 release. -Server, -client and -devel packages install ok, but when I attempt to install -shared, I get a dependency failure on zlib. Of course I have zlib installed (lots of other things depend on it), and rpm says it is there, and it's up to date. I've downgraded to 5.0.15 again (as nothing works in PHP without -shared installed) and that installs without problems. What's changed in .16 to create this seemingly broken dependency? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- 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]
Re: minimum processes at startup
On Fri, 25 Nov 2005, Gleb Paharenko wrote: Hello. I have mysql 3.23.55 without innodb and it has one process, with innodb it starts with 16 processes. Information about MySQL threads is available here: http://dev.mysql.com/doc/internals/en/threads.html CONNECT_PRIOR is the right value? You version of MySQL is very old, I strongly recommend you to upgrade. It was only for example. MySQL 4.* has only one(!) thread/process. Why? MySQL 5 has more again. regards -- Ondrej Koala Vacha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode
Mohsen wrote: But himself solved his problem. with : mysql_query(SET NAMES utf8); Even 4.0.x Wrong. I decided to prepare two different versions for my software: - A MySQL 4.0-friendly version using Romanizing method (Hats off to you, Ehsan) - A MySQL 4.1-compatible version. The code you mentioned belongs to the 2nd version. SET NAMES indicates what is in the SQL statements that the client sends. Thus, SET NAMES 'cp1251' tells the server future incoming messages from this client are in character set cp1251. It also specifies the character set for results that the server sends back to the client. (For example, it indicates what character set column values are if you use a SELECT statement.) MySQL Manual 4.1 - 10.3.6. Connection Character Sets and Collations. Kind Regards, Behzad - Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
The most frustrating thing
This has happened to me so many times I am finally going to ask. Why in the hell when I follow the directions given immediately after installing do I get an error? Here is what the thing says after installing (and I quote): PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/bin/mysqladmin -u root password 'new-password' /usr/local/bin/mysqladmin -u root -h blah.domain.ca password 'new-password' Ok, Ok, I'll do it! # mysqladmin -u root -h blah.domain.ca password 'password' This worked. # mysqladmin: connect to server at 'blah.domain.ca' failed error: 'Host 'localhost.domain.ca' is not allowed to connect to this MySQL server' Huh?? I'm using package 4.0.24 on OpenBSD 3.8. Grr. __ Find your next car at http://autos.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The most frustrating thing (correction)
--- Peter [EMAIL PROTECTED] wrote: This has happened to me so many times I am finally going to ask. Why in the hell when I follow the directions given immediately after installing do I get an error? Here is what the thing says after installing (and I quote): PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/bin/mysqladmin -u root password 'new-password' /usr/local/bin/mysqladmin -u root -h blah.domain.ca password 'new-password' Ok, Ok, I'll do it! # mysqladmin -u root -h blah.domain.ca password 'password' This worked. The above line should read: # mysqladmin -u root password 'password' # mysqladmin: connect to server at 'blah.domain.ca' failed error: 'Host 'localhost.domain.ca' is not allowed to connect to this MySQL server' The error was produced after trying this: # mysqladmin -u root -h blah.domain.ca password 'password' Huh?? I'm using package 4.0.24 on OpenBSD 3.8. Grr. __ Find your next car at http://autos.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How-to Juggle Two Databases . . .
I don't know if this is the best forum to ask this question, but I thought perhaps somebody here could at least point me in the right direction. I am looking to create an application (Win32 at least). This app will need the ability to write to a central database, but also be able to pull a snapshot of that database into itself for remote (i.e., can't access the database) operation. I anticipate that the local user will modify the local copy, then sync that copy to the server. Concurrently, I anticipate other users will be modifying the server copy and/or their own local copy. The question I have is how to keep all this going without causing data loss? I mean, I can't run an auto_increment because the record numbering on the remote and server copies would be in conflict. I also am not sure of the best way to re-sync. I once was on a project where we logged all database entries, as three servers operated without being able to directly share (long story). The logging was problematic to say the least. I would say that if it were possible to have all of the functionality of a database with the version control power of CVS (or subversion), that would be ideal. Any suggestions? Regards, Ben Wilson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie question: UPDATE
To those thinking of answering this question, There is additional information. For some reason, I found out that the version of PHPMyAdmin at my web hosting provider was missing the Insert data from a text file into the table feature found at the bottom of the page after clicking on the name of the table. I thought that the reason for this was because they were using an older version of PHPMyAdmin, so he was kind enough to download and install the recent stable version into a folder under my hosting account via ftp. He told me this copy was for me alone to use, since he could not upgrade it for the entire system. After I hung up with him, I launched the version of PHPMyAdmin he installed for me and went to look for the feature. Unfortunately, the feature was still not there! Is this feature special, an add on to PHPMyAdmin? Or should it be there with the standard installation? If it is special, where can it be found? Thank you for your help. Joe. -Original Message- From: Joe Herman [mailto:[EMAIL PROTECTED] Sent: Sunday, November 27, 2005 12:21 AM To: mysql@lists.mysql.com Subject: Newbie question: How to import data into a table? Hello folks, What is the best way to import an excel spreadsheet, or text file for that matter into a table? Is there a way to do this with PHPMyAdmin? Thank you so much for your kind help. Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL database design documentation
Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
- Original Message - From: Maurice van Peursem [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 27, 2005 6:33 PM Subject: MySQL database design documentation Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? For the most part, _any_ good database design book for _any_ decent relational database should do the job for you. That's because all (?) of the professional grade databases use the same SQL and the same normalization techniques to decide what columns belong in what tables and what primary and foreign keys should be used. Therefore, a good design book for DB2 or Oracle or Sybase would probably tell you almost exactly the same things as a good design book specifically intended for MySQL. You will still need to use the MySQL reference to help you with places where the MySQL syntax is slightly different than the syntax used by the other database but this really shouldn't happen too often. However, if you want a design book specifically written for MySQL, you may want to look at http://www.informit.com/articles/article.asp?p=30885rl=1. I should stress that I don't have this book, nor have I read it cover to cover. But the sample chapter on database design is pretty good, so, if the rest of the book is as good, you should come out okay. In fact, you may find that the sample chapter alone, which you can read online for free, may tell you everything you really want to know and save you the cost of the book. No guarantees on that but it's a starting point anyway. By the way, I have not seen any other MySQL Design books so there may be others that are better. The URL I've given you actually mentions some other books specifically for MySQL that may suit your personal learning style better. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 25/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
A couple of good links for databases. Database Design (quick and dirty, but gets the points across): http://www.geekgirls.com/menu_databases.htm - the from scratch side SQL: Basics: http://www.sqlcourse.com (you probably already know this stuff - but just in case. semi-Advanced: http://sqlcourse2.com (joins are specifically at http://sqlcourse2.com/joins.html). Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- life is a game... so have fun. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.15-5.0.16 upgrade dependency failure
The only times MySQL server interact directly with each other are: a) during replication b) NDB cluster operations c) FEDERATED tables Options b) and c) are available only in v5.0+. If what you want to do doesn't fall into one of those three categories, you will need to open a second connection in your code and send a second query to the second server. There is no other way around it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gary Richardson [EMAIL PROTECTED] wrote on 11/27/2005 11:48:19 AM: I've also experienced this. The 5.0.16 RPMs for RHEL3 worked fine on a RHEL3 box. As for the RHEL4, I even tried recompiling the source RPM's and still got the dependency error! I ended up using the generic RPM's as those installed fine. For me, it's a moot point anyway as 5.0.16 isn't stable enough for my app -- it crashes on subselects of the same table. I downgraded to 5.0.15. On 11/27/05, Marcus Bointon [EMAIL PROTECTED] wrote: I have a happily working 5.0.15 installation on RHEL4, installed from the standard RPMs. I'm upgrading using rpm -Uvh to the new 5.0.16 release. -Server, -client and -devel packages install ok, but when I attempt to install -shared, I get a dependency failure on zlib. Of course I have zlib installed (lots of other things depend on it), and rpm says it is there, and it's up to date. I've downgraded to 5.0.15 again (as nothing works in PHP without -shared installed) and that installs without problems. What's changed in .16 to create this seemingly broken dependency? Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gary. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL database design documentation
Relational Database Design Clearly Explained, Second Edition ISBN: 1558608206 The original edition was my first primer on relational databases. It was an excellent read. Ben Maurice van Peursem wrote: Hi, I'm relatively new to the database-scene. I've installed MySQL on Mac OSX 10.3, which was easy. I've installed Perl support for MySQL, which was suprisingly difficult. I've installed CocoaMySQL (http://cocoamysql.sourceforge.net/) to create, inspect and backup databases. And now I'm building my first database, and that is not as easy as I had hoped. I know that use of the 'JOIN' keyword can save me pages of Perl code, but how it works exactly is not yet clear to me. Therefore I'm looking for a book, or maybe other documentation (on the web?), that can point me in the right direction. More specifically, I'm looking for a book that explains how to design and build databases, with examples of the queries in MySQL. Most books describe how you install MySQL, and list the SQL commands, but this information I already have. Can any of you suggest to me some helpful learning material? Thanks, Maurice van Peursem The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Php is not working with MySQL
Hy I installed a Debian Sarge. Everything worked but I needed newer verison of mysql (4.1.11) and since I installed newer MySQL (from deb package) the php is not working with mysql. Please help. Joe - Win a Yahoo! Vespa NEW - Yahoo! Cars has 3 Vespa LX125s to be won Enter Now!
