Re: Help with Query
Thanks, this works great in the MySQL server...I guess I've never used temp tables before, but when I try to run this in a PHP script, I get table does not exist. How do I do this? --- Harald Fuchs [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED], Daren Cotter [EMAIL PROTECTED] writes: I have a table that keeps track of when members of my site are mailed. The important fields in the table are: member_id, mail_id I need to write a query that will return the # of members and # of mailings, like the table below: # of mailings sent # of members --- 1 10,000 2 20,000 ...... Meaning, there are 10,000 members that have been sent 1 mailing, and 20,000 members that have been sent 2 mailings. Is this possible in one query? I'd do it like that: SELECT nmails, COUNT(member_id) FROM ( SELECT member_id, COUNT(mail_id) AS nmails FROM tbl1 GROUP BY member_id ) AS nm GROUP BY nmails; Since MySQL versions 4.1.0 don't know about derived tables, you'd need a workaround: CREATE TEMPORARY TABLE nm SELECT member_id, COUNT(mail_id) AS nmails FROM tbl1 GROUP BY member_id; SELECT nmails, COUNT(member_id) AS nmem FROM nm GROUP BY nmails; [Filter fodder: SQL query] - 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 __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.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
Help with Query
I have a table that keeps track of when members of my site are mailed. The important fields in the table are: member_id, mail_id I need to write a query that will return the # of members and # of mailings, like the table below: # of mailings sent # of members --- 1 10,000 2 20,000 ...... Meaning, there are 10,000 members that have been sent 1 mailing, and 20,000 members that have been sent 2 mailings. Is this possible in one query? TIA, Daren __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.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: Help with Query
Jeff, That query simply gives me each mailing ID, along with the # of members associated with that mailing ID. What I NEED is to return the # of mailings sent to a member, and the number of members associated with that number. I.e., if I do: SELECT count(*) FROM member_mailings WHERE member_id = 1 That returns the number of mailings for member 1, say it's 25. That would be one tally in the 25 field for # of mailings sent. It's tough to explain, so I'm thinking I won't be able to accomplish it in one query? --- Jeff Shapiro [EMAIL PROTECTED] wrote: This should get you close: SELECT mail_id, count(member_id) AS `# of members` FROM yourtable GROUP BY mail_id; At 18:44 -0800 3/17/03, Daren Cotter wrote: I have a table that keeps track of when members of my site are mailed. The important fields in the table are: member_id, mail_id I need to write a query that will return the # of members and # of mailings, like the table below: # of mailings sent # of members --- 1 10,000 2 20,000 ...... Meaning, there are 10,000 members that have been sent 1 mailing, and 20,000 members that have been sent 2 mailings. Is this possible in one query? TIA, Daren -- _ ____ +--+ / | / /__ _/ /_ _|Jeff Shapiro | / |/ / _ \/ __ \/ ___/ __ \/ __ `/|Photography and Graphic Design| / /| / __/ / / (__ ) / / / /_/ / |Colorado Springs, CO, USA | /_/ |_/\___/_/ /_//_/ /_/\__,_/ |www.nensha.com ||| [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 __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.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: Help with Query
This seems to be doing the same thing as the previously mentioned query...simply listing all mailing IDs, along with the # of members it was sent to. I've included both queries with their results below. mysql SELECT COUNT(member_id), COUNT(mailing_id) FROM member_mailings GROUP BY mailing_id; +--+---+ | COUNT(member_id) | COUNT(mailing_id) | +--+---+ |1 | 1 | |25000 | 25000 | |1 | 1 | |25000 | 25000 | |53855 | 53855 | |53897 | 53897 | |53247 | 53247 | |15000 | 15000 | |1 | 1 | |1 | 1 | | 140901 |140901 | |1 | 1 | +--+---+ 12 rows in set (0.57 sec) mysql select mailing_id, count(*) from member_mailings group by mailing_id; ++--+ | mailing_id | count(*) | ++--+ | 1 |1 | | 2 |25000 | | 3 |1 | | 4 |25000 | | 6 |53855 | | 7 |53897 | | 8 |53247 | | 11 |15000 | | 12 |1 | | 13 |1 | | 15 | 140901 | | 16 |1 | ++--+ 12 rows in set (0.56 sec) --- Zak Greant [EMAIL PROTECTED] wrote: On Mon, Mar 17, 2003 at 09:52:44PM -0800, Daren Cotter wrote: Jeff, That query simply gives me each mailing ID, along with the # of members associated with that mailing ID. What I NEED is to return the # of mailings sent to a member, and the number of members associated with that number. I.e., if I do: SELECT count(*) FROM member_mailings WHERE member_id = 1 That returns the number of mailings for member 1, say it's 25. That would be one tally in the 25 field for # of mailings sent. It's tough to explain, so I'm thinking I won't be able to accomplish it in one query? Hello Daren, Assuming that your table looks something like this: +-+---+-+-+ | ... | member_id | mail_id | ... | +-+---+-+-+ | ... | 1 | 1 | ... | | ... | 2 | 1 | ... | | ... | 3 | 1 | ... | | ... | 1 | 2 | ... | | ... | 2 | 2 | ... | | ... | 3 | 3 | ... | +-+---+-+-+ Then this query should return the information that you desire: SELECT COUNT(member_id), COUNT(mail_id) FROM member_mailings GROUP BY mail_id; Cheers! -- Zak Greant MySQL AB Community Advocate - 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 __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.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
Multiple Referral Levels
I have a question regarding tracking multiple referral levels in a database. The number of referral levels tracked needs to be at least 4, but should be able to be expanded later (without modifying the database). The first design I considered was: table: id int(8) unsigned not null auto_increment, referer int(8) unsigned null, primary key (id), key tbl_referer(referer)); What I need to be able to do is give a breakdown of the # of members referred (on each level) for a specific member...say, member 10. Getting the # of referrals on level 1 is no problem: SELECT COUNT(*) FROM table WHERE id = 10 The second level isn't too tough either, using a simple join. But what about when I get down to level 4? Is it even possible to get this info in one query? How about level 10? The only other thing I can think of doing is storing not just the referer in the table, but something like: id ref1 ref2 ref3 ref4 ref5 etc... All of the logic would need to be taken care of during member registration, and querying to find the number of referrals on any given level for a member would be simple. However, this method does not allow for easy expansion of referral levels, which is what I want, and is probably not the best way of doing things. Can anyone offer any insight? __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.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: Load Data Infile
I would use some sort of scripting language (PHP for example) to format the date, and have that script import the data into your table, rather than using LOAD DATA INFILE. If you post a sample of how your data file is formatted, someone will gladly help you out. You might want to post this to the PHP mailing list instead, as this solution is somewhat off-topic from MySQL. --- Serge Paquin [EMAIL PROTECTED] wrote: This is the only option? That's not a very automated process and I must import this data once a day. I also will not be able to get them to reformate their data since I'm only one of many many people downloading everyday. Is their no way for load data to do this automaticaly? Serge. On Tue, 20 Aug 2002 21:31:45 +0100 DL Neil [EMAIL PROTECTED] wrote: Hello Serge, I am having troubles with Load Data Infile and cannot figure out the problem. I have a date field but the format is 'Jan 1 1986' rather than mysql's default. So when I import I only get '-00-00' rather than the date. How can I tell MySQL what the format is? I have read through the manual and couldn't find anything. Spent about an hour now looking and now hoping someone here has the answer. Alter the table by replacing the date field with a temporary replacement formatted as text (for example). Load the data. Alter the table to add the date field back into the table. Use SQL functions to format the date according to ISO. Alter the table to remove the temporary field. or Go back to the original source and have the date formatted correctly (ISO standard). Regards, =dn - 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 __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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
Data Directory
My Linux installation only has about 1gb in the /var partition, so I need to relocate my databases to the /home partition. I'm pretty sure the following commands will shutdown mysql, move the data directory, create a symbolic link, and then restart mysql. My question is, is this complete, or do I need to chown/chgrp/chmod the new symbolic link directory? mysqladmin -u root -p shudown mv /var/lib/mysql /home ln -s /home/mysql /var/lib/mysql /etc/rc.d/init.d/mysqld start Query, SQL __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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: Data Directory
What is the purpose of creating the symlink if I manually edit the data directory variable? Would it be better to: a) Move the data, and edit the data directory to the new location b) Move the data, and create a symlink to the new data TIA, --- Rahadul Kabir [EMAIL PROTECTED] wrote: Daren Cotter wrote: My Linux installation only has about 1gb in the /var partition, so I need to relocate my databases to the /home partition. I'm pretty sure the following commands will shutdown mysql, move the data directory, create a symbolic link, and then restart mysql. I think you should also change the datadir value in /etc/rc.d/init.d/mysql /usr/bin/safe_mysqld set the datadir=/home/mysql or something My question is, is this complete, or do I need to chown/chgrp/chmod the new symbolic link directory? nope mysqladmin -u root -p shudown mv /var/lib/mysql /home ln -s /home/mysql /var/lib/mysql /etc/rc.d/init.d/mysqld start Query, SQL __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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 __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.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
Impossible Query?
I have three tables, affiliates, clients, and sales. The affiliates table stores all of the information about affiliates, clients about clients, sales about sales. In the clients table, there is a field for affiliate_id (affiliates refer clients), and in the sales table there is a field for client_id. I need a query that will show me a list of all affiliates and the number of sales each affiliate has generated. I know this will involve a left join, but I can't figure it out, since it involves that third table. Which actually brings up another question: would be be better to store the affiliate_id in the sales table? The reason I do it this way, is because if an affiliate refers a client, and the client is involved in numerous sales, the affiliate should be credited each time. TIA! - 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
Impossible Query?
I have three tables, affiliates, clients, and sales. The affiliates table stores all of the information about affiliates, clients about clients, sales about sales. In the clients table, there is a field for affiliate_id (affiliates refer clients), and in the sales table there is a field for client_id. I need a query that will show me a list of all affiliates and the number of sales each affiliate has generated. I know this will involve a left join, but I can't figure it out, since it involves that third table. Which actually brings up another question: would be be better to store the affiliate_id in the sales table? The reason I do it this way, is because if an affiliate refers a client, and the client is involved in numerous sales, the affiliate should be credited each time. TIA! - 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
More Query Help
First off, I would like to thank everyone on this lists who helps people like myself! I'm having trouble with the following query: SELECT username, first_name, email, DATE_FORMAT(signup_date, '%b %e, %Y') AS signup_date FROM members WHERE referer = (id) It selects a list of all members from the database referred by a certain member. However, the query needs to be modified to also select the number of people that person has referred. I planned to just send another query looping through the results, but if a member has referred 100 people, that's 100 queries, and probably not the best idea? So to do this query, do I need to left join the table to itself? Maybe something like: SELECT m1.username, m1.first_name, m1.email, DATE_FORMAT(m1.signup_date, '%b %e, %Y') AS signup_date, COUNT(m2.id) AS count FROM members AS m1 LEFT JOIN members AS m2 ON m1.referer = m2.id WHERE m1.referer = 4 GROUP BY m1.referer Is that even close? heh - 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
More Query Help [more info]
I probably did not provide enough info the first time. The members table is setup as follows: Id (primary key) Referer (relates to the primary key of the table) I am given a member ID, say 4. I need to display all members who have been referred by member 4 (obviously no problem). However, for each row that matches, I also need the number of members referred by THAT member. I tried a simple join, but it doesn't display members with 0 referrals, so I'm thinking I need to left join the table to itself. The query I initially posted: SELECT m1.username, m1.first_name, m1.email, DATE_FORMAT(m1.signup_date, '%b %e, %Y') AS signup_date, COUNT(m2.id) AS count FROM members AS m1 LEFT JOIN members AS m2 ON m1.referer = m2.id WHERE m1.referer = 4 GROUP BY m1.referer; Does not work. It returns only one row (should be 3)...the count at the end is 3, which is the total of all members referred by all the members that should be returned...leading me to think I'm close, just not quite there! Would appreciate any help. Thanks all =) - 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
Left join?
I have the following tables: Member_interests: Member_id Interest_id Interests: Name Interest_id I need a query that selects each interest name, and the # of members who have selected it...sample output: Boating 25 Hiking 10 .. Swimming0 Jumping 0 Talking 0 The following query works great, but does not display the Interest names with 0 members: SELECT DISTINCT(mi.interest_id), COUNT(*) AS count, i.name FROM member_interests AS mi, interests AS i WHERE mi.interest_id = i.id GROUP BY mi.interest_id ORDER BY i.name Is there a way to have the 0's displayed as well? I'm thinking a left join would probably be involved? - 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: Normalization question
I have a question about the setup of the tables in my database. In my members table, I store a lot of info about demographics, such as marital status, income, etc. The way I'm doing this is the enum type. Is it better to use this, or would it be better to create separate tables for each demographic I want to store? Advantages of this would be no additional programming required when I want to list the # of members who have selected each demographic...plus I could add new items any time I wanted (i.e. I want to add widowed to the list of marital statuses). What is the correct way to do this? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 04, 2002 6:24 PM To: Daren Cotter Subject: Re: Normalization question Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I have a question about the setup of the tables in my database. In my members table, I store a lot of info about demographics, such as marital status, income, etc. The way I'm doing this is the enum type. Is it better to use this, or would it be better to create separate tables for each demographic I want to store? Advantages of this would be no additional programming required when I want to list the # of members who have selected each demographic...plus I could add new items any time I wanted (i.e. I want to add widowed to the list of marital statuses). What is the correct way to do this? - 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
I am having major troubles creating this query...ok here's some background info: I have three tables: members, which contains info about the member, such as city, state, zip, marital status, etc; interests, which stores just an interest_id and name; and member_interests, which stores just member_id and interest_id. What I need to do is create a query that selects certain members from the DB depending on certain demographics. For example, the query might need to select all members from the DB where their country is USA, Marital Status is Single, and have Golf selected as an interest. The first two are no problem...but, is there any way to create ONE query that would validate all the info stored in members table, as well as the member_interests table? And if not, can someone provide a sample solution method? TIA! - 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 Help (more)
As a follow up to my previous question, two possible solutions came to mind: 1) Query members table for all members matching criteria stored in that table (country, marital status, income, etc). Then, take all those member_ids, and query member_interests table for members who match there. i.e., SELECT member_id FROM member_interests WHERE interests_id = (XX) AND member_id IN (list of all member IDs here) 2) #1, reversed, which would make more sense, since I also need name and email. If there's any way to do this in one query, I should do it that way...otherwise, am I close on the best solution? Again, TIA! - 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
Nested query
I need to run a query that selects the usernames of all the members a particular member has referred on the second level. So member 5 refers 10, 11, and 12, I need the usernames of everyone referred by 10, 11, or 12. Currently I run one query to get 10, 11, 12, make that a string, then do another query with member in ($string). Is there a way to do it with a nested query? Something like: SELECT username FROM members WHERE referer IN (SELECT id FROM members WHERE referer = 5); TIA! - 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
Nested query
I need to run a query that selects the usernames of all the members a particular member has referred on the second level. So member 5 refers 10, 11, and 12, I need the usernames of everyone referred by 10, 11, or 12. Currently I run one query to get 10, 11, 12, make that a string, then do another query with member in ($string). Is there a way to do it with a nested query? Something like: SELECT username FROM members WHERE referer IN (SELECT id FROM members WHERE referer = 5); TIA! - 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 syntax help
What you had looks fine except the date...change what you had to: AND date = '2002-03-17'; # date needs quotes around it Should work. -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 8:49 AM To: mysql list (choose midget) Subject: Query syntax help I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no'# shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date = 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! Thanks, Rory - 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: [PHP] Re: A stupid question...
I would think the easiest way would be to use the string functions of MySQL itself...then you don't have the overhead of the PHP application having to check each row of data (a wise person on this board once answered a question similar to this for me). Somthing like... $query = SELECT * FROM tbl_name WHERE LEFT(last_name, 1) == 'A'); Using a variable passed in... $query = SELECT * FROM tbl_name WHERE LEFT(last_name, 1) == '$letter'); NOTE: LEFT() is a special function, I'd consider it a derivative of SUBSTRING() -Original Message- From: michael kimsal [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 10, 2002 8:17 PM To: Chuck \ Pup\\ Payne Cc: mysql lists.mysql.com Subject: Re: [PHP] Re: A stupid question... Chuck Pup Payne wrote: I want to sort by a letter in a colomn. Let say I want to sort the colomn last_name. I can do order by but I can do just the A's. http://www.myserver.com/mysort.php?Letter=A Like to create a link on a web A then sort only the last name are A. I hope that's helps. I can order by, but I can't so a sort like the example above. Chuck Payne Magi Design and Support One of two things to do: When you're inserting the data, figure out the first letter and store that as a separate column (letter perhaps) Second, probably easier to implement in your case with existing data, is to use LIKE. $sql = select * from datatable where last_name like '$letter%'; The % is a wildcard symbol, so if $letter is a then a last name of adams, aames, aston, etc. would all match. I know there's someway to have mysql do a string manipulation to compare just part of a column's data with something, so you could do something similar to a 'substr' in PHP - but it's late and I can't remember off the top of my head. Hope that helps... -- Michael Kimsal http://www.phphelpdesk.com Taking the ? out of ?php 734-480-9961 - 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
very tough query
ok, here are the 3 tables i have that are related: mysql desc poll_questions; ++-+--+-++-- --+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++-- --+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++-- --+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ I'm pretty sure this is completely normalized. I have no problem joining the 3 tables to do SELECTS...but what happens when I want to delete a poll? I can easily elete from the poll_questions and poll_answers tables, but how do I delete the necessary rows in the poll_votes table? I tried doing a DELETE query just like a SELECT, but it didn't work: DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); Please help! Thanks! - 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: very tough query
Thanks much for the try, but I get an error: mysql DELETE - poll_questions, poll_answers, poll_votes - FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); ERROR 1064: You have an error in your SQL syntax near 'poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, ' at line 2 -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 8:27 AM To: [EMAIL PROTECTED] Cc: Daren Cotter Subject: Re: very tough query Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - 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
indexing question
I am going to have a table called mail_queue, that is going to store the member id, the mailing id, and the date. Each time a new mailing is setup (usually daily), another 100,000 rows (one for each member) will be added to the table. When a member reads the mailing, that row will be removed from the table. This is the most normalized way to set this up, and allows me to: *) Make sure members don't get credit for reading the same mailing twice *) Not allow members who aren't allowed credit for a specific mailing don't get credited *) See how many people read each mailing (by determining how many mailings were sent, and subtract the number of rows left in the table for that mailing id) My question is about indexing. I know a lot about indexing, and using left-most indexes, etc...since this table will have millions of rows, I'm wondering what kind of index I should use? I for sure need an index on the mail_id field (the primary key will be between member_id and mail_id), so an index of mail_id AND member_id makes sense. This index would work for queries like: WHERE mail_id = XXX WHERE mail_id = XXX AND member_id = YYY However, that leaves out queries like: WHERE member_id = YYY That is also a query I need to be able to do on a regular basis, and if this query yields 20,000 results, with 5 million rows in the table, it's going to take forever (I assume). So I was thinking about making two indexes: mail_id AND member_id member_id Would this take up way too much disk space? Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 - 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
weird error
has anyone ever seen this MySQL error? The database has a high-traffic table with about a million rows, is this the problem? Warning: MySQL: Unable to save result set TIA, Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 - 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 help...
David, The way I accomplish this is using two queries...first I write a general query that shows the IP address of any IP that has more than one user associated with it: SELECT DISTINCT(ip) AS ip, COUNT(*) AS count FROM members GROUP BY ip HAVING COUNT 1 ORDER BY count DESC I tie this into a PHP script that lists all the IPs (as links) to another report. This report selects all the usernames for that IP SELECT username FROM members WHERE ip = '$ip' ORDER BY username This works well for me, you just have to pass the variable from one page to another. Hope that helped! Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 -Original Message- From: David Wolf [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 2:17 PM To: Steve Meyers; [EMAIL PROTECTED] Subject: Re: Query help... It's still not quite doing what I want. I only want a list of IP's where there are more than 1 instance of an ip--and then display each of the multiple occurrences of the single ip (and repeat for each ip which has more than 1 occurrence).. The queries are all so close, but, don't give what I want :( David - Original Message - From: Steve Meyers [EMAIL PROTECTED] To: David Wolf [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 24, 2001 2:38 PM Subject: RE: Query help... I think you're looking for: SELECT username, ip, count(*) FROM users GROUP BY 1, 2 Steve Meyers -Original Message- From: David Wolf [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 12:21 PM To: [EMAIL PROTECTED] Subject: Query help... I'm trying to come up with a query to do the following... I'm not having lots of luck :( The table is a user database. The columns I'm interested in are: username, lastip. I'm interested in pulling information out that would show usernames for each IP that appears more than once in the database.. i.e. usernameip persona1.1.1.1 personb1.2.3.4 personc1.1.1.1 I'd be interested in seeing persona and personc (both have 1.1.1.1 ip's) I've tried.. SELECT lastip,count(*) FROM users GROUP BY lastip; but that only gives me an unordered list of the # of times an IP is used.. SELECT distinct(count(*)) FROM users GROUP BY lastip; but that doesn't join the info -- nor give me the ip's -- just frequency (when I add 'lastip' to the select I get an error) I'm thinking that I'm going about this the wrong way.. But, I can't quite get a clue. Can anyone help me out here? Thanks! David Wolf - 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 - 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
very strange query behavior
can anybody explain this to me? mysql select count(*) from members where country_id = 1 and read_array not like '%20270%'; +--+ | count(*) | +--+ |34884 | +--+ mysql select count(*) from members where country_id = 1 and read_array like '%20270%'; +--+ | count(*) | +--+ |13554 | +--+ mysql select count(*) from members where country_id = 1; +--+ | count(*) | +--+ |63546 | +--+ 34884 + 13554 63546 This is a very important aspect of our business, can anyone offer any insight as to what may be wrong? TIA, Sincerely, Daren Cotter CEO, InboxDollars.com [EMAIL PROTECTED] http://www.inboxdollars.com (507) 382-0435 - 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
tough query
i have a query that needs to do the following: in my members table, i store the member id, and the referer id of the member that referred them (the referer_id links to the member_id). for example, a member signs up and gets the id of 0200289, and was referred by 0001008. anyway, over time, when members are deleted, the referer field is invalid. so if member 0001008 is deleted, member 0200289 has an invalid referer_id stored in the db. i need to produce a query that is going to show me all accounts w/ an invalid referer id. i am pretty sure this can be done by doing a left join from the members table to itself...can anyone help me out? i think this is somewhat close: select 1.member_id, count(*) as count from members as 1 left join members as 2 on 1.referer_id = 2.member_id where 2.first_name is not null; tia, Daren Cotter CEO, InboxDollars.com http://www.inboxdollars.com (507) 382-0435 - 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: normalization question
The data wouldn't need to be stored for any longer than two to three months, so that shouldn't be a problem...what about my query to get all members that have not read the mailing, is that possible (assuming I don't use the reverted logic you were talking about). Daren Cotter CEO, InboxDollars.com http://www.inboxdollars.com (507) 382-0435 -Original Message- From: Rene Churchill [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 19, 2001 7:34 AM To: Daren Cotter Cc: [EMAIL PROTECTED] Subject: Re: normalization question Hi Daren, How long do you need to store this information? The normalized table that you describe is simple and easy to index. Having several million rows in the table won't bother MySQL. So is it sufficient to keep the data around for a month and then prune it out of the database? Long term records could be dumped out into text logfiles for safe keeping. Another possiblity is to invert your storage logic. By that, I mean insert a row in the cross-reference table for every user that has NOT read the email. Then when they read it, remove that row from the table. This will automatically prune down the table size as more and more of your readers check their email. I would HIGHLY suggest using the DELAYED option on that size of an insert. You may not want to go this logic inversion route because of the havoc it will play with other queries. For example, since any user that does NOT have a matching row in the cross-reference table is assumed to have read the message, any new user joining you will automatically be assumed to have read all messages to date. Rene Daren Cotter wrote: I have a table which needs to store which emails each member has read and had their account credited for. Currently, I have a field read_array longtext in my main members table, and each time a member reads an email, it simply concats to the end of this array. For example, they read email 288, their read array is: 288 They read 298, the read_array is: 288||298 Using this method (which is far from normalization, I know), I have been able to accomplish the two major things I need to be able to accomplish: 1) Insure that no member can get credit for reading the same mailing twice (check their current read_array, using ereg()) 2) Run a query to see how many members have read each mailing (WHERE read_array LIKE '%mail_id%') I know that both of these tasks would be easier if I created a new table, and stored the member_id, mail_id, and date, and the table would then be normalized...however, I send approximately one new mailing per day, and this would mean 100,000 new rows in this table every day. Can somone offer me expert advice as to which method is better? Using an array that will grow to unlimited size, or using a table that is going to grow much larger every day? Also, as a deterrant to switching to the normalized method...I frequently run queries to do the following: *) After I send a mailing, a week later, I send the mailing again to all members who did not confirm reading the email. My Query is like: select member_id, (more) FROM members WHERE read_array not like '%mail_id%' Is there any possible way I could accompish this task in one query if I had this process normalized? -- Rene Churchillhttp://www.vtwebwizard.com Internet Consulting 802-244-5151 Specializing in Web Programming - 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
normalization question
I have a table which needs to store which emails each member has read and had their account credited for. Currently, I have a field read_array longtext in my main members table, and each time a member reads an email, it simply concats to the end of this array. For example, they read email 288, their read array is: 288 They read 298, the read_array is: 288||298 Using this method (which is far from normalization, I know), I have been able to accomplish the two major things I need to be able to accomplish: 1) Insure that no member can get credit for reading the same mailing twice (check their current read_array, using ereg()) 2) Run a query to see how many members have read each mailing (WHERE read_array LIKE '%mail_id%') I know that both of these tasks would be easier if I created a new table, and stored the member_id, mail_id, and date, and the table would then be normalized...however, I send approximately one new mailing per day, and this would mean 100,000 new rows in this table every day. Can somone offer me expert advice as to which method is better? Using an array that will grow to unlimited size, or using a table that is going to grow much larger every day? Also, as a deterrant to switching to the normalized method...I frequently run queries to do the following: *) After I send a mailing, a week later, I send the mailing again to all members who did not confirm reading the email. My Query is like: select member_id, (more) FROM members WHERE read_array not like '%mail_id%' Is there any possible way I could accompish this task in one query if I had this process normalized? TIA, Daren Cotter - 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
Left Join Query
This query is driving me nuts! I have two tables, affiliates, and advertisers. The advertisers table has an affiliate_id field, which matches the affiliates tables' primary key. I'm trying to write a query that will return all affiliate id's, contact names, and the # of rows in the advertisers table. I'm sure this requires a left join, but the following query does NOT work: SELECT af.affiliate_id, af.contact_name, af.contact_email, count(ad.affiliate_id) AS count FROM affiliates AS af LEFT JOIN advertisers AS ad ON af.affiliate_id = ad.affiliate_id GROUP BY ad.affiliate_id ORDER BY count DESC Note, I want to return all affiliates, even if there are no matching rows in the advertisers table, which is why I'm assuming a left join is necessary. TIA, Daren Cotter - 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
tough query
i have 3 tables: offers, rewards, and sources. sources has just a source name and source id. both the offers and rewards tables have a source_id field which relates to the sources table. i need to write a query that selects the source name and the count in each table with that source id. is this possible? i tried: select s.source_name, count(o.source_id) as offer_count, count(r.source_id) as reward_count from sources as s, offers as o, rewards as r where s.source_id = o.source_id and s.source_id = r.source_id group by s.source_id order by s.source_name; the AND makes it produce no results. so i tried: select s.source_name, count(o.source_id) as offer_count, count(r.source_id) as reward_count from sources as s, offers as o, rewards as r where s.source_id = o.source_id or s.source_id = r.source_id group by s.source_id order by s.source_name; notice the or instead of and...this produces invalid results. someone help please! =) TIA, Daren Cotter - 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 Help
I have a database which has about 100,000 emails. I need to write some function to retrieve the domains of all these emails, and the count of each, to help catch cheaters. I began by just selecting all the emails with my query, and trying to use PHP to do all the work, but I think this could all be done w/ mysql. I'm thinking there must be some way, using the string functions, to write a query that just retrieves the domain of each email, and then I could use DISINCT and COUNT for the rest. By domain, I mean this: [EMAIL PROTECTED] becomes yahoo.com, [EMAIL PROTECTED] becomes subdomain.domain.com. If anyone could help me out with this query, I'd really appreciate it. TIA, Daren Cotter - 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 Question
I have a members table which stores, among other things, a Member ID and a Points field. We get reports sent to us in a CSV format, with a list of all Members signing up for a certain offer. Basically, what I need to do, is create a query like this: UPDATE members SET points = points + (offer_value) WHERE member_id = (member_id) for each member ID. With a small VB program I could create a file that has these queries (upwards of 10,000), but I've heard something about a query like this: UPDATE members SET points = points + (offer_value) WHERE member_id IN (7,8,9,10,23,etc) Just wondering which format would be better for the server, assuming there are upwards of 10,000 member_ids, whether it be one of these solutions or another one. TIA, Daren Cotter - 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
Referer Count
I have a table, which keeps track of member information (including which member referred the member). To get a count of the # of referrals for member 25, my query is: SELECT COUNT(*) FROM members WHERE ref1 = 25; To get a list of the top referers and the # of referrals they have, my query is: SELECT DISTINCT(ref1) AS member_id, COUNT(*) AS count FROM members GROUP BY ref1 ORDER BY count DESC LIMIT 100 However, what I need, is a list of the top referers, along with their member information...name, email, password, etc. I tried using the following query, as I read about it in the MySQL manual, but it doesn't work: SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password, concat(UCASE(SUBSTRING(b.first_name,1,1)), LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name AS name, b.email, b.html_mail, b.ref1, DATE_FORMAT(b.signup_date, '%b %e, %Y') AS signup_date FROM members AS a, members AS b WHERE a.active_member = 'Y' AND a.ref1 = b.member_id GROUP BY a.ref1 ORDER BY count DESC LIMIT 10 This gives me correct info for the distinct a.ref1 and count fields, and produces data for the rest of the fields, but it is not actually that member's data. Is this possible to do with one query? If I want to get the top 100 referers' data, I don't want to do 100 separate queries. Please help! Thanks, Daren Cotter - 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: Referer Count
Actually, I did get it figured out...it was a small mistake SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password, concat(UCASE(SUBSTRING(b.first_name,1,1)) , LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name AS name, b.email, b.html_mail, b.ref1, DATE_FORMAT(b.signup_ date, '%b %e, %Y') AS signup_date FROM members AS a, members AS b WHERE a.active_member = 'Y' AND a.ref1 = b.member_i d GROUP BY a.ref1 ORDER BY count DESC LIMIT 20; Daren Cotter CEO, InboxDollars.com http://www.inboxdollars.com (507) 382-0435 -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 22, 2001 12:58 PM To: Daren Cotter; [EMAIL PROTECTED] Subject: Re: Referer Count At 2:31 PM -0800 3/22/01, Daren Cotter wrote: I have a table, which keeps track of member information (including which member referred the member). To get a count of the # of referrals for member 25, my query is: SELECT COUNT(*) FROM members WHERE ref1 = 25; To get a list of the top referers and the # of referrals they have, my query is: SELECT DISTINCT(ref1) AS member_id, COUNT(*) AS count FROM members GROUP BY ref1 ORDER BY count DESC LIMIT 100 However, what I need, is a list of the top referers, along with their member information...name, email, password, etc. I tried using the following query, as I read about it in the MySQL manual, but it doesn't work: You're asking for a summary as well as a listing. You'll need two queries. One to get the member_id for the top 25 referers, another to pull the information for those members. You can use ...WHERE member_id IN (list) on the second query, where "list" is a comma separated list of the member_id values. Probably best to put this stuff in a Perl script or something so that you can manipulate the list and construct the queries easily. SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password, concat(UCASE(SUBSTRING(b.first_name,1,1)), LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name AS name, b.email, b.html_mail, b.ref1, DATE_FORMAT(b.signup_date, '%b %e, %Y') AS signup_date FROM members AS a, members AS b WHERE a.active_member = 'Y' AND a.ref1 = b.member_id GROUP BY a.ref1 ORDER BY count DESC LIMIT 10 This gives me correct info for the distinct a.ref1 and count fields, and produces data for the rest of the fields, but it is not actually that member's data. Is this possible to do with one query? If I want to get the top 100 referers' data, I don't want to do 100 separate queries. Please help! -- Paul DuBois, [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 - 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
Data File
I have a query I'm sending to the DB...however, instead of having the server send me the data back, I want to put it into a CSV file for use in an Excel Spreadsheet. This means either tab-delimited, or separated by commas. Can someone help? Thanks, - 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
Error 2013
I recently moved to a new server, and successfully copied the database over to the new server. However, at least half of my queries are returning the error: ERROR 2013: Lost connection to MySQL server during query The error seems to be random, and only with longer queries. I'm guessing this is one of the server variables. Can someone please tell me which one it is, so I can fix it? Thanks - 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
Resolution
Since I posted so many times about my problem, I feel obligated to notify everyone I have resolved it, and exactly what was wrong. I have two fields: last_update, and last_login. Both fields are timestamps, with defaults of "000". My queries were doing things like: MONTHNAME(last_login). Apparently, the previous version of MySQL I was using, 3.22.23 I believe, didn't have a problem with this. The new version I'm using, 3.23.32, apparently has a problem with this. To resolve this, I modified all my queries to use IF()...so I use: IF (last_login = '000', 'NEVER', (formatted date/time here)) AS last_login Just thought I would post to help anyone out who may have the same problem in the future! - 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
Index Question
My question is about indexes...basically, I'm wondering how many indexes is too much, and what the drawbacks are of having more indexes on a table? I'm guessing INSERT and UPDATE queries probably take longer? My table has the following fields: member_id, first_name, last_name, username, password, email, street1, street2, city, state, zip, country_id, signup_date, signup_ip, ref_id, sex, age, income I know for sure I will want to index the username and email fields. However, should I index the entire field, or would 5 or 6 characters be enough to index? I will also be selecting BY country_id, signup_date, and ref_id as well, is it okay to index all of them? I will also be using the signup_ip field to delete duplicate accounts, should I therefore index that field so the "distinct" operator works better? Is it okay to have 6 indexes on a table like this? The table may have upwards of a million entries. TIA! - 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
weird date behavior
I have a field in my table that stores the date a member has signed up...i run a query using distinct to show me how many members signup each day. Yesterday, our server crashed, and today i am seeing weird behavior with mysql: SELECT count(*) FROM members WHERE signup_date = now(); 122 SELECT count(*) FROM members; 108984 INSERT INTO members (blah blah) VALUES (blah blah); SELECT count(*) FROM members WHERE signup_date = now(); 122 SELECT count(*) FROM members; 108985 The total count is incremented, but not for today's date. Anyone have any ideas? - 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
more date problems
okay, this is a follow-up to my past email... since I KNOW more than 122 people have signed up today, I did the following query: SELECT signup_date, member_id FROM members ORDER BY member_id DESC limit 200; there are at LEAST 200 people that have signed up today. however, when i do: SELECT count(*) FROM members WHERE signup_date = now(); I only get 122 results! And YES, I have tried "2001-03-01" instead of "now()". I ran ISAMCHK with the extended flag, and got no errors whatsoever. Does anyone have any idea what could be going on? I run this query quite often, is it possible the result is somehow being saved in a buffer? Should I shutdown the mysql server and restart it? - 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: more date problems
Quentin, that does not work either, I still get the 125 number, when there are actually 500 records =( I am using version 3.22.32 -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 01, 2001 4:18 PM To: 'Daren Cotter'; [EMAIL PROTECTED] Subject: RE: more date problems Hi, Can you try "where signup_date '2001-02-28 23:59:59'", and see what that gives. Also, what version are you on - I have had some date problems where the end of one month and the begining of another get confused (usually when doing a UNIX_TIMESTAMP). Maybe then use mysqlbug to report the issue, and include the results of your tests. Not much help, I know, but the more information you post, the more help we can be. Regards -Original Message----- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Friday, 2 March 2001 1:03 p.m. To: [EMAIL PROTECTED] Subject: more date problems okay, this is a follow-up to my past email... since I KNOW more than 122 people have signed up today, I did the following query: SELECT signup_date, member_id FROM members ORDER BY member_id DESC limit 200; there are at LEAST 200 people that have signed up today. however, when i do: SELECT count(*) FROM members WHERE signup_date = now(); I only get 122 results! And YES, I have tried "2001-03-01" instead of "now()". I ran ISAMCHK with the extended flag, and got no errors whatsoever. Does anyone have any idea what could be going on? I run this query quite often, is it possible the result is somehow being saved in a buffer? Should I shutdown the mysql server and restart it? - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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: more date problems
Ok, here goes... mysql select member_id, signup_date from members order by member_id desc limit 410; +---+-+ | member_id | signup_date | +---+-+ (here's the last 20 or so rows) | 0120472 | 2001-03-01 | | 0120471 | 2001-03-01 | | 0120470 | 2001-03-01 | | 0120469 | 2001-03-01 | | 0120468 | 2001-03-01 | | 0120467 | 2001-03-01 | | 0120466 | 2001-03-01 | | 0120465 | 2001-02-28 | | 0120464 | 2001-02-28 | | 0120463 | 2001-02-28 | | 0120462 | 2001-02-28 | | 0120461 | 2001-02-28 | | 0120460 | 2001-02-28 | | 0120459 | 2001-02-28 | | 0120458 | 2001-02-28 | +---+-+ This tells me that there are about 400 people that have signed up TODAY. The date is correct on the server: mysql select now(); +-+ | now() | +-+ | 2001-03-01 19:33:05 | +-+ So, the question is, why does this not return 400, like it should??? OK, weird! Now I am getting this error... mysql select count(*) from members where signup_date = '2001-03-01'; ERROR 1032: Can't find record in 'members' I have checked the tables using the checking utility, they show up fine. What's going on? =) -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 01, 2001 4:45 PM To: 'Daren Cotter'; [EMAIL PROTECTED] Subject: RE: more date problems Can you post some of the data - e.g the first 500 rows of the 'order by' select, and the results of the select with signup_date='2001-03-01'. Also, how about Select now(), signup_date from , to see what things are being compared. Regards Quentin -Original Message- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Friday, 2 March 2001 3:34 p.m. To: Quentin Bennett; 'Daren Cotter'; [EMAIL PROTECTED] Subject: RE: more date problems Quentin, that does not work either, I still get the 125 number, when there are actually 500 records =( I am using version 3.22.32 -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 01, 2001 4:18 PM To: 'Daren Cotter'; [EMAIL PROTECTED] Subject: RE: more date problems Hi, Can you try "where signup_date '2001-02-28 23:59:59'", and see what that gives. Also, what version are you on - I have had some date problems where the end of one month and the begining of another get confused (usually when doing a UNIX_TIMESTAMP). Maybe then use mysqlbug to report the issue, and include the results of your tests. Not much help, I know, but the more information you post, the more help we can be. Regards -Original Message----- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Friday, 2 March 2001 1:03 p.m. To: [EMAIL PROTECTED] Subject: more date problems okay, this is a follow-up to my past email... since I KNOW more than 122 people have signed up today, I did the following query: SELECT signup_date, member_id FROM members ORDER BY member_id DESC limit 200; there are at LEAST 200 people that have signed up today. however, when i do: SELECT count(*) FROM members WHERE signup_date = now(); I only get 122 results! And YES, I have tried "2001-03-01" instead of "now()". I ran ISAMCHK with the extended flag, and got no errors whatsoever. Does anyone have any idea what could be going on? I run this query quite often, is it possible the result is somehow being saved in a buffer? Should I shutdown the mysql server and restart it? - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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
date troubles
I should note, however, that this query works FINE: mysql select count(*) from members where signup_date = '2001-02-28'; +--+ | count(*) | +--+ | 732 | +--+ This APPEARS to work, but the "127" should be more like 420 mysql select count(*) from members where signup_date '2001-02-28'; +--+ | count(*) | +--+ | 127 | +--+ As opposed to before, when I got an error doing the following query, it now works, but does not return the correct value. I shut-down and restart the server, which is why I'm guessing I no longer get the error: mysql select count(*) from members where signup_date = now(); +--+ | count(*) | +--+ | 127 | +--+ Could this have anything to do with mysql caching this query somehow? I don't see how it works for one date, and not the others? - 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
Please help!
Someone please help me. This query has always worked until now, now all of a sudden I get a weird error: SELECT DISTINCT(signup_date), COUNT(*) AS count, CONCAT(SUBSTRING(MONTHNAME(signup_date),1,3), ' ', DAYOFMONTH(signup_date), ', ', YEAR(signup_date)) AS signup_date_display FROM members GROUP BY signup_date ORDER BY signup_date DESC; ERROR 1030: Got error 127 from table handler Does anyone know what could be wrong? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
