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
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
