MAX select problem
Hello, I'm trying to get the date and amount of the most visits to my site over a given time period using : SELECT max(visits) as maximum FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) This gives me the right figure, but when I try to pull out the date that this occured on with, SELECT max(visits) as maximum, visit_date FROM visit WHERE (((visit_date = '$sdatestring') and (visit_date '$edatestring')) and (site_id=$site_id)) GROUP BY visit_date I get a completely different and wrong answer, any ideas? Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BOOLEAN Vs NON-Boolean
Hello, I've just upgraded to 4.1 for boolean search facilities, but I think I may lose more than I 've gained. I just really need the +keyword1 keyword2 functionality so all results must contain keyword1, but I then need to order by relevance score which I've lost. Is it worth performing two non-boolean searches to get what I need or could I use SELECT id, keywords, MATCH (keywords) AGAINST (keyword1 keyword2') AS score FROM table WHERE MATCH (keywords) AGAINST ('+keyword1 keyword2' in boolean mode) order by score desc; Which approach would have the biggest overhead, an could there be a better approach? Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple GROUP / ORDER problem
Hello, If got a simple sessions table basically holds a session id and datetime field for last modification also a session type, so I can have several records with the same session id, with different types and different modification time. I want to get the latest modified record for any given session, and I'm using SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC on this example data set : session_id | type | date_modified d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Although this returns the individual sessions in the correct order, the group by is returning the earliest record for that session_id so I get: d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Rather than d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Which is what I want, I'm sure this is a misunderstanding on my part, but does any one have any ideas? All the best, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set Problem
Is it possible to change the character set just for an individual table and if so which character set should I try to display this european characters? Cheers, Lee - Original Message - From: Sumito_Oda [EMAIL PROTECTED] To: Lee Denny [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 03, 2005 1:42 PM Subject: Re: Character Set Problem Hello, Is the MySQL server that you are using MySQL4.1.x or MySQL5.0.x? As for most binarys of PHP and MySQL, the default charset of the MySQL connection client is set as 'latin1'. Therefore, if charset with the server is not 'latin1', it is necessary to set the MySQL connection client properly. It is whether to set to use the charset that you use by default, to compile the binary or to set the MySQL connection first by 'SET NAMES' syntax. http://dev.mysql.com/doc/mysql/en/set-option.html Regards, -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set Problem
Hello, this is probably quite simple but I've got a text file that has non-english characters, when I view it I see : 'Dcouvrez un rseau europen d'htels et de restaurants beignant dans une atmosphre conviviale et familliale' I've imported this straight into my myisam DB which is set up with default charsets and collations - and these characters are just the same. I've looked into this but can't really grasp charsets and collations. I'd be happy to translate these codes back into the default charset (english characters). I'm using PHP to query the database, but would like to change the data in the DB if possible. I know this is a bit vague but I was wondering if anyone had any insight into this. Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplication of records within the same table.
Hello, I've got a table with a simple structure: site_id, page_id,header_text,main_text.. I just need to duplicate existing records with a new site_id, so : 1,1,Hello,some text.. 1,2,Hello Again, some more text... gets copied to 2,3,Hello,some text.. 2,4,Hello Again, some more text... page_id is already auto_increment. I'm sure that an INSERT .. SELECT statement should do it, but I'm unsure of the correct syntax. cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search Strategy - Need Help
Hello, I'm doing fairly straight forward fulltext searches, but I want to nest them - basically do a keyword search on 'phrase 1' and then search the results this returns for 'phrase 2', for example if phrase 1 is 'ford' and phrase 2 is 'focus' - I search once for 'ford' and then go through the record-set this returns for 'focus'. My first thought is use a temporary table - but is there a way of doing this with one query? Cheers, lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tricky Date Query
Hello, I need to perform a select that compares two dates I need to return all records that haven't had date_2 set after a given number of days since date_1. I'm sure this can be done in one query but I just can't get my head around this one. Can anyone help? Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Notifying User when DB is Updated via browser
Hello, This may be slightly off-topic, apologies if so but I was wondering if anyone could point me in the right direction. I'm currently storing applications from a web site into our mysql DB, our client has a web-based backoffice. He has to refresh his screen every 20 minutes to see the latest applications. Is there any way of using some sort client-server technology to alert the user, via the browser that a new entry has appeared in the DB - I'm already sending an email but this is still not the best solution. Thanks in advance, Lee Denny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Design and Bianry Operations
Hello, I'm after some advice on database design: I've got an object - for an example a hotel - and I want to keep information about this hotel, criteria that it either has or hasn't (TV, swimming pool etc). I want to search on criteria and return the most appropriate match. Bearing in mind I've currently got over 200 criteria and want to expand this, how should approach the design of my criteria table. Should I have a table with Hotel Id and then a char(1) (Y/N) field for each criteria and then a seperate look-up table for criteria name. I get the feeling there is a more efficient method using binary operations (only one field populated with zeros and ones) but I can't find anything to help in the manuals. Any thoughts? Lee Denny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC problems
I'm trying to use the mm.mysql JDBC drivers on windows NT I've just downloaded the mysql-connector-java-2.0.14.zip file and extracted it into my jdk1.2.2/jre/lib/ext folder. I try to connect with import java.sql.*; public class dbconnect { public static void main(String[] args) { String url = jdbc:mysql://mydomain.co.uk/mydatabase; String username = ; String password = ; // Load database driver if not already loaded try { Class.forName(org.gjt.mm.mysql.Driver).newInstance() ; Connection connection = DriverManager.getConnection(url, username, password); ... And get the error 'ClassNotFoundException'. I've tried the class 'com.mysql.jdbc.Driver' as suggested by the documentation in the .zip file but this gives the same error. I'm obviously putting the class files in the wrong place - but this is my first crack at Java so need help. Thanks in advance, Lee - 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
Perl DBI
Hi, How can i use Perl DBI to create tables in mysql? Denny __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.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
Migration to MySQL from flatfiles
Hello all, Please pardon my newbie-ness I've been looking at somehow migrating from a flatfile user system (/etc/passwd, etc) to a MySQL backend for authentication for all my processes... qpopper, postfix, and apache/frontage (ick) I can't seem to find anything that would enable me to migrate too easily... I've tried LDAP with an OpenLDAP testbox but it is SO SLOW!!! (I'm about to try IBM's LDAP Server to see how it performs) The problem is that I'll eventually have 3 or more servers for POP3, SMTP, and User Webspace And I need to consolidate to a single auth source Can anyone point me in the right direction? Denny Snyder Network Engineer Susquehanna Communications 1050 E. King St York, PA 17403 Office: (717)771-2613 Fax:(717)843-5400 [EMAIL PROTECTED] [EMAIL PROTECTED] Nothing in life is worse than SPAM well maybe cold coffee! (or Lutefisk?) ;) - 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 with java
Hi, Is there any examples on developing a java interface with mysql? Thanks, Denny - 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
Speed problem with sorted select statements
Description: I try to get the last 5 different times and the first 5 different times. There is a difference of aproximately 1 minute and 40 seconds between this to queries. I do not see reason for the difference in the execution times. I am the only one who works on the database. Nobody else has acces to it. The times shown in the examples does not vary. There are always the same. There are no other programs aktiv on my machine. It is not a problem of workload or network traffic. I have looked on the mailing list but I could not find something helpfull. There was no mysqlbug script in the mysql directories. So I tried to collect all the necessary information as described in the documentation. I tried to reproduce the behaviour with smaller tables but it was not possible. I know that I do not need the Time index but I tried it to see if the timing changes. It does not. There is no other reason for this index. -- MySQL dump 8.17 -- -- Host: localhostDatabase: snoopertest7 - -- Server version 4.0.0-alpha-nt -- -- Table structure for table 'eventlist' -- CREATE TABLE eventlist ( ID double default NULL, eventTime char(25) default NULL, sameTimeCounter char(3) default NULL, interface char(10) default NULL, telegramType char(30) default NULL, telegramID double default NULL, KEY TelegramTypeIndex (telegramType), KEY TelegramIDIndex (telegramID), KEY eventTimeIndex (eventTime,sameTimeCounter,ID), KEY Time (eventTime) ) TYPE=MyISAM; How-To-Repeat: mysql select distinct eventTime from eventList where eventTime '99' order by eventTime desc limit 0,5; +--+ | eventTime| +--+ | 000617153001 030 | | 000617153000 970 | | 000617153000 910 | | 000617153000 880 | | 000617153000 850 | +--+ 5 rows in set (1 min 39.89 sec) mysql explain select distinct eventTime from eventList where eventTime '99' order by eventTime desc limit 0,5; +---+---+-+--+-+ --+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+-+--+-+ --+-+--+ | eventList | range | eventTimeIndex,Time | Time | 26 | NULL | 3418451 | where used; Using index; Using temporary | +---+---+-+--+-+ --+-+--+ 1 row in set (0.00 sec) mysql select distinct eventTime from eventList where eventTime '0' order by eventTime asc limit 0,5; +--+ | eventTime| +--+ | 000617102959 970 | | 000617103000 030 | | 000617103000 100 | | 000617103000 160 | | 000617103000 220 | +--+ 5 rows in set (0.00 sec) mysql explain select distinct eventTime from eventList where eventTime '0' order by eventTime asc limit 0,5; +---+---+-+--+-+ --+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+---+-+--+-+ --+-+--+ | eventList | range | eventTimeIndex,Time | Time | 26 | NULL | 3418459 | where used; Using index; Using temporary; Using filesort | +---+---+-+--+-+ --+-+--+ 1 row in set (0.00 sec) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Speed problem with sorted select statements Submitter-Id: submitter ID Originator: Denny Fritzsche Organization: Alcatel Canada MySQL support: none Severity: serious Priority: high Category: mysqld Class:sw-bug Release:mysql-4.0.0-alpha-nt Exectutable: mysqld-nt Environment: Compaq Deskpro EN, x86 Family 6 Model 8 Stepping 3, 256 MB RAM, i815 Chip Set System:NT4 SP6 Compiler: Architecture: i - 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
Quick REGEXP problem
Hello, Can any one tell me why $sql=SELECT * FROM table WHERE pcsort REGEXP '^.$parts[0].[0-9]'; (pcscot being a CHAR(10), $parts[0] being a single alpha charcater.) works OK in 3.23 but not in 3.22. I'm trying to find UK postcodes such as e17 but not ee17. All the best, Lee - 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 in installing mysql
Hi, I am new to linux and i currently trying to install mysql on my redhat 7.1. I always got this error message at the end of the make Internal error: Segmentation fault.. I wonder if anyone would tell me what's going on? My machine is running on cyrix P133 with 32MB ram. Thanks, Denny - 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