Re: where column
- Original Message - From: "Olav Mørkrid" <[EMAIL PROTECTED]> To: Sent: Friday, August 24, 2007 1:07 AM Subject: where column hello does anyone know what is returned when you do a where column without further parameters? SELECT * FROM TABLE WHERE COLUMN; for integer columns it seems to return non-zero columns, but for other types of columns the results seemed unpredictable. In my opinion, the statement should not execute at all since it isn't syntactically correct. In the dialects of SQL I have used - and I've been using SQL for a lot of years - simply saying "WHERE hiredate" (or whatever column name you want) is an incomplete statement since the column name must be followed by some kind of operator, such as =, <, >, LIKE, or whatever. Despite that, I am not up-to-date on MySQL and they may support an extension that lets you write SQL like that; in that case, the MySQL manual for your version should make it clear what happens if you write that. But I still think it should not execute at all. The WHERE clause is a filter to prevent rows that don't satisfy the condition from appearing in your result set; "WHERE columnname" is not a complete condition in my opinion so it simply should execute. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where column
hello does anyone know what is returned when you do a where column without further parameters? SELECT * FROM TABLE WHERE COLUMN; for integer columns it seems to return non-zero columns, but for other types of columns the results seemed unpredictable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ERROR on DELETE
Hi there ! Does anyone knows if there is a specific MySQL ERROR (SQLSATE) for DELETE of a not found key? I have tried MySQL Error 1032 e 1176 (SQLSTATE HY000) but it does not seems to work .. Thanks in advance Nilson
RE: Dynamic tables--always a bad idea?
We know that we won't need to do those sorts of queries except for statistical analysis which can happen offline (and for that we'll assemble the data back into a single table). Each table is for a specific user and there's no need to run queries across users (for this data). Doug -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, August 23, 2007 2:02 PM To: 'Douglas Pearson'; mysql@lists.mysql.com Subject: RE: Dynamic tables--always a bad idea? How are you going to do queries that join or merge thousands of tables? or won't that be necessary? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -Original Message- > From: Douglas Pearson [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 23, 2007 4:35 PM > To: mysql@lists.mysql.com > Subject: Dynamic tables--always a bad idea? > > We're trying to figure out how to design a particularly > critical table in > our database schema. The choices are to use a single large table or a > series of dynamically created small tables. > > This table will receive the majority of traffic (queries and > updates) in the > database so it's a key part of the design. The data set > means we're either > looking at 1 table with perhaps 10 million records or 100,000 > tables each > with about 100 records. > > "Standard" SQL theory seems to say we should use a single > table. It's more > flexible and some queries simply aren't possible across > multiple tables (or > at least not efficiently). But in this case we're happy to live with > reduced flexibility if it gives us substantially better performance. > > Early empirical testing with 100,000 records suggests the > single large table > becomes progressively slower to access as it grows in size > (average access > time goes from ~4ms/transaction up to around ~80ms for our > test cases--MySQL > 5.0 on CentOS). The multiple dynamic tables don't seem to have this > property--access remains pretty much constant as you might expect > (~4ms/transaction). > > So the question is, even given this 20x performance benefit > are we still > fools to consider the dynamic table model? Are we going to run into > max-tables or max-file-handle limits or other problems that > will eventually > bite us? Or is this speed difference just an artifact of > poor indexing > choices or similar? Or are dynamic tables OK sometimes? > > Doug > > P.S. Here's the table in question: > > CREATE TABLE one_big_table ( >rank bigint not null auto_increment unique, >item_idint not null, >user_idint not null, >count smallintnot null default 1, >added datetimenot null, >primary key(rank, user_id) > ) engine=InnoDB; > > > > -- > 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: Dynamic tables--always a bad idea?
How are you going to do queries that join or merge thousands of tables? or won't that be necessary? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -Original Message- > From: Douglas Pearson [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 23, 2007 4:35 PM > To: mysql@lists.mysql.com > Subject: Dynamic tables--always a bad idea? > > We're trying to figure out how to design a particularly > critical table in > our database schema. The choices are to use a single large table or a > series of dynamically created small tables. > > This table will receive the majority of traffic (queries and > updates) in the > database so it's a key part of the design. The data set > means we're either > looking at 1 table with perhaps 10 million records or 100,000 > tables each > with about 100 records. > > "Standard" SQL theory seems to say we should use a single > table. It's more > flexible and some queries simply aren't possible across > multiple tables (or > at least not efficiently). But in this case we're happy to live with > reduced flexibility if it gives us substantially better performance. > > Early empirical testing with 100,000 records suggests the > single large table > becomes progressively slower to access as it grows in size > (average access > time goes from ~4ms/transaction up to around ~80ms for our > test cases--MySQL > 5.0 on CentOS). The multiple dynamic tables don't seem to have this > property--access remains pretty much constant as you might expect > (~4ms/transaction). > > So the question is, even given this 20x performance benefit > are we still > fools to consider the dynamic table model? Are we going to run into > max-tables or max-file-handle limits or other problems that > will eventually > bite us? Or is this speed difference just an artifact of > poor indexing > choices or similar? Or are dynamic tables OK sometimes? > > Doug > > P.S. Here's the table in question: > > CREATE TABLE one_big_table ( >rank bigint not null auto_increment unique, >item_idint not null, >user_idint not null, >count smallintnot null default 1, >added datetimenot null, >primary key(rank, user_id) > ) engine=InnoDB; > > > > -- > 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]
Dynamic tables--always a bad idea?
We're trying to figure out how to design a particularly critical table in our database schema. The choices are to use a single large table or a series of dynamically created small tables. This table will receive the majority of traffic (queries and updates) in the database so it's a key part of the design. The data set means we're either looking at 1 table with perhaps 10 million records or 100,000 tables each with about 100 records. "Standard" SQL theory seems to say we should use a single table. It's more flexible and some queries simply aren't possible across multiple tables (or at least not efficiently). But in this case we're happy to live with reduced flexibility if it gives us substantially better performance. Early empirical testing with 100,000 records suggests the single large table becomes progressively slower to access as it grows in size (average access time goes from ~4ms/transaction up to around ~80ms for our test cases--MySQL 5.0 on CentOS). The multiple dynamic tables don't seem to have this property--access remains pretty much constant as you might expect (~4ms/transaction). So the question is, even given this 20x performance benefit are we still fools to consider the dynamic table model? Are we going to run into max-tables or max-file-handle limits or other problems that will eventually bite us? Or is this speed difference just an artifact of poor indexing choices or similar? Or are dynamic tables OK sometimes? Doug P.S. Here's the table in question: CREATE TABLE one_big_table ( rank bigint not null auto_increment unique, item_id int not null, user_id int not null, countsmallintnot null default 1, addeddatetimenot null, primary key(rank, user_id) ) engine=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seriously Disruptive DBA Needed!!!!!!!!! - London, United Kingdom
Hi, I work for a mobile technology company in London. We are looking to recruit a MySQL DBA. The world of the enterprise DBA is changing, be part of it. Like it or not, open source technology is making serious inroads into the enterprise environment. Justifying the purchase of a heavyweight RDBMS with it's associated overhead, when credible Open Source alternatives exist, is getting harder and harder. Our client positively welcomes this change. Without it, they wouldn't exist.. Having said this, business models may be changing, but operational requirements are not. Although they are committed to Open Source, they are more passionately committed to their service, their subscribers, and not being woken up at unsociable hours of the morning. Our client is hiring a DBA to be part of it's Engineering team. This is both a production support and development role that requires architect grade thinking. They are a small team of open source and telephony experts, many with a track record of contribution to Open Source projects - That's the disruptive part. The serious part is their focus on quality of service, sustainability and our global aspirations. Your CV will tell a credible story that involves successful implementations at an enterprise scale. Your focus will be on service delivery and sustainability, not be a simple "shopping list" of products and acronyms (our client are smart people, and their attention span is short). You will bring to our client mature DBA skills to bear on a rapidly growing environment. If you agree that the world is changing, and want to be right at the forefront of these changes, we need to talk. Whether you're a seasoned Oracle DBA who doesn't know their INNODB from their MyISAM, or a hardcore MySQL DBA with contributions to the code base, our client wants to hear from you. Our client is in a position to offer an exceptional basic - they want quality people and that´s why I am working with them - to source them quality and exceptional candidates not only for this role but several others that they are recruiting for. Perhaps we could speak? Thanks James = -- Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database architecture and security
I concur. Also it makes it easier to remove a customer if they leave. Finally your backups will only lock up one customer's database at time and for a much shorter period. On Thu, August 23, 2007 10:50, Jerry Schwartz said: > Personally, I think I'd go with one DATABASE per customer. That way the > your > code would be the same, and easier to handle. It would be easier to manage > the security at the database level, I suspect. I'd set up a ../inc > directory > outside the web server root that would have one file per customer, and > would > have the customer-specific variables such as database name, password, and > so > forth. Each file would be named after a customer. You'd prompt for a user > name and password, include the appropriate customer-specific .inc file, > check the password against what the user supplied, and if it passed then > create a session with the .inc file variables stored as session variables. > > Regards, > > Jerry Schwartz > The Infoshop by Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > www.giiexpress.com > www.etudes-marche.com > > >> -Original Message- >> From: Jason Pruim [mailto:[EMAIL PROTECTED] >> Sent: Thursday, August 23, 2007 10:59 AM >> To: MySQL List >> Subject: Database architecture and security >> >> Hi Everyone, >> >> Just had a quick question about a database I'm working on. >> >> I am planning on having the database open to customers of mine to >> store their mailing addresses on-line, and be able to manage the >> records. >> >> Is it safe, to have 1 database with lots of tables? Or am I safer >> setting up separate databases for everyone? >> >> I should mention, no one will be accessing the database directly, >> it'll be through a web interface and php to display it. >> >> Any info would be greatly appreciated! >> >> >> -- >> >> Jason Pruim >> Raoset Inc. >> Technology Manager >> MQC Specialist >> 3251 132nd ave >> Holland, MI, 49424 >> www.raoset.com >> [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database architecture and security
Personally, I think I'd go with one DATABASE per customer. That way the your code would be the same, and easier to handle. It would be easier to manage the security at the database level, I suspect. I'd set up a ../inc directory outside the web server root that would have one file per customer, and would have the customer-specific variables such as database name, password, and so forth. Each file would be named after a customer. You'd prompt for a user name and password, include the appropriate customer-specific .inc file, check the password against what the user supplied, and if it passed then create a session with the .inc file variables stored as session variables. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -Original Message- > From: Jason Pruim [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 23, 2007 10:59 AM > To: MySQL List > Subject: Database architecture and security > > Hi Everyone, > > Just had a quick question about a database I'm working on. > > I am planning on having the database open to customers of mine to > store their mailing addresses on-line, and be able to manage the > records. > > Is it safe, to have 1 database with lots of tables? Or am I safer > setting up separate databases for everyone? > > I should mention, no one will be accessing the database directly, > it'll be through a web interface and php to display it. > > Any info would be greatly appreciated! > > > -- > > Jason Pruim > Raoset Inc. > Technology Manager > MQC Specialist > 3251 132nd ave > Holland, MI, 49424 > www.raoset.com > [EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On 8/23/07, Jason Pruim <[EMAIL PROTECTED]> wrote: > > > > b)Terminating TCP connections and ensuring that each PHP script > > runs to > > completion, anyway, and that the database isn't left in an > > indeterminate > > state due to this. > > > > Dave. > > What do you mean by "b"? If all the connections come from the local > box how could I configure that to make sure it's all set up so it > won't leave the database all messed up? I don't know all of the technical details, but if a user uses the STOP button on a browser or the TCP connection is otherwise terminated, there is a feedback mechanism where the PHP script producing the HTML can be terminated (it has no further utility, as it would be feeding a non-existent connection at that point). A worst case is where the PHP script is aborted in a critical section (depending on how transactions are handled in the database) so that the database is left in an inconsistent state. Whether this is possible depends on how you choose to do locking and transactions. The safest approaches I'm aware of are: #1)Form the database results and close the database connection before generating output. #2)Use the PHP function intended for that purpose. Here is the URL for the PHP function: http://us.php.net/manual/en/function.ignore-user-abort.php http://us.php.net/manual/en/features.connection-handling.php Best regards, Dave
Re: Database architecture and security
On Aug 23, 2007, at 11:50 AM, David T. Ashley wrote: On 8/23/07, Jason Pruim <[EMAIL PROTECTED]> wrote: I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Assuming that the web server runs on the same box as the MySQL daemon ... you want to firewall the server so that nobody can connect to the MySQL daemon directly from outside the box. It is also a bad idea to allow the users to have shell accounts on that box unless you have taken additional security precautions (specifically, being sure the MySQL userid/ password you're using are secure from all but the web server UID/GID, and that no other userid/passwords have access to the database you're using). Once that is done, all access to the database is controlled by the PHP scripts, and there is no security advantage to having multiple databases. I'm assuming that users have to log in individually (jsmith, bjones, etc.) and that the PHP scripts then carefully control what each user is allowed to modify. I'm also going to assume that you've handled all the obvious technology issues, such as: a)Database transactions/atomic actions. b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave. The server is currently firewalled to block all but the necessary ports from outside the local network. No user, other then myself, and a few admins on the server will have shell access... The MySQL userid/password will be changed once I go live with it, or get into the final testing. What do you mean by "b"? If all the connections come from the local box how could I configure that to make sure it's all set up so it won't leave the database all messed up? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:28 AM, Rolando Edwards wrote: Think about how your going to make backups. 1) Would you backup one database with all the mailing lists together ? If I went the route of 1 database, Many tables, I would just backup the entire database and all the tables in one shot. Unless I"m misunderstanding how MySQL handles the backups, I would think that it would preserve the individual tables? 2) Would you keep the backups of each user separate ? I probably should, but hadn't thought that far ahead yet. Still working on getting the PHP Scripts and everything else set. 3) Could users ask you to restore mailing lists from the past ? The only reason I could see them asking for that is if they went through and deleted the entire database, which has to be done manually, meaning you have to hit "delete" on each record to delete it. I would hope they would realize what they were doing before going through the entire database... You could make one mysqldump for everybody from one database if no one ever asks for restoration of past mailing lists. You could create a database for each user. Then, backup (mysqldump) each database for each user. Should they request a restore, it's becomes an easy thing. How you answer the three questions I posed should help you decide. I am starting to lean towards the multiple databases with 1 table even though it makes more files on my server, I think it gives me the most flexibility/security. That way I can also edit 1 database and not screw it up for the entire user base, just the one user :) - Original Message - From: "Jason Pruim" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/ New_York Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:44 AM, Gary Josack wrote:
I'd never have a separate database for everyone or even a separate
table for everyone. Here's a rough idea of how I'd do it
mysql> CREATE TABLE customer (
-> `custid` INT NOT NULL AUTO_INCREMENT,
-> `lastname` VARCHAR(25) not null,
-> `firstname` VARCHAR(25) NOT NULL,
-> PRIMARY KEY(custid)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE address (
-> `addressid` INT NOT NULL AUTO_INCREMENT,
-> `custid` INT NOT NULL,
-> `address` VARCHAR(100) NOT NULL,
-> `city` VARCHAR(50),
-> `state` CHAR(2) NOT NULL,
-> `zip` MEDIUMINT(5) NOT NULL,
-> PRIMARY KEY(addressid)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM customer;
++--+---+
| custid | lastname | firstname |
++--+---+
| 1 | Bolton | Mike |
| 2 | Vader| Darth |
++--+---+
2 rows in set (0.00 sec)
mysql> INSERT INTO address (custid, address, city, state, zip) VALUES
-> (1, '123 house drive.', 'Davie', 'FL', 33314),
-> (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
-> (2, 'The Death Star', 'SPACE', 'NA', 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM address;
+---++---+-+---+---+
| addressid | custid | address | city| state | zip |
+---++---+-+---+---+
| 1 | 1 | 123 house drive. | Davie | FL| 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL| 33521 |
| 3 | 2 | The Death Star| SPACE | NA| 6 |
+---++---+-+---+---+
3 rows in set (0.00 sec)
mysql> SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+--+---+---+-+---+---+
| lastname | firstname | address | city| state | zip |
+--+---+---+-+---+---+
| Bolton | Mike | 123 house drive. | Davie | FL| 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL| 33521 |
| Vader| Darth | The Death Star| SPACE | NA| 6 |
+--+---+---+-+---+---+
3 rows in set (0.01 sec)
mysql> SELECT address, city, state, zip FROM customer JOIN address
USING (custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+---+-+---+---+
| address | city| state | zip |
+---+-+---+---+
| 123 house drive. | Davie | FL| 33314 |
| 54325 awesome way | Sunrise | FL| 33521 |
+---+-+---+---+
Now each customer/person can have multiple addresses listed.
I really like the idea of being able to have multiple addresses, some
of our customers right now have lots of seasonal addresses... But
that's a little bit out of my comfort zone right now... I'll add it
to the feature list though and keep your e-mail to reference :)
Thanks! :)
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On 8/23/07, Jason Pruim <[EMAIL PROTECTED]> wrote: > > I am planning on having the database open to customers of mine to > store their mailing addresses on-line, and be able to manage the > records. > > Is it safe, to have 1 database with lots of tables? Or am I safer > setting up separate databases for everyone? > > I should mention, no one will be accessing the database directly, > it'll be through a web interface and php to display it. Assuming that the web server runs on the same box as the MySQL daemon ... you want to firewall the server so that nobody can connect to the MySQL daemon directly from outside the box. It is also a bad idea to allow the users to have shell accounts on that box unless you have taken additional security precautions (specifically, being sure the MySQL userid/password you're using are secure from all but the web server UID/GID, and that no other userid/passwords have access to the database you're using). Once that is done, all access to the database is controlled by the PHP scripts, and there is no security advantage to having multiple databases. I'm assuming that users have to log in individually (jsmith, bjones, etc.) and that the PHP scripts then carefully control what each user is allowed to modify. I'm also going to assume that you've handled all the obvious technology issues, such as: a)Database transactions/atomic actions. b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave.
Re: Database architecture and security
I'd never have a separate database for everyone or even a separate table
for everyone. Here's a rough idea of how I'd do it
mysql> CREATE TABLE customer (
-> `custid` INT NOT NULL AUTO_INCREMENT,
-> `lastname` VARCHAR(25) not null,
-> `firstname` VARCHAR(25) NOT NULL,
-> PRIMARY KEY(custid)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE address (
-> `addressid` INT NOT NULL AUTO_INCREMENT,
-> `custid` INT NOT NULL,
-> `address` VARCHAR(100) NOT NULL,
-> `city` VARCHAR(50),
-> `state` CHAR(2) NOT NULL,
-> `zip` MEDIUMINT(5) NOT NULL,
-> PRIMARY KEY(addressid)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM customer;
++--+---+
| custid | lastname | firstname |
++--+---+
| 1 | Bolton | Mike |
| 2 | Vader| Darth |
++--+---+
2 rows in set (0.00 sec)
mysql> INSERT INTO address (custid, address, city, state, zip) VALUES
-> (1, '123 house drive.', 'Davie', 'FL', 33314),
-> (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
-> (2, 'The Death Star', 'SPACE', 'NA', 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM address;
+---++---+-+---+---+
| addressid | custid | address | city| state | zip |
+---++---+-+---+---+
| 1 | 1 | 123 house drive. | Davie | FL| 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL| 33521 |
| 3 | 2 | The Death Star| SPACE | NA| 6 |
+---++---+-+---+---+
3 rows in set (0.00 sec)
mysql> SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+--+---+---+-+---+---+
| lastname | firstname | address | city| state | zip |
+--+---+---+-+---+---+
| Bolton | Mike | 123 house drive. | Davie | FL| 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL| 33521 |
| Vader| Darth | The Death Star| SPACE | NA| 6 |
+--+---+---+-+---+---+
3 rows in set (0.01 sec)
mysql> SELECT address, city, state, zip FROM customer JOIN address USING
(custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+---+-+---+---+
| address | city| state | zip |
+---+-+---+---+
| 123 house drive. | Davie | FL| 33314 |
| 54325 awesome way | Sunrise | FL| 33521 |
+---+-+---+---+
Now each customer/person can have multiple addresses listed.
Jason Pruim wrote:
Hi Everyone,
Just had a quick question about a database I'm working on.
I am planning on having the database open to customers of mine to
store their mailing addresses on-line, and be able to manage the records.
Is it safe, to have 1 database with lots of tables? Or am I safer
setting up separate databases for everyone?
I should mention, no one will be accessing the database directly,
it'll be through a web interface and php to display it.
Any info would be greatly appreciated!
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
Think about how your going to make backups. 1) Would you backup one database with all the mailing lists together ? 2) Would you keep the backups of each user separate ? 3) Could users ask you to restore mailing lists from the past ? You could make one mysqldump for everybody from one database if no one ever asks for restoration of past mailing lists. You could create a database for each user. Then, backup (mysqldump) each database for each user. Should they request a restore, it's becomes an easy thing. How you answer the three questions I posed should help you decide. - Original Message - From: "Jason Pruim" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/New_York Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database architecture and security
Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
RE: seoparator help
mysql> select format(300,0); +---+ | format(300,0) | +---+ | 3,000,000 | +---+ 1 row in set (0.00 sec) mysql> select format(300,2); +---+ | format(300,2) | +---+ | 3,000,000.00 | +---+ 1 row in set (0.00 sec) Cheers, Andrew -Original Message- From: coolcoder [mailto:[EMAIL PROTECTED] Sent: Thu, 23 August 2007 11:55 To: mysql@lists.mysql.com Subject: seoparator help Was wondering if anyone could help me with this little problem I'm having. I'd like to have a comma separator after every 3 digits. E.g "3,000,000". How would i go about this? This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: seoparator help
Not really a MySQL problem, this is a presentation problem. MySQL will store the number as digits only (unless you are storing in a character field - but why would you?). If using PHP, for instance, the output of the field would be number_format($fieldvalue) or if you want the answer to two decimal places number_format($fieldvalue, 2) Regards Terry http://booksihaveread.awardspace.co.uk - Original Message - > *From:* coolcoder <[EMAIL PROTECTED]> > *To:* mysql@lists.mysql.com > *Date:* Thu, 23 Aug 2007 03:55:27 -0700 (PDT) > > Was wondering if anyone could help me with this little problem I'm > having. > I'd like to have a comma separator after every 3 digits. E.g > "3,000,000". > How would i go about this? > > > > > > > www.coderewind.com > Best Place to hunt for Code > -- > View this message in context: > http://www.nabble.com/seoparator-help-tf4316769.html#a12291343 > Sent from the MySQL - General mailing list archive at Nabble.com. > -- No virus found in this outgoing message. Checked by AVG. Version: 7.5.484 / Virus Database: 269.12.2/967 - Release Date: 22/08/2007 18:51 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
seoparator help
Was wondering if anyone could help me with this little problem I'm having. I'd like to have a comma separator after every 3 digits. E.g "3,000,000". How would i go about this? www.coderewind.com Best Place to hunt for Code -- View this message in context: http://www.nabble.com/seoparator-help-tf4316769.html#a12291343 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is the average of an int column returned as a string
I'd guess this may be a funny related to Crystal Reports as I don't recall experiencing this myself. Rhys -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 22 August 2007 22:21 To: Rhys Campbell; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string I have tried that and that seems to be working for me. I am just wondering why all of the sudden queries that were returning the averages as numeric values as string values. Thank you for your suggestion. Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] -Original Message- From: Rhys Campbell [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 22, 2007 3:34 AM To: Eric Lommatsch; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
