Re: MySQL database design, one column, 10 entries?
You need to add an additional table, favorites. It should have three columns: favoriteID INT, userID INT, favoriteTypeID INT REFERENCES FavoriteType. Plus the additional column for the rating, assuming it's associated with a favorite. Each row represents a single preference (this way users can have an arbitrary number of preferences). Each user would be associated with multiple rows in the table. The favoriteID is so that you can delete/update favorites easily; you want to be able to talk about a particular row. You might want a SELECT like this: SELECT * FROM favorites WHERE userID = $id ORDER BY rating DESC LIMIT 0,10 to get the top ten favorites for user identified by $id. You might also want to make an additional table, favoriteType, unless each favorite is completely unique (in which case the third column in the table above would have VARCHAR or TEXT type). (You should avoid that kind of design if possible, because most likely anything you want to rank is going to have similarities across users) favoriteTypeID INT favoriteLabel VARCHAR You should do some research on normalization and database design. There are some good books on the topic. Quinten On Sun, 11 Aug 2002, david wrote: Date: Sun, 11 Aug 2002 00:43:14 -0400 From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL database design, one column, 10 entries? I am creating several tables in MySQL and linking via primary keys. I am held up on one issue, for one row in one table i have a column 'favorites' where i want to hold up to 10 unique entries, how do i implement this? userTable userId varchar(20) name varchar(30) email varchar(40) preferences userId varchar(20) styles varchar(20) favorites preferences userId styles favorites rating 01 'modern''#1 sleek' 15 '#2 ultra-sleek'20 '#3 un-sleek' 12 '#4 plain' 9 etc, up to 10 or so. 02 'gothic''#1 dark' 21 '#2 tall' 4 '#3 scary' 2 etc, etc,... I can't just make it a really long varchar(1000) because of the other columns that act on the same data. I hope that this is an appropriate question for this MySQL list. TIA -david - 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 - 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
Feature request; Field for comments
Hi mysql users, mysql developers! I have just started using mysql for a database project and I wonder that one function is missing. I am using myphpadmin as utility. It is possible to write a comment to each table, but it is not possible to write a comment to each field. Why not? I think this would be a usefull feature which should be implemented, especially if you work with several members. Example (these are all fields in a table): nick [... mysql stuff] - here the nickname of each user is stored. email [...] - the email of the user emailcmp [...] - the email of the company the user is working for as you see, the feature is beginning to get usefull when the fields get more complicated and unique naming is getting a problem. favouritekey - a binary code to store the users interests as documented in keyrules.rtf I think this would be a great enhanchement, especially for bigger groups of developers. What do you think? Thanks in advance Dieter -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net - 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
MySQL database design
MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd - 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
Mysql Problem Query
From Anish MAthew India Dear sir i am using Linux 7.2 as my OS and having mysql version 3.36. i am a novice learner of mysql though i am familiar with Oracle and sybase. when i got to no the GNU publice license and open source technology i thought of learning mysql. but i have a problem to start with and i decided to share with u to get some technical help. problem my sqlserver is not running when i issue the command mysql the response i got was cant connect to local mysql server through socket /var/lib/mysql.sock (111) when i issued the command mysql.server start i got starting mysql daemon with databases from /var/lib/mysql mysqld ended and the daemon ended abruptly without starting the server. what would be the problem if you can give a technical workaround to this problem i would be grateful. awaiting your reply bye anish mathew __ Give your Company an email address like ravi @ ravi-exports.com. Sign up for Rediffmail Pro today! Know more. http://www.rediffmailpro.com/signup/ - 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
Re: Mysql Problem Query
did you also install the databases that are part of the mysql installation? -Ll on 8/11/02 5:59 AM, Anish Mathew, typed: my sqlserver is not running when i issue the command mysql the response i got was cant connect to local mysql server through socket /var/lib/mysql.sock (111) when i issued the command mysql.server start i got starting mysql daemon with databases from /var/lib/mysql mysqld ended and the daemon ended abruptly without starting the server. - 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
Commands out of sync Error
Hi, I'm sometimes experiencing in my application the commands out of sync; You can't run this command now error. According to the MySQL manuel, it means : This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without a mysql_use_result() or mysql_store_result() in between. The main question is since I use PHP, should I see this error ? (I mean mysql_user_result(), mysql_free_result() and mysql_store_result() are internally called by PHP, but it should be called in the right order so that this error never happen). Is this possible it's a bug in PHP (or perhaps in MySQL C API ??). I'm using PHP 4.2.2 and MySQL 4.0.3 (but 4.2.1 and MySQL 4.0.2 already show this behaviour). Any ideas ? Thanks, Jocelyn - 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
mysql_com.h header name space pollution
Description: NET is defined on line 135 in myslq_com.h. When I include mysql.h (for embedded mysql) I cannot compile a program which also includes netwm_def.h, which defines a completely different NET. How-To-Repeat: Try to compile something that includes these two header files: #include mysql.h #include kwin.h (using KDE from CVS) and you'll get: In file included from /home/dnaber/prg/kde3/include/kwin.h:27, from kmmessage.cpp:47: /home/dnaber/prg/kde3/include/netwm_def.h:171: conflicting types for `struct NET' /home/dnaber/prg/mysql4/include/mysql/mysql_com.h:135: previous declaration as `typedef struct st_net NET' Fix: Please rename NET to something like MySQL_NET Submitter-Id: submitter ID Originator:Daniel Naber Organization: MySQL support: none Synopsis: mysql_com.h header name space pollution Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.2-alpha (Source distribution) Environment: Suse 7.2 with KDE from CVS and Qt 3.06 from KDE's CVS (qt-copy) System: Linux pluto 2.4.4-4GB #1 Wed May 16 00:37:55 GMT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs gcc version 2.95.3 20010315 (SuSE) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1341670 Jul 12 12:40 /lib/libc.so.6 -rw-r--r--1 root root 24534176 Jul 12 12:35 /usr/lib/libc.a -rw-r--r--1 root root 178 Jul 12 12:35 /usr/lib/libc.so -rw-r--r--1 root root 859134 Mai 11 2001 /usr/lib/libc-client.a lrwxrwxrwx1 root root 20 Jun 4 2001 /usr/lib/libc-client.so - libc-client.so.2000c -rwxr-xr-x1 root root 720640 Mai 11 2001 /usr/lib/libc-client.so.2000c Configure command: ./configure --prefix=/home/dnaber/prg/mysql4 --with-embedded-server - 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
Re: Mysql Problem Query
Also, see what errors you are getting in mysql hostname.err file Regards, Bhavin. - Original Message - From: [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Sunday, August 11, 2002 3:30 AM Subject: Re: Mysql Problem Query did you also install the databases that are part of the mysql installation? -Ll on 8/11/02 5:59 AM, Anish Mathew, typed: my sqlserver is not running when i issue the command mysql the response i got was cant connect to local mysql server through socket /var/lib/mysql.sock (111) when i issued the command mysql.server start i got starting mysql daemon with databases from /var/lib/mysql mysqld ended and the daemon ended abruptly without starting the server. - 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 - 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
Need reversible encryption as string
I want to securely store a value that is used as a password to log someone into a Web application. I also want to be able to allow the user to search for their email address and have their password sent back to them (in readable form). Encrypt, MD5, and Password are non-reversible and thus will not work for my needs. The Encode function creates a value that is stored as binary. It seems that I cannot do a match type search, but I have to convert the stored password on each row as follows: SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND DECODE(LoginPassword,'MySalt')=$PasswordEntered I would think that using the SQL shown would require a table scan, meaning that each and every record in the visitors table must be examined, the LoginPassword decoded and compared. There is also no way to index this field. I do not think this is the best solution after adding 100,000 records. I see that MySQL 4 offers AES_ENCRYPT() and AES_DECRYPT(), will this offer a solution? I believe this will allow me to store the password as a string of characters (and not binary data) so that a match can be made without having to decode the password, since I can decode what is entered by the user using the same salt and compare the two encrypted strings. SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND LoginPassword=DECODE($PasswordEntered,'MySalt') The decrypt process would only be used when needing to send the result back to the user. Am I understanding this correctly? -- Michael __ ||| Michael Collins ||| ||| Kuwago Inc||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - 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
Re: Need reversible encryption as string
An easier (and more secure) way, surely, is to use one-way encryption... and if a user forgets his/her password, replace it with a random alphanumeric string and mail that to them instead with instructions to change it to one of their own choosing as soon as possible. Mike - Original Message - From: Michael Collins [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 11, 2002 5:25 PM Subject: Need reversible encryption as string I want to securely store a value that is used as a password to log someone into a Web application. I also want to be able to allow the user to search for their email address and have their password sent back to them (in readable form). Encrypt, MD5, and Password are non-reversible and thus will not work for my needs. The Encode function creates a value that is stored as binary. It seems that I cannot do a match type search, but I have to convert the stored password on each row as follows: SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND DECODE(LoginPassword,'MySalt')=$PasswordEntered I would think that using the SQL shown would require a table scan, meaning that each and every record in the visitors table must be examined, the LoginPassword decoded and compared. There is also no way to index this field. I do not think this is the best solution after adding 100,000 records. I see that MySQL 4 offers AES_ENCRYPT() and AES_DECRYPT(), will this offer a solution? I believe this will allow me to store the password as a string of characters (and not binary data) so that a match can be made without having to decode the password, since I can decode what is entered by the user using the same salt and compare the two encrypted strings. SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND LoginPassword=DECODE($PasswordEntered,'MySalt') The decrypt process would only be used when needing to send the result back to the user. Am I understanding this correctly? -- Michael __ ||| Michael Collins ||| ||| Kuwago Inc||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - 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 - 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
Re: Need reversible encryption as string
At 9:54 AM -0700 8/11/02, Mike Wexler wrote: I would think that using the SQL shown would require a table scan, meaning that each and every record in the visitors table must be examined, the LoginPassword decoded and compared. There is also no way to index this field. I do not think this is the best solution after adding 100,000 records. If you have an index on EmailAddress this will not require a complete scan. It will do a simple index lookup on the email address. Get any matching records and compare the password on each of these. OK that is the answer. Thank you for the help! -- Michael __ ||| Michael Collins ||| ||| Kuwago Inc||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - 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
Re: Need reversible encryption as string
At 9:25 -0700 8/11/02, Michael Collins wrote: I want to securely store a value that is used as a password to log someone into a Web application. I also want to be able to allow the user to search for their email address and have their password sent back to them (in readable form). Encrypt, MD5, and Password are non-reversible and thus will not work for my needs. The Encode function creates a value that is stored as binary. It seems that I cannot do a match type search, but I have to convert the stored password on each row as follows: SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND DECODE(LoginPassword,'MySalt')=$PasswordEntered If you write the query like that, yes, it will use a complete scan. But you could also use ... AND LoginPassword = ENCODE($PasswordEntered,'MySalt') which doesn't perform a calculation on the LoginPassword column and thus can use an index. This is similar to your query below, but I think you want ENCODE(), not DECODE(), since you're storing encrypted strings. I would think that using the SQL shown would require a table scan, meaning that each and every record in the visitors table must be examined, the LoginPassword decoded and compared. There is also no way to index this field. I do not think this is the best solution after adding 100,000 records. I see that MySQL 4 offers AES_ENCRYPT() and AES_DECRYPT(), will this offer a solution? I believe this will allow me to store the password as a string of characters (and not binary data) so that a match can be made without having to decode the password, since I can decode what is entered by the user using the same salt and compare the two encrypted strings. SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND LoginPassword=DECODE($PasswordEntered,'MySalt') The decrypt process would only be used when needing to send the result back to the user. Am I understanding this correctly? -- Michael __ ||| Michael Collins ||| ||| Kuwago Inc||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - 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
Re: Need reversible encryption as string
At 12:07 PM -0500 8/11/02, Paul DuBois wrote: If you write the query like that, yes, it will use a complete scan. But you could also use ... AND LoginPassword = ENCODE($PasswordEntered,'MySalt') which doesn't perform a calculation on the LoginPassword column and thus can use an index. This is similar to your query below, but I think you want ENCODE(), not DECODE(), since you're storing encrypted strings. Thank you Paul for the clarification. At 5:30 PM +0100 8/11/02, Mike Hall wrote: An easier (and more secure) way, surely, is to use one-way encryption... and if a user forgets his/her password, replace it with a random alphanumeric string and mail that to them instead with instructions to change it to one of their own choosing as soon as possible. I will consider this option since I have just learned that Encode cannot be used since it stores the value as binary. Another application that uses the database apparently cannot work with this binary value. In addition, I would rather not use MySQL 4 until it is beta (at least) and so will have to wait to use AES_ENCRYPT() and AES_DECRYPT(), but would this be my solution if I was using MySQL 4? In conclusion, there is no reversible encryption available in MySQL 3.+ that can be stored as a text string(?) -- Michael __ ||| Michael Collins ||| ||| Kuwago Inc||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - 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
Re: Need reversible encryption as string
Hi. On Sun 2002-08-11 at 12:07:47 -0500, [EMAIL PROTECTED] wrote: At 9:25 -0700 8/11/02, Michael Collins wrote: [...] SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND DECODE(LoginPassword,'MySalt')=$PasswordEntered If you write the query like that, yes, it will use a complete scan. I beg to differ. If there is an index on EmailAddress it will be used and only all entries with the same email address (which should be one or only one) will be scanned. But you could also use ... AND LoginPassword = ENCODE($PasswordEntered,'MySalt') But that's a good idea anyhow. Bye, Benjamin. -- [EMAIL PROTECTED] - 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
Re: Need reversible encryption as string
At 19:51 +0200 8/11/02, Benjamin Pflugmann wrote: Hi. On Sun 2002-08-11 at 12:07:47 -0500, [EMAIL PROTECTED] wrote: At 9:25 -0700 8/11/02, Michael Collins wrote: [...] SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND DECODE(LoginPassword,'MySalt')=$PasswordEntered If you write the query like that, yes, it will use a complete scan. I beg to differ. If there is an index on EmailAddress it will be used and only all entries with the same email address (which should be one or only one) will be scanned. You're right. I was speaking only with respect to evaluation of the condition on LoginPassword. But you could also use ... AND LoginPassword = ENCODE($PasswordEntered,'MySalt') But that's a good idea anyhow. You're right there, too. :-) Bye, Benjamin. -- [EMAIL PROTECTED] - 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
Re: [OT] assigning new passwords (was: Need reversible encryption as string)
Hi. I drag this a bit to off-topic here, but the answer below brings up a question which bothered me for some time... On Sun 2002-08-11 at 17:30:46 +0100, [EMAIL PROTECTED] wrote: An easier (and more secure) way, surely, is to use one-way encryption... and if a user forgets his/her password, replace it with a random alphanumeric string and mail that to them instead with instructions to change it to one of their own choosing as soon as possible. Although I always liked this idea best security-wise, it can be abused quite easily. Whenever someone enters some account, the password for this account will be reset (and an email send). If the email works fine, this is only a major annoyance. If the email of the account does not work anymore, this is a DoS service for that account: The password the account owner knew has been changed and he has no possibility to retrieve the new one. How do you prevent this? My current solution works with the old password. Surely this has it's drawbacks security-wise, but it can only be abused to send these e-mails to people, for which a limit is realized. So back to my question above. With the good-security solution, how can I prevent the abuse mentioned? Bye, Benjamin. -- [EMAIL PROTECTED] - 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
Re: [OT] assigning new passwords (was: Need reversible encryption as string)
- Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Mike Hall [EMAIL PROTECTED] Cc: Michael Collins [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, August 11, 2002 7:05 PM Subject: Re: [OT] assigning new passwords (was: Need reversible encryption as string) On Sun 2002-08-11 at 17:30:46 +0100, [EMAIL PROTECTED] wrote: An easier (and more secure) way, surely, is to use one-way encryption... and if a user forgets his/her password, replace it with a random alphanumeric string and mail that to them instead with instructions to change it to one of their own choosing as soon as possible. Although I always liked this idea best security-wise, it can be abused quite easily. Whenever someone enters some account, the password for this account will be reset (and an email send). If the email works fine, this is only a major annoyance. If the email of the account does not work anymore, this is a DoS service for that account: The password the account owner knew has been changed and he has no possibility to retrieve the new one. How do you prevent this? The way I worked around this problem was to send two emails. When a reset password request is set in the database, I generate a confirmation hash and store that in the database. I then email a message to the user saying someone has requested that your password be reset. if this was you click here [http://www.mywebsite.com/resetpass.php?user=2356confirm=a8b767bb9cf0938dc7 f40603f33987e5]. When the user clicks on that link, it checks the confirm hash against the one I stored in the database. If they match, it clears the hash, resets the password and emails the user again informing him/her what the new password is. --Mike - 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
Re: MySQL database design
At 8/11/2002, you wrote: MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Hi Lloyd, Spending plenty of time designing the database is be the best decision you can make. If the base is solid and flexible you'll end up with a solid and flexible application and not rewriting database structure will every small feature request. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd I'm no guru, but I'd like to tell you my view on this as about a year ago I struggled same battle and I think I learned a lot from it. Real gurus out there please feel free to correct my writings :) The design should always be related to what the relations there are between items you describe with the database. Here I see you are building a database of personnel and gear they use, groups they belong to and dirs they can access etc. But not much relativity there now. When normalization is on level 5 you see mostly id's :) Ask questions. Think examples of queries you will have to do. Ask yourself what if someone asks me to pull out data that has . Build and test queries to get that data. Make assumption that there is a LOT of data. Think space usage. The questions I would ask about your db structure are e.g.: Will data be repeated (in a table)? - there is now a lot of repeated data. A computer table could hold just model id and serial, and then have id's for cpu, hdd, ram, ports, manufacturer etc. An IP table could hold IP's, and there is not much point storing an IP with a computer as one computer will definitely have more than one IP's. Same way: OSes table could be OSes osID brandID displaynameID date_added date_modified or with even more normalization OSes osID osdataID .and with that you'd have an extra table. OSes osdataID os_name os_manufacturer os_release_data os_price os_etc plus to that you'd have a separate table OSes_to_comps OSes_to_comps_id compID osID date_added date_modified .which will let you have one computer have more than one OS (perfectly possible). 'OSes_to_comps_id' is really not needed but I like to have it there anyway. More questions: How do you link a person to a device? Or several persons to same device? Can one person have more than one device? What if one person has two computers and 4 displays? How do you link a device to a group (list all devices of a group) How do you link gear together? Answer to all above: use intermediate tables, like the 'OSes' example above. I would do a table 'person_to_computer' which would have 'PersID' and 'compID'. This way one person can have several computers. Using same method: 'person_to_display' table would just link a person to a display. These intermediate tables take very little room (just use two int cols or so - use same int lenght as you autoincrement keys) and make searching e.g. what displays a person has? much easier. They also make it possible to have complex relationships with minimal effort. SELECT displays.make, displays.model, personnel.firstName, personnel.lastName FROM displays, personnel, personnel_to_display WHERE personnel_to_display.dispID = displays.dispID AND personnel_to_display.persID = personnel.persID Display detail could be splitted to several tables just like in OSes example (you could build manufacturer and model tables which are shared accross the database), so that you could get display info by SELECT manufacturer.manufacturer_id manufacturer.manufacturer_name, model.model_id model.model_name FROM manufacturer, model, displays WHERE displays.manufacturer_id = manufacturer.manufacturer_id AND displays.model_id = model.model_id Heavy normalization will most likely make programming the application more complex, but it will pay off in speed and flexibility to change and add things. PS. About table names: - mixing case will make you make
lost mysql database tables
I seem to have lost the mysql database tables. I have other databases that work. what is the best way to add the right tables to mysql Dave - 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
Re: lost mysql database tables
Hello Dave, Phew... what a situation! ;-) You can always run mysql_install_db script on *NIX, but it clears your existing permissions. On Windows you can do an xcopy for your mysql database from the distribution package, but that doesn't save your permissions, either. First, though, make sure that you really don't have any disk files in the data\mysql directory. If there are any .MYD and .MYI files, they can contain your permissions. The .frm files can then be copied from another similar server (they only contain the table definitions, not the actual data). What about backups? ;-) Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** On Sun, 11 Aug 2002, Dave Reinhardt wrote: I seem to have lost the mysql database tables. I have other databases that work. what is the best way to add the right tables to mysql Dave - 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 - 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
Help needed.
I've never used a MySQL database before to create a dynamic website. I've done it loads of times with Access and ASP but now I need some help on developing firstly the database in a windows environment. Then I'll use PHP to access the database but on a Linux or Unix server? Anyone out there know of any (preferably free) tools that will allow me to develop in that way? Or do I need to take a different route? advice much appreciated. kind regards Steve. - 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
Re: MySQL database design
on 8/11/02 2:47 PM, Pekka Saarinen, typed: At 8/11/2002, you wrote: MySQL queries.. tables... design. http://garnet.acns.fsu.edu/~tlr7425/my_tables.gif There you will see a rough draft of what I am trying to do. Perhaps you will see some places that I will need to use a table_map? Or you can advise me of how to arrange my keys, or otherwise develop this db? I'm learning and need this help to better understand keys and normalization, while developing a db that can withstand expansion. I understand that it looks like I am trying to normalize what appears to be almost all the way, I could be wrong about that; but this is what I am interested in. Hi Lloyd, Spending plenty of time designing the database is be the best decision you can make. If the base is solid and flexible you'll end up with a solid and flexible application and not rewriting database structure will every small feature request. Advice from some gurus on something that *I am working on* would help me to understand much better than all the books I have! (I was on this list before, but I had to change my subscription address.) Thanks in Advance, Lloyd I'm no guru, but I'd like to tell you my view on this as about a year ago I struggled same battle and I think I learned a lot from it. Real gurus out there please feel free to correct my writings :) The design should always be related to what the relations there are between items you describe with the database. Here I see you are building a database of personnel and gear they use, groups they belong to and dirs they can access etc. But not much relativity there now. When normalization is on level 5 you see mostly id's :) Ask questions. Think examples of queries you will have to do. Ask yourself what if someone asks me to pull out data that has . Build and test queries to get that data. Make assumption that there is a LOT of data. Think space usage. The questions I would ask about your db structure are e.g.: Will data be repeated (in a table)? - there is now a lot of repeated data. A computer table could hold just model id and serial, and then have id's for cpu, hdd, ram, ports, manufacturer etc. An IP table could hold IP's, and there is not much point storing an IP with a computer as one computer will definitely have more than one IP's. Same way: OSes table could be OSes osID brandID displaynameID date_added date_modified or with even more normalization OSes osID osdataID .and with that you'd have an extra table. OSes osdataID os_name os_manufacturer os_release_data os_price os_etc plus to that you'd have a separate table OSes_to_comps OSes_to_comps_id compID osID date_added date_modified .which will let you have one computer have more than one OS (perfectly possible). 'OSes_to_comps_id' is really not needed but I like to have it there anyway. More questions: How do you link a person to a device? Or several persons to same device? Can one person have more than one device? What if one person has two computers and 4 displays? How do you link a device to a group (list all devices of a group) How do you link gear together? Answer to all above: use intermediate tables, I believe that what you called intermediate table books are calling table map, or something with the word map in it. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was banned or something! Thank you, thank you, and thank you again. like the 'OSes' example above. I would do a table 'person_to_computer' which would have 'PersID' and 'compID'. This way one person can have several computers. Using same method: 'person_to_display' table would just link a person to a display. These intermediate tables take very little room (just use two int cols or so - use same int lenght as you autoincrement keys) Could you please elaborate a bit on, ...use same int as you autoincrement keys... -for example which keys should (or must be?) be autoincremented? and make searching e.g. what displays a person has? much easier. They also make it possible to have complex relationships with minimal effort. SELECT displays.make, displays.model, personnel.firstName, personnel.lastName FROM displays, personnel, personnel_to_display WHERE personnel_to_display.dispID = displays.dispID AND personnel_to_display.persID = personnel.persID Display detail could be splitted to several tables just like in OSes example (you could build manufacturer and model tables which are shared accross the database), so that you could get display info by SELECT
Re: MySQL database design
At 8/11/2002, you wrote: I believe that what you called intermediate table books are calling table map, or something with the word map in it. Hi Lloyd, Books are nice to have (you can lie down to a sofa or sit in the loo with a book), and what I started with (SAMS Teach Yourseft MySQL in 21 days) are great to start with, but they don't get you very far in design process. The best resource is to design things out on a paper and coding tests on real data. What you have done here is extremely helpful. You don't know how much I appreciate it, especially when I was feeling so ignored -almost like I was banned or something! Thank you, thank you, and thank you again. Np. Nice to hear it was useful! :) Could you please elaborate a bit on, ...use same int as you autoincrement keys... -for example which keys should (or must be?) be autoincremented? I meant to say that if you refer to a key from table A from another table (B) you must use the same data type in both. If you have an autoincrement key int(11) on one table and you use it as int(5) on some intermediate table you'll end up having problems pretty soon. There should be (can be?) only one autoincrement key per table. Autoincrements are useful for stuff that you need to refer later by id, like adding to a list of photographs, list of car parts - the autoincrement system takes cares you don't have duplicate ids and that whenever you insert a new item you'll have a new unique key. Heavy normalization will most likely make programming the application more complex, but it will pay off in speed and flexibility to change and add things. I needed to be told that. because books seem to want you to shy away from over-normalization (beyond level 3). Well, the books don't tell much about normalizing much because the big companies take a lot of money to do it for you :) I think other reason is that writers think that keeping focus too long on one subject is not good for the reading process. Who knows. But it is not hard to normalize, and actually the further you go the more clearly you'll see the real data in your database. Pekka - Pekka Saarinen http://photography-on-the.net - - 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
query quandary
I have a query I'm trying to construct on a table which contains a title and date of records. I want to sort the query so that records are listed by title with the newest dates first--keeping the records with identical titles together and order by date within the title groups. For example: Title 2, 10-30-2001 Title 2, 10-15-2001 Title 2, 10-01-2001 Title 1, 10-20-2001 Title 1, 10-10.2001 Title 3, 10-05-2001 My attempt at SELECT title, date FROM tablename GROUP BY title, date DESC produced an appropriately grouped result set, with the titles ordered within the groups by date, BUT it put the groups in order alphabetically, whereas I'm wanting them ordered by the date of the first title in the groups. Any suggestions? Thanks, Mark Jones _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - 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
Re-installing MySQL
Hello folks, I'd need to re-install MySQL server on my RH 7.3 box. As I installed the server from RPMs, I tried to update MySQL with rpm and -Uvh options. Results were like this: [root@pilotmedia root]# rpm -Uvh MySQL-3.23.51-1.i386.rpm Preparing...### [100%] package MySQL-3.23.51-1 is already installed OK Well, I decided to uninstall the installed package... but... [root@pilotmedia root]# rpm -e MySQL-3.23.51-1.i386.rpm error: package MySQL-3.23.51-1.i386.rpm is not installed So, any ideas what could I do? It's rather interesting situation... Thanks, Ville Mattila Ikaalinen, Finland - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re-installing MySQL
In the last episode (Aug 12), Ville Mattila said: Hello folks, I'd need to re-install MySQL server on my RH 7.3 box. As I installed the server from RPMs, I tried to update MySQL with rpm and -Uvh options. Results were like this: [root@pilotmedia root]# rpm -Uvh MySQL-3.23.51-1.i386.rpm Preparing...### [100%] package MySQL-3.23.51-1 is already installed OK Well, I decided to uninstall the installed package... but... [root@pilotmedia root]# rpm -e MySQL-3.23.51-1.i386.rpm error: package MySQL-3.23.51-1.i386.rpm is not installed The package name does not include .rpm. -- Dan Nelson [EMAIL PROTECTED] - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query problem/mysql
Hi, friends: I have just created a small web site with php/mysql. I do a query into one of my tables whose .MYI=338K and .MYD=7.6M. I could only retrieve some part of the data and then the browser just died there. My query is select * from Forces. The primary key is combination of several fields. Table Force has 132 fields. Its size reaches 20 MB. How can I solve the problem? Will adding an index help? I am just intending to print out the whole table. Could you please help me out? Thank you very much! Duo From: Benjamin Pflugmann [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED] CC: Michael Collins [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Need reversible encryption as string Date: Sun, 11 Aug 2002 19:51:57 +0200 Hi. On Sun 2002-08-11 at 12:07:47 -0500, [EMAIL PROTECTED] wrote: At 9:25 -0700 8/11/02, Michael Collins wrote: [...] SELECT * FROM MYDB WHERE EmailAddress=$EmailEntered AND DECODE(LoginPassword,'MySalt')=$PasswordEntered If you write the query like that, yes, it will use a complete scan. I beg to differ. If there is an index on EmailAddress it will be used and only all entries with the same email address (which should be one or only one) will be scanned. But you could also use ... AND LoginPassword = ENCODE($PasswordEntered,'MySalt') But that's a good idea anyhow. Bye, Benjamin. -- [EMAIL PROTECTED] - 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 _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - 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
Query problem/mysql
Hi, friends: I have just created a small web site with php/mysql. I do a query into one of my tables whose .MYI=338K and .MYD=7.6M. I could only retrieve some part of the data and then the browser just died there. My query is select * from Forces. The primary key is combination of several fields. Table Force has 132 fields. Its size reaches 20 MB. How can I solve the problem? Will adding an index help? I am just intending to print out the whole table. Could you please help me out? Thank you very much! Frank _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - 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
Re: Query problem/mysql
In the last episode (Aug 12), duo fu said: I have just created a small web site with php/mysql. I do a query into one of my tables whose .MYI=338K and .MYD=7.6M. I could only retrieve some part of the data and then the browser just died there. My query is select * from Forces. The primary key is combination of several fields. Table Force has 132 fields. Its size reaches 20 MB. How can I solve the problem? Will adding an index help? I am just intending to print out the whole table. The browser just died there? My guess is it crashed trying to display the ~60MB html page required to display 20MB worth of data. How much memory do you have in the PC you're running the browser on? -- Dan Nelson [EMAIL PROTECTED] - 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
Crash with stack trace
Description: Mysql crashed How-To-Repeat: Don't know how to reproduce - server running a busy site with multiple concurrent clients. Impossible to tell which caused the crash, but I'll gather more evidence (try --log) and follow up if I can find anything specific. Fix: Wish I knew.. Submitter-Id: submitter ID Originator: Organization: spamcop.net MySQL support: licence Synopsis: mysql crashes Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.39 (Source distribution) Environment: System: Linux sandyman 2.4.18 #8 SMP Thu Jul 18 18:24:01 EDT 2002 i686 unknown Architecture: i686 - dual proc Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 19 12:57 /lib/libc.so.6 - libc-2.2.3.so -rwxr-xr-x1 root root 4783716 May 25 2001 /lib/libc-2.2.3.so -rw-r--r--1 root root 24721042 May 25 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 May 25 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --with-mysqld-user=mysql --with-unix-socket-path=/var/run/mysql/mysql.sock --localstatedir=/var/lib/mysql --with-pthread --enable-thread-safe-client --enable-assembler --with-raid --with-libwrap --without-bench i386-slackware-linux Resolved stack trace: 0x8071b58 + 134683480 0x825a668 + 136685160 0x8173840 + 135739456 0x816f5bd + 135722429 0x815960e + 135632398 0x812a29b + 135439003 0x8135131 + 135483697 0x8136142 + 135487810 0x8136332 + 135488306 0x8122a7d + 135408253 0x80c91ad + 135041453 0x80a6c41 + 134900801 0x807bad0 + 134724304 0x807d955 + 134732117 0x8078ed3 + 134713043 0x807ece1 + 134737121 0x80780ae + 134709422 0x8257c7c + 136674428 0x828d3ba + 136893370 Other log entries: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=53 max_connections=100 threads_connected=20 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 802411 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x86d75b50 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfc9e2f8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: [snip backtrace, decoded above] New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow inst\ructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x85bd180 = delete from statcache where spamdate 1028258534 thd-thread_id=195877 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 195877 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. - 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
mysql password file
is there a password file accessable from root for mysql? - 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
Re: Feature request; Field for comments
On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: I have just started using mysql for a database project and I wonder that one function is missing. I am using myphpadmin as utility. It is possible to write a comment to each table, but it is not possible to write a comment to each field. Why not? I think this would be a usefull feature which should be implemented, especially if you work with several members. I think this would be a great enhanchement, especially for bigger groups of developers. What do you think? Hi, Actually, do yourself a big favour and upgrade to the latest release of phpMyAdmin that I released earlier this evening. We support comment fields for columns ourselves now after we saw a feature request for it. -- Robin Hugh Johnson E-Mail : [EMAIL PROTECTED] Home Page : http://www.orbis-terrarum.net/?l=people.robbat2 ICQ# : 30269588 or 41961639 - 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
