MySQL and FreeBSD 5.x - Using LinuxThreads or not?
Hi all, We are about to upgrade one of our database servers to a Dual Xeon machine with HT. We have been testing FreeBSD 5.x for 6-7 months on a Uniprocessor box and we haven't had any problems with the platform. We would like to use FreeBSD 5 on our new database server since the SMP support is superior in the new release compared to the 4.x branch. I did some reading on the topic at Jeremy Zawodny's website: http://jeremy.zawodny.com/blog/archives/000697.html and he suggests that the best solution is to use LinuxThreads when compiling MySQL under FreeBSD. His initial article covered FreeBSD 4.x - http://jeremy.zawodny.com/blog/archives/000203.html Have any of you had any experience with stability/performance under FreeBSD 5.x and MySQL 4.0.x? Should the LinuxThread library still be used when compiling for maximum performance or has the problems been solved in Regards -- Lasse Laursen · VP, Hosting Technology · NetGroup A/S St. Kongensgade 40H · DK-1264 Copenhagen K, Denmark Phone: +45 3370 1526 · Fax: +45 3313 0066 - Don't be fooled by cheap finnish imitations - BSD is the One True Code -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using create table with show fields
I would like to do something like Create table xyz as show fields from test.abc; Or maybe Select Field from (show fields from test.abc); Is this possible ?, can I treat the execution of 'show fields' as a select statement Thanks, Neal Katz
transactions
Hello there. i'm working with transactions, and i have one situtation where i start a transaction and execute the insert/update statement, then i execute another start transaction for another db and execute the insert/update, and if this return a error i do the rollback, and then i do the rollback for the first transaction. example: start transaction insert/update start transaction (for another database) insert/update ... rollback or commit rollback or commit (this rollback isn't working) my problem is that the rollback for the first transaction isn't working, and i don't understand why! can you help me? thanks, André Brás -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selectinmg most recent dates from multiple table items
SELECT DISTINCT sensor_id, other_fields FROM table_name ORDER BY time_stamp_field DESC LIMIT 40 Not sure if that will work, but does it point you in the right direction? j- k- On Saturday 06 March 2004 07:22 pm, Tim McDonough wrote: On Sat, 06 Mar 2004 20:40:24 -0600, Paul DuBois wrote: You can use your LIMIT clause as well, as long as by that you don't mean 5 most recent from *each* table. This seems similar to something I'm working on which I haven't sorted out to my satisfaction yet. We have a system that collects and stores data that is time stamped in a mysql database. There is data from 40 sensors and it does not arrive at exactly the same time so each sensor reading and it's time stamp are stored. The table contains a date/time, the sensor ID, and the value. One request is to have a web page that will display each sensor and it's most recent measurement so the most recent is always shown. regardless of which sensor it came from. How do I create a query that will extract the most recent data (latest date) from the database for each of the sensors and have it sorted it by the sensor ID? This isn't obvious to me from reading the documentation on queries and I've been searching and reading through messages without success yet. I suspect a big part of the problem is I'm fairly new to sql queries and am not exactly sure what terms to search for. Tim -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VB and mySql
I used to program with VB using MS access as the db. VB was very quick to develope nice looking forms. Which language would be the best to use with mysql to create similiar forms. These forms would be for scrolling through the db, adding, updating, deleteing, printing... Walt
RE: VB and mySql
You shouldn't have any problems using VB to write software with MySQL. -Original Message- From: Walt [mailto:[EMAIL PROTECTED] Sent: Sunday, March 07, 2004 11:01 AM To: [EMAIL PROTECTED] Subject: VB and mySql I used to program with VB using MS access as the db. VB was very quick to develope nice looking forms. Which language would be the best to use with mysql to create similiar forms. These forms would be for scrolling through the db, adding, updating, deleteing, printing... Walt
AW: VB and mySql
Walt You can also use VB with MySQL without problems. There are several connectors available at the MySQL site Freddie -Ursprüngliche Nachricht- Von: Walt [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 7. März 2004 18:01 An: [EMAIL PROTECTED] Betreff: VB and mySql I used to program with VB using MS access as the db. VB was very quick to develope nice looking forms. Which language would be the best to use with mysql to create similiar forms. These forms would be for scrolling through the db, adding, updating, deleteing, printing... Walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VB and mySql
I am new to mysql and started a tutorial for it and php. Mysql is running ok on my redhat server. I thought VB might be to unstable. VB is easy to use but I think its not web based. Also all the job adds I see want mysql and php. thanks for responding Walt - Original Message - From: Freddie Sorensen [EMAIL PROTECTED] To: 'Walt' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, March 07, 2004 12:20 PM Subject: AW: VB and mySql Walt You can also use VB with MySQL without problems. There are several connectors available at the MySQL site Freddie -Ursprüngliche Nachricht- Von: Walt [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 7. März 2004 18:01 An: [EMAIL PROTECTED] Betreff: VB and mySql I used to program with VB using MS access as the db. VB was very quick to develope nice looking forms. Which language would be the best to use with mysql to create similiar forms. These forms would be for scrolling through the db, adding, updating, deleteing, printing... Walt
Hierarchical list...
Hi ! There is any way to have hierarchical list with Mysql, like the Oracle START WITH and CONNECT BY ? Something like : CREATE TABLE links_categories ( id int(11) NOT NULL auto_increment, parent int(11) default NULL, libelle varchar(50) default NULL, PRIMARY KEY (id) ) ; The Oracle syntax is : SELECT id, parent, libelle FROM links_categories CONNECT BY parent = PRIOR START WITH parent = 2 In fact, I need to select all the links categories witch are the son of 2, sons of the sons of 2, and so on... SELECT id, parent, libelle FROM links_categories WHERE (id = 2 or parent = 2) Works for the father and the son, but for the others... :( A + -- ** Richard CARLIER http://www.rcarlier.net (o_- Votre association et Internet (mai 2002, VMP, 2-7440-6013-5) //\- Profession Webmaster (nov. 2000, Les Echos, 2-84211-112-5) V_/_ - Cocktails, boissons (http://www.atontour.com) *~* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partial Replication ?
You can suppress writing the delete query to the binary log. mysqlset sql_log_bin = 0; mysqldelete ... ; mysqlset sql_log_bin=1; Bonnet R?my [EMAIL PROTECTED] wrote: Hello, I have a database which is flushed every four hours, and I want to replicate it without replicating the delete queries . Is this possible ? (sorry for my awful english) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Cluster on Windows
I'm trying to understand whether setting up a MySQL cluster running on Windows 2000/2003 is possible. I am not a cluster expert, actually I have only read the MS white papers about it and done some Google search, but still not tried to do it in practice, so forgive me if I make silly questions. So far, I have gathered the following information. Windows 2000/2003 supports clustering in two forms: Cluster service (for high availability, i.e. failover) and Load Balancing. Currently I am only interested in failover. I have read that a typical configuration would be two identical servers, with a shared RAID disk array. In an active-passive configuration, only one of the servers would accept client requests and access the shared data. The other would be idle. If the first server failed, the second one would become active. I have also read that in order to fully support failover, applications would need special resource DLLs, written according to MS standards, which would be used to exchange messages with the cluster service. However, the documentation mentioned a generic resource DLL, which could be used to manage cluster unaware applications in a cluster configuration. Has anyone here ever tried to use this generic resource DLL? Would it work also for services (e.g. MySQL)? In theory, the service on the secondary cluster node would have to be started/stopped according to failover, else it could lead to access conflicts to the shared data. I also have a server application which would need to be on the cluster. It does not need independent data storage (it uses MySQL). Would this application be correctly started/stopped by the generic resource DLL? On MySQL website, I saw that a new product, MySQL Cluster, is in the works, but there is not much information about it (apart from the fact that it will be covered in the MySQL Conference). Do anybody know whether it will be available for Windows? Has an approximate release date been defined? I have already read about Replication, but as far as I know it has some problems. First, it is not transparent to the clients: even if you have a master and a slave which could take the master's place in case of failure, its IP would not be the same. Second, I would have to write the script to switch the master in case of failure. Third, this would solve the high availability problem for the database, but not for my server application... Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hierarchical list...
check this thread - lots of links for more info. http://lists.mysql.com/mysql/157588 olinux --- Richard Carlier wrote: Hi ! There is any way to have hierarchical list with Mysql, like the Oracle START WITH and CONNECT BY ? __ Do you Yahoo!? Yahoo! Search - Find what youre looking for faster http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transactions
Andre, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, March 07, 2004 3:28 PM Subject: transactions Hello there. i'm working with transactions, and i have one situtation where i start a transaction and execute the insert/update statement, then i execute another start transaction for another db and execute the insert/update, and if this return a error i do the rollback, and then i do the rollback for the first transaction. example: start transaction insert/update start transaction (for another database) insert/update ... rollback or commit rollback or commit (this rollback isn't working) my problem is that the rollback for the first transaction isn't working, and= i don't understand why! can you help me? maybe you should use savepoints. Though, then if you rollback the outer transaction, it will also rollback the inner 'transaction'. If that is not ok, then you must use 2 different connections to MySQL. http://www.innodb.com/ibman.php#Savepoints thanks, Andr=E9 Br=E1s Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using create table with show fields
At 20:13 +0700 3/7/04, neal wrote: I would like to do something like Create table xyz as show fields from test.abc; Or maybe Select Field from (show fields from test.abc); Is this possible ?, can I treat the execution of 'show fields' as a select statement Thanks, Neal Katz Sounds like you want CREATE TABLE new_table LIKE old_table; This requires 4.1.1 or newer, however. The output of SHOW FIELDS cannot be treated as a SELECT statement, unless you write your own program to parse the output. If you were writing your own program, though, it'd be easier to use the output of SHOW CREATE TABLE, change the table name in the output, then execute the resulting statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Possible drawbacks of suggested mysql install?
Hi, we started with tha same configuration you have, and tune the MS Sql and MySQL for the memory the server (windows NT smallbusiness 4.0), and started to try our aplications against the two off them. All work really nice the two server with the same database and the same data and MySQL work faster that MS Sql. So, if you are doing it to get you people the feeling off working outside ms sql, that's a great start. Osvaldo Sommer -Original Message- From: Defryn, Guy [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 7:51 PM Cc: [EMAIL PROTECTED] Subject: Possible drawbacks of suggested mysql install? Hi there, I would like to know some professional opinions on the following: -Are there any drawbacks on installing Mysql on a windows server? Would it be possible to install it on the same server that hosts SQL server? Can they coexist properly? I do not expect too much usage of the Mysql server. We want to provide the MySQL service to our staff but might not be able to justify seperate servers. Are there any good articles available on this matter Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 3/3/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 3/3/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump of UTF8 db
Odd. When I dump my utf8 database it works fine. I use mysqldump -u user --password=password -a -A --add-drop-table -c public_html/backup.sql This backs up my entire database. Here is the limk to it in the manual http://www.mysql.com/doc/en/mysqldump.html I hope it helps. Respectfully, Ligaya Turmelle PHPCommunity.org: Open Source, Open Community For more information or to join the movement www.phpcommunity.org Theodosios Paschalidis [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi all, I was just testing if my utf8 table would restore properly. When I execute mysqldump -umysqladmin -pmysqladmin test C:\mysql\bin\test-bp.sql No tables are created. Instead I what get in my DOS console (WinXpPro) is the beginning and end of the dump file (having skipped all the restoration bits!) which is what follows I am new at this. Could anybody please offer any ideas on what goes wrong here? I need to resolve this in order backup my whole database. Thank you for your time, Theo -- MySQL dump 10.4 -- -- Host: localhostDatabase: test -- -- -- Server version 4.1.1a-alpha-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLI ENT=utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ibdata1
At 12:24 -0300 3/5/04, cytron wrote: I lost my ibdata1 file, I don't have backup, and now my tables InnoDB don't open. Well, that's a problem. If you've lost your data, and you have no backup, then ... you've lost your data. Possibly if your operating system allows some kind of undelete operation, you might get the file back. Otherwise, the situation looks grim. MySQL error: 1016 (Can't open the file: 'table.InnoDB') please, give me a solutions. Are 27 tables. please. Wilker Azevedo -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored procedure strange behavior?
Hi I have a few questions concerning stored procedures: 1. If I create a stored procedure (like the one below), why does the returned values not change, though in the stored prcoedure the id has been generated? 2. Is there any better way to hand over multiple values and how can I unset global varaibles? thx Philip delimiter | drop procedure if exists create_obj | CREATE PROCEDURE `create_obj` ( out success int(2), out success_msg varchar(255), out obj_id int(10), inout obj_hostname varchar(255), inout obj_type varchar(25) ) LANGUAGE SQL not deterministic begin declare done int default 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; insert into idsdb.obj values (NULL,obj_hostname,obj_type); if ! done then select LAST_INSERT_ID() into obj_id; set success = 1; set success_msg = concat(added host with object id: , obj_id); else set success = -1; set success_msg=Could not insert new object; end if; end | call create_obj(@a,@b,@id,'test1','ddd')| select @a,@b,@id | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Optimization
Hi, Does anybody know where I can find information about query optimization in MySQL? Of which the techniques that are used? Regards, Volnei Galbino
Re: Query Optimization
At 21:55 -0300 3/7/04, Volnei Galbino wrote: Hi, Does anybody know where I can find information about query optimization in MySQL? Of which the techniques that are used? Regards, Volnei Galbino Yes, there's a chapter on optimization in the MySQL Reference Manual. http://www.mysql.com/doc/en/MySQL_Optimisation.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT ... GROUP BY
Warning: SQL newbie... I'm trying to create a query where the most recent entry for each user is returned from a forum table, and sorted by username. The fields are simply: user_id, username, post, last_updated. No primary key defined. I've tried the simple SELECT * FROM posts GROUP BY user_id DESC ORDER BY username. It sorta returns what I'm after, but not the latest post from each user, (but the first post). Now my understanding of the GROUP BY function may not be correct, but according to the MySQL manual adding DESC after GROUP BY should return what I'm after but doesn't appear to do so. Any hints? PS. Table type is InnoDB, running on MySQL 4.0.16-max-nt. Darran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ... GROUP BY
You should change it to something like this: Select * from posts group by user_id order by username, timestamp DESC That should give you the last post. Donny -Original Message- From: Darran Kartaschew [mailto:[EMAIL PROTECTED] Sent: Sunday, March 07, 2004 9:42 PM To: [EMAIL PROTECTED] Subject: SELECT ... GROUP BY Warning: SQL newbie... I'm trying to create a query where the most recent entry for each user is returned from a forum table, and sorted by username. The fields are simply: user_id, username, post, last_updated. No primary key defined. I've tried the simple SELECT * FROM posts GROUP BY user_id DESC ORDER BY username. It sorta returns what I'm after, but not the latest post from each user, (but the first post). Now my understanding of the GROUP BY function may not be correct, but according to the MySQL manual adding DESC after GROUP BY should return what I'm after but doesn't appear to do so. Any hints? PS. Table type is InnoDB, running on MySQL 4.0.16-max-nt. Darran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT ... GROUP BY
Darran Kartaschew wrote: Warning: SQL newbie... I'm trying to create a query where the most recent entry for each user is returned from a forum table, and sorted by username. The fields are simply: user_id, username, post, last_updated. No primary key defined. I've tried the simple "SELECT * FROM posts GROUP BY user_id DESC ORDER BY username". It sorta returns what I'm after, but not the latest post from each user, (but the first post). Now my understanding of the GROUP BY function may not be correct, but according to the MySQL manual adding DESC after GROUP BY should return what I'm after but doesn't appear to do so. Any hints? PS. Table type is InnoDB, running on MySQL 4.0.16-max-nt. Darran You need to do this in 2 steps. Step 1: select user_id, max(last_updated) as max_last_updated from posts group by user_id You probably need to do this into a temporary table or something. Check the docs for more details, but you can do something like: create temporary table tmp_latest_post_by_user ( select ... ) to create temporary tables ( fill in the . ) Step 2: select * from posts inner join tmp_latest_post_by_user on posts.user_id=tmp_latest_post_by_user and posts.last_updated=tmp_latest_post_by_user.max_last_updated In this step you join the main table with the temporary table of latest posts by user, getting only the rows that match from both tables ( inner join ). Dan --- By the way, I had a look at your company's web site ( looks pretty nice ) and thought that since you're in the Natural Health business, you have a little more to loose than most Australians over the Free Trade Agreement. If you don't want the largest of the US drug manufacturers making things difficult for you ( copyright patent law ), I suggest opposing the FTA. See http://yro.slashdot.org/article.pl?sid=04/03/05/229228mode=thread for the latest Slashdot story on it... Sorry about the political rant, but this stuff is important and we don't have much time. -- signature Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ... GROUP BY
Excellent, that worked... Yours Sincerely Darran -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, 8 March 2004 14:03 To: Darran Kartaschew; [EMAIL PROTECTED] Subject: Re: SELECT ... GROUP BY You need to do this in 2 steps. Step 1: select user_id, max(last_updated) as max_last_updated from posts group by user_id You probably need to do this into a temporary table or something. Check the docs for more details, but you can do something like: create temporary table tmp_latest_post_by_user ( select ... ) to create temporary tables ( fill in the . ) Step 2: select * from posts inner join tmp_latest_post_by_user on posts.user_id=tmp_latest_post_by_user and posts.last_updated=tmp_latest_post_by_user.max_last_updated In this step you join the main table with the temporary table of latest posts by user, getting only the rows that match from both tables ( inner join ). Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT ... GROUP BY
Three solutions to this problem, including the one here, are discussed in the manual: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html Michael Darran Kartaschew wrote: Excellent, that worked... Yours Sincerely Darran -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Monday, 8 March 2004 14:03 To: Darran Kartaschew; [EMAIL PROTECTED] Subject: Re: SELECT ... GROUP BY You need to do this in 2 steps. Step 1: select user_id, max(last_updated) as max_last_updated from posts group by user_id You probably need to do this into a temporary table or something. Check the docs for more details, but you can do something like: create temporary table tmp_latest_post_by_user ( select ... ) to create temporary tables ( fill in the . ) Step 2: select * from posts inner join tmp_latest_post_by_user on posts.user_id=tmp_latest_post_by_user and posts.last_updated=tmp_latest_post_by_user.max_last_updated In this step you join the main table with the temporary table of latest posts by user, getting only the rows that match from both tables ( inner join ). Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query date ranges
Kevin Waterson wrote:
I have been trying this is several ways, currently I have a mess
MySQL 4.1.1
PHP as the interface
I have a table of with a date range called seasons.
in it I have two date ranges and an amount to be charged
for each day in the range
2004-01-01 00:00:00 2004-06-01 00:00:0044
2004-06-02 00:00:00 2004-10-31 00:00:00110
seasonDateFrom seasonDateTo seasonRateWeekly
2004-06-02 00:00:002004-10-31 00:00:00 42.86
2004-01-01 00:00:002004-06-01 00:00:00 34.29
When I take a booking I have yet another range
$bookingDateFrom and $BookingDateTo
I need to get the SUM(seasonRateWeekly) for each day in the booking range.
Currently , and here is the bad bit, I can get it to work if I calculate
the number of days in the booking range, then loop through them in php
with foreach and increment a counter
SELECT seasonRateWeekly FROM seasons WHERE DATE_ADD('{$newbookingDateFrom}', INTERVAL $i DAY)
BETWEEN seasonDateFrom AND seasonDateTo
This of course is almost useless as it takes 40 queries for 40 days. Not efficient at
all.
But I need the individual values, I think, to be able to query across season ranges
should
a booking range span two, or more, seasons.
As always, any help greatfully recieved
Kind regards
Kevin
Kevin,
I'm finding your description of the problem just a little confusing.
When you say that you have two date ranges in your rates table
(seasons), you mean that currently you have just the 2 rows quoted
(twice?) above, right? And even though the column is named
seasonRateWeekly, it contains daily rates? Also, you say the range
start and end are dates, but they appear to be datetimes? The following
suggestions are based on my best guess as to what you want:
First, I must say that if there are only two rates, making a table out
of them and trying to do this in SQL seems like overkill. Surely it
would be simpler to just code them in your application. But I'm
guessing that was just an example.
A slightly less easy but more flexible solution, as you're already
looping through all the days in the booking range, would be to read in
all the season start/end dates and rates (1 query) and do the
calculation in your application.
Assuming, however, that your season rates table is just an example, I
imagine you might have several seasons, and may wish to have the
flexibility to have different rates for certain days (weekends,
holidays, etc.). In that case, I would make a rates table with one row
for each day of the year. Something like
CREATE TABLE rates (day_of_year DATE, daily_rate DECIMAL(8,2) UNSIGNED)
Fill it with one row for each day of the year and set each day's rate
according to the seasonal rate (can be done with one UPDATE statement
per season), then set any special days/rates you want. Once you have
your daily rates set up, your query becomes simply
SELECT SUM(daily_rate) FROM rates
WHERE day_of_year BETWEEN $bookingDateFrom AND $BookingDateTo
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp and alter table
Hi,
I have a table that looks like:
mysql desc test;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| timestamp | timestamp(14) | YES | | NULL| |
| text | text | YES | | NULL| |
| text2 | varchar(255) | YES | | NULL| |
| choose| enum('y','n') | | | y | |
+---+---+--+-+-+---+
4 rows in set (0.00 sec)
I want to change the column choose to ENUM('y', 'n', 'weekly') without
changing the timestamp. How can I do that?
Thanks,
Batara
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weekly activity report
Response -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple table update
update gci_copy,dis set
gci_copy.products_description=concat_ws('br',gci_copy.products_description,dis.products_model,dis.products_description)
where gci_copy.products_image=dis.products_image
only able to caoncat one match
possiable work arounds.
_
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
