RE: Conditional Insert
Hi Douglas,
Thanks for your prompt reply. I read through the description of REPLACE and
tested it as well. It seems just fine, but the idea that it would DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
-Original Message-
From: Douglas Sims [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 29 August, 2006 10:48 AM
To: Ravi Kumar.
Cc: mysql@lists.mysql.com
Subject: Re: Conditional Insert
Hi Ravi
Standard SQL syntax doesn't provide for that. You could wrap the whole
thing in a transaction, possibly in a stored procedure, but this would be
rather awkward.
However, MySQL has a command called REPLACE which I think will do exactly
what you want:
http://dev.mysql.com/doc/refman/5.0/en/replace.html
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 12:08 AM, Ravi Kumar. wrote:
Dear Members,
I wish to insert and update conditionally into a table using a single
SQL statement. That is if a record already exists in a table, the
statement should update it, else it should insert it. Probably
something like
this:
if (select * from UserPoints where username = 'ravi') then (update
UserPoints set points = points + 5 where username = 'ravi') else
(insert into UserPoints (username, points) values ('ravi', 0)) end if
I can not do it pragmatically using PHP, because the environment is
highly concurrent and it may result in a 'race condition' often.
The version of MySQL we are using is 4.1.14-standard-log.
Please reply soon, it is urgent.
Have a nice day!
Thanks and regards,
Ravi.
--
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: Conditional Insert
Hi Ravi
You are correct. I was just sitting here thinking about this after I
sent that and realized that what I said was incorrect; the REPLACE
command will not do what you want, because it delete a record instead
of updating it, it has no way to know what the previous value is.
You probably should define a stored procedure and call that. Here is
an example which seems to work:
mysql show create table ravitest;
+--
+---
+
| Table| Create
Table
|
+--
+---
+
| ravitest | CREATE TABLE `ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--
+---
+
1 row in set (0.00 sec)
mysql show create procedure r1;
+---+--
+---
---+
| Procedure | sql_mode | Create
Procedure
|
+---+--
+---
---+
| r1| | CREATE PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points
+p where ravitest.username=n;
END IF;
END |
+---+--
+---
---+
1 row in set (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of
REPLACE and
tested it as well. It seems just fine, but the idea that it would
DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and
insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
-Original Message-
From: Douglas Sims [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 29 August, 2006 10:48 AM
To: Ravi Kumar.
Cc: mysql@lists.mysql.com
Subject: Re:
Re: Conditional Insert
Hi Ravi,
you can take a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
which might take care of your problem.
/Johan
Douglas Sims skrev:
Hi Ravi
You are correct. I was just sitting here thinking about this after I
sent that and realized that what I said was incorrect; the REPLACE
command will not do what you want, because it delete a record instead of
updating it, it has no way to know what the previous value is. You
probably should define a stored procedure and call that. Here is an
example which seems to work:
mysql show create table ravitest;
+--+---+
| Table| Create
Table
|
+--+---+
| ravitest | CREATE TABLE `ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--+---+
1 row in set (0.00 sec)
mysql show create procedure r1;
+---+--+--+
| Procedure | sql_mode | Create
Procedure
|
+---+--+--+
| r1| | CREATE PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points+p
where ravitest.username=n;
END IF;
END |
+---+--+--+
1 row in set (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of
REPLACE and
tested it as well. It seems just fine, but the idea that it would
DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and
insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
-Original Message-
From: Douglas Sims
Re: Conditional Insert
Much better. Good job.
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:26 AM, Johan Höök wrote:
Hi Ravi,
you can take a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
which might take care of your problem.
/Johan
Douglas Sims skrev:
Hi Ravi
You are correct. I was just sitting here thinking about this
after I sent that and realized that what I said was incorrect; the
REPLACE command will not do what you want, because it delete a
record instead of updating it, it has no way to know what the
previous value is. You probably should define a stored procedure
and call that. Here is an example which seems to work:
mysql show create table ravitest;
+--
+
-
--+ | Table| Create
Table
|
+--
+
-
--+ | ravitest | CREATE TABLE
`ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--
+
-
--+ 1 row in set (0.00 sec)
mysql show create procedure r1;
+---+--
+
-
-
-
---+ | Procedure |
sql_mode | Create
Procedure
|
+---+--
+
-
-
-
---+ | r1
| | CREATE PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points
+p where ravitest.username=n;
END IF;
END |
+---+--
+
-
-
-
---+ 1 row in set
(0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of
REPLACE and
tested it as well. It seems just fine, but the idea that it would
DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete
and
Re: Conditional Insert
Douglas Sims wrote: Hi Ravi You are correct. I was just sitting here thinking about this after I sent that and realized that what I said was incorrect; the REPLACE command will not do what you want, because it delete a record instead of updating it, it has no way to know what the previous value is. You probably should define a stored procedure and call that. Here is an example which seems to work: Why not use this INSERT INTO table VALUES(..) ON DUPLICATE KEY UPDATE X = $X, y=$y .. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Conditional Insert
Dear All,
Thanks for the prompt replies. The version of MySQL we are using
(4.1.14-standard-log) supports INSERT ON DUPLICATE, hence I am using this.
Have a fantastic day!
Regards,
Ravi.
_
From: Douglas Sims [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 29 August, 2006 12:02 PM
To: Johan Höök
Cc: Ravi Kumar.; mysql@lists.mysql.com
Subject: Re: Conditional Insert
Much better. Good job.
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:26 AM, Johan Höök wrote:
Hi Ravi,
you can take a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
which might take care of your problem.
/Johan
Douglas Sims skrev:
Hi Ravi
You are correct. I was just sitting here thinking about this after I sent
that and realized that what I said was incorrect; the REPLACE command will
not do what you want, because it delete a record instead of updating it, it
has no way to know what the previous value is. You probably should define a
stored procedure and call that. Here is an example which seems to work:
mysql show create table ravitest;
+--+
---+ | Table| Create Table
|
+--+
---+ | ravitest | CREATE TABLE `ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--+
---+ 1 row in set (0.00 sec)
mysql show create procedure r1;
+---+--+
--+ | Procedure | sql_mode | Create
Procedure
|
+---+--+
--+ | r1| | CREATE
PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points+p where
ravitest.username=n;
END IF;
END |
+---+--+
--+ 1 row in set (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of REPLACE and
tested it as well. It seems just fine, but the idea that it would DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
-Original Message-
From: Douglas Sims [mailto:[EMAIL
replication breaks
Hi, I recently upgrade our master to MySQL 5.0.24. But, I changed on option to log_bin=server-log-bin, as a result the binary log has changed from server-bin.000228 to server-log-bin.01... So now, the slave throw me an could not find first log file in binary log index. I suppose I have to do a CHANGE MASTER TO ... MASTER_LOG_FILE='server-log-bin.01', MASTER_LOG_POS=? What position should I use 0 ? 1 ? 4? Thanks for your help, -- http://www.myspace.com/sakuradrop : forget the rest http://www.w-fenec.org/ Webzine rock/metal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1
Logan, David (SST - Adelaide) wrote: Hi Folks, Does anybody have any idea when 5.1 may come to General Release? I am particularly interested in MySQL Cluster as I have several databases (around 50) totalling 26Gb and would like to consider moving to this version because of the Cluster Disk Data Storage and the fact the current hardware doesn't have the RAM requirements to hold these databases. Robin from MySQL has said Q1 2007. Kai and Max have both mentioned Q4 2006. I plan to store the data on a SAN and was wondering if anybody had any experience with this as yet? I have current thoughts along the lines of 2 x Pentium 4 1Ghz, 1Gb RAM as management nodes 4 x Pentium 4 1Ghz, 1Gb RAM as SQL nodes 2 x Pentium 4 1Ghz (x 8 cpus), 8Gb RAM as data nodes You will have two copies of the data on those two data nodes. Kai demonstrated MySQL Cluster on 5.1.11 running four data nodes on his laptop, which basically allows one to have two copies of data mirrored over two nodes (see quickly hacked picture). At least with having more data nodes one had two copies of data you reduce the single point of failure. Having more data nodes allows you to split up data across nodes, etc. Regards --jm The databases are currently running, replicating and serving around 800 queries per second utilising a 100M network. Thanks --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doing sum's if certain conditions are true
Hi,
I am trying to build a query that does a sum if a certain parameter is set.
For example:
I have a row with four fields: code1, code2, duration and bytes. My current
query looks something like this: SELECT code1 as code, sum(bytes) as bin,
max(bytes) as min, avg(bytes) as ain, count(bytes) as cin, sum(duration) as
dur from data group by code; which returns something like this:
+---+--+---+-+--+-+
| code | bin | min | ain |
cin | dur |
+---+--+---+-+--+-+
| NONE | 103939170759 | 485089817 | 3739.1827 |
27797297 | 11681839027 |
Now, what i need todo is exclude certain info from the above NONE entry if
code2 is equal to something. So for example (in php terminology):
if(code == 'NONE') {
if(code2 == 'DENIED') { continue; }
else {
bin += bytes;
if(bytes min) { min = bytes; }
cin++;
dur += dur;
}
}
after that i could work out the average by dividing bin / cin for what in
the report is called ain. Is there any way of achieving this via the sql
query because the above is a hugely tedious way of doing it. I know mysql
has an if() statement, but I have no idea how to implement it using what i
want to achieve above.
Thanks in advance.
Ian
RE: Doing sum's if certain conditions are true
[snip]
+---+--+---+-+--
+-+
| code | bin | min | ain |
cin | dur |
+---+--+---+-+--
+-+
| NONE | 103939170759 | 485089817 | 3739.1827 |
27797297 | 11681839027 |
Now, what i need todo is exclude certain info from the above NONE entry
if
code2 is equal to something. So for example (in php terminology):
if(code == 'NONE') {
if(code2 == 'DENIED') { continue; }
else {
bin += bytes;
if(bytes min) { min = bytes; }
cin++;
dur += dur;
}
}
after that i could work out the average by dividing bin / cin for what
in
the report is called ain. Is there any way of achieving this via the sql
query because the above is a hugely tedious way of doing it. I know
mysql
has an if() statement, but I have no idea how to implement it using what
i
want to achieve above.
[/snip]
Basically;
SELECT SUM(IF(`code` = 'NONE', calculation, 0)) AS `ain` FROM `table`
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to deal with summary data and reports
I wrote a fairly large query that summarizes our sales into monthly totals by business unit, then left joins on some other information from a bunch of other tables. Running the query takes a long time. I'm considering whether to store my summary data as a table, like this: create table monthly_totals as ( /* enormous SQL goes here */ ) If I do that, then I must keep the summary data synchronized with the underlying data, as the underlying data gets updated once a day, every day. Can a stored procedure delete and then rebuild the summary table? If so, then I may write stored procedures that would build these summary reports and when we load new data, we rerun the stored procedures to build the summaries. I suspect this is a universal problem. All general advice would be appreciated. Perhaps a stored procedure is not the way to go. -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to deal with summary data and reports
Matthew Wilson writes: [...] I suspect this is a universal problem. All general advice would be appreciated. Perhaps a stored procedure is not the way to go. Mysql 5.1 has CREATE EVENT, that somewhat mimics Oracle's jobs. I prefer to run a shell script in a cron job, like this: #!/bin/bash mysql -NB -h... -u... -p... EOSQL drop table if exists foo_new; create table foo_new as select...; drop table if exists foo; alter table foo_new rename to foo; EOSQL If it fails, then at least there will be something to work with. Not having the latest data is sometimes better than not having any data at all ;) -- To sto si frustriran, zavidan tko zna na cemu i sto ne vidis dalje od svoje guzice je tuzno. Da onda barem imas toliko samokontrole da sutis umjesto da pravis budalu od sebe... izgleda da si prestar da se promjenis na bolje. - Davor Pasaric, hr.comp.mac -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone tried solidDB for MySQL?
http://www.solidtech.com/en/carrier-grade/index.asp http://dev.soliddb.com/en/index.php On 29/08/06, Ratheesh K J [EMAIL PROTECTED] wrote: What is this solidDB? where can i get much info on this?? Thanks, Ratheesh Bhat K J
Performance: LIMIT 1 with UPDATE
Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance: LIMIT 1 with UPDATE
FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- 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]
table sizes, rolling data.
table sizes, rolling data.
sorry for the double post. if i want to have a row of about 100 records. and everytime i insert a new record, it gets pushed on the top, and the bottom one gets pushed out, sort of like a heap. is this possible? i know i can just delete the record, etc, but i was wondering if there was a built in way of doing it. T
data time wrong format between mysql 5.01 and visual.net
hello i'm developing a program on visual.net 2003 where i need to extract data from a date time format column working with mysql.net component for extract via mysqlcommands and dataadapters im fillin datagrills but the column displayed only presents the date but not the time im still analizing data format presentation on viusual.net elements but it seems something is missing any comments? tanks a lot
Re: [PHP] switch()
[EMAIL PROTECTED] wrote:
I have something like this:
?php
$query = mysql_query(
SELECT col_1, col_2
FROM table
);
$result = mysql_fetch_array($query);
if ($result['col_1'] == 'value_1')
{
// do something
}
if ($result['col_2'] == 'value_2')
{
// do something
}
?
when I tried to use swiitch()
?php
switch (true)
{
case ($result['col_1'] == 'value_1'):
// do something
break;
case ($result['col_2'] == 'value_2'):
// do something
break;
}
?
it will give me only the first true case. if $result['col_1'] == 'value_1'
is true 2nd case will never be executed?
Does it mean I CANNOT use switch() in this case?
-afan
Why do you want to use a switch in this particular instance. You're
comparing apples and oranges (in this case, col_1 and col_2). You use
swithc to evaluate one variable against a number of choice, not multiple
variables against variable choices.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance: LIMIT 1 with UPDATE
Maybe it is the tiny extra time to parse the unnecessary LIMIT 1? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 10:09 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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: Performance: LIMIT 1 with UPDATE
Yeah, seems like for a uniquely index column expression that the LIMIT 1 provides no benefit (which seems like a logical conclusion). The performance on a large data set with both approaches is close enough to call equivalent. -Original Message- From: Rick James [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 11:51 AM To: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE Maybe it is the tiny extra time to parse the unnecessary LIMIT 1? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 10:09 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals 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: [PHP] switch()
Why do you want to use a switch in this particular instance. You're comparing apples and oranges (in this case, col_1 and col_2). You use swithc to evaluate one variable against a number of choice, not multiple variables against variable choices. I'm not comparing apples and oranges - just have such a case, but looks like I'm wrong about using switch() on wrong place. Just thought it could do it. :) thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data time wrong format between mysql 5.01 and visual.net
You tried the date_format function of mysql? Roberto William Aranda-W Roman [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] hello i'm developing a program on visual.net 2003 where i need to extract data from a date time format column working with mysql.net component for extract via mysqlcommands and dataadapters im fillin datagrills but the column displayed only presents the date but not the time im still analizing data format presentation on viusual.net elements but it seems something is missing any comments? tanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] switch()
[EMAIL PROTECTED] wrote: Why do you want to use a switch in this particular instance. You're comparing apples and oranges (in this case, col_1 and col_2). You use swithc to evaluate one variable against a number of choice, not multiple variables against variable choices. I'm not comparing apples and oranges - just have such a case, but looks like I'm wrong about using switch() on wrong place. Just thought it could do it. :) What you're saying in that case is either col_1 evaluates to value1 or col_2 evaluates to value2. Unless you have some program logic to prevent both from being true, you are comparing apples and oranges. And any time col_1, evaluates to true, the break keyword will stop your switch ladder. Remove break, and that won't happen. of course, if you do that, then the question arises about why you are using a switch ladder in the first place. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing sum's if certain conditions are true
Hello Ian barnes
I hope this query will resolve your problem if my understanding is correct
SELECT code1,
SUM(bytes) as sumofbytes,
MAX(bytes) as maximum,
MIN(bytes) as minimum,
COUNT(bytes) as bytecount,
SUM(duration) as duration,
AVG(bytes) as averagebyte
FROM mytable where code1'none' and code2'denied' group by code1
Thanks
Visolve Db team
- Original Message -
From: Ian Barnes [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, August 29, 2006 3:26 PM
Subject: Doing sum's if certain conditions are true
Hi,
I am trying to build a query that does a sum if a certain parameter is
set.
For example:
I have a row with four fields: code1, code2, duration and bytes. My
current
query looks something like this: SELECT code1 as code, sum(bytes) as bin,
max(bytes) as min, avg(bytes) as ain, count(bytes) as cin, sum(duration)
as
dur from data group by code; which returns something like this:
+---+--+---+-+--+-+
| code | bin | min | ain |
cin | dur |
+---+--+---+-+--+-+
| NONE | 103939170759 | 485089817 | 3739.1827 |
27797297 | 11681839027 |
Now, what i need todo is exclude certain info from the above NONE entry if
code2 is equal to something. So for example (in php terminology):
if(code == 'NONE') {
if(code2 == 'DENIED') { continue; }
else {
bin += bytes;
if(bytes min) { min = bytes; }
cin++;
dur += dur;
}
}
after that i could work out the average by dividing bin / cin for what in
the report is called ain. Is there any way of achieving this via the sql
query because the above is a hugely tedious way of doing it. I know mysql
has an if() statement, but I have no idea how to implement it using what i
want to achieve above.
Thanks in advance.
Ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Zip Code Distance
This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Don't use the zip code itself. It might work for some areas, but certainly doesn't work in the western washington. It's just cut uptoo weird. We bought a zip code database from um... www.zipcodedownload.com which has a big list of cities, zips, and lat/long coordinates. Once you have the lat/long you can do the math easily... Not sure if they have a zip code only version... but the one we bought was like $30 and works just fine... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
On 8/29/06, Jesse [EMAIL PROTECTED] wrote: This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? http://www.cryptnet.net/fsp/zipdy/ -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
Jesse wrote: This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse You need to get a table that gives you the latitude and longitude of each zip code. The location is of course some where near the center of the zip code so these calculations won't be perfect. Once you have the latitude and longitude it just takes some math to figure out the distance. Not sure where you get the data but someone here probably knows. You can find details on the calculations here... http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
On Tue, 2006-08-29 at 16:30 -0400, Jesse wrote: Does anyone have any ideas? One technique is to calculate set distances (5,10,25,50) between the zip codes in advance and stick the results in a table. Enjoy, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Zip Code Distance
That (using the zip code digits) won't work. Zip codes are assigned by population density, not by distance. A big city might have several zip codes, although they'd all likely have the same first three digits, but check Manhattan for an example. In our area, the core city (Hartford) and some of its suburbs have the same three digits (061), even though they have their own post offices; but there is a ring of suburbs that have a different prefix (060). It would be wrong to assume that all of the 060 suburbs are closer to each other than they are to the central city. I imagine that some of the farm-land areas cover a lot of territory with very few zip codes. I don't know how the mapping software does it, really. They must have big tables of some sort. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 4:31 PM To: MySQL List Subject: Zip Code Distance This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- 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: Zip Code Distance
Jesse, Back around 1995-1996 I built a VB app with an Access database that would do much of what you want - it was for shipping and terminal locations - then ported it over to ASP SQL Server a year or two later. I found several raw data files, either CSV or TSV, which were pretty easily imported into most any database. I believe I found the data files through the Census Bureau web site, http://www.census.gov. Took some searching to find, but from the files I finally found, the database listed ZIP code; Lat/Long of the rough center (centroid?) of the ZIP area: city state; population as of 1990 census, and a few other then-relevant columns that I cannot now recall. Dunno if that data is still available, but that's the first place I'd start looking. A warning, though - the terminology on the site doesn't always - or often - reflect what you find after you get where it pointed grin /. This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- 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]
SOS
Dear Sir: I have some problems with mysql 5.0 binary source in Linux(RedHat).I'm able to startup the mysql process. But when i type in :mysql -u root -h localhost -p, and then put the correct password, I can not enter the database. I took almost one week to deal with this issue. But Can you help me? May be the mysql version problem? 致 礼！ 李彦 [EMAIL PROTECTED] 2006-08-29
Re: select between date
On 8/3/06, Penduga Arus [EMAIL PROTECTED] wrote: On 8/1/06, Chris [EMAIL PROTECTED] wrote: Did you look at the link David sent you? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html thanks.. I manage to do that.. below is my solution. please advice if there is any better solution SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as a017tkhlahir, MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh FROM a017 MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY)) AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY)) ORDER BY bulan, tarikh I have notice problem with my sql statment above, when it run on the 25th for the month which have 31 days the statment isnt valid anymore. FYI my a017tkhlahir is in date format (-mm-dd) Please advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SOS
李彦 wrote: Dear Sir: I have some problems with mysql 5.0 binary source in Linux(RedHat).I'm able to startup the mysql process. But when i type in :mysql -u root -h localhost -p, and then put the correct password, I can not enter the database. I took almost one week to deal with this issue. But Can you help me? May be the mysql version problem? And the error message you get is. ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select between date
You have a table containing birthdates (date field, including year)
and you want to display all rows for which the birthday will occur in
the next week (seven days).
You tried this query:
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
but found a problem - that sometimes birthdates on or after the 25th
in months with 31 days will not show.
This could happen in December, after December 25, because then the
month field will be 12 but the month field for DATEADD(CURDATE(),
INTERVAL 7 DAY) will be 1 and so nothing will match the clause
BETWEEN 12 and 1
I don't immediately see that this would be a problem in other
months. For example:
mysql select 'fish' from t1 where 3 between 12 and 1;
Empty set (0.00 sec)
mysql select 'fish' from t1 where 3 between 1 and 12;
+--+
| fish |
+--+
| fish |
+--+
1 row in set (0.03 sec)
One solution is to create a new date from the birthday in the table
by taking the year from the current date and the month and day from
the birthdate and then checking to see if that date is in the next
seven days, that is, between CURDATE() and DATEADD(CURDATE(),
INTERVAL 7 DAY). Here is an example of how that might work:
mysql show create table birthdays;
+---
+---
---+
| Table | Create
Table
|
+---
+---
---+
| birthdays | CREATE TABLE `birthdays` (
`name` varchar(32) default NULL,
`birthdate` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---
+---
---+
1 row in set (0.00 sec)
mysql select * from birthdays;
+++
| name | birthdate |
+++
| P.G. Wodehouse | 1881-10-15 |
| John Marquand | 1893-11-10 |
| Ian Flemming | 1908-05-28 |
| John Grisham | 1955-02-08 |
| Jeffrey Archer | 1940-04-15 |
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+++
7 rows in set (0.00 sec)
mysql SELECT * FROM birthdays WHERE STR_TO_DATE(CONCAT_WS('-', YEAR
(CURDATE()), MONTH(birthdate), DAY(birthdate)), '%Y-%m-%d') BETWEEN
CURDATE() AND ADDDATE(CURDATE(), INTERVAL 7 DAY);
+++
| name | birthdate |
+++
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+++
2 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 10:13 PM, Penduga Arus wrote:
On 8/3/06, Penduga Arus [EMAIL PROTECTED] wrote:
On 8/1/06, Chris [EMAIL PROTECTED] wrote:
Did you look at the link David sent you?
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
thanks.. I manage to do that.. below is my solution. please advice if
there is any better solution
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
I have notice problem with my sql statment above, when it run on the
25th for the month which have 31 days the statment isnt valid anymore.
FYI my a017tkhlahir is in date format (-mm-dd)
Please advice.
--
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]
